Search⌘ K
AI Features
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Frame Specification

Explore the concept of frame specification in SQL window functions within MySQL. Learn to use frame units ROWS and RANGE with start and end points to calculate running totals, rolling averages, and other sliding window metrics on partitioned data. Understand syntax details, including UNBOUNDED PRECEDING and INTERVAL expressions, through practical examples involving session logs and production dates.

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)
The four components of the window specification

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 ...