如何按月份+PRODUCT_TYPE分组，展示每月全量产品类型的签约合同数？

你的问题出在原SQL仅返回有签单记录的月份+产品类型组合，没有签单的产品在对应月份不会出现在结果集中。要实现每月固定显示62条产品记录，需要先生成所有月份与所有产品类型的完整组合，再关联实际签单数据。

解决方案步骤 #

1. 生成时间范围内的所有月份：覆盖从2019-01到数据中最新的签单月份（或指定结束月份）
2. 获取系统内全部62种PRODUCT_TYPE：确保包含所有产品类型，哪怕没有签单记录
3. 交叉连接月份与产品类型：得到所有可能的月份-产品组合
4. 左连接原查询结果：将实际签单数据关联到组合中，无数据的填充为0

适配不同数据库的SQL示例 #

1. PostgreSQL版本

-- 生成所有目标月份
WITH months_list AS (
    SELECT TO_CHAR(generate_series(
        DATE '2019-01-01',
        (SELECT MAX(dtime_signature) FROM dm_sales.v_sales_dm_data),
        INTERVAL '1 month'
    ), 'yyyy-mm') AS months
),
-- 获取全部产品类型
product_types AS (
    SELECT DISTINCT PRODUCT_TYPE FROM dm_sales.v_sales_dm_data
),
-- 原查询的签单统计
signed_stats AS (
    SELECT 
        TO_CHAR(a.dtime_signature, 'yyyy-mm') AS months,
        a.PRODUCT_TYPE,
        COUNT(a.CONTRACT_NUMBER) AS CONTRACT_SIGNED
    FROM dm_sales.v_sales_dm_data a
    WHERE a.contract_state <> 'Cancelled'
      AND a.cnt_signed = 1
      AND a.loan_type = 'Consumer Loan'
      AND a.dtime_signature >= DATE '2019-01-01'         
    GROUP BY TO_CHAR(a.dtime_signature, 'yyyy-mm'), a.PRODUCT_TYPE
)
-- 关联所有组合与统计数据
SELECT 
    ml.months,
    pt.PRODUCT_TYPE,
    COALESCE(ss.CONTRACT_SIGNED, 0) AS CONTRACT_SIGNED
FROM months_list ml
CROSS JOIN product_types pt
LEFT JOIN signed_stats ss 
    ON ml.months = ss.months 
    AND pt.PRODUCT_TYPE = ss.PRODUCT_TYPE
ORDER BY ml.months ASC, pt.PRODUCT_TYPE ASC;

2. Oracle版本

WITH months_list AS (
    SELECT TO_CHAR(ADD_MONTHS(DATE '2019-01-01', LEVEL - 1), 'yyyy-mm') AS months
    FROM dual
    CONNECT BY ADD_MONTHS(DATE '2019-01-01', LEVEL - 1) <= 
               (SELECT MAX(dtime_signature) FROM dm_sales.v_sales_dm_data)
),
product_types AS (
    SELECT DISTINCT PRODUCT_TYPE FROM dm_sales.v_sales_dm_data
),
signed_stats AS (
    SELECT 
        TO_CHAR(a.dtime_signature, 'yyyy-mm') AS months,
        a.PRODUCT_TYPE,
        COUNT(a.CONTRACT_NUMBER) AS CONTRACT_SIGNED
    FROM dm_sales.v_sales_dm_data a
    WHERE a.contract_state <> 'Cancelled'
      AND a.cnt_signed = 1
      AND a.loan_type = 'Consumer Loan'
      AND a.dtime_signature >= DATE '2019-01-01'         
    GROUP BY TO_CHAR(a.dtime_signature, 'yyyy-mm'), a.PRODUCT_TYPE
)
SELECT 
    ml.months,
    pt.PRODUCT_TYPE,
    NVL(ss.CONTRACT_SIGNED, 0) AS CONTRACT_SIGNED
FROM months_list ml
CROSS JOIN product_types pt
LEFT JOIN signed_stats ss 
    ON ml.months = ss.months 
    AND pt.PRODUCT_TYPE = ss.PRODUCT_TYPE
ORDER BY ml.months ASC, pt.PRODUCT_TYPE ASC;

3. MySQL 8.0+版本

WITH RECURSIVE months_list AS (
    SELECT DATE_FORMAT('2019-01-01', '%Y-%m') AS months, '2019-01-01' AS month_date
    UNION ALL
    SELECT DATE_FORMAT(ADD_MONTHS(month_date, 1), '%Y-%m'), ADD_MONTHS(month_date, 1)
    FROM months_list
    WHERE month_date <= (SELECT MAX(dtime_signature) FROM dm_sales.v_sales_dm_data)
),
product_types AS (
    SELECT DISTINCT PRODUCT_TYPE FROM dm_sales.v_sales_dm_data
),
signed_stats AS (
    SELECT 
        DATE_FORMAT(a.dtime_signature, '%Y-%m') AS months,
        a.PRODUCT_TYPE,
        COUNT(a.CONTRACT_NUMBER) AS CONTRACT_SIGNED
    FROM dm_sales.v_sales_dm_data a
    WHERE a.contract_state <> 'Cancelled'
      AND a.cnt_signed = 1
      AND a.loan_type = 'Consumer Loan'
      AND a.dtime_signature >= '2019-01-01'         
    GROUP BY DATE_FORMAT(a.dtime_signature, '%Y-%m'), a.PRODUCT_TYPE
)
SELECT 
    ml.months,
    pt.PRODUCT_TYPE,
    IFNULL(ss.CONTRACT_SIGNED, 0) AS CONTRACT_SIGNED
FROM months_list ml
CROSS JOIN product_types pt
LEFT JOIN signed_stats ss 
    ON ml.months = ss.months 
    AND pt.PRODUCT_TYPE = ss.PRODUCT_TYPE
ORDER BY ml.months ASC, pt.PRODUCT_TYPE ASC;

关键说明 #

• CROSS JOIN 用来生成所有月份和产品类型的笛卡尔积，确保每个月份都有62条产品记录
• COALESCE/NVL/IFNULL 函数将无签单数据的NULL值替换为0，保证统计值的完整性
• 如果需要固定结束月份，可将MAX(dtime_signature)替换为指定日期（比如DATE '2024-06-01'）

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