SQL Window Functions (`OVER`)
January 5, 2025
full-stack
note-to-self
So, I found this confusing at first: OVER is how you define a window, and you can use a list of functions, window-specific and aggregate, to populate the window.
It seems like they should be called OVER-functions. So, thinking of it like this helped: The window is the set of rows you're seeing, and OVER connects the function that comes before it and applies to the set of rows. Function is the noun, OVER is (always) the verb and the result is the window, defined by PARTITION BY, ORDER BY, etc
"Run function OVER rows and display them in this window."
Window functions must have OVER. However, you can define them separately:
SELECT
region,
salesperson,
amount,
SUM(amount) OVER window_region AS region_total
FROM sales
WINDOW window_region AS (PARTITION BY region);
Most posts are for my own reference and reflection, and shouldn’t be taken as fully accurate or instructional.