Search code examples
sqloraclewindow-functions

How to understand the results of rows between 2 preceding and current row?

My SQL query is:

SELECT time, buy,
  avg(buy) OVER (ORDER BY time rows between 1 preceding and current row) as average_2,
  avg(buy) OVER (ORDER BY time rows between 2 preceding and current row) as average_3
FROM my_table;

I'm trying to understand these window functions. I used some test data and got results:

TIME                       BUY  AVERAGE_2  AVERAGE_3
------------------- ---------- ---------- ----------
2019-05-05 10:05:19          1          1          1
2019-05-05 10:05:22          2        1.5        1.5
2019-05-05 10:05:25          3        2.5          2
2019-05-05 10:05:27          4        3.5          3

I need to know: how do I get these results? Specially average_3?

What is the difference between ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and rows between 2 preceding and current row? I read many explanations from the internet, now I'm confused because they have explained with different syntax.

Solution

  • for your question "What is the difference between ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and rows between 2 preceding and current row ?"

    In average_3 you get the average between the two previous rows and the current row, the same happens in average_2 but only with a previous row, but it is better to see a good example of that.

    This post by Steve Stedman is really good, and it gives you a good example of that.