oracle 用以前的值或邻近值填写缺失的天数

试图拿出SQL脚本，填补失踪的日期

对于NAME_和PARAM_的每个组合，
对于PARAM_ = ABOVE，将缺失值设置为PARAM_ = ABOVE的最后已知值
对于PARAM_ = NEXT，将缺失值设置为当前日期的PARAM_ = ABOVE值
如果没有初始PARAM_ = ABOVE，则假定值为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_

我不能让它工作：
fiddle

您可以使用PARTITION艾德OUTER JOIN为每个name_生成date_和param_的所有组合，然后使用MODEL子句基于以前的值生成连续值：

SELECT name_, date_, param_, value
FROM   (
  SELECT t.name_,
         d.date_,
         p.param_,
         COALESCE(
           t.value,
           CASE WHEN t.date_ IS NOT NULL AND t.param_ IS NOT NULL THEN 0 END
         ) AS value,
         ROW_NUMBER() OVER (PARTITION BY t.name_ ORDER BY d.date_, p.param_) AS rn
  FROM   (SELECT 'ABOV' AS param_ FROM DUAL UNION ALL
          SELECT 'NEXT' FROM DUAL) p
         CROSS JOIN
         (SELECT DISTINCT date_ FROM table_name) d
         LEFT OUTER JOIN table_name t
         PARTITION BY (t.name_)
         ON (   p.param_ = t.param_
            AND d.date_  = t.date_)
)
MODEL
  PARTITION BY (name_)
  DIMENSION BY (rn)
  MEASURES (date_, param_, value)  
  RULES (
    value[rn>1] = COALESCE(
                    value[cv()],
                    CASE param_[cv()]
                    WHEN 'ABOV'
                    THEN value[cv() - 2]
                    ELSE value[cv() - 1]
                    END
                  )
  )

其中，对于样本数据：

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;

输出：
| 产品名称_|日期_|参数_|值|
| --|--|--|--|
| 一|2023-09-25 00：00：00| ABOV| * 空 *|
| 一|2023-09-25 00：00：00|下| * 空 *|
| 一|2023-09-26 00：00：00| ABOV| 0 |
| 一|2023-09-26 00：00：00|下| 11 |
| 一|2023-09-27 00：00：00| ABOV| 0 |
| 一|2023-09-27 00：00：00|下| 10 |
| 一|2023-09-28 00：00：00| ABOV| 0 |
| 一|2023-09-28 00：00：00|下| 12 |
| B| 2023-09-25 00：00：00| ABOV| * 空 *|
| B| 2023-09-25 00：00：00|下| 2 |
| B| 2023-09-26 00：00：00| ABOV| * 空 *|
| B| 2023-09-26 00：00：00|下| * 空 *|
| B| 2023-09-27 00：00：00| ABOV| * 空 *|
| B| 2023-09-27 00：00：00|下| * 空 *|
| B| 2023-09-28 00：00：00| ABOV| 0 |
| B| 2023-09-28 00：00：00|下| 4 |
如果您只希望每个name_的日期范围从最小值到最大值date_，则：

WITH dates (name_, date_, end_date) AS (
  SELECT name_, MIN(date_), MAX(date_)
  FROM   table_name
  GROUP BY name_
UNION ALL
  SELECT name_, date_ + 1, end_date
  FROM   dates
  WHERE  date_ + 1 <= end_date
),
params (param_) AS (
  SELECT 'ABOV' AS param_ FROM DUAL UNION ALL
  SELECT 'NEXT' FROM DUAL
),
combined (name_, date_, param_, value, rn) AS (
  SELECT d.name_,
         d.date_,
         p.param_,
         COALESCE(
           t.value,
           CASE WHEN t.date_ IS NOT NULL AND t.param_ IS NOT NULL THEN 0 END
         ) AS value,
         ROW_NUMBER() OVER (PARTITION BY d.name_ ORDER BY d.date_, p.param_) AS rn
  FROM   params p
         CROSS JOIN dates d
         LEFT OUTER JOIN table_name t
         ON (   p.param_ = t.param_
            AND d.name_  = t.name_
            AND d.date_  = t.date_ )
)
SELECT name_, date_, param_, value
FROM   combined
MODEL
  PARTITION BY (name_)
  DIMENSION BY (rn)
  MEASURES (date_, param_, value)  
  RULES (
    value[rn>1] = COALESCE(
                    value[cv()],
                    CASE param_[cv()]
                    WHEN 'ABOV'
                    THEN value[cv() - 2]
                    ELSE value[cv() - 1]
                    END
                  )
  )

其输出：
| 产品名称_|日期_|参数_|值|
| --|--|--|--|
| 一|2023-09-26 00：00：00| ABOV| 0 |
| 一|2023-09-26 00：00：00|下| 11 |
| 一|2023-09-27 00：00：00| ABOV| 0 |
| 一|2023-09-27 00：00：00|下| 10 |
| 一|2023-09-28 00：00：00| ABOV| 0 |
| 一|2023-09-28 00：00：00|下| 12 |
| B| 2023-09-25 00：00：00| ABOV| * 空 *|
| B| 2023-09-25 00：00：00|下| 2 |
| B| 2023-09-26 00：00：00| ABOV| * 空 *|
| B| 2023-09-26 00：00：00|下| * 空 *|
| B| 2023-09-27 00：00：00| ABOV| * 空 *|
| B| 2023-09-27 00：00：00|下| * 空 *|
| B| 2023-09-28 00：00：00| ABOV| 0 |
| B| 2023-09-28 00：00：00|下| 4 |
fiddle

oracle 用以前的值或邻近值填写缺失的天数

1条答案

相关问题

热门标签

最新问答