BigQuery中关联两表生成带嵌套列的日期匹配设备统计结果表需求

解决方案思路 #

先关联source和base表，筛选出每个com_date落在对应设备生效区间内的记录，再按id和com_date分组统计唯一设备数，同时用数组嵌套设备明细信息。

实现SQL #

WITH joined_data AS (
  SELECT
    s.id,
    s.com_date,
    b.device_id,
    b.start_date,
    b.end_date
  FROM
    `your-project.your-dataset.source` s
  INNER JOIN
    `your-project.your-dataset.base` b
  ON
    s.id = b.id
  WHERE
    s.com_date BETWEEN b.start_date AND b.end_date
)
SELECT
  id,
  com_date,
  COUNT(DISTINCT device_id) AS count,
  -- 嵌套设备信息，若需去重可加DISTINCT
  ARRAY_AGG(STRUCT(device_id, start_date, end_date) ORDER BY device_id) AS device_info
FROM
  joined_data
GROUP BY
  id,
  com_date
ORDER BY
  id,
  com_date;

代码说明 #

1. 关联筛选（CTE部分）：通过id关联两张表，用BETWEEN确保com_date处于设备的start_date到end_date生效区间内，过滤掉无效的设备记录。
2. 分组统计：按id和com_date分组，COUNT(DISTINCT device_id)计算该日期组合下的唯一设备数量；ARRAY_AGG(STRUCT(...))将设备的ID、生效/失效日期打包成嵌套数组，方便查看每个统计项对应的设备明细。
3. 可选优化：如果base表存在同一id+device_id的重复记录，可在ARRAY_AGG中添加DISTINCT关键字，避免嵌套数组里出现重复的设备信息。

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