Oracle查询：按日期范围将周起始日期作为列名展示合同数量总和

核心思路 #

要实现需求，关键是先将每条合同的日期映射到其所属周的周一（作为周起始标识），再按周聚合求和，最后通过转列操作将周起始日期作为列名展示。Oracle的TRUNC(date, 'IW')函数可以直接将日期转换为对应ISO周的周一，完美匹配需求中的周定义（周一至周日）。

方案1：静态PIVOT（适合固定日期范围） #

如果查询日期范围固定，可直接写静态PIVOT语句快速得到结果：

SELECT 
    "Wk_27-Nov-23", 
    "Wk_04-Dec-23", 
    "Wk_11-Dec-23"
FROM (
    -- 给每条记录标记所属周的列名（Wk_+周一日期）
    SELECT 
        'Wk_' || TO_CHAR(TRUNC(CDATE, 'IW'), 'DD-Mon-RR') AS week_label,
        QTY
    FROM A
    -- 过滤指定日期范围内的记录
    WHERE CDATE BETWEEN TO_DATE(:startdate, 'DD-Mon-RR') AND TO_DATE(:enddate, 'DD-Mon-RR')
)
PIVOT (
    -- 按周求和QTY
    SUM(QTY)
    -- 指定要转成列的周标签
    FOR week_label IN (
        'Wk_27-Nov-23' AS "Wk_27-Nov-23",
        'Wk_04-Dec-23' AS "Wk_04-Dec-23",
        'Wk_11-Dec-23' AS "Wk_11-Dec-23"
    )
);

方案2：动态SQL（适合任意日期范围） #

如果日期范围是动态输入的，需要自动生成对应周的列名，用动态SQL实现更灵活：

DECLARE
    v_start DATE := TO_DATE(:startdate, 'DD-Mon-RR');
    v_end DATE := TO_DATE(:enddate, 'DD-Mon-RR');
    v_pivot_columns VARCHAR2(1000);
    v_sql VARCHAR2(2000);
BEGIN
    -- 生成所有需要的周列名（自动遍历日期范围内的所有周一起始）
    SELECT LISTAGG(
        '''Wk_' || TO_CHAR(week_start, 'DD-Mon-RR') || ''' AS "Wk_' || TO_CHAR(week_start, 'DD-Mon-RR') || '"', 
        ', '
    ) INTO v_pivot_columns
    FROM (
        -- 生成日期范围内的所有周一起始日期
        SELECT TRUNC(v_start, 'IW') + (LEVEL - 1)*7 AS week_start
        FROM dual
        CONNECT BY TRUNC(v_start, 'IW') + (LEVEL - 1)*7 <= TRUNC(v_end, 'IW')
    );

    -- 拼接动态SQL语句
    v_sql := '
        SELECT *
        FROM (
            SELECT 
                ''Wk_'' || TO_CHAR(TRUNC(CDATE, ''IW''), ''DD-Mon-RR'') AS week_label,
                QTY
            FROM A
            WHERE CDATE BETWEEN :start_date AND :end_date
        )
        PIVOT (
            SUM(QTY)
            FOR week_label IN (' || v_pivot_columns || ')
        )
    ';

    -- 执行动态SQL并输出结果
    EXECUTE IMMEDIATE v_sql USING v_start, v_end;
END;
/

关键函数说明 #

• TRUNC(CDATE, 'IW')：将日期转换为对应ISO周的周一（ISO周定义为周一至周日），是实现周分组的核心。
• PIVOT：Oracle的行转列函数，用于将按周分组的行结果转换为列展示。
• LISTAGG：用于动态拼接PIVOT所需的列名列表，实现动态列的生成。

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