Partition by rearranges table on each query run-CodePudding

The below query always rearranges my table (2021-01-01 not followed by 2021-01-02 but any other random date ) at each run and messes up the average calculation. If I remove the partition by the table will get ordered by EventTime(date) correctly...but I have 6 kinds of Symbols I would like the average of. What am I doing wrong?

select ClosePrice, Symbol, EventTime, AVG(ClosePrice) over(
partition by Symbol
order by EventTime
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) [SMA]
from ClosePrices

CodePudding user response：

The query is missing an ORDER BY clause for the final results. The ORDER BY inside the OVER() expression only applies to that window.

The SQL language is based on relational set theory concepts, which explicitly deny any built-in order for tables. That is, there is no guaranteed order for your tables or queries unless you deliberately set one via an ORDER BY clause.

In the past it may have seemed like you always get rows back in a certain order, but if so it's because you've been lucky. There are lots of things that can cause a database to return results in a different order, sometimes even for different runs of the same query.

If you care about the order of the results, you MUST use ORDER BY:

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 EventTime