Postgresql – By using SQL Window Functions, how can I get the lead of the last row in a partition

postgresqlwindow functions

Considering the following simple example:

select lead(c) over (partition by a order by a)
from (values 
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) t(a,b,c)

The first two rows are records with the same ID (column a) and "date1" (column c). The ID duplication exists due to an unnest operation made in an array column that might have different sizes or number of elements. The unnest operation results in column b.

Now, I want to get the lead date of the next ID, "date2". The query I wrote outputs "date 1" for the first record, but I actually want to get "date 2". I'm not interested in the static solution to this problem, which would be lead(c,2) over (order by a), but on how to get the lead of a dynamic sized window.

Expected output:

date2
date2
NULL

Best Answer

You can specify a window that excludes the current group:

SELECT first_value(c) OVER w
FROM (VALUES
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) AS t(a,b,c)
WINDOW w AS (
   ORDER BY a
   ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP
);

 first_value 
-------------
 date2
 date2

(3 rows)

Related Solutions

Postgresql – postgres window functions: using CASE to partition by age group

You could use a LATERAL join for this. And no need for joining the users table twice:

FROM 
    competition_users AS cu
  LEFT JOIN users AS u
    ON cu.user_id = u.user_id
  LEFT JOIN LATERAL
    ( SELECT CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
                 THEN '0-15' 
                 ELSE '15+' 
             END AS ageGroup
    ) AS age
    ON TRUE
  LEFT JOIN current_competition_sessions AS ccs 
    ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = FALSE 
  AND cu.competition_id = :compId

and then the WINDOW would be left (almost) unchanged:

WINDOW
    ageGroupTimes AS (PARTITION BY age.ageGroup ORDER BY cus.time_in_seconds ASC)

Alternatively, a simple CTE or derived table with a normal LEFT JOIN could be used, without the need for the (9.3+ available only) LATERAL. With the following you'll only need to slightly modify your upper-level SELECT and WINDOW (removing table aliases mainly):

FROM
  ( SELECT 
        cu.*, 
        cus.time_in_seconds,
        u.gender,
        CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
            THEN '0-15' 
            ELSE '15+' 
        END AS ageGroup
    FROM 
        competition_users AS cu
      LEFT JOIN users AS u
        ON cu.user_id = u.user_id
      LEFT JOIN current_competition_sessions AS ccs 
        ON cu.competition_user_id = ccs.competition_user_id 
    WHERE cu.left_competition = FALSE 
      AND cu.competition_id = :compId
  ) AS d

Oracle analytic functions – how to get the highest RANKing row

I fail to see the point of a requirements such as avoiding WHERE rank_ =, but here it is, without RANK(), or hardcoding a constant (still, hardcoding is done by using FIRST_VALUE):

select distinct grp,
  first_value(result1) over (partition by grp order by mbr desc) as result1,
  first_value(result2) over (partition by grp order by mbr desc) as result2
from (
select
  grp, mbr, 
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
);

GRP                 RESULT1    RESULT2
---------------- ---------- ----------
A                        -1        1.3
B                     -20.2      -20.4
C                      -100      100.3

When this is easier to read in my opinion:

select grp, result1, result2 from (
select
  grp,
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2,
  rank() over (partition by grp order by mbr desc) as rank_
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
) where rank_ = 1
;

Best Answer

Related Solutions

Postgresql – postgres window functions: using CASE to partition by age group

Oracle analytic functions – how to get the highest RANKing row

Related Question