请求编写SQL：按customer_id分组，按月统计job_id数量并转置列

需要统计每个customer_id对应的job_id总数，并按job_id的创建月份分组，将月份转为列展示对应数量。现有数据表结构及数据如下：

期望输出结果：

解决方案：条件聚合实现行转列 #

由于仅涉及固定3个月的数据，使用条件聚合是最直接的方式，不同数据库的日期格式化函数略有差异，以下是主流数据库的实现代码：

MySQL 版本 #

SELECT
    customer_id,
    COUNT(job_id) AS job_id,
    SUM(CASE WHEN DATE_FORMAT(Date, '%b-%y') = 'Nov-23' THEN 1 ELSE 0 END) AS `Nov-23`,
    SUM(CASE WHEN DATE_FORMAT(Date, '%b-%y') = 'Dec-23' THEN 1 ELSE 0 END) AS `Dec-23`,
    SUM(CASE WHEN DATE_FORMAT(Date, '%b-%y') = 'Jan-24' THEN 1 ELSE 0 END) AS `Jan-24`
FROM
    your_table_name -- 替换为实际表名
GROUP BY
    customer_id
ORDER BY
    customer_id;

PostgreSQL 版本 #

SELECT
    customer_id,
    COUNT(job_id) AS job_id,
    SUM(CASE WHEN TO_CHAR(Date, 'Mon-YY') = 'Nov-23' THEN 1 ELSE 0 END) AS "Nov-23",
    SUM(CASE WHEN TO_CHAR(Date, 'Mon-YY') = 'Dec-23' THEN 1 ELSE 0 END) AS "Dec-23",
    SUM(CASE WHEN TO_CHAR(Date, 'Mon-YY') = 'Jan-24' THEN 1 ELSE 0 END) AS "Jan-24"
FROM
    your_table_name -- 替换为实际表名
GROUP BY
    customer_id
ORDER BY
    customer_id;

SQL Server 版本 #

SELECT
    customer_id,
    COUNT(job_id) AS job_id,
    SUM(CASE WHEN FORMAT(Date, 'MMM-yy') = 'Nov-23' THEN 1 ELSE 0 END) AS [Nov-23],
    SUM(CASE WHEN FORMAT(Date, 'MMM-yy') = 'Dec-23' THEN 1 ELSE 0 END) AS [Dec-23],
    SUM(CASE WHEN FORMAT(Date, 'MMM-yy') = 'Jan-24' THEN 1 ELSE 0 END) AS [Jan-24]
FROM
    your_table_name -- 替换为实际表名
GROUP BY
    customer_id
ORDER BY
    customer_id;

代码说明 #

• COUNT(job_id)：统计每个客户的总任务数；
• SUM(CASE...)：针对每个月份，判断当前记录的日期是否属于该月份，是则计1，否则计0，最终求和得到该月份的任务数；
• GROUP BY customer_id：按客户分组，确保每个客户只返回一行统计结果。

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