使用Partition by时每次查询结果重排影响均值计算该如何解决？

问题根因 #

你遇到的乱序问题和窗口函数的计算逻辑无关，是SQL查询的基础规则导致的：

• SQL本身没有默认排序规则，任何查询如果不在最外层加ORDER BY子句，数据库返回结果的顺序都是不保证的，哪怕窗口函数内部写了ORDER BY EventTime也只会影响窗口内的均值计算逻辑，不会约束最终返回结果的排列顺序
• 你去掉PARTITION BY后看起来结果有序，只是当前数据库执行计划凑巧按EventTime顺序返回的巧合，换个数据量、数据库版本或者执行环境都可能再次出现乱序

解决方案 #

如果需要让结果按指定顺序输出，直接在查询末尾加上最外层的排序规则即可，你需要按Symbol分组、同一Symbol下按EventTime排序的话，调整后的SQL如下：

select 
    ClosePrice, 
    Symbol, 
    EventTime, 
    AVG(ClosePrice) over(
        partition by Symbol
        order by EventTime
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) [SMA]
from ClosePrices
-- 加上最外层排序保证输出顺序
ORDER BY Symbol, EventTime

额外说明 #

你原有的窗口函数逻辑是正确的：PARTITION BY Symbol已经保证了不同品种的均值分开计算，哪怕最终结果看起来乱序，SMA的计算结果本身是对的，只是输出顺序没符合你的预期，加上外层排序后即可同时满足分组计算、结果有序的需求。

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