Totali progressivi e medie mobili

Nozioni di base sui totali progressivi

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue
ORDER BY day;

Totale progressivo per gruppo

SELECT id, user_id, total,
       SUM(total) OVER (
         PARTITION BY user_id ORDER BY created_at
       ) AS user_running_total
FROM orders;

Media mobile

SELECT day, revenue,
       AVG(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7d
FROM daily_revenue;

Media mobile centrata

SELECT day, revenue,
       AVG(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
       ) AS avg_7d_centred
FROM daily_revenue;

Distribuzione cumulativa

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day)
         / SUM(revenue) OVER ()  AS cum_share
FROM daily_revenue;

ROWS e RANGE

-- ROWS: 6 previous rows + this one (may skip dates with no data)
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- RANGE: include any row with a day within '6 days' of the current row
AVG(revenue) OVER (ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)

Massimo cumulativo

SELECT day, total_users,
       MAX(total_users) OVER (ORDER BY day) AS peak_so_far
FROM daily_metrics;

Conteggio cumulativo

SELECT id, ts,
       COUNT(*) OVER (ORDER BY ts) AS events_so_far
FROM events;

Individuare le anomalie

SELECT day, revenue, avg_7d,
  CASE WHEN revenue < 0.5 * avg_7d THEN 'WARNING' ELSE 'OK' END AS status
FROM (
  SELECT day, revenue,
         AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS avg_7d
  FROM daily_revenue
) sub;

NULL iniziali

Prestazioni

Riepilogo

Verifica rapida