PostgreSQL按产品首销月分组实现销售队列分析SQL方法

实现思路 #

• 计算每个产品（绑定code维度）的首次销售月份，作为队列起始点标记为month_0
• 取全量销售数据的最大月份作为统计终点，为每个产品生成从首销月到终点的连续自然月份序列，解决间隔缺月问题
• 将原始销售数据按「产品+月份」维度聚合总销量，和连续月份序列做左关联，无销量的月份自动填充0
• 计算每个统计月份和首销月的间隔月数，生成month_N格式的分组标记，最终输出符合队列分析要求的结果

完整可运行SQL代码 #

WITH base AS (
    -- 基础数据清洗
    SELECT 
        product,
        code,
        order_date::DATE AS order_date,
        quantity 
    FROM sales
    WHERE order_date::DATE BETWEEN '2020-01-01' AND CURRENT_DATE
),
first_date_of_sales AS (
    -- 计算每个产品的首销日期、首销所在自然月
    SELECT 
        product,
        code,
        MIN(order_date) AS first_order_date,
        DATE_TRUNC('month', MIN(order_date))::DATE AS first_month
    FROM base
    GROUP BY product, code
),
max_stat_month AS (
    -- 取全量数据的最大销售月份，作为连续月份生成的终点
    SELECT DATE_TRUNC('month', MAX(order_date))::DATE AS end_month FROM base
),
product_full_months AS (
    -- 为每个产品生成从首销月到统计终点的全部连续月份
    SELECT 
        f.product,
        f.code,
        f.first_month,
        GENERATE_SERIES(
            f.first_month,
            m.end_month,
            INTERVAL '1 month'
        )::DATE AS stat_month
    FROM first_date_of_sales f
    CROSS JOIN max_stat_month m
),
monthly_agg_sales AS (
    -- 按产品+月份聚合实际销售总量
    SELECT 
        product,
        code,
        DATE_TRUNC('month', order_date)::DATE AS sale_month,
        SUM(quantity) AS total_quantity
    FROM base
    GROUP BY product, code, DATE_TRUNC('month', order_date)
)
-- 最终关联输出结果
SELECT 
    pfm.product,
    pfm.code,
    COALESCE(mas.total_quantity, 0) AS quantity,
    'month_' || (
        EXTRACT(YEAR FROM AGE(pfm.stat_month, pfm.first_month)) * 12 
        + EXTRACT(MONTH FROM AGE(pfm.stat_month, pfm.first_month))
    )::INT AS grouped_date,
    TO_CHAR(pfm.stat_month, 'YYYY-MM') AS "Month"
FROM product_full_months pfm
LEFT JOIN monthly_agg_sales mas
    ON pfm.product = mas.product
    AND pfm.code = mas.code
    AND pfm.stat_month = mas.sale_month
ORDER BY pfm.product, pfm.stat_month;

关键逻辑说明 #

• 补全缺失月份依赖PostgreSQL内置的GENERATE_SERIES函数，无需额外维护公共日历表即可快速生成连续时间序列
• 月份间隔计算使用AGE时间函数，自动处理跨年场景的月份差计算，避免手动计算出现偏移
• 左关联聚合后的销量数据，通过COALESCE将无销量月份的空值替换为0，符合补零要求
• 基于提供的样例数据运行，输出结果和预期完全一致：Product_1在2020-03月无销售时自动填充0，月份标记、销量汇总均匹配规则

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