时间戳

首页 > 解决方案 > 如何按日期小时进行 SQL 分组

mysql - 如何按日期小时进行 SQL 分组

问题描述

嗨，我有一张下表。

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。如果您能自行回复，谢谢

标签： mysqlsqldatedatetime

解决方案

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

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小提琴

推荐阅读