分層查詢：connect_by_root不給根值-有解無憂

我在這里有一個像這樣的資料集：

BP_ID	VALID_FROM	有效	限制
1	15.05.1999	2000 年 7 月 16 日	100
1	01.01.2020	10.01.2020	100
1	10.01.2020	31.12.9999	100

我想根據這個 BP_ID 的 LIMIT 隨時間沒有變化的事實“聚合”到單個記錄：

BP_ID	VALID_FROM	有效	限制
1	15.05.1999	2000 年 7 月 16 日	100
1	01.01.2020	31.12.9999	100

我認為實作這一點的一個好主意是使用分層查詢并找到根valid_from。所以我嘗試了：

with pretab as (

select 1 as bp_id, 
       to_date('15.05.1999', 'dd.mm.yyyy') as valid_from,
       to_date('16.07.2000', 'dd.mm.yyyy') as valid_to,
       100 as limit 
  from dual
union
    select 1 as bp_id, 
           to_date('01.01.2020', 'dd.mm.yyyy') as valid_from,
           to_date('10.01.2020', 'dd.mm.yyyy') as valid_to,
           100 as limit 
      from dual
union
    select 1 as bp_id, 
           to_date('10.01.2020', 'dd.mm.yyyy') as valid_from,
          to_date('31.12.9999', 'dd.mm.yyyy') as valid_to,
           100 as limit 
      from dual

),
pretab2 as (
select t1.*, case when 
                  valid_to != coalesce(lead(valid_from) over (partition by bp_id order by valid_from), to_date('31.12.9999', 'dd.mm.yyyy'))
                  or limit !=  coalesce(lead(limit) over (partition by bp_id order by valid_from), 0)                  
              then 1 
              else 0 end as start_dummy from pretab t1
)


select bp_id, connect_by_root(valid_from), valid_to, limit from pretab2
CONNECT BY PRIOR bp_id = bp_id
and prior trunc(valid_to) = trunc(valid_from)
and prior limit = limit
start with start_dummy = 1;

不幸的是，第二行沒有為 connect_by_root() 回傳 01.01.2020。這是為什么？我將如何更改查詢以便它給我這個值？

uj5u.com熱心網友回復：

從 Oracle 12 開始，您可以使用MATCH_RECOGNIZE來執行逐行處理：

with pretab (bp_id, valid_from, valid_to, limit) as (
  select 1, DATE '1999-05-15', DATE '2020-01-01', 100 from dual union all
  select 1, DATE '2000-01-01', DATE '2000-01-10', 100 from dual union all
  select 1, DATE '2000-01-10', DATE '9999-12-31', 100 from dual union all
  select 2, DATE '2000-01-01', DATE '2001-12-31', 100 from dual union all
  select 2, DATE '2002-01-01', DATE '2002-12-31', 100 from dual union all
  select 2, DATE '2003-01-01', DATE '2003-12-31', 200 from dual union all
  select 2, DATE '2004-01-01', DATE '2006-12-31', 100 from dual union all
  select 2, DATE '2005-01-01', DATE '2005-12-31', 100 from dual
)
SELECT *
FROM   pretab
MATCH_RECOGNIZE(
  PARTITION BY bp_id
  ORDER     BY valid_from, valid_to
  MEASURES
    FIRST(limit)      AS limit,
    FIRST(valid_from) AS valid_from,
    MAX(valid_to)     AS valid_to
  PATTERN (same_overlapping_limit )
  DEFINE
    same_overlapping_limit AS
          limit      = FIRST(limit)
      AND valid_from - INTERVAL '1' DAY <= MAX(same_overlapping_limit.valid_to)
)

其中，輸出：

BP_ID 限制 VALID_FROM 有效

1 100 1999-05-15 00:00:00 9999-12-31 00:00:00

2 100 2000-01-01 00:00:00 2002-12-31 00:00:00

2 200 2003-01-01 00:00:00 2003-12-31 00:00:00

2 100 2004-01-01 00:00:00 2006-12-31 00:00:00

BP_ID	限制	VALID_FROM	有效
1	100	1999-05-15 00:00:00	9999-12-31 00:00:00
2	100	2000-01-01 00:00:00	2002-12-31 00:00:00
2	200	2003-01-01 00:00:00	2003-12-31 00:00:00
2	100	2004-01-01 00:00:00	2006-12-31 00:00:00

db<>在這里擺弄

uj5u.com熱心網友回復：

正確CONNECT BY的版本可能看起來像

with pretab as (

select 1 as bp_id, 
       to_date('15.05.1999', 'dd.mm.yyyy') as valid_from,
       to_date('01.01.2000', 'dd.mm.yyyy') as valid_to,
       100 as limit 
  from dual
union
    select 1 as bp_id, 
           to_date('01.01.2000', 'dd.mm.yyyy') as valid_from,
           to_date('10.01.2000', 'dd.mm.yyyy') as valid_to,
           100 as limit 
      from dual
union
    select 1 as bp_id, 
           to_date('10.01.2000', 'dd.mm.yyyy') as valid_from,
          to_date('31.12.9999', 'dd.mm.yyyy') as valid_to,
           100 as limit 
      from dual
union
select 2 as bp_id, 
       to_date('15.05.1999', 'dd.mm.yyyy') as valid_from,
       to_date('01.01.2002', 'dd.mm.yyyy') as valid_to,
       200 as limit 
  from dual
union
    select 2 as bp_id, 
           to_date('01.01.2002', 'dd.mm.yyyy') as valid_from,
           to_date('10.01.2002', 'dd.mm.yyyy') as valid_to,
           200 as limit 
      from dual
union
    select 2 as bp_id, 
           to_date('10.01.2002', 'dd.mm.yyyy') as valid_from,
          to_date('31.12.9999', 'dd.mm.yyyy') as valid_to,
           200 as limit 
      from dual
),
pretab2 as (
select t1.*, case when 
                  valid_from != lag(valid_to, 1, valid_from - 1) over (partition by bp_id order by valid_from)
                  or limit != lag(limit, 1, limit - 1) over (partition by bp_id order by valid_from)                  
              then 1 
              else 0 end as start_dummy 
from pretab t1
)
select bp_id, valid_from, valid_to, limit
from (
   select bp_id, connect_by_root(valid_from) valid_from, valid_to, limit, row_number() over(partition by bp_id order by level desc) rn
   from pretab2
   CONNECT BY PRIOR bp_id = bp_id
      and prior trunc(valid_to) = trunc(valid_from)
      and prior limit = limit 
   start with start_dummy = 1
) where rn = 1;

退貨

BP_ID   VALID_FROM  VALID_TO    LIMIT
1   1999-05-15 00:00:00 9999-12-31 00:00:00 100
2   1999-05-15 00:00:00 9999-12-31 00:00:00 200

uj5u.com熱心網友回復：

您的計算似乎start_dummy沒有回傳查詢應回傳的正確行。

如果您想使用CONNECT BY，這里有一個示例，它將回傳您期望的結果：

WITH
    pretab
    AS
        (SELECT 1                                        AS bp_id,
                TO_DATE ('15.05.1999', 'dd.mm.yyyy')     AS valid_from,
                TO_DATE ('01.01.2020', 'dd.mm.yyyy')     AS valid_to,
                100                                      AS LIMIT
           FROM DUAL
         UNION
         SELECT 1                                        AS bp_id,
                TO_DATE ('01.01.2000', 'dd.mm.yyyy')     AS valid_from,
                TO_DATE ('10.01.2000', 'dd.mm.yyyy')     AS valid_to,
                100                                      AS LIMIT
           FROM DUAL
         UNION
         SELECT 1                                        AS bp_id,
                TO_DATE ('10.01.2000', 'dd.mm.yyyy')     AS valid_from,
                TO_DATE ('31.12.9999', 'dd.mm.yyyy')     AS valid_to,
                100                                      AS LIMIT
           FROM DUAL)
    SELECT bp_id,
           CONNECT_BY_ROOT (valid_from),
           valid_to,
           LIMIT
      FROM pretab
CONNECT BY     PRIOR bp_id = bp_id
           AND PRIOR TRUNC (valid_to) = TRUNC (valid_from)
           AND PRIOR LIMIT = LIMIT
  ORDER BY LEVEL DESC
     FETCH FIRST 1 ROWS ONLY;

另一個可行的解決方案是使用GROUP BY. 僅當您確信日期中沒有任何間隔時，這才有效。

WITH
    pretab
    AS
        (SELECT 1                                        AS bp_id,
                TO_DATE ('15.05.1999', 'dd.mm.yyyy')     AS valid_from,
                TO_DATE ('01.01.2020', 'dd.mm.yyyy')     AS valid_to,
                100                                      AS LIMIT
           FROM DUAL
         UNION
         SELECT 1                                        AS bp_id,
                TO_DATE ('01.01.2000', 'dd.mm.yyyy')     AS valid_from,
                TO_DATE ('10.01.2000', 'dd.mm.yyyy')     AS valid_to,
                100                                      AS LIMIT
           FROM DUAL
         UNION
         SELECT 1                                        AS bp_id,
                TO_DATE ('10.01.2000', 'dd.mm.yyyy')     AS valid_from,
                TO_DATE ('31.12.9999', 'dd.mm.yyyy')     AS valid_to,
                100                                      AS LIMIT
           FROM DUAL)
  SELECT bp_id,
         MIN (valid_from),
         MAX (valid_to),
         LIMIT
    FROM pretab
GROUP BY bp_id, LIMIT;

uj5u.com熱心網友回復：

再次 - 如果您的資料沒有間隙并且一切都應該是連續的，您可以嘗試“會話化”：添加每次“限制”時遞增的會話 ID（我將其重命名為 lim 因為它可以成為保留字）變化。首先，使用 OLAP函式添加一個計數器，如果它lim與以前的不同，則該計數器會發生變化。其次，您在外部查詢中使用計數器對其執行運行求和。這會給你會話ID。最后，從帶有會話 id 的查詢中進行選擇，并獲得從日期開始的最小值和到日期的最大值，按、和分組。limLAG()bp_idlimsession_id

WITH
indata(bp_id,valid_from,valid_to,lim) as (                                             
          SELECT 1,DATE '1999-05-15',DATE '2020-01-01',100 FROM dual
UNION ALL SELECT 1,DATE '2020-01-01',DATE '2020-01-10',100 FROM dual
UNION ALL SELECT 1,DATE '2020-01-10',DATE '2020-05-15',100 FROM dual
UNION ALL SELECT 1,DATE '2020-05-15',DATE '2021-01-01',200 FROM dual
UNION ALL SELECT 1,DATE '2021-01-01',DATE '2021-01-10',200 FROM dual
UNION ALL SELECT 1,DATE '2021-01-10',DATE '9999-12-31',200 FROM dual
)
,
with_counter AS (
  SELECT
    bp_id
  , valid_from
  , valid_to
  , lim
  , CASE WHEN lim <> LAG(lim) OVER(PARTITION BY bp_id ORDER BY valid_from)
      THEN 1
      ELSE 0
    END AS counter
  FROM indata
)
,
with_session_id AS (
  SELECT
    bp_id
  , valid_from
  , valid_to
  , lim
  , SUM(counter) OVER(PARTITION BY bp_id ORDER BY valid_from) AS session_id
  FROM with_counter
)
SELECT
  bp_id
, MIN(valid_from) AS valid_from
, MAX(valid_to)   AS valid_to
, lim
FROM with_session_id
GROUP BY
  bp_id
, lim
, session_id;
-- out  bp_id | valid_from |  valid_to  | lim 
-- out ------- ------------ ------------ -----
-- out      1 | 1999-05-15 | 2020-05-15 | 100
-- out      1 | 2020-05-15 | 9999-12-31 | 200

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/478816.html

標籤：sql 甲骨文分层数据

上一篇：在Oracle11g中使用假脫機不會將SELECT查詢的結果發送到檔案

下一篇：Oracle的SQL子查詢COUNT