BigQuery中基于时间间隔分组行并求和的SQL实现问询

问题背景 #

现有存储多用户不同日期分钟级记录的表，time列为分钟级时间，已创建time_3min列用于标识3分钟时间间隔。需基于time_3min列分组，对value列求和，并按date和time_3min排序，得到指定格式的结果。

原始表数据 #

userId  date            time            time_3min   value
abc     2023-04-10      01:00:00        01:00:00    2
abc     2023-04-10      01:01:00        01:00:00    5
abc     2023-04-10      01:02:00        01:00:00    3
abc     2023-04-10      01:03:00        01:03:00    6
abc     2023-04-10      01:04:00        01:03:00    7
abc     2023-04-10      01:05:00        01:03:00    1
abc     2023-04-11      01:00:00        01:00:00    10
abc     2023-04-11      01:01:00        01:00:00    5
abc     2023-04-11      01:02:00        01:00:00    3
abc     2023-04-11      01:03:00        01:03:00    7
abc     2023-04-11      01:04:00        01:03:00    4
abc     2023-04-11      01:05:00        01:03:00    3
xyz     2023-04-10      01:00:00        01:00:00    11
xyz     2023-04-10      01:01:00        01:00:00    8
xyz     2023-04-10      01:02:00        01:00:00    6
xyz     2023-04-10      01:03:00        01:03:00    4
xyz     2023-04-10      01:04:00        01:03:00    6
xyz     2023-04-10      01:05:00        01:03:00    2
xyz     2023-04-11      01:00:00        01:00:00    11
xyz     2023-04-11      01:01:00        01:00:00    8
xyz     2023-04-11      01:02:00        01:00:00    7
xyz     2023-04-11      01:03:00        01:03:00    4
xyz     2023-04-11      01:04:00        01:03:00    6
xyz     2023-04-11      01:05:00        01:03:00    6

期望输出结果 #

userId  date            time_3min   sum(value)
abc     2023-04-10      01:00:00    10
abc     2023-04-10      01:03:00    14
abc     2023-04-11      01:00:00    18
abc     2023-04-11      01:03:00    14
xyz     2023-04-10      01:00:00    25
xyz     2023-04-10      01:03:00    12
xyz     2023-04-11      01:00:00    26
xyz     2023-04-11      01:03:00    16

解决方案 #

SQL语句实现 #

要实现分组求和与排序，需将用户ID、日期、3分钟间隔标识作为分组依据，同时按这三个字段排序以匹配期望结果的顺序：

SELECT 
    userId,
    date,
    time_3min,
    SUM(value) AS `sum(value)`
FROM 
    your_table_name
GROUP BY 
    userId, date, time_3min
ORDER BY 
    userId, date, time_3min;

关键说明 #

• GROUP BY子句：必须包含userId、date、time_3min三个字段。同一用户在不同日期可能有相同的time_3min值，需要通过日期区分分组；不同用户的记录也需独立分组，避免跨用户汇总。
• ORDER BY子句：先按userId排序，确保同一用户的记录集中展示；再按date排序，保证同用户下按日期先后排列；最后按time_3min排序，让同日期内的3分钟间隔按时间顺序输出，完全匹配期望结果的排列逻辑。

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