SQL如何判断分区内值是否已存在并统计各term、period新ID占比
SQL实现分区内新ID标记与占比统计
匹配规则
- 按period升序判定时间先后顺序,同一ID首次出现的记录,对应周期的新ID贡献为100%
- 同一ID在首次出现节点之后的重复记录,对应周期的新ID贡献为0
- 最终按term、period维度聚合,输出每个分组下的新ID占比
实现逻辑
- 使用窗口函数
ROW_NUMBER(),按id分区、按period升序排序,为每个ID的出现记录排定先后顺序 - 排序值为1的记录判定为首次出现,打标
is_new=1;其余重复出现的记录打标is_new=0 - 按
term、period分组聚合,计算新ID占比:组内新ID数 / 组内总ID数 * 100%
通用SQL实现代码
WITH id_sequence AS ( -- 为每个ID的出现记录按周期排序 SELECT term, period, id, ROW_NUMBER() OVER (PARTITION BY id ORDER BY period ASC) AS seq FROM raw_table -- 替换为实际的原始数据集表名 ), id_new_tag AS ( -- 标记新ID状态 SELECT term, period, CASE WHEN seq = 1 THEN 1 ELSE 0 END AS is_new FROM id_sequence ) -- 聚合计算各term、period的新ID占比 SELECT term, period, COUNT(*) AS total_id_cnt, SUM(is_new) AS new_id_cnt, ROUND(SUM(is_new) * 100.0 / COUNT(*), 2) AS new_id_ratio_pct FROM id_new_tag GROUP BY term, period ORDER BY term, period;
示例数据输出说明
针对给出的测试数据集(全量记录term=7,ID均为A2C5):
- period=201910:A2C5为首次出现节点,对应新ID占比100%
- period=201911至202004:A2C5已在前期记录中存在,无新增ID,对应新ID占比0%
内容的提问来源于stack exchange,提问作者psy
相关产品推荐
相关产品推荐

