Extract MIN and MAX values related to datetime values on Postgres 9+

Question

I am trying to query a table that has a collection of student grades recorded over time. I would like to produce a result set that grabs the student id, the first grade, first grade date, last grade, last grade date.

I think I need to use the MIN and MAX functions and some subqueries to achieve this, but I'm just not getting the results I need.

Is there an effective way to achieve the results below on PostgreSQL?

Database Example:

user_id | grade | grade_date
1       | A     | 01/05/2016
1       | B     | 01/15/2016
1       | C     | 01/31/2016
2       | A     | 01/05/2016
2       | B     | 01/15/2016
2       | C     | 01/31/2016
3       | A     | 01/05/2016
3       | B     | 01/15/2016
3       | C     | 01/31/2016
4       | A     | 01/05/2016
4       | B     | 01/15/2016
4       | C     | 01/31/2016

Results I'm aiming for:

user_id | first_grade | first_date | last_grade | last_date
1       | A           | 01/05/2016 | C          | 01/31/2016
2       | A           | 01/05/2016 | C          | 01/31/2016 
3       | A           | 01/05/2016 | C          | 01/31/2016 
4       | A           | 01/05/2016 | C          | 01/31/2016 

Answer 1

There are various ways to do it. An index on columns used in order/filter/join (user_id and grade_date + grade) will play an important role on a large table. Performances must be tested with real data and table/index design.

Using a window function (ROW_NUMBER()):

SELECT f.user_id, f.grade, f.grade_date, l.grade, l.grade_date 
FROM (
    SELECT user_id, grade, grade_date
        , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY grade_date) as n
    FROM data
) f
INNER JOIN (
    SELECT user_id, grade, grade_date
        , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY grade_date DESC) as n
    FROM data
) l
ON f.user_id = l.user_id 
    AND f.n = 1 AND l.n = 1;

ROW_NUMBER gives each row a number from 1 to N by grade_date up and down and only the first one of each is kept (n=1).

Using subqueries:

SELECT  user_id
    , ( SELECT grade FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date LIMIT 1
    )
    , ( SELECT grade_date FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date LIMIT 1
    )
    , ( SELECT grade FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date DESC LIMIT 1
    )
    , ( SELECT grade_date FROM data
        WHERE  user_id = d.user_id
        ORDER BY grade_date DESC LIMIT 1
    )
FROM (SELECT DISTINCT user_id FROM data) d
;

Each subquery only keep the first row and returns it.

Using MIN and MAX:

SELECT d.user_id, mn.grade, mn.grade_date, mx.grade, mx.grade_date
FROM (
    SELECT user_id, MIN(grade_date) as min_grade_date, MAX(grade_date) as max_grade_date
    FROM data
    GROUP BY user_id
) d
INNER JOIN data mn 
    ON mn.grade_date = d.min_grade_date AND mn.user_id = d.user_id 
INNER JOIN data mx 
    ON mx.grade_date = d.max_grade_date AND mx.user_id = d.user_id 
;

It may generate duplicate lines if a user has more than 1 grade on a first or last date.

See SQL Fiddle.

Answer 2

The most efficient way is probably using Windowed Aggregates (see @JulienVavasseur's answer).

This is just trying to avoid the join and minimize the Sort and Join WindowAgg steps:

SELECT user_id, first_grade, first_date, last_grade, last_date
FROM
 (   
   SELECT user_id, grade as first_grade, grade_date as first_date
      ,last_value(grade)         -- return the grade of the last row
       OVER (PARTITION BY user_id 
             ORDER BY grade_date -- same ORDER BY in all three functions
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_grade
      ,last_value(grade_date)    -- could be a MAX OVER, too, but this results in an additional WindowAgg step
       OVER (PARTITION BY user_id 
             ORDER BY grade_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_date
      ,ROW_NUMBER()              -- needed to return the 1st row
       OVER (PARTITION BY user_id
             ORDER BY grade_date) as rn
   FROM data
 ) as dt
WHERE rn = 1;

See fiddle

Answer 3

After testing with 1 million row, here's the best way from @Julien Vavasseur combine with Index on "user_id, grade, grade_date" . We should consider using window function when having a lot of rows.

drop table t1 ;
create table t1 (user_id int, grade int, grade_date date ) ;

insert into t1
select round(a/1000),a,  current_date + a 
from generate_series(1, 1000000) a -- 1 million row

create index t1_idx1 on t1 using btree (user_id, grade, grade_date)

SELECT d.user_id, mn.grade as first_grade, mn.grade_date as first_date, 
 mx.grade as last_grade, mx.grade_date as last_date
FROM (
    SELECT user_id, MIN(grade_date) as min_grade_date, MAX(grade_date) as max_grade_date
    FROM t1
    GROUP BY user_id
) d
JOIN t1 mn  ON mn.grade_date = d.min_grade_date AND mn.user_id = d.user_id 
JOIN t1 mx  ON mx.grade_date = d.max_grade_date AND mx.user_id = d.user_id 
;

Before indexing: output=1001 rows -> 1400ms-1500 ms

After: output=1001 rows -> 550ms-800 ms

Answer 4

Is this OK for you:

I assumed that your table name is mark. My solution is doing trick to nest grade into date data.

SELECT 
     user_id,
     to_timestamp(min_data/1000) as first_date,
     to_timestamp(max_data/1000) as last_date,
     case (min_data-trunc(min_data)) when 0.1 then 'A' when 0.2 then 'B' else 'C' end as first_grade,
     case (max_data-trunc(max_data)) when 0.1 then 'A' when 0.2 then 'B' else 'C' end as last_grade
FROM
(
    SELECT user_id,
           min(extract(epoch from grade_date)*1000 +  
                    case grade 
                          when 'A' then 0.1 
                          when 'B' then 0.2 else 0.3 end) as min_data,
           max(extract(epoch from grade_date)*1000 +  
                    case grade 
                          when 'A' then 0.1 
                          when 'B' then 0.2 
                          else 0.3  end) as max_data
    FROM mark group by user_id
) as Q1