المجاميع التراكمية والمتوسطات المتحركة

أساسيات المجموع التراكمي

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

المجموع التراكمي لكل مجموعة

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

المتوسط المتحرك

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

المتوسط المتحرك المركزي

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

التوزيع التراكمي

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

ROWS مقابل 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)

الحد الأقصى التراكمي

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

العد التراكمي

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

اكتشاف الحالات الشاذة

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 الأولية

الأداء

مراجعة

تحقق سريع