Giving similar value to group in SQL-CodePudding

I have a table that looks like the following

|Position | Name  | Name_key  |
|: ----   |:----  | ---------:| 
   1        Adult     123
   2        Child     555
   3        Child     666
   4        Adult     456
   5        Child     777 
   6        Adult     789
   7        Child     888
   8        Child     999

I would like to group together and add an additional column at the end. Each parent-child is group together by the order in the table. So, for example Adult 1 goes with child 1 and Adult 2 goes with child 2& 3 and so on. Each group needs to share the adult's name_entry. So, something like this:

|Position | Name  | Name_key  | parent_name_key
|: ----   |:----  |: ---------|:-------------- 
   1        Adult     123         123
   2        Child     555         123 
   3        Child     666         123
   4        Adult     456         456
   5        Child     777         456
   6        Adult     789         789
   7        Child     888         789
   8        Child     999         789

I will admit (obviously) I'm a newb at SQL. I've tried using some window functions and partitioning by the Name but couldn't get anywhere, really.

My SQL to get it the initial table looks something like this: select position, name, name_key from test

CodePudding user response：

I believe a correlated query like the following is what you are after, based on your data you want the largest Name_Key which belongs to an Adult which is at or before the current position:

select *, Coalesce((
  select Max(Name_Key)
    from t t2
    where t2.Name = 'Adult' 
      and t2.Position <= t.Position
  ), Name_Key) Parent_Name_Key
from t
order by Position;

CodePudding user response：

You can do it without subquery with analytic:

with data (Position, Name, Name_key ) as (
    select 1, 'Adult', 123 from dual union all
    select 2, 'Child', 555 from dual union all
    select 3, 'Child', 666 from dual union all
    select 4, 'Adult', 456 from dual union all
    select 5, 'Child', 777 from dual union all 
    select 6, 'Adult', 789 from dual union all
    select 7, 'Child', 888 from dual union all
    select 8, 'Child', 999 from dual -- union all
)
select d.*, last_value(decode(name, 'Adult', Name_key)) ignore nulls over(order by position) as parent_name_key
from data d
;


1   Adult   123 123
2   Child   555 123
3   Child   666 123
4   Adult   456 456
5   Child   777 456
6   Adult   789 789
7   Child   888 789
8   Child   999 789

CodePudding user response：

Here is the solution. I made use of sum window function to achieve this. I think there can be some other ways to solve this.

with assign_0_1 as
    (
        select 
            * , 
            case when Name = 'Adult' then 1 else 0 end as assign_0_1 , 
            sum(case when Name = 'Adult' then 1 else 0 end) over(order by position) as rolling_assign_0_1
        from relation
    )
    select b.position , b.name , b.Name_Key , a.Name_Key as parent_Name_key  from
    (select Name_key ,  rolling_assign_0_1  from assign_0_1 where assign_0_1 = 1) as a inner join assign_0_1 as b
    on a.rolling_assign_0_1 = b.rolling_assign_0_1;

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE:

SELECT position, name, name_key, parent_name_key
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY position
  MEASURES
    adult.name_key AS parent_name_key
  ALL ROWS PER MATCH
  PATTERN (adult child*)
  DEFINE adult AS name = 'Adult',
         child AS name = 'Child'
)

Which, for the sample data:

CREATE TABLE table_name (Position, Name, Name_key ) AS
  SELECT 1, 'Adult', 123 FROM DUAL UNION ALL
  SELECT 2, 'Child', 555 FROM DUAL UNION ALL
  SELECT 3, 'Child', 666 FROM DUAL UNION ALL
  SELECT 4, 'Adult', 456 FROM DUAL UNION ALL
  SELECT 5, 'Child', 777 FROM DUAL UNION ALL 
  SELECT 6, 'Adult', 789 FROM DUAL UNION ALL
  SELECT 7, 'Child', 888 FROM DUAL UNION ALL
  SELECT 8, 'Child', 999 FROM DUAL;

Outputs:

POSITION	NAME	NAME_KEY	PARENT_NAME_KEY
1	Adult	123	123
2	Child	555	123
3	Child	666	123
4	Adult	456	456
5	Child	777	456
6	Adult	789	789
7	Child	888	789
8	Child	999	789

fiddle

CodePudding user response：

You could use MODEL clause (it is fast and reliable) to create new column with the Parent name key following Position:

WITH    --  S a m p l e   D a t a : 
    tbl (POSITION, A_NAME, NAME_KEY ) AS 
        (   Select 1, 'Adult', 123 From Dual Union All
            Select 2, 'Child', 555 From Dual Union All
            Select 3, 'Child', 666 From Dual Union All
            Select 4, 'Adult', 456 From Dual Union All
            Select 5, 'Child', 777 From Dual Union All 
            Select 6, 'Adult', 789 From Dual Union All
            Select 7, 'Child', 888 From Dual Union All
            Select 8, 'Child', 999 From dual 
        )
--    M a i n   S Q L :
Select  POSITION, A_NAME, NAME_KEY, PARENT_NAME_KEY
From    tbl
MODEL   Dimension By (POSITION)
        Measures (A_NAME, NAME_KEY, 0 "PARENT_NAME_KEY", 0 "PARENT_POSITION")
        RULES ( PARENT_POSITION[ANY] = Nvl(Case When A_NAME[CV()] = 'Adult' Then CV(POSITION) End, Max(PARENT_POSITION)[POSITION < CV(POSITION)]), 
                PARENT_NAME_KEY[ANY] =  NAME_KEY[POSITION = PARENT_POSITION[CV()]] 
              )
/*    R e s u l t :
  POSITION A_NAME   NAME_KEY PARENT_NAME_KEY
---------- ------ ---------- ---------------
         1 Adult         123             123
         2 Child         555             123
         3 Child         666             123
         4 Adult         456             456
         5 Child         777             456
         6 Adult         789             789
         7 Child         888             789
         8 Child         999             789   */

Dimension (here used as a unique row addressing) already exist in column POSITION.
RULES clause defines the values for :

PARENT_POSITION column in a way that for ANY Dimension ( PARENT_POSITION[ANY] ), if Adult, takes the POSITION and holds it untill another Adult
PARENT_NAME_KEY column reads NAME_KEY for last Adult (actual row's PARENT_POSITION)