如何在BigQuery中实现每日唯一值统计及历史新增唯一值计数？

嘿，我来帮你搞定这个困扰已久的问题！先理清楚你的需求和现有问题：你需要统计每日的唯一Value数量，以及当日首次出现的Value数量（也就是之前从未出现过的新增值），你的现有SQL已经能搞定每日唯一值，但新增值的统计还没实现。

先明确你的原始数据 #

首先，你的数据表结构和数据是这样的：

解决方案思路 #

核心是先找出每个Value第一次出现的日期，然后基于这个日期来判断当日的Value是否是新增的。用窗口函数来实现会非常高效清晰。

完整SQL代码 #

-- 先给每个Value标记首次出现的日期
WITH value_first_seen AS (
    SELECT 
        Day,
        Value,
        -- 按Value分组，取每组最早的日期作为首次出现日期
        MIN(Day) OVER (PARTITION BY Value) AS first_seen_day
    FROM your_table_name -- 这里替换成你的实际表名
)
SELECT 
    Day,
    -- 统计当日的唯一Value数量
    COUNT(DISTINCT Value) AS "Daily Unique Counts",
    -- 只统计首次出现日期等于当日的Value，去重后计数就是新增数量
    COUNT(DISTINCT CASE WHEN first_seen_day = Day THEN Value END) AS "All Time Unique Counts"
FROM value_first_seen
GROUP BY Day
ORDER BY Day;

代码解释 #

1. CTE部分：value_first_seen这个公共表表达式会给每一行的Value附上它第一次出现的日期。PARTITION BY Value会把相同Value的行归为一组，MIN(Day)取这个组里最早的日期，也就是该Value首次出现的时间。
2. 主查询部分：
   • COUNT(DISTINCT Value)就是你需要的每日唯一值数量（注意你之前的SQL写的是COUNT(DISTINCT id)，这里应该改成Value，因为你要统计的是值的唯一性，不是ID）
   • CASE WHEN first_seen_day = Day THEN Value END会筛选出那些在当日首次出现的Value，对这些值去重后计数，就得到了当日的历史新增唯一值数量。

验证结果 #

执行这个SQL后，你会得到和你期望完全一致的结果：

兼容老版本数据库的替代方案 #

如果你的数据库不支持CTE（比如某些老旧的MySQL版本），可以用子查询代替，不过性能会差一些：

SELECT 
    Day,
    COUNT(DISTINCT Value) AS "Daily Unique Counts",
    COUNT(DISTINCT CASE WHEN (
        SELECT MIN(Day) FROM your_table_name t2 WHERE t2.Value = t1.Value
    ) = t1.Day THEN t1.Value END) AS "All Time Unique Counts"
FROM your_table_name t1
GROUP BY Day
ORDER BY Day;

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