By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;
7 rows affected
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
)
)
| NAME_ | DATE_ | PARAM_ | VALUE |
|---|---|---|---|
| A | 2023-09-25 00:00:00 | ABOV | null |
| A | 2023-09-25 00:00:00 | NEXT | null |
| A | 2023-09-26 00:00:00 | ABOV | 0 |
| A | 2023-09-26 00:00:00 | NEXT | 11 |
| A | 2023-09-27 00:00:00 | ABOV | 0 |
| A | 2023-09-27 00:00:00 | NEXT | 10 |
| A | 2023-09-28 00:00:00 | ABOV | 0 |
| A | 2023-09-28 00:00:00 | NEXT | 12 |
| B | 2023-09-25 00:00:00 | ABOV | null |
| B | 2023-09-25 00:00:00 | NEXT | 2 |
| B | 2023-09-26 00:00:00 | ABOV | null |
| B | 2023-09-26 00:00:00 | NEXT | null |
| B | 2023-09-27 00:00:00 | ABOV | null |
| B | 2023-09-27 00:00:00 | NEXT | null |
| B | 2023-09-28 00:00:00 | ABOV | 0 |
| B | 2023-09-28 00:00:00 | NEXT | 4 |
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 (
| NAME_ | DATE_ | PARAM_ | VALUE |
|---|---|---|---|
| A | 2023-09-26 00:00:00 | ABOV | 0 |
| A | 2023-09-26 00:00:00 | NEXT | 11 |
| A | 2023-09-27 00:00:00 | ABOV | 0 |
| A | 2023-09-27 00:00:00 | NEXT | 10 |
| A | 2023-09-28 00:00:00 | ABOV | 0 |
| A | 2023-09-28 00:00:00 | NEXT | 12 |
| B | 2023-09-25 00:00:00 | ABOV | null |
| B | 2023-09-25 00:00:00 | NEXT | 2 |
| B | 2023-09-26 00:00:00 | ABOV | null |
| B | 2023-09-26 00:00:00 | NEXT | null |
| B | 2023-09-27 00:00:00 | ABOV | null |
| B | 2023-09-27 00:00:00 | NEXT | null |
| B | 2023-09-28 00:00:00 | ABOV | 0 |
| B | 2023-09-28 00:00:00 | NEXT | 4 |