Oracle高效计算股票投资组合余额（移除嵌套子查询）

我在Stack Overflow（SO）未检索到同类问题，已参考以下相关帖子：

• R/SQL - Portfolio Performance
• SQL Server : Group By Price and Sum Amount
• SQL Query Balance
• Sql query to find total balance

上述帖子均无法解决我遇到的问题。

现有一套股票投资组合跟踪系统，包含两张核心表：一张记录用户提交的股票买卖订单，另一张存储多只股票多年的历史行情价格。

我需要一套高效的SQL脚本，计算每笔订单发生时点的投资组合总美元价值。当前使用的实现方案性能极差：会为每笔订单执行一次子查询，而订单表中有时会存在数万条订单记录。

现有SQL实现 #

运行环境为Oracle数据库：

-- 价格表不覆盖所有自然日（如周末、法定节假日无数据）
-- 单只股票单日最多存在一条价格记录
-- 该表包含数百只股票，单只股票对应数百条价格记录
with prices as (
select 'AAPL' ticker, date('2022-01-01') dt, 1.0 price union
select 'AAPL' ticker, date('2022-01-02') dt, 1.1 price union
select 'AAPL' ticker, date('2022-01-03') dt, 1.2 price union
select 'AAPL' ticker, date('2022-01-04') dt, 1.3 price union
select 'AAPL' ticker, date('2022-01-05') dt, 1.1 price union
select 'AAPL' ticker, date('2022-01-06') dt, 1.0 price union
select 'AAPL' ticker, date('2022-01-07') dt, 1.1 price union
select 'GOOG' ticker, date('2022-01-01') dt, 10.3 price union
select 'GOOG' ticker, date('2022-01-02') dt, 10.5 price union
select 'GOOG' ticker, date('2022-01-03') dt, 9.2 price union
select 'GOOG' ticker, date('2022-01-04') dt, 10.1 price union
select 'GOOG' ticker, date('2022-01-05') dt, 11.1 price union
select 'GOOG' ticker, date('2022-01-06') dt, 10.0 price union
select 'GOOG' ticker, date('2022-01-07') dt, 10.1 price union
select 'MSFT' ticker, date('2022-01-01') dt, 5.0 price union
select 'MSFT' ticker, date('2022-01-02') dt, 5.1 price union
select 'MSFT' ticker, date('2022-01-03') dt, 4.2 price union
select 'MSFT' ticker, date('2022-01-04') dt, 6.3 price union
select 'MSFT' ticker, date('2022-01-05') dt, 5.1 price union
select 'MSFT' ticker, date('2022-01-06') dt, 4.9 price union
select 'MSFT' ticker, date('2022-01-07') dt, 5.3 price
)

-- 订单表允许同一只股票单日存在多笔订单
-- 单日允许多只股票同时产生订单
-- 该表通常有数万条订单记录，但涉及的股票数量一般不超过20只
, orders as (
select 'AAPL' ticker, date('2022-01-02') dt, 'Buy' type, 1000 shares union
select 'GOOG' ticker, date('2022-01-02') dt, 'Buy' type, 100 shares union
select 'AAPL' ticker, date('2022-01-04') dt, 'Sell' type, -100 shares union
select 'AAPL' ticker, date('2022-01-04') dt, 'Sell' type, -50 shares union
select 'AAPL' ticker, date('2022-01-05') dt, 'Sell' type, -100 shares union
select 'GOOG' ticker, date('2022-01-05') dt, 'Buy' type, 1 shares
)

, summary as (
select
o.ticker,
o.dt,
p.price share_price,
sum(o.shares) order_shares,
sum(o.shares * p.price) order_dollars,
sum(sum(o.shares)) over(partition by o.ticker order by o.dt) balance_shares,
sum(sum(o.shares)) over(partition by o.ticker order by o.dt) * p.price balance_dollars,
(
select sum(o1.shares * p1.price)
from orders o1
inner join prices p1
on p1.ticker = o1.ticker
and p1.dt = o.dt
where o1.dt <= o.dt
) portfolio_balance_dollars
from orders o
inner join prices p on p.ticker = o.ticker and p.dt = o.dt
group by o.ticker, o.dt, p.price
order by o.dt, o.ticker, p.price
)

select s1.*
from summary s1

当前脚本输出如下：

ticker  dt          share_price  order_shares  order_dollars  balance_shares  balance_dollars  portfolio_balance_dollars
------  ----------  -----------  ------------  -------------  --------------  ---------------  -------------------------
AAPL    2022-01-02  1.1          1000          1100.0         1000            1100.0           2150.0                   
GOOG    2022-01-02  10.5         100           1050.0         100             1050.0           2150.0                   
AAPL    2022-01-04  1.3          -150          -195.0         850             1105.0           2115.0                   
AAPL    2022-01-05  1.1          -100          -110.0         750             825.0            1946.1                   
GOOG    2022-01-05  11.1         1             11.1           101             1121.1           1946.1                   

我需要寻找时间复杂度更低、运行更快的方案来计算portfolio_balance_dollars（投资组合总余额）字段。

可接受的近似方案 #

能实现上述需求的高效精确方案为最优解；如果无法高效实现逐订单查询所有持仓股票当日价格的逻辑，使用summary结果集中各股票的最新可得价格计算也可满足需求，由于订单发生频率较高，该近似结果精度符合要求，对应输出示例如下：

ticker  dt          share_price  order_shares  order_dollars  balance_shares  balance_dollars  portfolio_balance_dollars
------  ----------  -----------  ------------  -------------  --------------  ---------------  -------------------------
AAPL    2022-01-02  1.1          1000          1100.0         1000            1100.0           2150.0                   
GOOG    2022-01-02  10.5         100           1050.0         100             1050.0           2150.0                   
AAPL    2022-01-04  1.3          -150          -195.0         850             1105.0           2155.0                   
AAPL    2022-01-05  1.1          -100          -110.0         750             825.0            1946.1                   
GOOG    2022-01-05  11.1         1             11.1           101             1121.1           1946.1                   

该方案逻辑为：例如计算2022-01-04的组合余额时，GOOG持仓不再乘以2022-01-04的GOOG价格，而是乘以summary中GOOG最新的2022-01-02价格，得到当日组合余额2155.0，即直接复用summary中已有的各股票最新价格计算即可。

内容的提问来源于stack exchange，提问作者Chris du Plessis