MySql Group by hour [已关闭]

ogq8wdun 于 2023-10-15 发布在 Mysql

关注(0)|答案(2)|浏览(95)

已关闭，此问题需要details or clarity。它目前不接受回答。
**想改善这个问题吗？**通过editing this post添加详细信息并澄清问题。

2天前关闭。
Improve this question
我需要从日期时间条目列表中只选择完整的小时值。但是如果我选择了更多的列，按小时分组就不起作用了。因此，如果我不选择列“日期时间”的工作组。

SELECT 
datetime, 
DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%H')
FROM imagedata 
WHERE DIRECTORY = 17082901 
and `datetime` >= '2017_0721_000000' 
and `datetime` <= '2017_0921_131500'
GROUP BY datetime, DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%H')

示例：https://www.db-fiddle.com/f/iaNrxC35uFYhDNZSeXa76A/1
示例数据：

directory   datetime            year    mo  day time
17082901    2017_0829_200000    2017    8   29  200000
17082901    2017_0829_200700    2017    8   29  200700
17082901    2017_0829_201500    2017    8   29  201500
17082901    2017_0829_203000    2017    8   29  203000
17082901    2017_0829_204500    2017    8   29  204500
17082901    2017_0829_205100    2017    8   29  205100
17082901    2017_0829_210000    2017    8   29  210000
...

我需要一个这样的结果：

29.08.17 20:00
29.08.17 21:00
...

mysql

来源：https://stackoverflow.com/questions/77279168/mysql-group-by-hour

2条答案

按热度按时间

dxxyhpgq1#

试试这个：

SELECT 
  DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%d.%m.%y %H:00') AS formatted_datetime
FROM imagedata 
WHERE DIRECTORY = 17082901 
  AND `datetime` >= '2017_0721_000000' 
  AND `datetime` <= '2017_0921_131500'
GROUP BY formatted_datetime
ORDER BY formatted_datetime;

如果要选择所有字段：

WITH HourlyGroups AS (
  SELECT 
    DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%y.%m.%d %H:00') AS formatted_datetime,
    DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%H') AS hour
  FROM imagedata 
  WHERE DIRECTORY = 17082901 
    AND `datetime` >= '2017_0721_000000' 
    AND `datetime` <= '2017_0921_131500'
  GROUP BY formatted_datetime, hour
)
SELECT HourlyGroups.formatted_datetime, imagedata.*
FROM imagedata
JOIN HourlyGroups ON DATE_FORMAT(STR_TO_DATE(imagedata.datetime, " %Y_%m%d_%H%i%s"), '%H') = HourlyGroups.hour
ORDER BY HourlyGroups.formatted_datetime;

赞(0）回复(0）举报 2023-10-15

23c0lvtd2#

这里有一个很好的解决方案，我认为：

WITH cte AS (
SELECT id, DATETIME, DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%Y-%m-%d %H') AS xxx,
        RANK() OVER ( PARTITION BY DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%Y-%m-%d %H')
        ORDER BY time ASC
        ) AS r
FROM imagedata
WHERE
DIRECTORY = 17082901 
and `datetime` >= '2017_0721_000000' 
and `datetime` <= '2017_0921_131500'
)
SELECT id, DATETIME, DATE_FORMAT(STR_TO_DATE(datetime, " %Y_%m%d_%H%i%s"), '%Y-%m-%d %H') AS xxx
FROM cte
WHERE r = 1;

赞(0）回复(0）举报 2023-10-15

我来回答

MySql Group by hour [已关闭]

2条答案

相关问题

热门标签

最新问答