Oracle SQL如何按ID分组生成带缺失日期空值补位的逗号分隔值

核心实现逻辑 #

要解决缺失日期补Null的问题，核心是先构建全量日期+全量id的基准维度表，确保每个id都对应完整的日期序列，没有缺口，再关联原表补value，最后拼接时自然会在缺失位置填充占位值，顺序完全和日期序列对齐。

完整实现代码 #

WITH 
-- 1. 生成业务需要覆盖的连续日期序列，示例为按月粒度，可根据需求调整为日/季度等
date_range AS (
    SELECT ADD_MONTHS(min_month, LEVEL - 1) AS month_dt
    FROM (
        SELECT 
            TRUNC(MIN(datetime), 'MONTH') AS min_month,
            TRUNC(MAX(datetime), 'MONTH') AS max_month
        FROM your_table -- 替换为你的实际表名
    )
    CONNECT BY ADD_MONTHS(min_month, LEVEL - 1) <= max_month
),
-- 2. 提取所有不重复的id
id_distinct AS (
    SELECT DISTINCT id FROM your_table
),
-- 3. 生成每个id对应全量日期的基准行，没有数据的位置先留空
id_full_date AS (
    SELECT i.id, d.month_dt
    FROM id_distinct i
    CROSS JOIN date_range d
),
-- 4. 左关联原表补value，缺失的日期对应value为null
filled_value AS (
    SELECT 
        f.id,
        f.month_dt,
        t.value
    FROM id_full_date f
    LEFT JOIN your_table t
        ON f.id = t.id
        AND TRUNC(t.datetime, 'MONTH') = f.month_dt -- 关联规则和日期粒度保持一致
)
-- 5. 按id分组拼接，按日期排序，缺失位置填充Null字符串
SELECT
    id,
    LISTAGG(NVL(TO_CHAR(value), 'Null'), ',') WITHIN GROUP (ORDER BY month_dt) AS grouped_values
FROM filled_value
GROUP BY id;

注意事项 #

• 如果你的日期粒度是天，只需把所有TRUNC(xxx, 'MONTH')改为TRUNC(xxx, 'DAY')，ADD_MONTHS改为日期加减即可
• 如果业务日期范围是固定值，可直接写死date_range的起止日期，无需从原表取最大最小日期
• 若拼接结果长度超过Oracle varchar2的4000字符限制，可将LISTAGG替换为XMLAGG写法，逻辑完全一致：

  SELECT
      id,
      RTRIM(XMLAGG(XMLELEMENT(e, NVL(TO_CHAR(value), 'Null') || ',').EXTRACT('//text()') ORDER BY month_dt).GETCLOBVAL(), ',') AS grouped_values
  FROM filled_value
  GROUP BY id;
  

• 如不需要显示'Null'字符串，要留空占位，直接将NVL(TO_CHAR(value), 'Null')改为NVL(TO_CHAR(value), '')即可

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