Window Function Over clause

Question

In the Over Clause documentation mentions "Rows", and "Range"..

OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr>]
      [ROWS <expr> | RANGE <expr>]
     )

However, I couldn't find any detailed explanation regarding their functionality or any samples for their use. Can someone please explain what are they and how can they be used?

It's like any T-SQL rows/range in OVER, you find plenty of examples on Google. Like: http://stevestedman.com/2013/04/rows-and-range-preceding-and-following/ — Pentium10, Aug 05 '15 at 08:20

score 5 · Accepted Answer · edited May 23 '17 at 11:57

5

See user 'sprocket' answer at https://stackoverflow.com/a/27574474:

ROWS and RANGE allow the window function to look at a user defined window of rows, for example the previous 27 to run a moving average:

SELECT spend,
       SUM(spend) OVER (PARTITION BY user ORDER BY date ROWS BETWEEN 27 PRECEDING AND CURRENT ROW),
       user,
       date
FROM user_spend;

edited May 23 '17 at 11:57

Community

1
1

answered Aug 05 '15 at 21:52

Felipe Hoffa

54,922
16
151
325

Window Function Over clause

1 Answers1