中文
devexamples

SQL Window Function Running Totals

Use PostgreSQL window functions with explicit ROWS frames to compute running totals, moving averages, and each day's share of an account.

SqlData Fetching

Code

snippet
-- Schema assumptions (PostgreSQL 15+):
--   payments(id bigint PRIMARY KEY, account_id bigint NOT NULL,
--            paid_at date NOT NULL, amount_cents integer NOT NULL CHECK (amount_cents > 0))
-- The TEMP table mirrors that shape so the query runs in a scratch database.

CREATE TEMP TABLE payments (
    id           bigint PRIMARY KEY,
    account_id   bigint NOT NULL,
    paid_at      date NOT NULL,
    amount_cents integer NOT NULL CHECK (amount_cents > 0)
);

INSERT INTO payments (id, account_id, paid_at, amount_cents) VALUES
    (1, 10, DATE '2026-10-01', 1200),
    (2, 10, DATE '2026-10-02', 800),
    (3, 10, DATE '2026-10-04', 1500),
    (4, 20, DATE '2026-10-01', 500),
    (5, 20, DATE '2026-10-03', 700),
    (6, 20, DATE '2026-10-03', 300);

WITH daily AS (
    SELECT account_id, paid_at, SUM(amount_cents) AS day_cents
    FROM payments
    GROUP BY account_id, paid_at
)
SELECT
    account_id,
    paid_at,
    day_cents,
    SUM(day_cents) OVER (
        PARTITION BY account_id
        ORDER BY paid_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_cents,
    ROUND(AVG(day_cents) OVER (
        PARTITION BY account_id
        ORDER BY paid_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_cents,
    ROUND(100.0 * day_cents / SUM(day_cents) OVER (PARTITION BY account_id), 2) AS pct_of_account
FROM daily
ORDER BY account_id, paid_at;

Related Examples