CODEWITHSUNDEEP
sql
user3233874
user3233874

Reputation: 3

Postgres SQL to select the previous valid date

I am studying Postgres and run into this interesting problem that I have not been able to resolve it.

Assuming a company is shipping out goods once in a few days. This is the shipping table to record how many containers it ships out:

Branch (character); Date (date); containers (integer)

New York        2014-01-01  10
New York        2014-01-02  2
New York        2014-01-05  30
New York        2014-01-07  4
Toronto     2014-01-01  5
Toronto     2014-01-03  55
Toronto     2014-01-05  50
London          2014-01-01  60
London          2014-01-07  63
London          2014-01-12  64

I want to have this report:

Branch (character); Date (date); containers (integer); Previous_date; containers_of_previous_date; delta

New York    2014-01-01    10    NULL    NULL    NULL
New York    2014-01-02    2    2014-01-01    10    -8
New York    2014-01-05    30    2014-01-02    2    28
New York    2014-01-07    4    2014-01-05    30    -26
Toronto    2014-01-01    5    NULL      NULL    NULL
Toronto    2014-01-03    55    2014-01-01    5    50
Toronto    2014-01-05    50    2014-01-03    55    -5
London     2014-01-01    60    NULL    NULL    NULL
London    2014-01-07    63    2014-01-01    60    3
London    2014-01-12    64    2014-01-07    63    1

I use this:

Select a.*, b.*, (a.containers – b.containers) delta
From shipping a
Join shipping b 
ON 
b.date=(select date from shipping where date<a.date order by date desc limit 1)

This works when the table is small. If the table grows to 100,000 records, this query is slow. I wonder if your guys have more efficient way.

Thanks, Stephen

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Just use the lag() function!

Select s.Branch, s.Date, s.containers,
       lag(s.Date) over (partition by branch order by date) as prev_Date,
       lag(s.containers) over (partition by branch order by date) as prev_Containers
from shipping s;

Upvotes: 1

Related Questions