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

fetch previous, current and next record where it meets certain condition – Postgresql

I am trying to solve an issue where I need to find the previous,current and next record where the query matches certain condition. In the example image below I am interested in only records where pattern_id=23587462 (can be multiple), and it the previous machine_id = next machine_id, then fetch the 3 records.

select SL.*, row_number() over (PARTITION BY Machine_id Order by machine_id, SS2k) as M
from SL
where pattern_id in (23587462,2879003)

I am expecting these records plus the previous and the next record too

enter image description here

Update:- with the help of @shawnt00 and @SelVazi, I used their syntax/code and have a modified version as below,

  with cte as (Select SL.*, lead(machine_id,1) over (order by machine_id) as lead_mid, lead(pattern_id,1) over (order by machine_id) as lead_pid,
            lag(machine_id,1) over (order by machine_id) as lag_mid, lag(pattern_id,1) over (order by machine_id) as lag_pid,
            Case 
                when pattern_id in (23587462) then 'Current'
                when lead_pid in (23587462) and machine_id=lead_mid then 'Source'
                when lag_pid in (23587462) and machine_id=lag_mid then 'Loss'
            End as sourceloss
            from SL)
    Select * from cte 
    where sourceloss is not Null;
Tags:

2

Answers


  1. 0

    This is a working solution if the ss2k is unique and take on considération if the machine_id should be the same for pervious current and next record for the selected pattern_id :

    with cte as (
  select *, lag(ss2k) over (Order by id) as source,
  lead(ss2k) over (Order by id) as Loss
  from mytable
),
cte2 as (
  select c.id, t.machine_id, c.pattern_id, c.ss2k, c.machine_id as currentMachine, row_number() over (order by c.id) as rn
  from cte c
  inner join mytable t on t.ss2k = c.ss2k or t.ss2k = c.source or t.ss2k = c.loss
  where t.pattern_id = 23587462
  order by id
),
cte3 as (
  select c.id, c.machine_id, c.pattern_id, c.ss2k, 
  case when rn%3 = 1 then 'source'
       when rn%3 = 2 then 'current'
       when rn%3 = 0 then 'loss' 
  end as status,
  rn,
  (rn-1)/3+1 groupIds
  from cte2 c
  where c.currentMachine = c.machine_id
)
select c.machine_id, c.pattern_id, c.ss2k, c.status
from cte3 c
inner join (
  select groupids
  from cte3
  group by groupids
  having count(1) = 3
) as s on s.groupids = c.groupids

    First cte to get previous and next record using two window functions :

    Lag() function to get data of a previous row from the current row.

    Lead() function to get data of a previous row from the current row.

    cte2 to get the machine_id of the previous and next records.

    cte3 to group records by group of 3 records(previous, current and next), then with a join we get only data from group of 3 records (if length is less than 3 that means one of the records is not in the same machine so ignore the whole group).

    Demo here

    Login or Signup to reply.
  2. 0

    A naive lead/lag() method will work here without requiring any joins. It does require that there be three rows in the matching "group" of rows.

    with data as (select *, 23587462 as PATTERN from T), grp as (
    select *,
        case PATTERN
            when lead(Pattern_ID) over (partition by Machine_ID order by SS2K)
            then lead(SS2K)       over (partition by Machine_ID order by SS2K)
            when Pattern_ID
            then SS2K
            when lag(Pattern_ID)  over (partition by Machine_ID order by SS2K)
            then lag(SS2K)        over (partition by Machine_ID order by SS2K)
        end as grp,
        case PATTERN
            when lead(Pattern_ID) over (partition by Machine_ID order by SS2K)
            then -1
            when Pattern_ID
            then 0
            when lag(Pattern_ID)  over (partition by Machine_ID order by SS2K)
            then 1
         end as idx
    from data
), runs as (
   select *, count(*) over (partition by grp) as grp_length
   from grp
   where grp is not null
)
select *,
    case idx when -1 then 'Source' when 0 then 'Current' when 1 then 'Loss' end as D
from runs
where grp_length = 3
order by Machine_ID, SS2K;

    This does have problems if there are overlapping sets of rows. Take a look at the third set in the example link below for an example of that scenario (with Machine_ID of all 9’s.)

    Another approach eliminates that problem by examining each set of three consecutive rows. In that way the overlaps are no longer a problem. It’s not clear though how you’d want to label those rows:

    with runs as (
    select *,
        case when
            Pattern_ID = 23587462 and
            min(Machine_ID) over (
                partition by Machine_ID order by SS2K
                rows between 1 preceding and 1 following) =
            max(Machine_ID) over (
                partition by Machine_ID order by SS2K
                rows between 1 preceding and 1 following) and
            count(*) over (
                partition by Machine_ID
                order by SS2K rows between 1 preceding and 1 following) = 3
            then 1 end as flg
    from T
), tagged as (
    select *,
        case when 1 in (lag(flg)  over (order by SS2K),
                        flg,
                        lead(flg) over (order by SS2K)) then 1 end as keep
    from runs
)
select * from tagged where keep = 1
order by Machine_ID, SS2K;

    Both of the queries do assume that SS2K is unique for the relevant rows adjacent to the matches.

    https://dbfiddle.uk/7YU9IB1h

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

Back To Top
Search