I could not find an issue like this one on SO. I have seen the following:
- R/SQL - Portfolio Performance
- SQL Server : Group By Price and Sum Amount
- SQL Query Balance
- Sql query to find total balance
None of them help me with the issue I have.
Lets say that I have a system that keeps track of my portfolio of shares. I have two main tables. one with the buy and sell orders that I have made, and the other with share prices for many shares over multiple years.
I am looking for an efficient script that gives me the overall portfolio dollar value for each order I have made over time. The current implementation I am using is far too slow: it executes a sub-query for every order I have. I can sometimes have tens of thousands of orders in the table.
The SQL
I am running this on an Oracle database:
-- 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
Giving an output of
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
I am looking for a faster way to get portfolio_balance_dollars. One with less runtime complexity.
Alternative
An efficient answer for the above would be first prize. But instead of looking up the share prices of all the relevant shares on each order, it also suffices to use the latest share price for the relevant share found within summary itself. This is fine because there are orders taking place quite often. So the following output will also be good enough:
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
That is instead of multiplying GOOG share balance with the GOOG share price at 2022-01-04, we instead multiply the share amount by the share price at 2022-01-02 to get a balance of 2155 for 2022-01-02. So it takes the latest available share price shown in summary itself.
CodePudding user response：
You can use:
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;
Which, for the sample data, outputs:
TICKER DT SHARE_PRICE ORDER_SHARES ORDER_DOLLARS BALANCE_SHARES BALANCE_DOLLARS PORTFOLIO_BALANCE_DOLLARS AAPL 02-JAN-22 1.1 1000 1100 1000 1100 2150 GOOG 02-JAN-22 10.5 100 1050 100 1050 2150 AAPL 04-JAN-22 1.3 -150 -195 850 1105 2115 AAPL 05-JAN-22 1.1 -100 -110 750 825 1946.1 GOOG 05-JAN-22 11.1 1 11.1 101 1121.1 1946.1
db<>fiddle here