SQL查询选择今天和前一天的价格
问题描述:
我有历史股价数据,如下所示。我想要生成一个新表格,其中包含最近一天价格和前一天价格的每个代码。什么是最好的方法来做到这一点?我的数据库是Postgres。SQL查询选择今天和前一天的价格
+---------+------------+------------+
| ticker | price | date |
+---------+------------+------------|
| AAPL | 6 | 10-23-2015 |
| AAPL | 5 | 10-22-2015 |
| AAPL | 4 | 10-21-2015 |
| AXP | 5 | 10-23-2015 |
| AXP | 3 | 10-22-2015 |
| AXP | 5 | 10-21-2015 |
+------- +-------------+------------+
答
你可以做这样的事情:
with ranking as (
select ticker, price, dt,
rank() over (partition by ticker order by dt desc) as rank
from stocks
)
select * from ranking where rank in (1,2);
例子:http://sqlfiddle.com/#!15/e45ea/3
结果你的例子看起来像这个:
| ticker | price | dt | rank |
|--------|-------|---------------------------|------|
| AAPL | 6 | October, 23 2015 00:00:00 | 1 |
| AAPL | 5 | October, 22 2015 00:00:00 | 2 |
| AXP | 5 | October, 23 2015 00:00:00 | 1 |
| AXP | 3 | October, 22 2015 00:00:00 | 2 |
如果您的表格很大并且存在性能问题,请使用where
限制数据持续30天左右。
答
最好的办法是使用窗口函数和聚合case语句,该语句用于在数据上创建数据透视表。
你可以看到更多的窗口功能在这里:http://www.postgresql.org/docs/current/static/tutorial-window.html
下面是在那里你可能需要前往回答你的问题(抱歉,我无法验证它,由于没有一个Postgres数据库的伪代码版本建立)。
Select
ticker,
SUM(CASE WHEN rank = 1 THEN price ELSE 0 END) today,
SUM(CASE WHEN rank = 2 THEN price ELSE 0 END) yesterday
FROM (
SELECT
ticker,
price,
date,
rank() OVER (PARTITION BY ticker ORDER BY date DESC) as rank
FROM your_table) p
WHERE rank in (1,2)
GROUP BY ticker.
编辑 - 更新了case语句与 '其他'
使用'lag'或'lead'窗口函数。有关更多信息,请参阅手册。 (我没有downvote,顺便说一句)。 –