6

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 
6

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.

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

2

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.