Google BigQuery如何基于订阅起止日期高效统计历史每日活跃数？

优化方案 #

你原方案中新增虚拟ID做等值关联本质是实现笛卡尔积，完全可以用CROSS JOIN直接替换，省略冗余的虚拟ID字段逻辑，代码更简洁，执行效率和原方案一致，大表场景下还可以加前置过滤进一步优化。

优化后完整代码（兼容你原用的SQL引擎，如BigQuery）

WITH dummy AS
(
    SELECT DATE('2021-08-17') AS start_dt, DATE('2021-08-19') AS end_dt
    UNION ALL
    SELECT DATE('2021-08-18') AS start_dt, DATE('2021-08-20') AS end_dt
    UNION ALL
    SELECT DATE('2021-08-19') AS start_dt, DATE('2021-08-21') AS end_dt
),
date_series AS (
    -- 直接生成连续日期序列，无需冗余字段
    SELECT d AS cur_date
    FROM UNNEST(GENERATE_DATE_ARRAY('2021-08-16', '2021-08-22', INTERVAL 1 DAY)) d
)
SELECT 
    cur_date,
    SUM(IF(cur_date BETWEEN start_dt AND end_dt, 1, 0)) AS count
FROM date_series
-- 交叉连接实现日期与订阅记录全量匹配，替代虚拟ID关联
CROSS JOIN dummy
GROUP BY cur_date
ORDER BY cur_date

大表场景优化补充

如果订阅表数据量很大，可以在交叉连接前先过滤掉和统计日期范围完全无重叠的订阅记录，减少关联计算量：

CROSS JOIN (
    SELECT * FROM dummy
    WHERE end_dt >= '2021-08-16'
      AND start_dt <= '2021-08-22'
) dummy

上述方案输出结果和你原实现的聚合结果完全一致。

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