如何将Python实现的逻辑转为Redshift单条SQL查询？

要实现将2022年所有日期作为列名、展示账户每日余额的单条查询，核心是动态生成日期序列并转置为列——静态SQL无法处理动态列名，需要借助Redshift的动态SQL能力。以下是可行方案：

方案代码 #

DECLARE
    date_columns VARCHAR(MAX);
BEGIN
    -- 生成2022年所有日期的YYYY_MM_DD格式字符串，拼接为PIVOT所需的列列表
    SELECT STRING_AGG('''' || TO_CHAR(date_series, 'YYYY_MM_DD') || '''', ', ')
    INTO date_columns
    FROM (
        SELECT DATE '2022-01-01' + (n || ' days')::INTERVAL AS date_series
        FROM generate_series(0, 364) n
        WHERE DATE '2022-01-01' + (n || ' days')::INTERVAL <= DATE '2022-12-31'
    ) dates;

    -- 执行动态PIVOT查询
    EXECUTE '
        SELECT *
        FROM (
            SELECT 
                account_id,
                TO_CHAR(balance_date, ''YYYY_MM_DD'') AS date_col,
                balance
            FROM loan__balance_table
            WHERE balance_date BETWEEN ''2022-01-01'' AND ''2022-12-31''
        ) src
        PIVOT (
            MAX(balance)  -- 若单账户单日仅一条余额记录，MAX/AVG/SUM结果一致
            FOR date_col IN (' || date_columns || ')
        ) piv;
    ';
END;

关键说明 #

1. 日期序列生成：

   • 用generate_series(0, 364)生成0到364的整数（2022是平年，共365天），通过日期运算得到全年每一天。
   • 用TO_CHAR将日期格式化为YYYY_MM_DD字符串，并用STRING_AGG拼接成带单引号的逗号分隔列表，作为PIVOT的列参数。

2. 动态PIVOT转置：

   • 先从原表筛选2022年的余额数据，将日期格式化为统一字符串后，通过PIVOT将行转列，每个日期作为一列展示对应账户的余额。
   • 聚合函数MAX(balance)是PIVOT的必填项，若单账户单日仅一条余额记录，使用MAX/MIN/SUM/AVG均可，结果无差异。

3. 适配原表结构：

   • 若原表loan__balance_table的日期字段（如balance_date）已经是YYYY_MM_DD格式的字符串，可直接去掉TO_CHAR转换，用原字段作为date_col。

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