求助：编写SQL脚本补全缺失日期并按指定规则填充字段值

需求 #

• 针对每个NAME_和PARAM_的组合补全缺失的日期；
• 当PARAM_为ABOV时，缺失值填充为该PARAM_的最近已知值；
• 当PARAM_为NEXT时，缺失值填充为当前日期对应的ABOV参数值；
• 若初始无ABOV参数值，则默认值为NULL；
• 将输入中的NULL转换为0。

输入表结构及数据 #

CREATE TABLE table_name (NAME_, DATE_, PARAM_, VALUE) AS
  SELECT 'A', DATE '2023-09-26', 'ABOV', NULL FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-26', 'NEXT', 11 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-27', 'NEXT', 10 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-28', 'NEXT', 12 FROM DUAL UNION ALL

  SELECT 'B', DATE '2023-09-25', 'NEXT', 2 FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-28', 'ABOV', NULL FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-28', 'NEXT', 4 FROM DUAL;

期望输出表结构及数据 #

CREATE TABLE table_name2 (NAME_, DATE_, PARAM_, VALUE) AS
  SELECT 'A', DATE '2023-09-26', 'ABOV', 0 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-26', 'NEXT', 11 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-27', 'ABOV', 0 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-27', 'NEXT', 10 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-28', 'ABOV', 0 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-28', 'NEXT', 12 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-29', 'ABOV', 0 FROM DUAL UNION ALL
  SELECT 'A', DATE '2023-09-29', 'NEXT', 0 FROM DUAL UNION ALL

  SELECT 'B', DATE '2023-09-25', 'ABOV', NULL FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-25', 'NEXT', 2 FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-26', 'ABOV', NULL FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-26', 'NEXT', NULL FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-27', 'ABOV', NULL FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-27', 'NEXT', NULL FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-28', 'ABOV', 0 FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-28', 'NEXT', 4 FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-29', 'ABOV', 0 FROM DUAL UNION ALL
  SELECT 'B', DATE '2023-09-29', 'NEXT', 0 FROM DUAL;

尝试的错误代码 #

以下代码无法正常运行：

SELECT
  t.NAME_,
  t.DATE_,
  t.PARAM_,
  LAST_VALUE(VALUE_) IGNORE NULLS OVER (
    PARTITION BY t.NAME_, t.PARAM_ ORDER BY d.DATE_) AS VALUE_

FROM
    (SELECT DISTINCT DATE_ FROM table_name) d
    LEFT OUTER JOIN
    (SELECT NAME_, DATE_, PARAM_, COALESCE(VALUE, 0) as VALUE_  
    FROM table_name
    WHERE PARAM_ in ('ABOV', 'NEXT')) t

PARTITION BY
    (NAME_, PARAM_) ON (t.DATE_ = d.DATE_)

ORDER BY
    NAME_, PARAM_, DATE_

解决方案 #

正确的SQL脚本如下，核心逻辑说明：

1. 生成每个NAME_对应的完整日期范围（覆盖最小日期到最大日期+1，匹配期望输出的日期区间）；
2. 构建NAME_、日期、PARAM_的全量组合，补全所有缺失的日期行；
3. 处理ABOV参数的最近已知值，同时保留初始无ABOV记录时的NULL；
4. 针对NEXT参数，关联当前日期对应的ABOV值，原表已有值则优先保留；
5. 最后将非初始缺失的NULL转换为0，初始无ABOV的情况保持NULL。

WITH date_ranges AS (
    SELECT 
        NAME_,
        MIN(DATE_) AS min_date,
        MAX(DATE_) + 1 AS max_date
    FROM table_name
    GROUP BY NAME_
),
all_dates AS (
    SELECT 
        dr.NAME_,
        dr.min_date + LEVEL - 1 AS DATE_
    FROM date_ranges dr
    CONNECT BY LEVEL <= dr.max_date - dr.min_date + 1
    AND PRIOR NAME_ = NAME_
    AND PRIOR SYS_GUID() IS NOT NULL
),
all_combinations AS (
    SELECT 
        ad.NAME_,
        ad.DATE_,
        param.PARAM_
    FROM all_dates ad
    CROSS JOIN (SELECT DISTINCT PARAM_ FROM table_name WHERE PARAM_ IN ('ABOV', 'NEXT')) param
),
abov_data AS (
    SELECT 
        ac.NAME_,
        ac.DATE_,
        ac.PARAM_,
        LAST_VALUE(CASE WHEN t.VALUE IS NOT NULL THEN COALESCE(t.VALUE, 0) END IGNORE NULLS) 
            OVER (PARTITION BY ac.NAME_, ac.PARAM_ ORDER BY ac.DATE_) AS abov_value
    FROM all_combinations ac
    LEFT JOIN table_name t 
        ON ac.NAME_ = t.NAME_ 
        AND ac.DATE_ = t.DATE_ 
        AND ac.PARAM_ = t.PARAM_
    WHERE ac.PARAM_ = 'ABOV'
),
next_data AS (
    SELECT 
        ac.NAME_,
        ac.DATE_,
        ac.PARAM_,
        COALESCE(t.VALUE, (SELECT abov_value FROM abov_data a WHERE a.NAME_ = ac.NAME_ AND a.DATE_ = ac.DATE_)) AS next_value
    FROM all_combinations ac
    LEFT JOIN table_name t 
        ON ac.NAME_ = t.NAME_ 
        AND ac.DATE_ = t.DATE_ 
        AND ac.PARAM_ = t.PARAM_
    WHERE ac.PARAM_ = 'NEXT'
),
combined_data AS (
    SELECT NAME_, DATE_, PARAM_, abov_value AS VALUE FROM abov_data
    UNION ALL
    SELECT NAME_, DATE_, PARAM_, next_value AS VALUE FROM next_data
)
SELECT 
    NAME_,
    DATE_,
    PARAM_,
    CASE 
        WHEN PARAM_ = 'ABOV' AND VALUE IS NULL AND NOT EXISTS (SELECT 1 FROM table_name t WHERE t.NAME_ = combined_data.NAME_ AND t.PARAM_ = 'ABOV' AND t.DATE_ <= combined_data.DATE_) THEN NULL
        ELSE COALESCE(VALUE, 0)
    END AS VALUE
FROM combined_data
ORDER BY NAME_, DATE_, PARAM_;

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