英文:
Compute the date range count for every hour using a list of dates
问题
- 我想要从日期列表中获得Map<String hours, count >。
- 我还要按日期分组，每小时计数。
例如，在数据库中的值如下：
2023-07-18 10:10:00.000
2023-07-18 10:20:00.000
2023-07-18 11:10:00.000
2023-07-18 11:30:00.000
2023-07-18 11:35:00.000
2023-07-18 12:10:00.000
2023-07-18 12:30:00.000
我的预期结果应该如下所示，应该计算每个1小时范围的计数：
Map结果
<10-11 ,2>
<11-12 ,3>
<12-13 ,2>
我尝试了论坛提供的许多示例，但都无法正常工作。
英文:
- I want the Map<String hours, count > from the list of dates
- I also group by date and count for every 1 hour
Ex In DB Value looks like this
2023-07-18 10:10:00.000
2023-07-18 10:20:00.000
2023-07-18 11:10:00.000
2023-07-18 11:30:00.000
2023-07-18 11:35:00.000
2023-07-18 12:10:00.000
2023-07-18 12:30:00.000
My expected result should be as follow as should calculate each 1 hour range count
Map result
<10-11 ,2>
<11-12 ,3>
<12-13 ,2>
I tried lot of examples provide by the forum but does't work.
答案1
得分: 1
如果您期望在输出中看到<hour-range, count>，以下查询可能会解决您的问题 -
SELECT group_concat(distinct CONCAT(HOUR(date_time), ' - ', HOUR(date_time) + 1)) AS hour_range, COUNT(date_time) AS count
FROM your_table
GROUP BY HOUR(date_time)
ORDER BY HOUR(date_time);
英文:
In case you are expecting <hour-range, count> in your output, following query might solve your problem -
SELECT group_concat(distinct CONCAT(HOUR(date_time), '-', HOUR(date_time) + 1)) AS hour_range, COUNT(date_time) AS count
FROM your_table
GROUP BY HOUR(date_time)
ORDER BY HOUR(date_time);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论