I have a following table (Items):

Is there a way to add a 4th column with frame id if rows are between FrameStart and FrameEnd and null otherwise:

I tried to do it like the following

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 

but, of course, LAG(test) cannot be used.

CodePudding user response：

From Oracle 12c, you can use 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
)

Or you can use PATTERN ( framestart other*? (frameend | $) | other ) if you want to also match a trailing framestart without a frameend.

Which, for the sample data:

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;

Note: do not use INDEX (or other reserved words) as column names.

Outputs:

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

db<>fiddle here

CodePudding user response：

@MT0 beat me to it with a better answer, but here's an option using windowed functions...

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

Demo : https://dbfiddle.uk/?rdbms=oracle_21&fiddle=b8a0150b46315256f189506c5fb76fc5

CodePudding user response：

Solved it for Sql Server I think it will be similar in 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

CodePudding user response：

You can use SUM in analytic form - though it will work only if your data are consistent, i.e. the order of the frame records is valid.

Example

select 
 id, type,
 decode(sum(decode(type,'FrameStart',1,'FrameEnd',-1)) over (order by id),1,1,0,null) diff 
from tab1;

        ID TYPE             DIFF
---------- ---------- ----------
         1 other                
         2 FrameStart          1
         3 other               1
         4 other               1
         5 other               1
         6 FrameEnd             
         7 other                
         8 other    

Test Data

create table tab1 as
select 1 id, 'other' type from dual union all
select 2 id, 'FrameStart' type from dual union all
select 3 id, 'other' type from dual union all
select 4 id, 'other' type from dual union all
select 5 id, 'other' type from dual union all
select 6 id, 'FrameEnd' type from dual union all
select 7 id, 'other' type from dual union all
select 8 id, 'other' type from dual;