无PIVOT支持下，基于动态年度列的SQL行转列聚合查询求助

我有一张名为MyTable的表，结构及数据如下：

需要将metric按年度聚合，把不同年份转为列，列值为对应年度的metric总和，期望返回结果：

要求：

• 仅统计指定时间范围（比如WHERE date between ago(600d) and now()，其中600由应用传入）内的数据，并从该范围中提取唯一年份作为列
• 数据库不支持PIVOT语法

因为数据库不支持PIVOT，我们可以用条件聚合实现静态列转换；如果需要自动适配时间范围内的所有年份，就得结合动态SQL生成查询语句。

1. 静态条件聚合（已知年份时） #

如果已经明确时间范围内的年份（比如示例中的2022、2023），直接用SUM(CASE...)就能实现列转换：

SELECT
    tool,
    team,
    SUM(CASE WHEN EXTRACT(YEAR FROM date) = 2023 THEN metric ELSE 0 END) AS `2023`,
    SUM(CASE WHEN EXTRACT(YEAR FROM date) = 2022 THEN metric ELSE 0 END) AS `2022`
FROM MyTable
WHERE date BETWEEN ago(600d) AND now()
GROUP BY tool, team;

注意：年份提取函数因数据库不同略有差异：

• MySQL/MariaDB：用YEAR(date)替代EXTRACT(YEAR FROM date)
• SQL Server：用YEAR(date)
• Oracle：可用EXTRACT(YEAR FROM date)或TO_CHAR(date, 'YYYY')

2. 动态SQL（自动适配时间范围内的年份） #

如果时间范围的年份不确定，需要动态生成SUM(CASE...)语句，以下是主流数据库的实现示例：

MySQL/MariaDB #

-- 拼接动态SQL语句
SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'SUM(CASE WHEN YEAR(date) = ', YEAR(date), ' THEN metric ELSE 0 END) AS `', YEAR(date), '`'
        )
    ) INTO @sql
FROM MyTable
WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 600 DAY) AND NOW(); -- 替换为你的时间范围条件

SET @sql = CONCAT('SELECT tool, team, ', @sql, ' FROM MyTable WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 600 DAY) AND NOW() GROUP BY tool, team');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server #

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STRING_AGG(
    CONCAT(
        'SUM(CASE WHEN YEAR(date) = ', YEAR(date), ' THEN metric ELSE 0 END) AS [', YEAR(date), ']'
    ), ', '
)
FROM (SELECT DISTINCT YEAR(date) AS year FROM MyTable WHERE date BETWEEN DATEADD(DAY, -600, GETDATE()) AND GETDATE()) AS years;

SET @sql = CONCAT('SELECT tool, team, ', @sql, ' FROM MyTable WHERE date BETWEEN DATEADD(DAY, -600, GETDATE()) AND GETDATE() GROUP BY tool, team');

EXEC sp_executesql @sql;

PostgreSQL #

-- 生成动态SQL
WITH years AS (
    SELECT DISTINCT EXTRACT(YEAR FROM date)::INT AS year
    FROM MyTable
    WHERE date BETWEEN NOW() - INTERVAL '600 days' AND NOW()
)
SELECT
    'SELECT tool, team, ' ||
    STRING_AGG(
        CONCAT('SUM(CASE WHEN EXTRACT(YEAR FROM date) = ', year, ' THEN metric ELSE 0 END) AS "', year, '"'),
        ', '
    ) ||
    ' FROM MyTable WHERE date BETWEEN NOW() - INTERVAL ''600 days'' AND NOW() GROUP BY tool, team;'
INTO @sql
FROM years;

-- 执行动态SQL
EXECUTE @sql;

关键说明 #

• 时间范围中的600可以由应用动态传入，替换对应位置的数值即可
• 动态SQL会自动从指定时间范围提取唯一年份，生成对应的列，无需手动维护年份列表
• 没有对应年份数据的行会显示0，符合需求

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