BigQuery统计各频道独有唯一ID数量的SQL实现方法

BigQuery中存储了tv_data表，包含Id、Channel两个字段，样例数据如下：

需要按频道统计仅在该频道出现过的唯一ID数量，无需手动修改WHERE条件即可一次性查询所有频道的对应结果。

方案1：窗口函数实现（最优，仅需一次全表扫描） #

先统计每个ID关联的不同频道总数，仅统计关联总数为1的ID即可得到对应频道的专属ID数量：

WITH id_channel_count AS (
  SELECT 
    Id,
    Channel,
    COUNT(DISTINCT Channel) OVER (PARTITION BY Id) AS total_channels_for_id
  FROM tv_data
)
SELECT 
  Channel,
  COUNT(DISTINCT Id) AS num_ids_only_in_channel
FROM id_channel_count
WHERE total_channels_for_id = 1
GROUP BY Channel
ORDER BY Channel

如果需要展示没有符合条件ID的频道（统计值显示为0），可以使用如下调整版本：

WITH id_channel_count AS (
  SELECT 
    Id,
    Channel,
    COUNT(DISTINCT Channel) OVER (PARTITION BY Id) AS total_channels_for_id
  FROM tv_data
),
all_channels AS (
  SELECT DISTINCT Channel FROM tv_data
),
valid_stats AS (
  SELECT 
    Channel,
    COUNT(DISTINCT Id) AS num_ids_only_in_channel
  FROM id_channel_count
  WHERE total_channels_for_id = 1
  GROUP BY Channel
)
SELECT 
  a.Channel,
  IFNULL(v.num_ids_only_in_channel, 0) AS num_ids_only_in_channel
FROM all_channels a
LEFT JOIN valid_stats v ON a.Channel = v.Channel
ORDER BY a.Channel

方案2：关联逻辑实现（和原有单频道查询逻辑完全一致） #

如果希望沿用原有NOT IN的逻辑思路，可以通过自关联适配全频道查询：

SELECT
  t1.Channel,
  COUNT(DISTINCT t1.Id) AS num_ids_only_in_channel
FROM tv_data t1
LEFT JOIN tv_data t2 
  ON t1.Id = t2.Id 
  AND t1.Channel != t2.Channel
WHERE t2.Id IS NULL
GROUP BY t1.Channel
ORDER BY t1.Channel

针对提供的样例数据，上述语句返回结果如下：

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