Question 114178 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL – nth order events of colors appearing first time tin lane1

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
Tags:

4

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
  4. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search