Oracle SQL查询全年所有月份（含空值）报错ORA-01841及周维度实现

一、修复月份维度查询（解决ORA-01841错误+显示所有月份） #

错误原因分析 #

原SQL存在两个核心问题：

1. ORA-01841错误：MONAT_ZAEHLER中将日期转换为德语月份名称（字符串类型），但关联条件里直接和TRUNC(LP_BELEGUNG.GEN_DATUM, 'Month')（日期类型）比较，类型不匹配且字符串无法正确转换为有效日期，触发日期格式错误。
2. 无法显示无数据月份：WHERE条件TO_CHAR(GEN_DATUM, 'YYYY') = '2022'过滤掉了GEN_DATUM为NULL的行（即无数据的月份），导致这些维度无法展示。

修正后的SQL #

WITH MONAT_ZAEHLER AS (
    -- 生成2022年所有月份的起始日期（保留日期类型）
    SELECT ADD_MONTHS(TO_DATE('2022-01-01', 'YYYY-MM-DD'), LEVEL - 1) AS MONTH_DATE
    FROM DUAL
    CONNECT BY LEVEL <= 12
)
SELECT
    -- 将日期转成德语月份名称作为展示字段
    TO_CHAR(m.MONTH_DATE, 'Month', 'NLS_DATE_LANGUAGE = GERMAN') AS GRD_ROW_ID,
    COUNT(DISTINCT CASE WHEN lb.ART = 1 THEN lb.LP_BELEGUNG_ID END) AS "1",
    COUNT(DISTINCT CASE WHEN lb.ART = 2 THEN lb.LP_BELEGUNG_ID END) AS "2",
    COUNT(DISTINCT CASE WHEN lb.ART = 3 THEN lb.LP_BELEGUNG_ID END) AS "3",
    COUNT(DISTINCT CASE WHEN lb.ART = 99 THEN lb.LP_BELEGUNG_ID END) AS "99"
FROM MONAT_ZAEHLER m
-- 左连接确保所有月份都被保留
LEFT JOIN LP_BELEGUNG lb
    ON TRUNC(lb.GEN_DATUM, 'Month') = m.MONTH_DATE
    -- 年份条件移至JOIN的ON中，避免过滤无数据的月份
    AND TO_CHAR(lb.GEN_DATUM, 'YYYY') = '2022'
GROUP BY m.MONTH_DATE, TO_CHAR(m.MONTH_DATE, 'Month', 'NLS_DATE_LANGUAGE = GERMAN')
-- 按实际日期顺序排序，避免字符串排序混乱
ORDER BY m.MONTH_DATE;

关键修改点 #

• MONAT_ZAEHLER生成日期类型的月份起始日期，彻底避免类型不匹配错误。
• 使用LEFT JOIN替代FULL OUTER JOIN，仅保留目标年份的所有月份，无需保留无关数据行。
• 将年份过滤条件从WHERE移至JOIN的ON条件中，确保无数据的月份不会被过滤。
• 基于日期类型的MONTH_DATE分组和排序，避免字符串排序导致的月份顺序混乱（如"April"排在"August"之前）。

二、实现周维度统计展示 #

周定义说明 #

Oracle支持两种常见周规则：

1. ISO周：每周从周一开始，每年第一周至少包含4天，用IW格式符。
2. 默认周：每周从周日开始，用WW格式符。
   以下示例采用ISO周，可根据需求替换为WW。

周维度SQL代码 #

WITH WOCHE_ZAEHLER AS (
    -- 生成2022年所有ISO周的起始日期（周一）
    SELECT TRUNC(TO_DATE('2022-01-01', 'YYYY-MM-DD'), 'IW') + (LEVEL - 1)*7 AS WEEK_START_DATE
    FROM DUAL
    -- 生成足够覆盖一年的周数（最多53周）
    CONNECT BY LEVEL <= 53
    -- 过滤出属于2022年的周
    AND TRUNC(TO_DATE('2022-01-01', 'YYYY-MM-DD'), 'IW') + (LEVEL - 1)*7 <= TO_DATE('2022-12-31', 'YYYY-MM-DD')
)
SELECT
    -- 展示格式：年份-周数（如2022-W01）
    TO_CHAR(w.WEEK_START_DATE, 'YYYY-"W"IW') AS GRD_ROW_ID,
    COUNT(DISTINCT CASE WHEN lb.ART = 1 THEN lb.LP_BELEGUNG_ID END) AS "1",
    COUNT(DISTINCT CASE WHEN lb.ART = 2 THEN lb.LP_BELEGUNG_ID END) AS "2",
    COUNT(DISTINCT CASE WHEN lb.ART = 3 THEN lb.LP_BELEGUNG_ID END) AS "3",
    COUNT(DISTINCT CASE WHEN lb.ART = 99 THEN lb.LP_BELEGUNG_ID END) AS "99"
FROM WOCHE_ZAEHLER w
LEFT JOIN LP_BELEGUNG lb
    ON TRUNC(lb.GEN_DATUM, 'IW') = w.WEEK_START_DATE
    AND TO_CHAR(lb.GEN_DATUM, 'YYYY') = '2022'
GROUP BY w.WEEK_START_DATE, TO_CHAR(w.WEEK_START_DATE, 'YYYY-"W"IW')
ORDER BY w.WEEK_START_DATE;

关键说明 #

• WOCHE_ZAEHLER通过CONNECT BY生成最多53行周起始日期，再过滤掉超出2022年的周，确保覆盖全年所有周维度。
• 关联条件使用TRUNC(lb.GEN_DATUM, 'IW')将数据日期截断到ISO周起始日，与周维度表精准匹配。
• 展示字段采用YYYY-"W"IW格式，清晰标识年份和周数，便于阅读。

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