如何SQL按日期小时分组

Question

你好，我有一个下面的表格。

ID  Created Date                Deposit Money
18  2021-01-14 17:19:10.932030  16862895
17  2021-01-14 13:18:13.944762  625000
16  2021-01-14 12:19:55.565888  616000
15  2021-01-14 11:19:10.932030  16862895
14  2021-01-14 10:18:13.944762  625000
13  2021-01-14 09:19:55.565888  616000
12  2021-01-14 09:01:10.932030  16862895
11  2021-01-14 08:02:13.944000  625000
10  2021-01-14 08:01:55.565888  616000
9   2021-01-14 07:19:10.932030  16862895
8   2021-01-14 07:19:10.932030  16862895
7   2021-01-14 07:18:13.944762  625000
6   2021-01-14 07:18:13.944762  625000
5   2021-01-14 06:19:55.565888  616000
4   2021-01-14 06:19:55.565888  616000
3   2021-01-13 21:32:46.538236  19245
2   2021-01-13 19:33:30.693695  25560
1   2021-01-13 18:13:30.693695  588820

我要结果。

2021-01-13 16:00 ~ 2021-01-14 09:59  sum(deposit_money)
2021-01-14 10:00 ~ 2021-01-14 12:59  sum(deposit_money)
2021-01-14 13:00 ~ 2021-01-14 15:59  sum(deposit_money)
2021-01-14 16:00 ~ 2021-01-15 09:59  sum(deposit_money)

…

yesterday 16:00 ~ today 10:00
today 10:00 ~ today 13:00
today 13:00 ~ today 16:00
today 16:00 ~ tommorrow 10:00 

我不知道如何设置Group by。如果你能自己回复，谢谢你

Answer 1

您可以使用所需时间的时间表构建子查询，如下例所示:

SELECT p.start_from, p.end_to, SUM(t.deposit) AS total 
FROM deposits t
JOIN (
    SELECT
        TIMESTAMPADD(HOUR, 16, DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AS start_from,
        TIMESTAMPADD(SECOND, 10 * 60 * 60 - 1, CURDATE()) AS end_to
    UNION ALL SELECT 
        TIMESTAMPADD(HOUR, 10, CURDATE()),
        TIMESTAMPADD(SECOND, 13 * 60 * 60 - 1, CURDATE())
    UNION ALL SELECT 
        TIMESTAMPADD(HOUR, 13, CURDATE()),
        TIMESTAMPADD(SECOND, 16 * 60 * 60 - 1, CURDATE())
    UNION ALL SELECT 
        TIMESTAMPADD(HOUR, 16, CURDATE()),
        TIMESTAMPADD(SECOND, 10 * 60 * 60 - 1, DATE_ADD(CURDATE(), INTERVAL 1 DAY))
) p ON created BETWEEN start_from AND end_to
GROUP BY p.start_from, p.end_to

db-fiddle

Answer 2

我假设您真的希望对所有日期都这样，而不仅仅是当前日期。

您可以使用case表达式分配周期的开始:

select t.*,
       (case when time(created_date) < '10:00:00'
             then (date(created_date) - interval 1 day) + interval 16 hour
             when  time(created_date) < '13:00:00'
             then date(created_date) + interval 10 hour
             when time(created_date) < '16:00:00'
             then date(created_date) + interval 13 hour
             else date(created_date) + interval 16 hour
        end)
from t;

您可以轻松地将其合并到聚合中:

select (case when time(created_date) < '10:00:00'
             then (date(created_date) - interval 1 day) + interval 16 hour
             when  time(created_date) < '13:00:00'
             then date(created_date) + interval 10 hour
             when time(created_date) < '16:00:00'
             then date(created_date) + interval 13 hour
             else date(created_date) + interval 16 hour
        end) as period,
       count(*)
from t
group by period
order by min(created_date);

注意:这只处理周期开始。这似乎对你想要的结果已经足够了。

如果您只希望在一段时间内这样做，您可以包含一个where子句，根据您想要的时间段筛选行。