使用OVER窗口函数求和有序行时的去重问题咨询

原始数据 #

期望结果 #

尝试的SQL语句 #

SELECT 
  ROW_NUMBER() OVER (ORDER BY Sales DESC) AS '# TOP CUSTOMERS',
  SUM(Sales) OVER (ORDER BY Sales DESC) AS 'SUM SALES'
FROM Sales;

实际得到的错误结果 #

问题分析与解决方案 #

问题根源在于SUM(Sales) OVER (ORDER BY Sales DESC)的默认窗口框架：当排序字段存在重复值（比如这里的Sales=4出现两次），默认的RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW会将所有相同Sales值的行一次性纳入累计范围，导致第2、3行的累计值直接跳至18（10+4+4）。

要实现逐行累加的效果，只需明确指定窗口框架为ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，这样无论Sales是否重复，都会按行的顺序逐个累加：

SELECT 
  ROW_NUMBER() OVER (ORDER BY Sales DESC, Customer) AS '# TOP CUSTOMERS',
  SUM(Sales) OVER (ORDER BY Sales DESC, Customer ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'Total Sales'
FROM Sales;

另外在ORDER BY中加入Customer，是为了让重复Sales值的行拥有确定的排序顺序，避免不同执行环境下结果出现差异。

你不需要使用DENSE_RANK或PARTITION BY，调整窗口框架就能满足“按行排序并逐行求和”的需求。

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