| Version 3 (modified by , 2 weeks ago) ( diff ) |
|---|
Напредни извештаи од базата
Анализа на портфолио диверзификација и концентрациски ризик
Опис: Извештајот за секој корисник го анализира неговото портфолио од аспект на диверзификација: пресметува Herfindahl-Hirschman Index (HHI) за концентрација на вложувања, го идентификува доминантниот холдинг, го пресметува нереализираниот P&L по холдинг (разлика помеѓу тековна цена и avg_price), вкупната вредност на портфолиото, бројот на различни акции и класификација на ризик (HIGH CONCENTRATION RISK / MEDIUM CONCENTRATION RISK / WELL DIVERSIFIED) врз основа на HHI. Наменет за полугодишен/годишен преглед на ризикот на портфолијата на корисниците.
SQL:
CREATE OR REPLACE FUNCTION report_portfolio_diversification()
RETURNS TABLE (
user_id INTEGER,
username TEXT,
portfolio_id INTEGER,
portfolio_cash_balance NUMERIC,
total_holdings_value NUMERIC,
total_portfolio_value NUMERIC,
num_distinct_stocks BIGINT,
total_lots BIGINT,
unrealized_pnl NUMERIC,
unrealized_pnl_pct NUMERIC,
hhi_score NUMERIC,
risk_classification TEXT,
dominant_stock_symbol TEXT,
dominant_stock_weight_pct NUMERIC,
max_single_weight_pct NUMERIC,
min_single_weight_pct NUMERIC,
diversification_rank BIGINT
) AS $$
BEGIN
RETURN QUERY
WITH
-- Current market value and unrealized Profit and loss per holding lot
-- cast everything to numeric to avoid double precision propagation (error got fixed...)
holding_values AS (
SELECT
ph.portfolio_id AS hv_portfolio_id,
ph.stock_id AS hv_stock_id,
s.symbol AS hv_symbol,
ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value,
ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
FROM portfolio_holdings ph
JOIN stock s ON ph.stock_id = s.id
WHERE ph.quantity > 0
),
-- Aggregate per stock within each portfolio
stock_weights AS (
SELECT
hv.hv_portfolio_id AS sw_portfolio_id,
hv.hv_stock_id AS sw_stock_id,
hv.hv_symbol AS sw_symbol,
SUM(hv.market_value) AS stock_market_value,
SUM(hv.unrealized_pnl) AS stock_unrealized_pnl,
COUNT(*) AS lots_count
FROM holding_values hv
GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol
),
portfolio_totals AS (
SELECT
sw.sw_portfolio_id AS pt_portfolio_id,
SUM(sw.stock_market_value) AS total_holdings_value,
SUM(sw.stock_unrealized_pnl) AS total_unrealized_pnl,
COUNT(DISTINCT sw.sw_stock_id) AS num_stocks,
SUM(sw.lots_count) AS total_lots
FROM stock_weights sw
GROUP BY sw.sw_portfolio_id
),
-- HHI: Σ(weight_i)² where weight_i = stock_value / total_portfolio_value
-- HHI close to 1 = highly concentrated (risky), close to 0 = well diversified
hhi_calc AS (
SELECT
sw.sw_portfolio_id AS hhi_portfolio_id,
SUM(
POWER(
sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC),
2::NUMERIC
)
) AS hhi,
MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS max_weight_pct,
MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS min_weight_pct
FROM stock_weights sw
JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id
GROUP BY sw.sw_portfolio_id
),
dominant_stock AS (
SELECT DISTINCT ON (sw.sw_portfolio_id)
sw.sw_portfolio_id AS ds_portfolio_id,
sw.sw_symbol AS dominant_symbol,
ROUND(
sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC
, 2) AS dominant_weight_pct
FROM stock_weights sw
JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id
ORDER BY sw.sw_portfolio_id,
sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC
)
SELECT
u.id::INTEGER AS user_id,
u.username::TEXT,
p.id::INTEGER AS portfolio_id,
p.balance::NUMERIC AS portfolio_cash_balance,
COALESCE(pt.total_holdings_value, 0::NUMERIC) AS total_holdings_value,
(p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)) AS total_portfolio_value,
COALESCE(pt.num_stocks, 0)::BIGINT AS num_distinct_stocks,
COALESCE(pt.total_lots, 0)::BIGINT AS total_lots,
COALESCE(pt.total_unrealized_pnl, 0::NUMERIC) AS unrealized_pnl,
ROUND(
COALESCE(pt.total_unrealized_pnl, 0::NUMERIC)
/ NULLIF(
pt.total_holdings_value - COALESCE(pt.total_unrealized_pnl, 0::NUMERIC),
0::NUMERIC
) * 100::NUMERIC
, 2) AS unrealized_pnl_pct,
ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4) AS hhi_score,
CASE
WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK'
WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK'
ELSE 'WELL DIVERSIFIED'
END::TEXT AS risk_classification,
COALESCE(ds.dominant_symbol, 'N/A')::TEXT AS dominant_stock_symbol,
COALESCE(ds.dominant_weight_pct, 0::NUMERIC) AS dominant_stock_weight_pct,
COALESCE(hhi.max_weight_pct, 0::NUMERIC) AS max_single_weight_pct,
COALESCE(hhi.min_weight_pct, 0::NUMERIC) AS min_single_weight_pct,
RANK() OVER (
ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC
)::BIGINT AS diversification_rank
FROM users u
JOIN portfolios p ON u.id = p.user_id
LEFT JOIN portfolio_totals pt ON p.id = pt.pt_portfolio_id
LEFT JOIN hhi_calc hhi ON p.id = hhi.hhi_portfolio_id
LEFT JOIN dominant_stock ds ON p.id = ds.ds_portfolio_id
WHERE u.role = 'USER'
ORDER BY hhi_score ASC, total_portfolio_value DESC;
END;
$$ LANGUAGE plpgsql;
Релациона алгебра:
-- holding_values = π hv_portfolio_id ← ph.portfolio_id,
-- hv_stock_id ← ph.stock_id,
-- hv_symbol ← s.symbol,
-- market_value ← NUMERIC(ph.quantity) * NUMERIC(s.current_price),
-- unrealized_pnl ← NUMERIC(ph.quantity) * (NUMERIC(s.current_price) - NUMERIC(ph.avg_price)) (
-- σ quantity > 0 (portfolio_holdings) ⋈ stock_id = id stock )
-- stock_weights = γ hv_portfolio_id, hv_stock_id, hv_symbol;
-- sw_portfolio_id ← hv_portfolio_id,
-- sw_stock_id ← hv_stock_id,
-- sw_symbol ← hv_symbol,
-- stock_market_value ← SUM(market_value),
-- stock_unrealized_pnl ← SUM(unrealized_pnl),
-- lots_count ← COUNT(*) (holding_values)
-- portfolio_totals = γ sw_portfolio_id;
-- pt_portfolio_id ← sw_portfolio_id,
-- total_holdings_value ← SUM(stock_market_value),
-- total_unrealized_pnl ← SUM(stock_unrealized_pnl),
-- num_stocks ← COUNT(DISTINCT sw_stock_id),
-- total_lots ← SUM(lots_count) (stock_weights)
-- hhi_calc = γ sw_portfolio_id;
-- hhi_portfolio_id ← sw_portfolio_id,
-- hhi ← Σ POWER(stock_market_value / NULLIF(total_holdings_value, 0), 2),
-- max_weight_pct ← MAX(stock_market_value / NULLIF(total_holdings_value, 0) * 100),
-- min_weight_pct ← MIN(stock_market_value / NULLIF(total_holdings_value, 0) * 100) (
-- stock_weights ⋈ sw_portfolio_id = pt_portfolio_id portfolio_totals )
-- dominant_stock = π ds_portfolio_id ← sw_portfolio_id,
-- dominant_symbol ← sw_symbol,
-- dominant_weight_pct ← ROUND(stock_market_value / NULLIF(total_holdings_value, 0) * 100, 2)
-- TOP-1-PER-GROUP BY sw_portfolio_id,
-- ORDER BY stock_market_value / NULLIF(total_holdings_value, 0) DESC (
-- stock_weights ⋈ sw_portfolio_id = pt_portfolio_id portfolio_totals )
π user_id ← u.id,
username ← u.username,
portfolio_id ← p.id,
portfolio_cash_balance ← p.balance,
total_holdings_value ← COALESCE(pt.total_holdings_value, 0),
total_portfolio_value ← p.balance + COALESCE(pt.total_holdings_value, 0),
num_distinct_stocks ← COALESCE(pt.num_stocks, 0),
total_lots ← COALESCE(pt.total_lots, 0),
unrealized_pnl ← COALESCE(pt.total_unrealized_pnl, 0),
unrealized_pnl_pct ← ROUND(unrealized_pnl / NULLIF(total_holdings_value - unrealized_pnl, 0) * 100, 2),
hhi_score ← ROUND(COALESCE(hhi.hhi, 0), 4),
risk_classification ← CASE(hhi ≥ 0.25 → 'HIGH CONCENTRATION RISK',
hhi ≥ 0.10 → 'MEDIUM CONCENTRATION RISK',
else → 'WELL DIVERSIFIED'),
dominant_stock_symbol ← COALESCE(ds.dominant_symbol, 'N/A'),
dominant_stock_weight_pct ← COALESCE(ds.dominant_weight_pct, 0),
max_single_weight_pct ← COALESCE(hhi.max_weight_pct, 0),
min_single_weight_pct ← COALESCE(hhi.min_weight_pct, 0),
diversification_rank ← RANK() OVER ORDER BY COALESCE(hhi.hhi, 1) ASC (
τ hhi_score ASC, total_portfolio_value DESC (
σ role = 'USER' (users)
⋈ u.id = p.user_id portfolios
⟕ p.id = pt.pt_portfolio_id portfolio_totals
⟕ p.id = hhi.hhi_portfolio_id hhi_calc
⟕ p.id = ds.ds_portfolio_id dominant_stock
)
)
Детекција на волатилност и трговски активност по акција
Опис: Извештајот за секоја акција ги пресметува историската волатилност (стандардна девијација на дневни приноси пресметана со LAG прозорска функција), просечниот ценовен опсег во периодот, бројот на активни watchlist корисници, бројот на pending трговски барања и trend индикатор (UPTREND / DOWNTREND / SIDEWAYS) врз основа на наклон на линеарна регресија (least-squares slope) на историските цени. На крај се пресметуваат композитен activity_score и рангирање по волатилност и активност. Наменет за квартален/полугодишен мониторинг на добри акции наспроти лоши.
SQL:
CREATE OR REPLACE FUNCTION report_stock_volatility_and_activity(
p_from_date TIMESTAMP DEFAULT NULL,
p_to_date TIMESTAMP DEFAULT NULL
)
RETURNS TABLE (
stock_id INTEGER,
symbol TEXT,
stock_name TEXT,
current_price NUMERIC,
period_min_price NUMERIC,
period_max_price NUMERIC,
price_range_pct NUMERIC,
avg_daily_price NUMERIC,
historical_volatility_pct NUMERIC,
total_history_points BIGINT,
total_txn_volume NUMERIC,
total_txn_count BIGINT,
buy_txn_count BIGINT,
sell_txn_count BIGINT,
buy_sell_ratio NUMERIC,
active_watchlist_users BIGINT,
pending_trade_requests BIGINT,
trend_direction TEXT,
trend_slope NUMERIC,
activity_score NUMERIC,
volatility_rank BIGINT,
activity_rank BIGINT
) AS $$
BEGIN
RETURN QUERY
WITH
-- Volatility: stddev of daily returns computed with LAG window function
-- computing daily return per row using LAG window function
daily_returns AS (
SELECT
sh.stock_id AS dr_stock_id,
sh.price AS dr_price,
(sh.price - LAG(sh.price) OVER (
PARTITION BY sh.stock_id ORDER BY sh.timestamp
)) / NULLIF(LAG(sh.price) OVER (
PARTITION BY sh.stock_id ORDER BY sh.timestamp
), 0) AS dr_daily_return
FROM stock_history sh
WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date)
AND (p_to_date IS NULL OR sh.timestamp <= p_to_date)
),
-- aggregate per stock
price_stats AS (
SELECT
dr.dr_stock_id AS ps_stock_id,
COUNT(*) AS ps_total_points,
MIN(dr.dr_price) AS ps_min_price,
MAX(dr.dr_price) AS ps_max_price,
AVG(dr.dr_price) AS ps_avg_price,
COALESCE(STDDEV(dr.dr_daily_return) * 100, 0) AS ps_volatility_pct
FROM daily_returns dr
GROUP BY dr.dr_stock_id
),
-- trend least-squares linear regression slope on (row_number, price)
price_ordered AS (
SELECT
sh.stock_id AS po_stock_id,
ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp) AS po_rn,
sh.price AS po_price
FROM stock_history sh
WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date)
AND (p_to_date IS NULL OR sh.timestamp <= p_to_date)
),
trend_calc AS (
SELECT
po.po_stock_id AS tc_stock_id,
ROUND(
((COUNT(*) * SUM(po.po_rn * po.po_price) - SUM(po.po_rn) * SUM(po.po_price))
/ NULLIF(COUNT(*) * SUM(po.po_rn * po.po_rn) - SUM(po.po_rn) * SUM(po.po_rn), 0))::NUMERIC
, 6) AS tc_slope
FROM price_ordered po
GROUP BY po.po_stock_id
),
-- transctikomn stats per stock in period
txn_stats AS (
SELECT
t.stock_id AS ts_stock_id,
SUM(t.price * t.quantity) AS ts_total_volume,
COUNT(*) AS ts_total_count,
COUNT(*) FILTER (WHERE t.type = 'BUY') AS ts_buy_count,
COUNT(*) FILTER (WHERE t.type = 'SELL') AS ts_sell_count
FROM transactions t
WHERE (p_from_date IS NULL OR t.timestamp >= p_from_date)
AND (p_to_date IS NULL OR t.timestamp <= p_to_date)
GROUP BY t.stock_id
),
-- watchlist interest stock
watchlist_stats AS (
SELECT
w.stock_id AS ws_stock_id,
COUNT(DISTINCT w.user_id) AS ws_active_watchers
FROM watchlist w
GROUP BY w.stock_id
),
-- pending trade requests per stock
pending_stats AS (
SELECT
s.id AS pst_stock_id,
COUNT(tr.id) AS pst_pending_count
FROM trade_request tr
JOIN stock s ON tr.stock_symbol = s.symbol
WHERE tr.status = 'PENDING'
GROUP BY s.id
)
SELECT
s.id::INTEGER AS stock_id,
s.symbol::TEXT,
s.name::TEXT AS stock_name,
s.current_price::NUMERIC,
COALESCE(ps.ps_min_price, s.current_price)::NUMERIC AS period_min_price,
COALESCE(ps.ps_max_price, s.current_price)::NUMERIC AS period_max_price,
ROUND(
((COALESCE(ps.ps_max_price, s.current_price)
- COALESCE(ps.ps_min_price, s.current_price))
/ NULLIF(COALESCE(ps.ps_min_price, s.current_price), 0) * 100)::NUMERIC
, 2)::NUMERIC AS price_range_pct,
COALESCE(ps.ps_avg_price, s.current_price)::NUMERIC AS avg_daily_price,
COALESCE(ps.ps_volatility_pct, 0)::NUMERIC AS historical_volatility_pct,
COALESCE(ps.ps_total_points, 0)::BIGINT AS total_history_points,
COALESCE(ts.ts_total_volume, 0)::NUMERIC AS total_txn_volume,
COALESCE(ts.ts_total_count, 0)::BIGINT AS total_txn_count,
COALESCE(ts.ts_buy_count, 0)::BIGINT AS buy_txn_count,
COALESCE(ts.ts_sell_count, 0)::BIGINT AS sell_txn_count,
ROUND(
(COALESCE(ts.ts_buy_count, 0)::NUMERIC
/ NULLIF(COALESCE(ts.ts_sell_count, 0), 0))::NUMERIC
, 2)::NUMERIC AS buy_sell_ratio,
COALESCE(ws.ws_active_watchers, 0)::BIGINT AS active_watchlist_users,
COALESCE(pst.pst_pending_count, 0)::BIGINT AS pending_trade_requests,
CASE
WHEN tc.tc_slope > 0.01 THEN 'UPTREND'
WHEN tc.tc_slope < -0.01 THEN 'DOWNTREND'
ELSE 'SIDEWAYS'
END::TEXT AS trend_direction,
COALESCE(tc.tc_slope, 0)::NUMERIC AS trend_slope,
ROUND(
(COALESCE(ws.ws_active_watchers, 0) * 2.0
+ COALESCE(ts.ts_total_count, 0) * 1.0
+ COALESCE(pst.pst_pending_count, 0) * 3.0)::NUMERIC
, 2)::NUMERIC AS activity_score,
RANK() OVER (
ORDER BY COALESCE(ps.ps_volatility_pct, 0) DESC
)::BIGINT AS volatility_rank,
RANK() OVER (
ORDER BY (
COALESCE(ws.ws_active_watchers, 0) * 2.0
+ COALESCE(ts.ts_total_count, 0) * 1.0
+ COALESCE(pst.pst_pending_count, 0) * 3.0
) DESC
)::BIGINT AS activity_rank
FROM stock s
LEFT JOIN price_stats ps ON s.id = ps.ps_stock_id
LEFT JOIN trend_calc tc ON s.id = tc.tc_stock_id
LEFT JOIN txn_stats ts ON s.id = ts.ts_stock_id
LEFT JOIN watchlist_stats ws ON s.id = ws.ws_stock_id
LEFT JOIN pending_stats pst ON s.id = pst.pst_stock_id
ORDER BY historical_volatility_pct DESC, activity_score DESC;
END;
$$ LANGUAGE plpgsql;
Релациона алгебра:
-- price_stats = γ sh.stock_id;
-- ps_stock_id ← sh.stock_id,
-- ps_total_points ← COUNT(*),
-- ps_min_price ← MIN(sh.price),
-- ps_max_price ← MAX(sh.price),
-- ps_avg_price ← AVG(sh.price),
-- ps_volatility_pct ← STDDEV(daily_return) * 100,
-- каде daily_return = (price_t - price_{t-1}) / price_{t-1}
-- пресметан преку LAG прозорска функција (
-- σ timestamp ∈ [from, to] (stock_history sh) )
-- price_ordered = π po_stock_id ← sh.stock_id,
-- po_rn ← ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp),
-- po_price ← sh.price (
-- σ timestamp ∈ [from, to] (stock_history sh) )
-- trend_calc = γ po.po_stock_id;
-- tc_stock_id ← po_stock_id,
-- tc_slope ← (n·Σ(po_rn·po_price) − Σpo_rn·Σpo_price) / (n·Σpo_rn² − (Σpo_rn)²) (
-- price_ordered po )
-- txn_stats = γ t.stock_id;
-- ts_stock_id ← t.stock_id,
-- ts_total_volume ← SUM(t.price * t.quantity),
-- ts_total_count ← COUNT(*),
-- ts_buy_count ← COUNT(* | type = 'BUY'),
-- ts_sell_count ← COUNT(* | type = 'SELL') (
-- σ timestamp ∈ [from, to] (transactions t) )
-- watchlist_stats = γ w.stock_id;
-- ws_stock_id ← w.stock_id,
-- ws_active_watchers ← COUNT(DISTINCT w.user_id) (watchlist w)
-- pending_stats = γ s.id;
-- pst_stock_id ← s.id,
-- pst_pending_count ← COUNT(tr.id) (
-- σ status = 'PENDING' (trade_request tr) ⋈ trade_stock_symbol = symbol stock s )
π stock_id ← s.id, symbol ← s.symbol, stock_name ← s.name, current_price ← s.current_price,
period_min_price ← COALESCE(ps_min_price, current_price),
period_max_price ← COALESCE(ps_max_price, current_price),
price_range_pct ← (ps_max_price - ps_min_price) / NULLIF(ps_min_price, 0) * 100,
avg_daily_price ← COALESCE(ps_avg_price, current_price),
historical_volatility_pct ← COALESCE(ps_volatility_pct, 0),
total_history_points ← COALESCE(ps_total_points, 0),
total_txn_volume ← COALESCE(ts_total_volume, 0),
total_txn_count ← COALESCE(ts_total_count, 0),
buy_txn_count ← COALESCE(ts_buy_count, 0),
sell_txn_count ← COALESCE(ts_sell_count, 0),
buy_sell_ratio ← COALESCE(ts_buy_count, 0) / NULLIF(ts_sell_count, 0),
active_watchlist_users ← COALESCE(ws_active_watchers, 0),
pending_trade_requests ← COALESCE(pst_pending_count, 0),
trend_direction ← CASE(tc_slope > 0.01 → 'UPTREND', tc_slope < -0.01 → 'DOWNTREND', else → 'SIDEWAYS'),
trend_slope ← COALESCE(tc_slope, 0),
activity_score ← ws_active_watchers*2 + ts_total_count*1 + pst_pending_count*3,
volatility_rank ← RANK() OVER ORDER BY ps_volatility_pct DESC,
activity_rank ← RANK() OVER ORDER BY activity_score DESC (
τ historical_volatility_pct DESC, activity_score DESC (
stock s
⟕ s.id = ps.ps_stock_id price_stats ps
⟕ s.id = tc.tc_stock_id trend_calc tc
⟕ s.id = ts.ts_stock_id txn_stats ts
⟕ s.id = ws.ws_stock_id watchlist_stats ws
⟕ s.id = pst.pst_stock_id pending_stats pst
)
)
Note:
See TracWiki
for help on using the wiki.
