从SQLite中的不完整交易时间序列获取每日股票投资组合

Question

我试图为以下问题提出SQL解决方案。我有一张桌子，可以保存库存交易。由于不每天进行交易，表中的时间序列是不完整的。

餐桌交易

日期	仓库	股票	收购或出售	股票
2022-02-01	A	MSFT	购买	100
2022-02-04	B	AAPL	购买	20
2022-02-05	A	MSFT	卖出	80
2022-02-02	A	PG A PG	购买	50
2022-02-02-08	PG	​	购买	30
2022-02-08	A	PG	购买	20
2022-02-09	A	PG	购买	50
2022-02-10	B	T	购买	10

现在，我想写一个SQL语句，这使我每天的每日份额是自2022-02-01以来每天的股票。预期的结果是。

日期	仓库	股票	股票计数
2022-02-01	A	MSFT	100
2022-02-02	A	MSFT	100
2022-02-03	A	MSFT	100
2022-02-04	A	MSFT	100
2022-02-02-04	B	AAPL	20
2022-02-02-02-02-05	A	MSFT	20
2022-02-05	B	AAPL	20
2022-02-06	A	MSFT	20
2022-02-06	B	AAPL	20
2022-02-07	A	MSFT	20
2022-02-07	A	PG	50
2022-02-07	B	AAPL	20
2022-02-08	A	PG	50
20222-02-02	​	​	​
​02-08	A	PG	100
2022-02-08	B	AAPL	50
2022-02-09	a	MSFT	20
2022-02-09	A	PG	150
2022-02-09	B	AAPL	50
2022-02-10	A	MSFT	20
2022-02-10	A	PG	150
2022-02-10	B	AAPL AAPL	50
2022-2022-02-10	B T 10 B	T	10

到目前为止，我所做的事情：

• a：在2022-02-01和当前日期之间获取所有日期的列表（例如2022-02-10）。
• B：按仓库和按日期订购的股票列出的股票列的运行总额。
• 一个左联接B

WITH RECURSIVE dates(date) AS (
  VALUES('2022-02-01')
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < DATE()
)
SELECT dt.Date
    , tr.Date
    , tr.Ticker
    , tr.Shares AS [Share Count]
FROM dates dt
LEFT JOIN (
    SELECT Date
        , Depot
        , Ticker
        , SUM(shares) OVER (PARTITION BY Depot, Ticker ORDER BY Date ROWS UNBOUNDED PRECEDING) AS Shares
    FROM TRANSACTION
    ORDER BY Depot, Date, Ticker
) ON (
    tr.Date <= dt.Date
)
ORDER BY dt.Date, Ticker

似乎没有解决问题，因为运行总计无法正确汇总日期行。结果看起来更像合成日期表和事务表之间的一种交叉连接。

我在哪里错了？有人解决了我的问题吗？

多谢！

Answer 1

您快到了，只是在扩展您的想法。
我在以下示例中有硬编码的结束日期，但是可以根据要求更改。

日期CTE只是在所需范围内建立日期。
在下一个子查询（T1）中，还有另外两个列 - 最大日期和列S_SHARES的列MD，以根据购买（添加）（添加）或减去出售的股票运行总和。

然后，我们从日期最大日期（列MD）和原始日期之间的两个集合值中选择。

WITH RECURSIVE dates(date1) AS (
  select date('2022-02-01')
  UNION ALL
  SELECT  date(date1, '+1 day')
  FROM dates
  WHERE date1 <  date('2022-02-10')
) select d.date1,depot,ticker, s_shares 
from dates d, 
(select date1, 
case 
coalesce(lead(date1) over (partition by depot, ticker order by date1),0) 
when 0 then date('2022-02-10', '+1 day')
else lead(date1) over (partition by depot, ticker order by date1) 
end md,
depot, ticker, buyorsell,
sum(case when buyorsell = 'SELL' then -(Shares) else Shares end) 
over (partition by depot, ticker order by Date1 ROWS UNBOUNDED PRECEDING) s_shares
from tic) t1
where d.date1< t1.md
and d.date1>= t1.date1
order by depot, ticker, d.date1;

请参阅小提琴