我在 SO 上找不到像這樣的問題。我看過以下內容：
- R/SQL - 投資組合性能
- SQL Server：按價格和金額分組
- SQL 查詢余額
- Sql查詢查找總余額
他們都沒有幫助我解決我遇到的問題。
假設我有一個跟蹤我的股票投資組合的系統。我有兩張主桌。一個是我所做的買賣訂單，另一個是多年來許多股票的股價。
我正在尋找一個高效的腳本，它可以為我隨著時間的推移所下的每筆訂單提供整體投資組合的美元價值。我正在使用的當前實作太慢了：它為我擁有的每個訂單執行一個子查詢。有時我的表中可以有數以萬計的訂單。
SQL
我在 Oracle 資料庫上運行它：
-- Will not have prices on all days (eg weekends and public holidays)
-- One price per day at most for a given ticker
-- This table has hundreds of tickers and hundreds of entries for each ticker
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
)
-- Can have more than 1 order on the same day and same ticker
-- Can have multiple orders for multiple tickers on the same day
-- This table can have tens of thousands of orders but usually less than 20 tickers
, 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
也就是說，我們不是將 GOOG 股票余額乘以 2022 年 1 月 4 日的 GOOG 股價，而是將股票數量乘以 2022 年 1 月 2 日的股價，得到 2022 年 1 月 2 日的 2155 股余額。summary因此，它采用本身顯示的最新可用股價。
db<>小提琴
uj5u.com熱心網友回復：
您可以使用：
SELECT *
FROM (
SELECT t.*,
SUM(balance_dollars) OVER (PARTITION BY dt)
AS portfolio_balance_dollars
FROM (
SELECT t.ticker,
o.dt,
p.price AS share_price,
o.shares AS order_shares,
o.shares * p.price AS order_dollars,
SUM(o.shares) OVER (PARTITION BY t.ticker ORDER BY o.dt)
AS balance_shares,
SUM(o.shares) OVER (PARTITION BY t.ticker ORDER BY o.dt)
* p.price AS balance_dollars
FROM ( SELECT DISTINCT ticker FROM orders ) t
LEFT OUTER JOIN (
SELECT ticker,
dt,
SUM(shares) AS shares
FROM orders
GROUP BY ticker, dt
) o
PARTITION BY (o.dt)
ON (t.ticker = o.ticker)
CROSS JOIN LATERAL (
SELECT p.*
FROM prices p
WHERE p.ticker = t.ticker
AND p.dt <= o.dt
ORDER BY p.dt DESC
FETCH FIRST ROW ONLY
) p
ORDER BY
o.dt,
t.ticker,
share_price
) t
)
WHERE order_shares IS NOT NULL;
其中，對于樣本資料，輸出：
股票代碼 DT 分享價格 ORDER_SHARES ORDER_DOLLARS BALANCE_SHARES BALANCE_DOLLARS PORTFOLIO_BALANCE_DOLLARS 蘋果 22 年 1 月 2 日 1.1 1000 1100 1000 1100 2150 谷歌 22 年 1 月 2 日 10.5 100 1050 100 1050 2150 蘋果 22 年 1 月 4 日 1.3 -150 -195 850 1105 2115 蘋果 22 年 1 月 5 日 1.1 -100 -110 750 825 1946.1 谷歌 22 年 1 月 5 日 11.1 1 11.1 101 1121.1 1946.1
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/491965.html
上一篇：用于將SQL查詢轉換為Pandas的Pythonsum()錯誤
下一篇：根據記錄值更改查詢