按月份计算前N个月平均值及近13月多指标滚动均值查询需求

Got it, let's work through this problem step by step. The goal is to calculate monthly metrics (distinct counts and sums) for the last 13 months, then compute rolling 3-month averages for each metric—including handling the earliest months where we don't have a full 3 months of data.

Approach #

Here's the plan:

1. Generate all 13 months: Even if some months have no data, we need to include them to ensure our rolling window works correctly.
2. Aggregate raw data into monthly metrics: Calculate the distinct counts and sum for each month from your table.
3. Fill in missing months: Join our generated month list with the aggregated metrics, replacing null values with 0 (or keep nulls if that makes sense for your use case).
4. Compute rolling averages: Use window functions to calculate the average of each metric over the current month and the two preceding months. For the first couple of months, this will automatically use whatever data is available.

SQL Query #

WITH date_series AS (
    -- Generate the last 13 months (includes the current month and the 12 prior)
    SELECT
        generate_series(
            DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months'),
            DATE_TRUNC('month', CURRENT_DATE),
            INTERVAL '1 month'
        ) AS month_start
),
monthly_metrics AS (
    -- Calculate core metrics per month from raw data
    SELECT
        DATE_TRUNC('month', date_col) AS month_start,
        COUNT(DISTINCT col1) AS distinct_col1,
        COUNT(DISTINCT col2) AS distinct_col2,
        SUM(col3) AS sum_col3
    FROM your_table
    WHERE date_col >= (SELECT MIN(month_start) FROM date_series)
    GROUP BY DATE_TRUNC('month', date_col)
),
full_monthly_data AS (
    -- Ensure every month in our 13-month range is present, fill missing data with 0
    SELECT
        ds.month_start,
        COALESCE(mm.distinct_col1, 0) AS distinct_col1,
        COALESCE(mm.distinct_col2, 0) AS distinct_col2,
        COALESCE(mm.sum_col3, 0) AS sum_col3
    FROM date_series ds
    LEFT JOIN monthly_metrics mm ON ds.month_start = mm.month_start
),
rolling_averages AS (
    -- Compute rolling 3-month averages for each metric
    SELECT
        month_start,
        distinct_col1,
        distinct_col2,
        sum_col3,
        AVG(distinct_col1) OVER (
            ORDER BY month_start
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avg_distinct_col1,
        AVG(distinct_col2) OVER (
            ORDER BY month_start
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avg_distinct_col2,
        AVG(sum_col3) OVER (
            ORDER BY month_start
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS avg_sum_col3
    FROM full_monthly_data
)
SELECT * FROM rolling_averages;

Key Notes #

• Handling missing months: The date_series CTE ensures we don't skip any months. If a month has no data, COALESCE sets the metrics to 0—you can remove this if you prefer null values instead.
• Adjusting the date range: If you want the last 13 completed months (excluding the current month), change the generate_series start to CURRENT_DATE - INTERVAL '13 months' and end to CURRENT_DATE - INTERVAL '1 month'.
• SQL dialect differences: If you're using MySQL (which doesn't support generate_series), replace the date_series CTE with a recursive CTE to generate the month list.
• Rolling window definition: The ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means we include the current month plus the two before it. If you want the average of the three months before the current one (excluding current), use ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING.

内容的提问来源于stack exchange，提问作者Carlos Siestrup