PostgreSQL窗口函数“ lag（）”仅从当前结果集中提取

熊猫栈

我将股票报价作为PostgreSQL和AngularJS的学习经验。

在我的代码查询中，我尝试发现前一天的价格变化。为了方便测试，我现在正在PHP中实现数据库查询，稍后我将移植到AngularJS。

数据库设置

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

funds
--pk
--fund_name
--summary

查询在价格表中输入相应的信息，以获取每个基金的最新价格和之前的价格（以及其他信息）。这个$ query是我的PHP文件中的一行。

$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)';

结果

[
{"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"}
]

如您所见，lag（）窗口函数仅使用当前结果集来提取前一条记录的prices.price字段。

我现在很茫然。有人指导吗？

戈登·利诺夫（Gordon Linoff）

我猜您想要的前一天价格fund。这需要一个partition by子句：

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 ;

如果您只想在最后一个日期使用，请使用子查询：

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);

该WHERE子句在分析函数之前进行求值，因此过滤会影响所选择的记录（如果有）LAG()。注意：这假设price_date所有资金的最高限额都相同，但这是问题的逻辑。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。