SQL - nth order events of colors appearing first time tin lane1-CodePudding

I have an event table with lane1 and lane2 mapping colors as below.

event	lane1	lane2
79	red	yellow
83	red	white
87	blue	red
91	yellow	white
96	green	white
100	navy	yellow
103	yellow	green
107	yellow	blue
111	yellow	navy
115	blue	navy
119	yellow	grey
123	white	grey

Required Output:

Capture the nth entry of the color that appears in lane1 for the first time. For the above input the required output is

event	lane1	nth_entry
79	red	1
87	blue	1
91	yellow	2
96	green	1
100	navy	1
123	white	4

I tried aggregation query, like lane1, min(event) but not getting the required output.

select lane1, min(event) from colors group by lane1

CodePudding user response：

You can perform a self-join on the first occurrences of lane1 values:

select e1.ev, e1.lane1, sum((e2.lane1 = e1.lane1 or e2.lane2 = e1.lane1)::int) 
from (select e.lane1, min(e.event) ev from events e group by e.lane1) e1 
join events e2 on e2.event <= e1.ev 
group by e1.ev, e1.lane1 order by e1.ev

See fiddle.

CodePudding user response：

One further option while exploiting window functions is:

linearizing your data,
computing the nth_entry
extracting first value for each lane1 value

WITH cte AS (
    SELECT event_, lane1 AS lane, 1 AS numLane FROM tab
    UNION ALL
    SELECT event_, lane2 AS lane, 2 AS numLane FROM tab
), ranked AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY lane ORDER BY event_) AS nth_entry
    FROM cte
)
SELECT event_, lane, nth_entry
FROM ranked
WHERE numlane = 1
ORDER BY ROW_NUMBER() OVER(PARTITION BY lane ORDER BY event_)
FETCH FIRST 1 ROWS WITH TIES

Check the demo here.

CodePudding user response：

We unpivot lane2 using union all and then use row_number() to give us the first time a color appears in lane1 and how many time it appeared in lane2 previously.

select    event
         ,color as lane1
         ,nth_entry
from 
         (
          select    *
                    ,row_number() over(partition by color, lane order by event) as rn
                    ,row_number() over(partition by color order by event)       as nth_entry
          from      (
                    select event, lane1 as color, 'lane1' as lane from t
          union all select event, lane2, 'lane2'                  from t
                    ) t
         ) t
where     lane = 'lane1'
and       rn = 1
order by  event

event	lane1	nth_entry
79	red	1
87	blue	1
91	yellow	2
96	green	1
100	navy	1
123	white	4

Fiddle

CodePudding user response：

You could try:

WITH lane2_color_count as (
  SELECT
    lane2,
    COUNT(1) AS nth_entry

  FROM colors
  GROUP BY lane2
)

SELECT
  min(colors.event) as event,
  colors.lane1,
  lane2_color_count.nth_entry
FROM
 colors 
LEFT JOIN lane2_color_count
  ON lane2_color_count.lane2 = colors.lane1

GROUP BY colors.lane1, lane2_color_count.nth_entry

ORDER BY event