SQL数据补全需求：按date_-name_-param_元组实现值填充规则

• 为每个date_与name_-param_元组的组合生成对应行；
• VALUE值填充规则：重复最后已知值，将NULL转换为0，若为全新行（即该date_、name_、param_组合从未存在且无历史记录）则显示NULL。

输入数据 #

NAME_   DATE_       PARAM_  VALUE
A       16-09-2023  A1      NULL
A       16-09-2023  A2      11
A       17-09-2023  A2      10
A       17-09-2023  A3      0
A       18-09-2023  A1      12
B       16-09-2023  B1      2
B       18-09-2023  B1      NULL
B       18-09-2023  B2      4

预期输出 #

NAME_   DATE_       PARAM   VALUE
A       16-09-2023  A1      0
A       16-09-2023  A2      11
A       16-09-2023  A3      NULL
A       17-09-2023  A1      0
A       17-09-2023  A2      10
A       17-09-2023  A3      0
A       18-09-2023  A1      12
A       18-09-2023  A2      10
A       18-09-2023  A3      0
B       16-09-2023  B1      2
B       16-09-2023  B2      NULL
B       17-09-2023  B1      2
B       17-09-2023  B2      NULL
B       18-09-2023  B1      0
B       18-09-2023  B2      4

尝试的SQL代码 #

WITH Dates AS (
  SELECT DISTINCT DATE_ AS date_value
  FROM test
),
NameParam AS (
  SELECT DISTINCT NAME_, PARAM_
  FROM test
)
SELECT
  npc.NAME_,
  d.date_value AS DATE_,
  npc.PARAM_,
  NVL(yt.VALUE_, 0) as VALUE_
FROM Dates d
CROSS JOIN NameParam npc
LEFT JOIN test yt
  ON d.date_value = yt.DATE_
  AND npc.NAME_ = yt.NAME_
  AND npc.PARAM_ = yt.PARAM_
ORDER BY npc.NAME_, d.date_value, npc.PARAM_

修正后的SQL代码 #

WITH AllCombinations AS (
    -- 生成所有name_、param_、date_的笛卡尔积组合
    SELECT 
        np.NAME_,
        np.PARAM_,
        d.DATE_
    FROM (SELECT DISTINCT NAME_, PARAM_ FROM test) np
    CROSS JOIN (SELECT DISTINCT DATE_ FROM test) d
),
ConvertedOriginal AS (
    -- 将原数据中的NULL转换为0
    SELECT 
        NAME_,
        PARAM_,
        DATE_,
        CASE WHEN VALUE IS NULL THEN 0 ELSE VALUE END AS converted_val
    FROM test
),
CombinedData AS (
    -- 关联组合数据与转换后的原数据，标记是否有历史记录
    SELECT 
        ac.NAME_,
        ac.PARAM_,
        ac.DATE_,
        co.converted_val,
        EXISTS (
            SELECT 1 FROM test t
            WHERE t.NAME_ = ac.NAME_ 
              AND t.PARAM_ = ac.PARAM_ 
              AND t.DATE_ <= ac.DATE_
        ) AS has_history
    FROM AllCombinations ac
    LEFT JOIN ConvertedOriginal co
        ON ac.NAME_ = co.NAME_
        AND ac.PARAM_ = co.PARAM_
        AND ac.DATE_ = co.DATE_
),
FilledValues AS (
    -- 按name_+param_分组，按日期排序，向前填充最后一个非空值
    SELECT 
        NAME_,
        PARAM_,
        DATE_,
        has_history,
        LAST_VALUE(converted_val IGNORE NULLS) OVER (
            PARTITION BY NAME_, PARAM_ 
            ORDER BY DATE_ 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS filled_val
    FROM CombinedData
)
SELECT 
    NAME_,
    DATE_,
    PARAM_ AS PARAM,
    -- 按规则输出最终VALUE：无历史则NULL，否则用填充值（空则取0）
    CASE 
        WHEN has_history = FALSE THEN NULL 
        ELSE COALESCE(filled_val, 0) 
    END AS VALUE
FROM FilledValues
ORDER BY NAME_, DATE_, PARAM_;

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