Introduction
ROWS
RANGE
15. Boundaries with RANGE
Default window frame
Revision

Instruction

Great. The window frame of RANGE is defined just like the window frame of ROWS: use BETWEEN ... AND ..., or the abbreviated version.

You can use UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING, as well as CURRENT ROW, but you can't use n PRECEDING and n FOLLOWING with RANGE.

Why? With ROWS, we always knew that there was a single current row, and so we could easily calculate the previous/next rows. With RANGE, the database must understand what "three preceding values" means. It's easy to understand "three preceding days" but what are "three numbers preceding 14.5"? The SQL standard defined the meaning of n PRECEDING and n FOLLOWING for RANGE, but the database usually do not implement it. Refer to the documentation of your database to make sure.

Exercise

For each stock_change with product_id = 7, show its id, quantity, changed date and another column which will count the number of stock changes with product_id = 7 on that particular date.

Stuck? Here's a hint!

Use the abbreviated version: RANGE CURRENT ROW.

Console

Code editor

Result

TableConsole