如何在BigQuery中连接分钟级数据与15分钟及日度聚合表？

问题场景 #

我在BigQuery中有一张存储不同日期全天分钟级数据的基础表，另外创建了两张表分别用于计算15分钟求和值与日度求和值，示例表如下：

基础表（分钟级数据） #

user    age date            time            output
abc     24  2023-02-15      11:00:00        3
abc     24  2023-02-15      11:01:00        4
abc     24  2023-02-15      11:02:00        6
abc     24  2023-02-15      11:03:00        3
abc     24  2023-02-15      11:04:00        5
abc     24  2023-02-15      11:05:00        62
abc     24  2023-02-15      11:06:00        5
abc     24  2023-02-15      11:07:00        23
abc     24  2023-02-15      11:08:00        5
abc     24  2023-02-15      11:09:00        3
abc     24  2023-02-15      11:10:00        8
abc     24  2023-02-15      11:11:00        6
abc     24  2023-02-15      11:12:00        3
abc     24  2023-02-15      11:13:00        45
abc     24  2023-02-15      11:14:00        2
abc     24  2023-02-15      11:15:00        4
abc     24  2023-02-15      11:16:00        2
abc     24  2023-02-15      11:17:00        12
abc     24  2023-02-15      11:18:00        3
abc     24  2023-02-15      11:19:00        44
abc     24  2023-02-15      11:20:00        20
abc     24  2023-02-15      11:21:00        23
abc     24  2023-02-15      11:22:00        4
abc     24  2023-02-15      11:23:00        6
abc     24  2023-02-15      11:24:00        28
abc     24  2023-02-15      11:25:00        12
abc     24  2023-02-15      11:26:00        22
abc     24  2023-02-15      11:27:00        8
abc     24  2023-02-15      11:28:00        8
abc     24  2023-02-15      11:29:00        5

15分钟聚合表 #

user    date            time_15min  15sum_output
abc     2023-02-15      11:00:00    183
abc     2023-02-15      11:15:00    201

日度聚合表 #

user    date            dailysum_output 
abc     2023-02-15      384 

需要将这些表连接，生成包含基础表所有列及对应15分钟、日度聚合值的最终表，示例如下：

期望结果表 #

user    age date            time           output   15sum_output    dailysum_output
abc     24  2023-02-15      11:00:00        3       183             384
abc     24  2023-02-15      11:01:00        4       183             384
abc     24  2023-02-15      11:02:00        6       183             384
abc     24  2023-02-15      11:03:00        3       183             384
abc     24  2023-02-15      11:04:00        5       183             384
abc     24  2023-02-15      11:05:00        62      183             384
abc     24  2023-02-15      11:06:00        5       183             384
abc     24  2023-02-15      11:07:00        23      183             384
abc     24  2023-02-15      11:08:00        5       183             384
abc     24  2023-02-15      11:09:00        3       183             384
abc     24  2023-02-15      11:10:00        8       183             384
abc     24  2023-02-15      11:11:00        6       183             384
abc     24  2023-02-15      11:12:00        3       183             384
abc     24  2023-02-15      11:13:00        45      183             384
abc     24  2023-02-15      11:14:00        2       183             384
abc     24  2023-02-15      11:15:00        4       201             384
abc     24  2023-02-15      11:16:00        2       201             384
abc     24  2023-02-15      11:17:00        12      201             384
abc     24  2023-02-15      11:18:00        3       201             384
abc     24  2023-02-15      11:19:00        44      201             384
abc     24  2023-02-15      11:20:00        20      201             384
abc     24  2023-02-15      11:21:00        23      201             384
abc     24  2023-02-15      11:22:00        4       201             384
abc     24  2023-02-15      11:23:00        6       201             384
abc     24  2023-02-15      11:24:00        28      201             384
abc     24  2023-02-15      11:25:00        12      201             384
abc     24  2023-02-15      11:26:00        22      201             384
abc     24  2023-02-15      11:27:00        8       201             384
abc     24  2023-02-15      11:28:00        8       201             384
abc     24  2023-02-15      11:29:00        5       201             384

此前尝试按日期进行左连接未达到预期效果，需要用BigQuery SQL实现该需求。

解决方案 #

核心是将基础表的时间字段转换为对应的15分钟周期起始时间，再以此为关联条件连接15分钟聚合表；日度聚合表则按user和date关联即可。

方法一：关联现有聚合表 #

假设三张表分别名为minute_table、15min_agg_table、daily_agg_table，SQL语句如下：

SELECT
  mt.user,
  mt.age,
  mt.date,
  mt.time,
  mt.output,
  t15.15sum_output,
  td.dailysum_output
FROM minute_table mt
LEFT JOIN 15min_agg_table t15
  ON mt.user = t15.user
  AND mt.date = t15.date
  -- 将基础表时间转换为15分钟周期起始时间，匹配聚合表的time_15min
  AND TIMESTAMP_TRUNC(TIMESTAMP(mt.date || ' ' || mt.time), MINUTE, 15) = TIMESTAMP(t15.date || ' ' || t15.time_15min)
LEFT JOIN daily_agg_table td
  ON mt.user = td.user
  AND mt.date = td.date
ORDER BY mt.time;

方法二：从基础表直接计算聚合值（更高效） #

如果无需单独维护聚合表，可直接在查询中计算15分钟和日度总和，减少数据冗余：

SELECT
  user,
  age,
  date,
  time,
  output,
  -- 计算当前行所属15分钟周期的总和
  SUM(output) OVER (
    PARTITION BY user, date, TIMESTAMP_TRUNC(TIMESTAMP(date || ' ' || time), MINUTE, 15)
  ) AS 15sum_output,
  -- 计算当日总和
  SUM(output) OVER (PARTITION BY user, date) AS dailysum_output
FROM minute_table
ORDER BY time;

关键说明 #

• 15分钟周期匹配：使用TIMESTAMP_TRUNC函数将分钟级时间截断到最近的15分钟起始点，确保每条数据匹配对应聚合值。
• 关联条件：必须同时匹配user、date及转换后的15分钟时间，避免跨用户/日期的错误关联。
• 方法二优势：无需提前创建聚合表，查询时实时计算，适合对数据实时性要求较高的场景。

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