SQL 如何按时间维度统计新增唯一值的数量？

实现思路 #

核心逻辑是找到每个ID首次出现的月份，再按首次出现的月份分组统计ID数量，即可得到每个月的新增唯一ID数。

通用SQL写法（兼容绝大多数数据库） #

假设原始表名为user_records，SQL语句如下：

SELECT 
    first_appear_month AS Date,
    COUNT(id) AS cnt_new_id
FROM (
    -- 子查询获取每个ID首次出现的月份
    SELECT 
        id,
        MIN(Date) AS first_appear_month
    FROM user_records
    GROUP BY id
) t
GROUP BY first_appear_month
ORDER BY first_appear_month;

日期字段为标准日期类型的适配写法 #

如果同一个月存在多日的记录，需要先将日期截断到月份维度，以下为不同数据库的适配示例：

MySQL 版本 #

SELECT 
    first_appear_month AS Date,
    COUNT(id) AS cnt_new_id
FROM (
    SELECT 
        id,
        MIN(DATE_FORMAT(Date, '%Y-%m-01')) AS first_appear_month
    FROM user_records
    GROUP BY id
) t
GROUP BY first_appear_month
ORDER BY first_appear_month;

PostgreSQL 版本 #

SELECT 
    first_appear_month::DATE AS Date,
    COUNT(id) AS cnt_new_id
FROM (
    SELECT 
        id,
        MIN(DATE_TRUNC('month', Date)) AS first_appear_month
    FROM user_records
    GROUP BY id
) t
GROUP BY first_appear_month
ORDER BY first_appear_month;

窗口函数实现写法 #

也可以通过窗口函数标记每个ID的首次出现记录，再统计：

SELECT 
    Date,
    COUNT(id) AS cnt_new_id
FROM (
    SELECT 
        id,
        Date,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY Date ASC) AS rn
    FROM user_records
) t
WHERE rn = 1
GROUP BY Date
ORDER BY Date;

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