تقارير التواريخ الشائعة (MTD وWoW وYoY)

ثلاثة تقارير كلاسيكية للفترات

من بداية الشهر حتى تاريخه

SELECT SUM(total) AS mtd_revenue
FROM orders
WHERE created_at >= date_trunc('month', NOW())
  AND created_at <  date_trunc('month', NOW()) + INTERVAL '1 month';

من بداية العام حتى تاريخه

SELECT SUM(total) AS ytd_revenue
FROM orders
WHERE created_at >= date_trunc('year', NOW());

مقارنة أسبوع بأسبوع

SELECT
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW())
  ) AS this_week,
  SUM(total) FILTER (
    WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week'
      AND created_at <  date_trunc('week', NOW())
  ) AS last_week
FROM orders
WHERE created_at >= date_trunc('week', NOW()) - INTERVAL '1 week';

مقارنة عام بعام

SELECT date_trunc('month', created_at) AS month, SUM(total)
FROM orders
WHERE created_at >= NOW() - INTERVAL '2 years'
GROUP BY 1
ORDER BY 1;

-- Then plot with two lines: current year, previous year.

سلسلة يومية من دون فجوات

SELECT day, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(
  CURRENT_DATE - INTERVAL '30 days',
  CURRENT_DATE,
  INTERVAL '1 day'
) AS day
LEFT JOIN orders o ON date_trunc('day', o.created_at) = day
GROUP BY day
ORDER BY day;

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

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

الفرق بين الفترات

SELECT month,
       revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS prev_year,
       (revenue - LAG(revenue, 12) OVER (ORDER BY month))
         / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) AS yoy_growth
FROM monthly_revenue;

آخر 7 / 30 يومًا (نافذة متحركة)

-- Trailing 7 days, ending now:
WHERE created_at >= NOW() - INTERVAL '7 days';

-- This calendar week, from Monday:
WHERE created_at >= date_trunc('week', NOW());

اعتبارات المناطق الزمنية

SELECT date_trunc('day', created_at AT TIME ZONE 'America/Los_Angeles') AS la_day,
       COUNT(*)
FROM events
GROUP BY 1;

التجميع المسبق باستخدام العروض المجسّدة

مراجعة

تحقق سريع