Frame Specification
Expand your knowledge of window functions by diving into frame specifications.
We'll cover the following...
Row-based queries in SQL consist of a window function (e.g., LEAD()) and a window specification. Four optional components constitute the window specification, namely:
A name
A row partitioning
A row ordering within the partitions
A frame within the partitions
In particular, the SQL syntax that corresponds to this concept looks as follows:
<window_function> OVER (<window_name> PARTITION BY <attribute> ORDER BY <attribute> <frame_specification>)└───────────┘ └──────────────────────┘ └──────────────────┘ └───────────────────┘(1) (2) (3) (4)
While the first three components are easy to grasp, the last component has more details. This so-called frame specification enables us to define the frame sliding over each partition’s rows. As stated earlier, the frame specification is not mandatory, however, if we do not provide one, the default frame will be used. The default frame starts at the first row of a partition and extends to the last row of that ...