如何在BigQuery中计算2018-01-01至每月末的累计唯一客户数？

需求说明 #

需要统计从2018-01-01到当前年度每个月末的累计唯一客户数，例如：

• 2022年1月统计2018-01-01至2022-01-31的唯一客户总数
• 2022年2月统计2018-01-01至2022-02-28/29的唯一客户总数
  以此实现月度环比分析，现有SQL仅能查询单个月份的结果，需调整为批量输出所有目标月份的统计数据。

现有SQL问题 #

当前SQL固定了日期范围，无法自动生成每个月末的累计统计：

select count(distinct customername) from table
where billingdate between "2018-01-01" and "2022-01-30";

解决方案 #

通过生成目标月末日期序列，结合子查询批量计算每个日期的累计唯一客户数，以下是主流数据库的实现代码：

MySQL 版本 #

WITH monthly_end_dates AS (
    SELECT LAST_DAY(date_add('2018-01-01', INTERVAL (n-1) MONTH)) AS end_date
    FROM (
        SELECT @row := @row + 1 AS n FROM 
        (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t1,
        (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t2,
        (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3,
        (SELECT @row := 0) t0
    ) nums
    WHERE end_date <= LAST_DAY(CURDATE())
)
SELECT 
    end_date AS 统计月末日期,
    (SELECT COUNT(DISTINCT customername) FROM table WHERE billingdate BETWEEN '2018-01-01' AND med.end_date) AS 累计唯一客户数
FROM monthly_end_dates med
ORDER BY end_date;

PostgreSQL 版本 #

WITH monthly_end_dates AS (
    SELECT generate_series(
        date_trunc('month', '2018-01-01'::date) + interval '1 month - 1 day',
        date_trunc('month', current_date) + interval '1 month - 1 day',
        interval '1 month'
    )::date AS end_date
)
SELECT 
    end_date AS 统计月末日期,
    (SELECT COUNT(DISTINCT customername) FROM table WHERE billingdate BETWEEN '2018-01-01' AND med.end_date) AS 累计唯一客户数
FROM monthly_end_dates med
ORDER BY end_date;

SQL Server 版本 #

WITH monthly_end_dates AS (
    SELECT DATEADD(DAY, -1, DATEADD(MONTH, n, '2018-01-01')) AS end_date
    FROM (
        SELECT TOP (DATEDIFF(MONTH, '2018-01-01', GETDATE()) + 1) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.all_columns
    ) nums
    WHERE end_date <= EOMONTH(GETDATE())
)
SELECT 
    end_date AS 统计月末日期,
    (SELECT COUNT(DISTINCT customername) FROM table WHERE billingdate BETWEEN '2018-01-01' AND med.end_date) AS 累计唯一客户数
FROM monthly_end_dates med
ORDER BY end_date;

注意事项 #

• 替换代码中的table为实际业务表名
• 若customername或billingdate字段名与实际不符，需对应修改
• 结果按日期升序排列，可直接对比相邻月份的累计客户数实现环比分析

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