Код IT
← Каталог

SQL для аналитики — Накопительная выручка и сравнение с прошлым днём

Фрагмент из «SQL для аналитики»: Накопительная выручка и сравнение с прошлым днём.

sql projectencyclopedia7-04-analitika-1122 embed URL статья в энциклопедии
SQL main.sql 
SELECT
    sale_date,
    region,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum,
    LAG(amount, 1) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS prev_day_amount
FROM sales;
SELECT
    sale_date,
    region,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum,
    LAG(amount, 1) OVER (
        PARTITION BY region
        ORDER BY sale_date
    ) AS prev_day_amount
FROM sales;