如何通过SQL直接实现日期范围内各分类每日操作数量的行列转换统计

当然可以直接用SQL实现！而且这种方式通常比代码处理更高效，尤其是数据量较大的时候。核心思路是生成日期范围内的所有日期，再关联所有分类做统计，最后把分类名称“透视”成列。下面分两种情况给出具体实现：

情况1：分类是固定的（不会新增/删除） #

如果你的分类列表是固定的，用静态透视就能快速解决。这里以MySQL为例（其他数据库思路类似，语法稍作调整即可）：

WITH RECURSIVE date_range AS (
    -- 替换成你的起始日期参数
    SELECT DATE('2016-01-01') AS day 
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM date_range
    -- 替换成你的结束日期参数
    WHERE day < DATE('2021-12-31') 
),
-- 把每个日期和所有分类做交叉组合，确保每个日期都覆盖所有分类
date_category AS (
    SELECT dr.day, c.id AS category_id, c.name AS category_name
    FROM date_range dr
    CROSS JOIN categories c
),
-- 统计每个日期+分类的操作数量（没有操作则为0）
action_counts AS (
    SELECT 
        dc.day,
        dc.category_name,
        COUNT(a.id) AS count
    FROM date_category dc
    LEFT JOIN actions a 
        -- 注意：如果created_date是字符串类型，要先转成日期，比如STR_TO_DATE(a.created_date, '%m/%d/%Y')
        ON dc.day = DATE(a.created_date) 
        AND dc.category_id = a.category_id
    GROUP BY dc.day, dc.category_name
)
-- 透视结果：把分类名称转为列
SELECT 
    day,
    MAX(CASE WHEN category_name = 'Cat-1' THEN count ELSE 0 END) AS `Cat-1`,
    MAX(CASE WHEN category_name = 'Cat-2' THEN count ELSE 0 END) AS `Cat-2`
    -- 如果有更多固定分类，继续添加对应的CASE语句即可
FROM action_counts
GROUP BY day
ORDER BY day;

关键步骤解释： #

1. date_range：用递归CTE生成指定范围内的所有日期，确保即使某天没有任何操作，也会出现在结果里（满足你“每一天都展示”的需求）。
2. date_category：通过交叉连接（CROSS JOIN）把每个日期和所有分类组合，保证每个日期下每个分类都有一条记录，避免遗漏分类。
3. action_counts：左连接actions表统计操作数，没有匹配的操作时COUNT(a.id)会返回0。
4. 透视结果：用CASE语句把分类名称转成列，MAX函数用来聚合每个日期下的分类统计值。

情况2：分类是动态的（会新增/删除） #

如果分类会动态变化，不想每次新增分类都修改SQL，可以用动态SQL来自动生成透视列。还是以MySQL为例：

-- 第一步：自动拼接所有分类对应的CASE语句
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN category_name = ''',
        name,
        ''' THEN count ELSE 0 END) AS `',
        name,
        '`'
    )
) INTO @sql
FROM categories;

-- 第二步：拼接完整的SQL语句（替换日期参数）
SET @sql = CONCAT('
WITH RECURSIVE date_range AS (
    SELECT DATE(''2016-01-01'') AS day
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM date_range
    WHERE day < DATE(''2021-12-31'')
),
date_category AS (
    SELECT dr.day, c.id AS category_id, c.name AS category_name
    FROM date_range dr
    CROSS JOIN categories c
),
action_counts AS (
    SELECT 
        dc.day,
        dc.category_name,
        COUNT(a.id) AS count
    FROM date_category dc
    LEFT JOIN actions a 
        ON dc.day = DATE(a.created_date) 
        AND dc.category_id = a.category_id
    GROUP BY dc.day, dc.category_name
)
SELECT day, ', @sql, ' 
FROM action_counts
GROUP BY day
ORDER BY day;
');

-- 第三步：执行动态生成的SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

其他数据库适配提示： #

• PostgreSQL：可以用crosstab函数（需要先安装tablefunc扩展），语法更简洁。
• SQL Server：可以用PIVOT关键字结合动态SQL实现。
• 旧版本MySQL（<8.0）：递归CTE不支持，可以提前创建一个日历表（存储所有可能的日期），或者用数字表生成日期范围。

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