Amazon Redshift
数据库开发人员指南 (API Version 2012-12-01)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。点 击 Getting Started with Amazon AWS to see specific differences applicable to the China (Beijing) Region.

开窗函数的数据的唯一排序

如果开窗函数的 ORDER BY 子句不生成数据的唯一排序和总排序,则行的顺序是不确定的。如果 ORDER BY 表达式生成重复的值(部分排序),则这些行的返回顺序可能会在多次运行中有所不同,并且开窗函数可能会返回意外的或不一致的结果。

例如,以下查询在多次运行中返回了不同的结果,因为 order by dateid 未生成 SUM 开窗函数的数据的唯一排序。

Copy
select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 1730.00 | 1730.00 1827 | 708.00 | 2438.00 1827 | 234.00 | 2672.00 ... select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 234.00 | 234.00 1827 | 472.00 | 706.00 1827 | 347.00 | 1053.00 ...

在这种情况下,向该开窗函数添加另一个 ORDER BY 列可解决此问题:

Copy
select dateid, pricepaid, sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+--------- 1827 | 234.00 | 234.00 1827 | 337.00 | 571.00 1827 | 347.00 | 918.00 ...