Views, CTEs and window functions

Real analysis takes several steps. SQL has three tools to keep them readable: views, CTEs and window functions.

Views

A view is a saved query with a name, used like a table. It stores no data: each time it is queried, its query runs. Define a business measure once, such as earned premium, and every report that uses the view agrees. A materialised view stores its result, and is brought up to date with refresh materialized VIEW: fast to read, but only as fresh as its last refresh.

Common table expressions

A CTE is a named step inside one query, written with WITH. Each step can use the ones before it, so a long query reads top to bottom, like a recipe. Since PostgreSQL 12 a CTE is usually folded into the main query, so it costs nothing extra.

WITH open_claims AS (
    SELECT * FROM claims WHERE status = 'open'
),
by_cause AS (
    SELECT cause, COUNT(*) AS claims, SUM(reserve) AS reserves
    FROM open_claims
    GROUP BY cause
)
SELECT * FROM by_cause
ORDER BY reserves DESC;

Window functions

An aggregate with GROUP BY collapses rows into one per group. A window function computes over related rows but keeps every row: each claim with the total of its policy, or each payment with the running total so far. OVER (...) defines the window:

• PARTITION BY: the groups, like GROUP BY, but without collapsing them.
• ORDER BY: the order within each partition, for rankings, running totals and LAG.
• A frame, such as ROWS BETWEEN 2 preceding AND current ROW: which rows around the current one count.

SELECT claim_id, paid_on, amount,
       SUM(amount) OVER (PARTITION BY claim_id ORDER BY paid_on, payment_id) AS paid_so_far
FROM claim_payments
ORDER BY claim_id, paid_on
LIMIT 10;