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);
These posts are for my own understanding. Reader beware. Info may be wrong but it reflects my current understanding.