Investigating window frames
In addition to the PARTITION BY
and ORDER BY
clauses, window functions can include frames which tell the database engine what we include as relevant to the window.
Here we will look at four different frames to understand how the results would differ. For each example, we want to fill in the window frame for the following query:
SELECT
ir.IncidentDate,
ir.IncidentTypeID,
SUM(ir.NumberOfIncidents) OVER (
PARTITION BY ir.IncidentTypeID
ORDER BY ir.IncidentDate
<< WINDOW FRAME >>
) AS NumberOfIncidents
FROM dbo.IncidentRollup ir;
We will specify ROWS
or RANGE
based on whether we want to include individual rows or a range of values. We will specify the "preceding" clause, which tells how many rows (or what range of values) we want prior to the current row. We will also specify the "following" clause, which tells what we want from the current row forward.
This exercise is part of the course
Time Series Analysis in SQL Server
Hands-on interactive exercise
Turn theory into action with one of our interactive exercises
