SQL单表多日期按月统计问题：非直接分组实现多维度计数

方法一：条件聚合（高效推荐） #

这种方式无需拆分原始记录，通过条件判断直接完成多维度聚合，兼容绝大多数SQL数据库（MySQL、PostgreSQL、SQL Server等）。

假设表名为customer_deals，核心字段：

• id (PK)：唯一主键
• transfer_date：线索移交销售日期
• meeting_date：会议日期
• sign_date：合同签署日期
• contract_amount：合同金额

SQL示例：

-- 生成表中所有涉及到的统计月份（避免遗漏有数据的月份）
WITH all_months AS (
    SELECT DISTINCT
        DATE_TRUNC('month', COALESCE(transfer_date, meeting_date, sign_date)) AS stat_month
    FROM customer_deals
    WHERE COALESCE(transfer_date, meeting_date, sign_date) IS NOT NULL
)
SELECT
    am.stat_month,
    -- 当月线索移交次数
    SUM(CASE WHEN DATE_TRUNC('month', cd.transfer_date) = am.stat_month THEN 1 ELSE 0 END) AS transfer_count,
    -- 当月会议次数
    SUM(CASE WHEN DATE_TRUNC('month', cd.meeting_date) = am.stat_month THEN 1 ELSE 0 END) AS meeting_count,
    -- 当月合同签署次数
    SUM(CASE WHEN DATE_TRUNC('month', cd.sign_date) = am.stat_month THEN 1 ELSE 0 END) AS sign_count,
    -- 当月签署合同的金额总和
    SUM(CASE WHEN DATE_TRUNC('month', cd.sign_date) = am.stat_month THEN cd.contract_amount ELSE 0 END) AS total_contract_amount
FROM all_months am
CROSS JOIN customer_deals cd
GROUP BY am.stat_month
ORDER BY am.stat_month;

核心逻辑： #

1. all_months CTE提取所有存在有效日期（移交/会议/签合同）的月份，确保统计覆盖所有有业务发生的时段。
2. 通过CROSS JOIN将每个月份与全表记录关联，用CASE语句判断每条记录的对应日期是否落在当前统计月份，完成计数和金额累加。
3. 若需指定统计范围（如近12个月），可替换all_months为手动生成的月份序列（比如PostgreSQL用generate_series，MySQL用递归CTE）。

方法二：UNION ALL拆分记录后聚合 #

先将每条记录按三个日期维度拆分成独立条目，再聚合统计，适合需要先看明细再汇总的场景。

SQL示例：

-- 拆分每条记录为单个日期类型的条目
WITH date_entries AS (
    SELECT
        DATE_TRUNC('month', transfer_date) AS stat_month,
        'transfer' AS date_type,
        0 AS contract_amount
    FROM customer_deals
    WHERE transfer_date IS NOT NULL
    UNION ALL
    SELECT
        DATE_TRUNC('month', meeting_date) AS stat_month,
        'meeting' AS date_type,
        0 AS contract_amount
    FROM customer_deals
    WHERE meeting_date IS NOT NULL
    UNION ALL
    SELECT
        DATE_TRUNC('month', sign_date) AS stat_month,
        'sign' AS date_type,
        contract_amount AS contract_amount
    FROM customer_deals
    WHERE sign_date IS NOT NULL
)
-- 聚合转成宽表格式
SELECT
    stat_month,
    SUM(CASE WHEN date_type = 'transfer' THEN 1 ELSE 0 END) AS transfer_count,
    SUM(CASE WHEN date_type = 'meeting' THEN 1 ELSE 0 END) AS meeting_count,
    SUM(CASE WHEN date_type = 'sign' THEN 1 ELSE 0 END) AS sign_count,
    SUM(CASE WHEN date_type = 'sign' THEN contract_amount ELSE 0 END) AS total_contract_amount
FROM date_entries
GROUP BY stat_month
ORDER BY stat_month;

核心逻辑： #

1. date_entries CTE将每条记录的有效日期拆分为独立条目，标记日期类型并保留签合同金额。
2. 后续通过CASE语句聚合不同类型的计数和金额，得到每个月份的汇总结果。
3. 若数据库支持PIVOT语法（如SQL Server），可替换最后的CASE聚合为PIVOT，写法更简洁，但兼容性稍差。

关键细节提示 #

• NULL值处理：所有统计需过滤NULL日期，避免生成无效的月份统计项。
• 日期截断兼容性：不同数据库的日期截断语法有差异：
  • MySQL/MariaDB：用DATE_FORMAT(date_col, '%Y-%m-01')或STR_TO_DATE(CONCAT(YEAR(date_col), '-', MONTH(date_col), '-01'), '%Y-%m-%d')
  • SQL Server：2022+版本用DATE_TRUNC(month, date_col)，旧版本用DATEFROMPARTS(YEAR(date_col), MONTH(date_col), 1)
  • Oracle：用TRUNC(date_col, 'MM')
• 金额统计准确性：仅将签合同日期落在当月的记录金额计入总和，避免重复统计。

内容的提问来源于stack exchange，提问作者Sylwester Skibiński