在BigQuery中如何计算user_id的滚动去重计数？

原始数据 #

date_  user_id
2021-10-05      123
2021-10-05      234
2021-10-05      345
2021-10-06      123
2021-10-06      234
2021-10-06      111
2021-10-07      123
2021-10-07      234
2021-10-07      111
2021-10-07      122

数据生成SQL #

SELECT 
  DATE('2021-10-05') AS date_, '123' AS user_id
  UNION ALL SELECT DATE('2021-10-05'), '234'
  UNION ALL SELECT DATE('2021-10-05'), '345'
  UNION ALL SELECT DATE('2021-10-06'), '123'
  UNION ALL SELECT DATE('2021-10-06'), '234'
  UNION ALL SELECT DATE('2021-10-06'), '111'
  UNION ALL SELECT DATE('2021-10-07'), '123'
  UNION ALL SELECT DATE('2021-10-07'), '234'
  UNION ALL SELECT DATE('2021-10-07'), '111'
  UNION ALL SELECT DATE('2021-10-07'), '122'

需求与期望结果 #

需要计算每个日期的历史累计去重用户数，即截至当前日期（含当天）所有出现过的唯一user_id数量，期望输出如下：

date_       rolling_count_distinct
2021-10-05                            3
2021-10-06                            4
2021-10-07                            5

这等价于对每个日期单独执行WHERE date_ <= '目标日期'后统计去重user_id数量。

解决方案 #

通用SQL实现（适配多数SQL引擎） #

WITH table_1 AS (
   SELECT DATE('2021-10-05') AS date_, '123' AS user_id
   UNION ALL SELECT DATE('2021-10-05'), '234'
   UNION ALL SELECT DATE('2021-10-05'), '345'
   UNION ALL SELECT DATE('2021-10-06'), '123'
   UNION ALL SELECT DATE('2021-10-06'), '234'
   UNION ALL SELECT DATE('2021-10-06'), '111'
   UNION ALL SELECT DATE('2021-10-07'), '123'
   UNION ALL SELECT DATE('2021-10-07'), '234'
   UNION ALL SELECT DATE('2021-10-07'), '111'
   UNION ALL SELECT DATE('2021-10-07'), '122'
),
-- 提取每个用户首次出现的日期
user_first_date AS (
    SELECT user_id, MIN(date_) AS first_date
    FROM table_1
    GROUP BY user_id
),
-- 提取所有存在记录的日期
all_dates AS (
    SELECT DISTINCT date_ FROM table_1
)
-- 统计每个日期前（含当天）首次出现的用户总数
SELECT 
    ad.date_,
    COUNT(ufd.user_id) AS rolling_count_distinct
FROM all_dates ad
LEFT JOIN user_first_date ufd 
    ON ufd.first_date <= ad.date_
GROUP BY ad.date_
ORDER BY ad.date_;

逻辑说明 #

1. user_first_date：通过MIN(date_)得到每个用户第一次出现的日期，避免重复计算同一用户；
2. all_dates：提取数据中所有的日期，确保每个日期都能出现在结果中；
3. 关联统计：将日期表与用户首次日期表关联，统计每个日期范围内首次出现的用户数量，即为截至该日期的滚动去重用户数。

特定引擎简化实现（如BigQuery） #

部分SQL引擎支持窗口函数中的COUNT(DISTINCT)，可以直接用以下语句：

WITH table_1 AS (
   SELECT DATE('2021-10-05') AS date_, '123' AS user_id
   UNION ALL SELECT DATE('2021-10-05'), '234'
   UNION ALL SELECT DATE('2021-10-05'), '345'
   UNION ALL SELECT DATE('2021-10-06'), '123'
   UNION ALL SELECT DATE('2021-10-06'), '234'
   UNION ALL SELECT DATE('2021-10-06'), '111'
   UNION ALL SELECT DATE('2021-10-07'), '123'
   UNION ALL SELECT DATE('2021-10-07'), '234'
   UNION ALL SELECT DATE('2021-10-07'), '111'
   UNION ALL SELECT DATE('2021-10-07'), '122'
)
SELECT 
    DISTINCT date_,
    COUNT(DISTINCT user_id) OVER (
        ORDER BY date_ 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rolling_count_distinct
FROM table_1
ORDER BY date_;

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