使用Oracle Model Clause将不存在的行添加到结果集中

izkcnapc  于 2023-06-05  发布在  Oracle
关注(0)|答案(2)|浏览(244)

鉴于表

D           V
--------------
2019-03-02, 13
2019-10-28, 12
2019-11-22, 34
2020-01-18, 21
2020-04-11, 39

我想添加一条日期为2019-12-31的记录,复制按年份分区的最后一条记录

2019-03-02, 13
2019-10-28, 12
2019-11-22, 34
2019-12-31, 34 <<
2020-01-18, 21
2020-04-11, 39
2020-12-31, 39 <<

如何利用示范条款做到这一点?我甚至不知道从哪里开始。

owfi6suc

owfi6suc1#

在阅读了model子句的示例、查看了语法并试图弄清楚如何将行插入到模型中之后,我得出的结论是,使用MODEL子句解决您的问题并不容易(可能是不可能的)。
然而,如果你想使用一个合适的方法来解决这个问题(而不是试图使用一个MODEL子句来解决它实际上并不是为之设计的),你可以使用一个递归的子查询因式分解子句:

WITH bounds (d, v, next_d) AS (
  SELECT d,
         v,
         LEAD(d, 1, SYSDATE) OVER ( ORDER BY d )
  FROM   table_name
UNION ALL
  SELECT ADD_MONTHS(TRUNC(d + INTERVAL '1' DAY, 'YY'), 12) - INTERVAL '1' DAY,
         v,
         next_d
  FROM   bounds
  WHERE  ADD_MONTHS(TRUNC(d + INTERVAL '1' DAY, 'YY'), 12) - INTERVAL '1' DAY
           < next_d
)
SEARCH DEPTH FIRST BY d SET d_order
SELECT d, v
FROM   bounds;

对于您的示例数据:

CREATE TABLE table_name (D, V) AS
SELECT DATE '2019-03-02', 13 FROM DUAL UNION ALL
SELECT DATE '2019-10-28', 12 FROM DUAL UNION ALL
SELECT DATE '2019-11-22', 34 FROM DUAL UNION ALL
SELECT DATE '2020-01-18', 21 FROM DUAL UNION ALL
SELECT DATE '2020-04-11', 39 FROM DUAL

输出:
| D| V|
| - -----|- -----|
| 2019-03-02 00:00:00|十三|
| 2019-10-28 00:00:00|十二岁|
| 2019-11-22 00:00:00|三十四|
| 2019-12-31 00:00:00|三十四|
| 2020-01-18 00:00:00|二十一|
| 2020-04-11 00:00:00|三十九|
| 2020-12-31 00:00:00|三十九|

mlnl4t2r

mlnl4t2r2#

可以使用模型:

with data(dat, val) as (
    select to_date('2019-03-02', 'yyyy-mm-dd'), 13 from dual union all
    select to_date('2019-10-28', 'yyyy-mm-dd'), 12 from dual union all
    select to_date('2019-11-22', 'yyyy-mm-dd'), 34 from dual union all
    select to_date('2020-01-18', 'yyyy-mm-dd'), 21 from dual union all
    select to_date('2020-04-11', 'yyyy-mm-dd'), 39 from dual -- union all
),
endofyear(dat, val) as (
    select to_date(y || '-12-31', 'yyyy-mm-dd'), null
    from (
        select distinct extract(year from dat) as y from data
    )
)
select dat, val from (
    select row_number() over(order by dat) as rn, d.* from (
        select * from endofyear e
        where not exists(select 1 from data d where e.dat = d.dat)
        union all
        select * from data
    ) d
) 
model
    dimension by( rn )
    measures( dat, val )
    rules (
        dat[ANY] = dat[cv()],
        val[ANY] = nvl(val[cv()], val[cv()-1])
    )
order by dat
;

02/03/2019 00:00:00 13
28/10/2019 00:00:00 12
22/11/2019 00:00:00 34
31/12/2019 00:00:00 34
18/01/2020 00:00:00 21
11/04/2020 00:00:00 39
31/12/2020 00:00:00 39

相关问题