PostgreSQL如何实现动态日期列的PIVOT转置？

仅通过SQL完全可以实现动态日期转列的需求，无需依赖API层转置，但具体实现方式取决于你使用的数据库类型——不同数据库对动态透视的支持逻辑略有差异。以下结合主流数据库给出落地方案：

核心逻辑 #

由于你的meta_date是动态非固定值，需要通过动态SQL或数据库特定的透视函数，自动生成对应日期的列名，完成行转列。你提到meta_date已被API处理确保可分组，这为SQL透视提供了必要前提。

1. PostgreSQL 实现方案 #

PostgreSQL无原生PIVOT语法，可借助tablefunc扩展的crosstab函数，或用动态SQL拼接实现：

方案一：动态SQL拼接（灵活适配任意日期范围）

DO $$
DECLARE
    date_cols TEXT;
BEGIN
    -- 生成所有日期列的定义（如："2022-08-27" NUMERIC, "2022-08-28" NUMERIC...）
    SELECT string_agg(DISTINCT quote_ident(mu.meta_date::TEXT) || ' NUMERIC', ', ')
    INTO date_cols
    FROM metas_usuario mu
    JOIN usuario u ON mu.user_id = u.pk
    JOIN metas_type mt ON mt.id = mu.meta_type_id
    WHERE 
        u.del = 0
        AND u.fkp = '2453ff2c-6494-4a6d-a15f-f70384b669c1'
        AND mu.meta_date BETWEEN SYMMETRIC '2022-08-27' AND '2022-09-24'
        AND mt.id = 4;

    -- 执行动态透视查询
    EXECUTE format('
        SELECT gerente, %s
        FROM (
            SELECT 
                u.u AS gerente,
                mu.meta_date::TEXT AS date_col,
                mu.meta
            FROM
                usuario u
            RIGHT JOIN 
                metas_usuario mu ON mu.user_id = u.pk
            JOIN
                metas_type mt ON mt.id = mu.meta_type_id 
            WHERE 
                u.del = 0
                AND u.fkp = ''2453ff2c-6494-4a6d-a15f-f70384b669c1''
                AND mu.meta_date BETWEEN SYMMETRIC ''2022-08-27'' AND ''2022-09-24''
                AND mt.id = 4
        ) AS src
        PIVOT (
            MAX(meta)
            FOR date_col IN (%s)
        ) AS pvt
        ORDER BY gerente ASC', date_cols, string_agg(DISTINCT quote_ident(mu.meta_date::TEXT), ', '));
END $$;

2. SQL Server 实现方案 #

SQL Server有原生PIVOT语法，但动态列需用动态SQL拼接：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 生成日期列列表
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(meta_date)
                      FROM (
                          SELECT DISTINCT CONVERT(VARCHAR, mu.meta_date, 23) AS meta_date
                          FROM metas_usuario mu
                          JOIN usuario u ON mu.user_id = u.pk
                          JOIN metas_type mt ON mt.id = mu.meta_type_id
                          WHERE 
                              u.del = 0
                              AND u.fkp = '2453ff2c-6494-4a6d-a15f-f70384b669c1'
                              AND mu.meta_date BETWEEN '2022-08-27' AND '2022-09-24'
                              AND mt.id = 4
                      ) AS dates
                      ORDER BY meta_date
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 拼接并执行动态透视查询
SET @query = '
SELECT gerente, ' + @cols + '
FROM (
    SELECT 
        u.u AS gerente,
        CONVERT(VARCHAR, mu.meta_date, 23) AS date_col,
        mu.meta
    FROM
        usuario u
    RIGHT JOIN 
        metas_usuario mu ON mu.user_id = u.pk
    JOIN
        metas_type mt ON mt.id = mu.meta_type_id 
    WHERE 
        u.del = 0
        AND u.fkp = ''2453ff2c-6494-4a6d-a15f-f70384b669c1''
        AND mu.meta_date BETWEEN ''2022-08-27'' AND ''2022-09-24''
        AND mt.id = 4
) AS src
PIVOT (
    MAX(meta)
    FOR date_col IN (' + @cols + ')
) AS pvt
ORDER BY gerente ASC';

EXECUTE sp_executesql @query;

3. MySQL 实现方案 #

MySQL无原生PIVOT，需用动态SQL拼接CASE WHEN逻辑：

SET @sql = NULL;

-- 生成动态列的CASE语句
SELECT GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN meta_date = ''', DATE_FORMAT(mu.meta_date, '%Y-%m-%d'), ''' THEN meta END) AS `', DATE_FORMAT(mu.meta_date, '%Y-%m-%d'), '`')
) INTO @sql
FROM metas_usuario mu
JOIN usuario u ON mu.user_id = u.pk
JOIN metas_type mt ON mt.id = mu.meta_type_id
WHERE 
    u.del = 0
    AND u.fkp = '2453ff2c-6494-4a6d-a15f-f70384b669c1'
    AND mu.meta_date BETWEEN '2022-08-27' AND '2022-09-24'
    AND mt.id = 4;

-- 拼接完整查询语句
SET @sql = CONCAT('
SELECT u.u AS gerente, ', @sql, '
FROM usuario u
RIGHT JOIN metas_usuario mu ON mu.user_id = u.pk
JOIN metas_type mt ON mt.id = mu.meta_type_id 
WHERE 
    u.del = 0
    AND u.fkp = ''2453ff2c-6494-4a6d-a15f-f70384b669c1''
    AND mu.meta_date BETWEEN ''2022-08-27'' AND ''2022-09-24''
    AND mt.id = 4
GROUP BY u.u
ORDER BY gerente ASC');

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

总结 #

• 主流数据库都支持动态SQL，完全可以在SQL层面实现动态日期转列，无需API层介入。
• 若meta_date是固定范围的少数值，也可编写静态透视语句，但动态SQL更适配日期范围不固定的场景。
• 你已确保meta_date可分组，无需额外处理分组逻辑，直接套用上述方案即可。

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