用于傳播幀值的SQL視窗函式

我有一個下表（專案）：

如果行在 FrameStart 和 FrameEnd 之間，則有沒有辦法添加帶有 frame id 的第 4 列，否則為 null：

我試著像下面這樣

SELECT 
index, 
type, 
id, 
CASE WHEN (type = 'FrameStart') THEN id WHEN (type = 'FrameEnd') THEN null ELSE LAG(test) OVER(ORDER BY index)  END as test  
FROM Items 

但是，當然LAG(test)不能使用。

uj5u.com熱心網友回復：

從 Oracle 12c 開始，您可以使用MATCH_RECOGNIZE：

SELECT id,
       "INDEX",
       type,
       CASE type
       WHEN 'FrameEnd' THEN NULL
       ELSE test
       END AS test
FROM   table_name
MATCH_RECOGNIZE (
  ORDER BY "INDEX"
  MEASURES
    framestart.id AS test
  ALL ROWS PER MATCH
  PATTERN ( framestart other*? frameend | other )
  DEFINE
    framestart AS type = 'FrameStart',
    frameend   AS type = 'FrameEnd',
    other      AS 1 = 1
)

或者，PATTERN ( framestart other*? (frameend | $) | other )如果您還想匹配framestart不帶frameend.

其中，對于樣本資料：

CREATE TABLE table_name ("INDEX", type, id) AS
SELECT 1, 'Other',      2244596 FROM DUAL UNION ALL
SELECT 2, 'FrameStart',     888 FROM DUAL UNION ALL
SELECT 3, 'Other',       235235 FROM DUAL UNION ALL
SELECT 4, 'Other',      8957567 FROM DUAL UNION ALL
SELECT 5, 'Other',        14124 FROM DUAL UNION ALL
SELECT 6, 'FrameEnd',         0 FROM DUAL UNION ALL
SELECT 7, 'Other',       275823 FROM DUAL UNION ALL
SELECT 8, 'Other',       789798 FROM DUAL;

注意：不要使用INDEX（或其他保留字）作為列名。

輸出：

ID	指數	型別	測驗
2244596	1	其他	空值
888	2	幀開始	888
235235	3	其他	888
8957567	4	其他	888
14124	5	其他	888
0	6	幀尾	空值
275823	7	其他	空值
789798	8	其他	空值

db<>在這里擺弄

uj5u.com熱心網友回復：

@MT0 以更好的答案擊敗了我，但這里有一個使用視窗函式的選項......

WITH
  framed AS
(
  SELECT
    items.*,
    SUM(CASE WHEN type IN ('FrameStart', 'FrameEnd') THEN 1 ELSE 0 END) OVER (ORDER BY ix)   AS frame_id
  FROM
    items
)
SELECT
  framed.*,
  MAX(CASE WHEN type = 'FrameStart' THEN id END) OVER (PARTITION BY frame_id)
FROM
  framed
ORDER BY
  ix

演示：https : //dbfiddle.uk/?rdbms=oracle_21&fiddle=b8a0150b46315256f189506c5fb76fc5

uj5u.com熱心網友回復：

如果您的index列是一個真正的序列，您可以使用遞回 CTE（如果不是，您必須添加額外的 row_number() 列并使用它）。

您吉斯通過對表index，當你看到你FramaStart保持ID，當你看到你FragmeEnd復位，在所有其他情況下，您復制以前的值。

例子

with t1 ( "INDEX", type, id, test) as (
select 
 "INDEX", type, id,
 case when type = 'FrameStart' then id end as test
from tab1 where "INDEX" = (select min("INDEX") from tab1)
union all
select 
  tab1."INDEX", tab1.type, tab1.id,
  case 
     when tab1.type = 'FrameStart' then tab1.id
     when tab1.type = 'FrameEnd' then null else t1.test end
from  tab1
join t1 on t1."INDEX" 1 = tab1."INDEX" 
)
select * from t1;

     INDEX TYPE               ID       TEST
---------- ---------- ---------- ----------
         1 Other         2244596           
         2 FrameStart        888        888
         3 Other          235235        888
         4 Other         8957567        888
         5 Other           14124        888
         6 FrameEnd            0           
         7 Other          275823           
         8 Other          789798 

uj5u.com熱心網友回復：

添加該列后，可以通過合并對其進行更新。

下面的代碼獲取計算排名的第一個值。

alter table Items add test number;

MERGE INTO Items Tgt
USING
(
  SELECT "index"
  , FIRST_VALUE(CASE WHEN "type" LIKE 'Frame%Start' THEN id END) 
          OVER (PARTITION BY Rnk ORDER BY "index") as FrameStartId
  FROM 
  (
    SELECT "index", "type", id
    , SUM(CASE WHEN "type" LIKE 'Frame%' THEN 1 ELSE 0 END) 
          OVER (ORDER BY "index") AS Rnk
    FROM Items itm
    ORDER BY "index"
  ) q
) Src
ON (Tgt."index" = Src."index" AND Src.FrameStartId IS NOT NULL) 
WHEN MATCHED THEN
UPDATE SET test = Src.FrameStartId;

select *
from Items
order by "index"

索引 | 型別 | 身份證 | 測驗
----: | :--------- | ------: | ---：
    1 | 其他 | 2244596 | 空值
    2 | 幀開始 | 第888話 888
    3 | 其他 | 235235 | 888
    4 | 其他 | 8957567 | 888
    5 | 其他 | 14124 | 888
    6 | 幀結束 | 0 | 空
    7 | 其他 | 275823 | 空
    8 | 其他 | 789798 | 空值

關于db<>fiddle 的演示在這里

uj5u.com熱心網友回復：

為 Sql Server 解決了它我認為它在 Oracle 中會類似：

;with start_end_frames as
(
    select indx,type,id
    from Items
    where type = 'FrameStart' or type = 'FrameEnd'
)
, match_start_end as
(
    select indx, 
           lead(indx)over(order by indx) as nextIndx,
           type,
           lead(type)over(order by indx) as nextType,
           id
    from start_end_frames
)
, frame_intervals as
(
    select indx,nextIndx,id 
    from match_start_end
    where type = 'FrameStart' and nextType = 'FrameEnd'
)
select i.indx,i.type,i.id,f.id
from  frame_intervals f right join  Items i
on f.indx <= i.indx and  i.indx  < f.nextIndx

標籤：sql 甲骨文 窗函数

上一篇：是否可以在Oracle的檢查約束中使用多個條件？

下一篇：PLSQL：求和不存在？