我需要按天计算我的股票投资组合头寸。在我的 postgres (v10( 数据库中，我有：
投资组合表
+----------------------+-------------------------------+
| id(INTEGER) | portfolio_name(CHAR) |
+----------------------+-------------------------------+
| portfolio_1 | my portfolio |
+----------------------+-------------------------------+
发生业务表
+-------------+-----------------------+-------------------+--------------+--------------+-----------------------------+------------------------+
| id(INTEGER) | portfolio_id(INTEGER) | stock_id(INTEGER) | price(FLOAT) | share(FLOAT) | transaction_date(TIMESTAMP) | transaction_type(CHAR) |
+-------------+-----------------------+-------------------+--------------+--------------+-----------------------------+------------------------+
| 1 | portfolio_1 | stock_1 | 12,34 | 3000 | 2019-06-03 15:36:29 +0000 | BUY_SELL |
| 2 | portfolio_1 | stock_2 | 30,34 | 2000 | 2019-06-03 15:36:29 +0000 | BUY_SELL |
| 3 | portfolio_1 | stock_1 | 35,34 | -1000 | 2019-06-13 12:06:29 +0000 | BUY_SELL |
+-------------+-----------------------+-------------------+--------------+--------------+-----------------------------+------------------------+
股票价值表
+-------------+-------------------+---------------------------+-------------------+--------------------+-------------------+------------------+
| id(INTEGER) | stock_id(INTEGER) | value_date(TIMESTAMP) | open_value(FLOAT) | close_value(FLOAT) | high_value(FLOAT) | low_value(FLOAT) |
+-------------+-------------------+---------------------------+-------------------+--------------------+-------------------+------------------+
| 1 | stock_1 | 2019-01-01 10:33:16 +0000 | 30.6 | 30.6 | 30.6 | 30.6 |
| 2 | stock_1 | 2019-01-02 10:33:16 +0000 | 30.6 | 30.6 | 30.6 | 30.6 |
| 3 | stock_1 | 2019-01-03 10:33:16 +0000 | 30.6 | 30.6 | 30.6 | 30.6 |
| 4 | stock_1 | 2019-01-04 10:33:16 +0000 | 30.6 | 30.6 | 30.6 | 30.6 |
| 5 | stock_1 | 2019-01-05 10:33:16 +0000 | 30.6 | 30.6 | 30.6 | 30.6 |
| 6 | stock_1 | 2019-01-06 10:33:16 +0000 | 30.6 | 30.6 | 30.6 | 30.6 |
| .... | | | | | | |
| 17 | stock_2 | 2019-01-05 10:33:16 +0000 | 10.4 | 10.4 | 10.4 | 10.4 |
| 18 | stock_2 | 2019-01-06 10:33:16 +0000 | 10.4 | 10.4 | 10.4 | 10.4 |
| .... | | | | | | |
+-------------+-------------------+---------------------------+-------------------+--------------------+-------------------+------------------+
我想用SQL查询生成这个结果：
+-----------------------+-----------------------+-----------------+------------------+
| portfolio_id(INTEGER) | stock_id(INTEGER) | date(TIMESTAMP) | share(FLOAT) |
+-----------------------+-----------------------+-----------------+------------------+
| portfolio_1 | stock_1 | 2019-06-03 | 3000 |
| portfolio_1 | stock_2 | 2019-06-03 | 2000 |
| portfolio_1 | stock_1 | 2019-06-04 | 3000 |
| portfolio_1 | stock_2 | 2019-06-04 | 2000 |
| portfolio_1 | stock_1 | 2019-06-05 | 3000 |
| portfolio_1 | stock_2 | 2019-06-06 | 2000 |
| ... | | | |
| portfolio_1 | stock_1 | 2019-06-13 | 2000 (3000-1000) |
| .... | | | |
| portfolio_1 | stock_1 | now | 2000 |
| portfolio_1 | stock_2 | now | 2000 |
+-----------------------+-----------------------+-----------------+------------------+
在使用子查询之后，我无法使用 SQL 进行操作。任何帮助将不胜感激。谢谢！
编辑：你可以在这里找到一个数据库小提琴 https://www.db-fiddle.com/f/oSZ4SbvK5uFWSzmdTH5eAy/1
您应该能够通过使用generate_series()生成日期来执行此操作：
select p.portfolio_id, p.stock_id, p.dte,
Sum(t.share) over (partition by p.portfolio_id, p.stock_id order by p.dte) as shares
from (select t.portfolio_id, s.stock_id, generate_series(t.min_td, current_date, interval '1 day') as dte
from (select portfolio_id, min(transaction_date)::date as min_td
from transactions t
group by portfolio_id
) t cross join
(select distinct stock_id from transactions) s
) p left join
transactions t
on t.portfolio_id = p.portfolio_id and t.stock_id = p.stock_id and t.transaction_date::date = p.dte
order by 1, 2, 3;
这是一个数据库<>小提琴。