为何使用count(person_id) over(order by date)查询会出现同日重复记录?
为什么count() over()会返回同日重复记录
核心逻辑差异:窗口函数不会自动压缩结果行粒度
首先要明确窗口函数和普通GROUP BY聚合的本质区别:
- 普通
GROUP BY会把分组键相同的多行压缩成单行,最终返回的结果行数和分组数完全一致 - 窗口函数是逐行给原始数据集的每一行附加计算值,不会对原数据集的行做合并、去重、压缩——哪怕不同行的窗口计算结果完全一样,这些行也会全部保留在最终结果里。
你的查询实际执行过程拆解
你写的内层子查询:
select distinct person_id, date from events
这个子查询的结果粒度是每一个不重复的(person_id, date)组合对应一行——也就是说,只要同一天有多个不同用户产生了事件记录,子查询就会返回多行同日期的记录。
外层套的窗口函数:
count(person_id) over (order by pre.date)
这个计算过程完全不会主动把这些同日期的多行合并,而是逐行计算并返回对应结果。举个实际测试的样例,假设子查询pre返回的原始数据是:
| person_id | date |
|---|---|
| 1 | 2024-01-01 |
| 2 | 2024-01-01 |
| 3 | 2024-01-02 |
最终查询返回的结果和原数据集的行数完全一致,一共三行:
| date | count(person_id) |
|---|---|
| 2024-01-01 | 2 |
| 2024-01-01 | 2 |
| 2024-01-02 | 3 |
你观察到的同日期重复,本质就是子查询本身就存在同日期的多行记录,窗口函数没有对这些行做压缩,自然就会出现重复行。
为什么之前用sum() over()没遇到过这个问题
你之前熟悉的累计值计算流程,是先通过GROUP BY date把同日期的多行压缩成每天单行的粒度,再对这个已经压好的单粒度数据集套sum() over(order by date)计算累计值——此时原数据集本身每天就只有一行,窗口函数逐行返回的时候自然不会出现同日重复。
补充说明:默认窗口帧规则的影响
你当前写的窗口函数没有显式指定窗口范围,对于带ORDER BY的窗口,多数数据库的默认窗口帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,这种规则下,同ORDER BY值的行会共享同一个计算结果:
还是拿上面的样例数据来说,两个2024-01-01的行,计算出来的count值都是2(因为RANGE模式下会把同日期的所有行划进同一个范围,统计的是截至当前日期的总人数),而不是逐行递增的1、2——但即便两个行的日期、计算结果完全一样,这些行也不会被自动合并,最终还是会返回两行同日的记录,这也是你看到重复现象的最直观来源。
内容的提问来源于stack exchange,提问作者user14626416
相关产品推荐
相关产品推荐

