如何按连续特定时间戳分组合并行并汇总价格？

输入表 #

期望输出 #

核心思路 #

要实现前一日19:00与次日7:00的记录分组，关键是给这两条记录生成相同的分组日期键：

• 19:00的记录直接用当天日期作为分组键
• 7:00的记录用「当天日期减1天」作为分组键

这样两组记录会被归为同一组，再按name和分组键求和即可。

具体SQL实现 #

PostgreSQL版本 #

SELECT
    name,
    group_date::DATE AS time,
    SUM(price) AS price
FROM (
    SELECT
        name,
        price,
        CASE
            WHEN EXTRACT(HOUR FROM time) = 19 THEN DATE(time)
            WHEN EXTRACT(HOUR FROM time) = 7 THEN DATE(time) - INTERVAL '1 day'
        END AS group_date
    FROM your_table
) AS grouped_records
GROUP BY name, group_date
ORDER BY group_date;

MySQL版本 #

SELECT
    name,
    group_date AS time,
    SUM(price) AS price
FROM (
    SELECT
        name,
        price,
        CASE
            WHEN HOUR(time) = 19 THEN DATE(time)
            WHEN HOUR(time) = 7 THEN DATE_SUB(DATE(time), INTERVAL 1 DAY)
        END AS group_date
    FROM your_table
) AS grouped_records
GROUP BY name, group_date
ORDER BY group_date;

代码解释 #

1. 内层子查询：通过CASE语句根据时间的小时数生成统一的分组日期，确保19:00和次日7:00的记录共享同一个分组键
2. 外层查询：按name和分组日期分组，对price求和，得到每组的总价格
3. 最后按分组日期排序，保证结果顺序符合预期

内容的提问来源于stack exchange，提问作者Pranav Arora