CODEWITHSUNDEEP
sqlpostgresql
PANDA Stack
PANDA Stack

Reputation: 1363

PostgreSQL window function "lag()" only pulls from current resultset

I'm making a stock ticker as a learning experience for PostgreSQL and AngularJS.

In my ticker query, I attempt to discover the change in price from the previous day. I'm implementing the DB queries in PHP right now for ease of testing and I'll port to AngularJS later.

DB Setup

prices
--pk
--fund (foreign key to funds.pk)
--price
--price_date

funds
--pk
--fund_name
--summary

Query Get the latest price and the price before it (as well as other info) for each fund with an entry in the prices table. This $query is a single line in my PHP file.

$query = 'SELECT prices.price_date, 
prices.price, 
(lag(prices.price) over (ORDER BY prices.price_date DESC)) as last_price, 
prices.fund, 
funds.fund_name 
FROM prices 
INNER JOIN funds ON prices.fund=funds.pk 
WHERE price_date=(SELECT price_date FROM prices ORDER BY price_date DESC LIMIT 1)';

Result

[
{"price_date":"2015-09-08","price":"17.5901","last_price":null,"fund":"1","fund_name":"L Income"},
{"price_date":"2015-09-08","price":"22.8859","last_price":"17.5901","fund":"2","fund_name":"L 2020"},
{"price_date":"2015-09-08","price":"24.6693","last_price":"22.8859","fund":"3","fund_name":"L 2030"},
{"price_date":"2015-09-08","price":"26.1456","last_price":"24.6693","fund":"4","fund_name":"L 2040"},
{"price_date":"2015-09-08","price":"14.7756","last_price":"26.1456","fund":"5","fund_name":"L 2050"},
{"price_date":"2015-09-08","price":"14.8181","last_price":"14.7756","fund":"6","fund_name":"G Fund"},
{"price_date":"2015-09-08","price":"16.93","last_price":"14.8181","fund":"7","fund_name":"F Fund"},
{"price_date":"2015-09-08","price":"26.369","last_price":"16.93","fund":"8","fund_name":"C Fund"},
{"price_date":"2015-09-08","price":"35.9595","last_price":"26.369","fund":"9","fund_name":"S Fund"},
{"price_date":"2015-09-08","price":"24.0362","last_price":"35.9595","fund":"10","fund_name":"I Fund"}
]

As you can see, the lag() window function is only drawing on the current resultset for pulling the previous record's prices.price field.

I am at a loss now. Does anyone have guidance?

Upvotes: 0

Views: 549

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

I am guessing that you want the previous day's price for the fund. This requires a partition by clause:

SELECT p.price_date, p.price, 
       lag(p.price) over (PARTITION BY p.fund ORDER BY p.price_date DESC) as last_price, 
       p.fund, p.fund_name 
FROM prices p INNER JOIN
     funds f
     ON p.fund = f.pk ;

If you then want this only for the last date, then use a subquery:

SELECT pf.*
FROM (SELECT p.price_date, p.price, 
             lag(p.price) over (PARTITION BY p.fund ORDER BY p.price_date DESC) as last_price, 
             p.fund, p.fund_name 
      FROM prices p INNER JOIN
           funds f
           ON p.fund = f.pk
     ) pf
WHERE price_date = (SELECT price_date FROM prices ORDER BY price_date DESC LIMIT 1);

The WHERE clause is evaluated before the analytic functions, so the filtering affects which record (if any) is chosen by the LAG(). Note: this assumes that the max price_date is the same for all funds, but this is the logic in the question.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

If you need to compare it to the price from previous day, you should use a conditional so it always picks up values from the previous day. 

SELECT prices.price_date, 
prices.price, 
case when price_date = (select max(prices_date) from prices) then 
lag(prices.price) over (ORDER BY prices.price_date) 
end as last_price, 
prices.fund, 
funds.fund_name 
FROM prices 
INNER JOIN funds ON prices.fund = funds.pk 
WHERE price_date=(SELECT price_date FROM prices ORDER BY price_date DESC LIMIT 1)

Upvotes: 0

Related Questions