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

تحتاج المراجعة الشهرية لأعمال رانيا إلى ثلاثة أشياء في شريحة واحدة: الإيراد حسب الشهر، وما تراكم من إجمالي السنة حتى الآن، وهل نما كل شهر مقارنةً بسابقه. يمكنك حساب رقم ما منذ بداية السنة في جدول بيانات لاحقًا. لكن دوال النافذة نفسها التي رتّبت المنتجات في الدرس الماضي تستطيع أيضًا أن تراكم وتقارن، فيخرج الجدول كله من SQL مباشرةً، وبطريقة قابلة لإعادة الإنتاج.

المجاميع التراكمية ومنذ بداية السنة

دالة التجميع كدالة نافذة مع ORDER BY داخل OVER تصبح حسابًا تراكميًا: كل صف يرى الصفوف حتى نفسه. أضف PARTITION BY فيبدأ الحساب من جديد لكل مجموعة. هذه طلبات Cartwheel الشهرية مع عدد منذ بداية السنة يبدأ من جديد في كل يناير:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY month
)
SELECT
  month,
  orders,
  SUM(orders) OVER (
    PARTITION BY substr(month, 1, 4)
    ORDER BY month
  ) AS orders_ytd
FROM monthly
ORDER BY month
LIMIT 14;

مفتاح الـ partition هو السنة، مأخوذة من الأحرف الأربعة الأولى في تسمية الشهر. يغلق ديسمبر 2024 السنة على 552 طلبًا، ويبدأ يناير 2025 من جديد بـ 84.

نافذتان على الصف نفسه تتكاملان جيدًا. قسمة المجموع التراكمي على مجموع الـ partition كله، 100.0 * SUM(orders) OVER (PARTITION BY substr(month, 1, 4) ORDER BY month) / SUM(orders) OVER (PARTITION BY substr(month, 1, 4))، تعطي النسبة التراكمية من السنة التي بلغها كل شهر. في نشاط موسمي مثل Cartwheel، هذا هو الرقم الذي تتابعه المالية فعلًا: بنهاية أكتوبر 2025 كان قد أُنجز أقل بقليل من ثلثي السنة.

المقارنة بالصف السابق

يعيد LAG(x) قيمة x من الصف السابق بترتيب النافذة، ويعيد LEAD(x) قيمتها من الصف التالي. بعدها يصبح التغيّر بين فترتين عملية حسابية بسيطة:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY month
)
SELECT
  month,
  orders,
  LAG(orders) OVER (ORDER BY month) AS prev_month,
  orders - LAG(orders) OVER (ORDER BY month) AS change,
  ROUND(100.0 * (orders - LAG(orders) OVER (ORDER BY month))
        / NULLIF(LAG(orders) OVER (ORDER BY month), 0), 1) AS change_pct,
  LAG(orders, 12) OVER (ORDER BY month) AS same_month_last_year
FROM monthly
ORDER BY month DESC
LIMIT 4;

قفز نوفمبر 2025 بـ 193 طلبًا، ثم هبط ديسمبر بـ 140: Black Friday، ثم الركود الذي يليه. الأرقام الشهرية المتتالية مشوّشة في نشاط موسمي، ولهذا يستخدم العمود الأخير LAG(orders, 12)، أي القيمة قبل اثني عشر صفًا، لمنظور سنة مقابل سنة: 325 طلبًا في نوفمبر هذا العام مقابل 120 في نوفمبر الماضي.

لا يكون LAG(orders, 12) «الشهر نفسه في السنة الماضية» إلا إذا كان لكل شهر صف. الشهر الذي لا طلبات فيه لن يكون له صف، وستنزاح كل مقارنة بعده شهرًا واحدًا بصمت. وهذا الافتراض على وشك أن يصبح مهمًا.

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

المتوسط المتحرك يخفّف التشويش اليومي. تحتاج النافذة إلى إطار (frame) صريح، أي نطاق الصفوف حول الصف الحالي الذي تراه دالة التجميع: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW هو الصف الحالي مع الستة التي قبله.

يعدّ الإطار الصفوف لا الأيام. إذا لم يكن في يوم ما طلبات، فلا صف له، ويمتد الإطار بصمت إلى زمن أبعد. يُظهر الشهر الأول في Cartwheel مدى سوء هذا التضليل: في يناير 2024 وصلت 8 طلبات فقط، في 8 أيام مختلفة. المتوسط على 7 صفوف من الصفوف الموجودة يقول «طلب واحد يوميًا» طوال الشهر. والحقيقة نحو ربع ذلك.

الإصلاح هو تقويم: ولّد صفًا لكل يوم بـ CTE تكراري، واربط به الأعداد اليومية بـ LEFT JOIN، وحوّل الأيام المفقودة إلى أصفار:

WITH RECURSIVE days(day) AS (
  SELECT '2024-01-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2024-01-31'
),
daily AS (
  SELECT date(order_date) AS day, COUNT(*) AS orders
  FROM orders
  WHERE status <> 'cancelled'
    AND order_date >= '2024-01-01' AND order_date < '2024-02-01'
  GROUP BY day
),
filled AS (
  SELECT d.day, COALESCE(x.orders, 0) AS orders
  FROM days AS d
  LEFT JOIN daily AS x ON x.day = d.day
),
smoothed AS (
  SELECT day, orders,
         ROUND(AVG(orders) OVER (
           ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
         ), 2) AS orders_7d_avg
  FROM filled
)
SELECT * FROM smoothed
WHERE day >= '2024-01-25'
ORDER BY day;

الآن يعرض 31 يناير 0.43 طلب يوميًا خلال الأسبوع الأخير، وهو الرقم الصادق. لاحظ موضع شرط التاريخ: في الـ SELECT الأخير، بعد حساب المتوسط المتحرك في smoothed. تعمل دوال النافذة بعد WHERE، فالتصفية في الـ query نفسه ستترك الأيام الأولى المعروضة بلا تاريخ سابق يُحسب متوسطه. وحيلة التقويم نفسها تحمي LAG(orders, 12) من الأشهر المفقودة.

أصبحت قادرًا على الترتيب والمراكمة والمقارنة. يضع الدرس التالي هذه الأدوات في خدمة التحليلين اللذين يطلبهما كل فريق منتج: القمع (funnel) ومعدل الاحتفاظ (retention) لكل cohort.