| | 4 | '''Опис:''' Извештајот за секој корисник го анализира неговото портфолио од аспект на диверзификација: пресметува Herfindahl-Hirschman Index (HHI) за концентрација на вложувања, го идентификува доминантниот холдинг, го пресметува нереализираниот P&L по холдинг (разлика помеѓу тековна цена и avg_price), вкупната вредност на портфолиото, бројот на различни акции и класификација на ризик (HIGH CONCENTRATION RISK / MEDIUM CONCENTRATION RISK / WELL DIVERSIFIED) врз основа на HHI. Наменет за полугодишен/годишен преглед на ризикот на портфолијата на корисниците. |
| | 5 | |
| | 6 | '''SQL:''' |
| | 7 | {{{ |
| | 8 | CREATE OR REPLACE FUNCTION report_portfolio_diversification() |
| | 9 | RETURNS TABLE ( |
| | 10 | user_id INTEGER, |
| | 11 | username TEXT, |
| | 12 | portfolio_id INTEGER, |
| | 13 | portfolio_cash_balance NUMERIC, |
| | 14 | total_holdings_value NUMERIC, |
| | 15 | total_portfolio_value NUMERIC, |
| | 16 | num_distinct_stocks BIGINT, |
| | 17 | total_lots BIGINT, |
| | 18 | unrealized_pnl NUMERIC, |
| | 19 | unrealized_pnl_pct NUMERIC, |
| | 20 | hhi_score NUMERIC, |
| | 21 | risk_classification TEXT, |
| | 22 | dominant_stock_symbol TEXT, |
| | 23 | dominant_stock_weight_pct NUMERIC, |
| | 24 | max_single_weight_pct NUMERIC, |
| | 25 | min_single_weight_pct NUMERIC, |
| | 26 | diversification_rank BIGINT |
| | 27 | ) AS $$ |
| | 28 | BEGIN |
| | 29 | RETURN QUERY |
| | 30 | WITH |
| | 31 | -- Current market value and unrealized Profit and loss per holding lot |
| | 32 | -- cast everything to numeric to avoid double precision propagation (error got fixed...) |
| | 33 | holding_values AS ( |
| | 34 | SELECT |
| | 35 | ph.portfolio_id AS hv_portfolio_id, |
| | 36 | ph.stock_id AS hv_stock_id, |
| | 37 | s.symbol AS hv_symbol, |
| | 38 | ph.quantity::NUMERIC * s.current_price::NUMERIC AS market_value, |
| | 39 | ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl |
| | 40 | FROM portfolio_holdings ph |
| | 41 | JOIN stock s ON ph.stock_id = s.id |
| | 42 | WHERE ph.quantity > 0 |
| | 43 | ), |
| | 44 | -- Aggregate per stock within each portfolio |
| | 45 | stock_weights AS ( |
| | 46 | SELECT |
| | 47 | hv.hv_portfolio_id AS sw_portfolio_id, |
| | 48 | hv.hv_stock_id AS sw_stock_id, |
| | 49 | hv.hv_symbol AS sw_symbol, |
| | 50 | SUM(hv.market_value) AS stock_market_value, |
| | 51 | SUM(hv.unrealized_pnl) AS stock_unrealized_pnl, |
| | 52 | COUNT(*) AS lots_count |
| | 53 | FROM holding_values hv |
| | 54 | GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol |
| | 55 | ), |
| | 56 | portfolio_totals AS ( |
| | 57 | SELECT |
| | 58 | sw.sw_portfolio_id AS pt_portfolio_id, |
| | 59 | SUM(sw.stock_market_value) AS total_holdings_value, |
| | 60 | SUM(sw.stock_unrealized_pnl) AS total_unrealized_pnl, |
| | 61 | COUNT(DISTINCT sw.sw_stock_id) AS num_stocks, |
| | 62 | SUM(sw.lots_count) AS total_lots |
| | 63 | FROM stock_weights sw |
| | 64 | GROUP BY sw.sw_portfolio_id |
| | 65 | ), |
| | 66 | -- HHI: Σ(weight_i)² where weight_i = stock_value / total_portfolio_value |
| | 67 | -- HHI close to 1 = highly concentrated (risky), close to 0 = well diversified |
| | 68 | hhi_calc AS ( |
| | 69 | SELECT |
| | 70 | sw.sw_portfolio_id AS hhi_portfolio_id, |
| | 71 | SUM( |
| | 72 | POWER( |
| | 73 | sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC), |
| | 74 | 2::NUMERIC |
| | 75 | ) |
| | 76 | ) AS hhi, |
| | 77 | MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS max_weight_pct, |
| | 78 | MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC) AS min_weight_pct |
| | 79 | FROM stock_weights sw |
| | 80 | JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id |
| | 81 | GROUP BY sw.sw_portfolio_id |
| | 82 | ), |
| | 83 | dominant_stock AS ( |
| | 84 | SELECT DISTINCT ON (sw.sw_portfolio_id) |
| | 85 | sw.sw_portfolio_id AS ds_portfolio_id, |
| | 86 | sw.sw_symbol AS dominant_symbol, |
| | 87 | ROUND( |
| | 88 | sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC |
| | 89 | , 2) AS dominant_weight_pct |
| | 90 | FROM stock_weights sw |
| | 91 | JOIN portfolio_totals pt ON sw.sw_portfolio_id = pt.pt_portfolio_id |
| | 92 | ORDER BY sw.sw_portfolio_id, |
| | 93 | sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC |
| | 94 | ) |
| | 95 | |
| | 96 | SELECT |
| | 97 | u.id::INTEGER AS user_id, |
| | 98 | u.username::TEXT, |
| | 99 | p.id::INTEGER AS portfolio_id, |
| | 100 | p.balance::NUMERIC AS portfolio_cash_balance, |
| | 101 | COALESCE(pt.total_holdings_value, 0::NUMERIC) AS total_holdings_value, |
| | 102 | (p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)) AS total_portfolio_value, |
| | 103 | COALESCE(pt.num_stocks, 0)::BIGINT AS num_distinct_stocks, |
| | 104 | COALESCE(pt.total_lots, 0)::BIGINT AS total_lots, |
| | 105 | COALESCE(pt.total_unrealized_pnl, 0::NUMERIC) AS unrealized_pnl, |
| | 106 | ROUND( |
| | 107 | COALESCE(pt.total_unrealized_pnl, 0::NUMERIC) |
| | 108 | / NULLIF( |
| | 109 | pt.total_holdings_value - COALESCE(pt.total_unrealized_pnl, 0::NUMERIC), |
| | 110 | 0::NUMERIC |
| | 111 | ) * 100::NUMERIC |
| | 112 | , 2) AS unrealized_pnl_pct, |
| | 113 | ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4) AS hhi_score, |
| | 114 | CASE |
| | 115 | WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK' |
| | 116 | WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK' |
| | 117 | ELSE 'WELL DIVERSIFIED' |
| | 118 | END::TEXT AS risk_classification, |
| | 119 | COALESCE(ds.dominant_symbol, 'N/A')::TEXT AS dominant_stock_symbol, |
| | 120 | COALESCE(ds.dominant_weight_pct, 0::NUMERIC) AS dominant_stock_weight_pct, |
| | 121 | COALESCE(hhi.max_weight_pct, 0::NUMERIC) AS max_single_weight_pct, |
| | 122 | COALESCE(hhi.min_weight_pct, 0::NUMERIC) AS min_single_weight_pct, |
| | 123 | RANK() OVER ( |
| | 124 | ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC |
| | 125 | )::BIGINT AS diversification_rank |
| | 126 | FROM users u |
| | 127 | JOIN portfolios p ON u.id = p.user_id |
| | 128 | LEFT JOIN portfolio_totals pt ON p.id = pt.pt_portfolio_id |
| | 129 | LEFT JOIN hhi_calc hhi ON p.id = hhi.hhi_portfolio_id |
| | 130 | LEFT JOIN dominant_stock ds ON p.id = ds.ds_portfolio_id |
| | 131 | WHERE u.role = 'USER' |
| | 132 | ORDER BY hhi_score ASC, total_portfolio_value DESC; |
| | 133 | END; |
| | 134 | $$ LANGUAGE plpgsql; |
| | 135 | |
| | 136 | }}} |
| | 137 | |
| | 138 | '''Релациона алгебра:''' |
| | 139 | {{{ |
| | 140 | -- holding_values = π hv_portfolio_id ← ph.portfolio_id, |
| | 141 | -- hv_stock_id ← ph.stock_id, |
| | 142 | -- hv_symbol ← s.symbol, |
| | 143 | -- market_value ← NUMERIC(ph.quantity) * NUMERIC(s.current_price), |
| | 144 | -- unrealized_pnl ← NUMERIC(ph.quantity) * (NUMERIC(s.current_price) - NUMERIC(ph.avg_price)) ( |
| | 145 | -- σ quantity > 0 (portfolio_holdings) ⋈ stock_id = id stock ) |
| | 146 | |
| | 147 | -- stock_weights = γ hv_portfolio_id, hv_stock_id, hv_symbol; |
| | 148 | -- sw_portfolio_id ← hv_portfolio_id, |
| | 149 | -- sw_stock_id ← hv_stock_id, |
| | 150 | -- sw_symbol ← hv_symbol, |
| | 151 | -- stock_market_value ← SUM(market_value), |
| | 152 | -- stock_unrealized_pnl ← SUM(unrealized_pnl), |
| | 153 | -- lots_count ← COUNT(*) (holding_values) |
| | 154 | |
| | 155 | -- portfolio_totals = γ sw_portfolio_id; |
| | 156 | -- pt_portfolio_id ← sw_portfolio_id, |
| | 157 | -- total_holdings_value ← SUM(stock_market_value), |
| | 158 | -- total_unrealized_pnl ← SUM(stock_unrealized_pnl), |
| | 159 | -- num_stocks ← COUNT(DISTINCT sw_stock_id), |
| | 160 | -- total_lots ← SUM(lots_count) (stock_weights) |
| | 161 | |
| | 162 | -- hhi_calc = γ sw_portfolio_id; |
| | 163 | -- hhi_portfolio_id ← sw_portfolio_id, |
| | 164 | -- hhi ← Σ POWER(stock_market_value / NULLIF(total_holdings_value, 0), 2), |
| | 165 | -- max_weight_pct ← MAX(stock_market_value / NULLIF(total_holdings_value, 0) * 100), |
| | 166 | -- min_weight_pct ← MIN(stock_market_value / NULLIF(total_holdings_value, 0) * 100) ( |
| | 167 | -- stock_weights ⋈ sw_portfolio_id = pt_portfolio_id portfolio_totals ) |
| | 168 | |
| | 169 | -- dominant_stock = π ds_portfolio_id ← sw_portfolio_id, |
| | 170 | -- dominant_symbol ← sw_symbol, |
| | 171 | -- dominant_weight_pct ← ROUND(stock_market_value / NULLIF(total_holdings_value, 0) * 100, 2) |
| | 172 | -- TOP-1-PER-GROUP BY sw_portfolio_id, |
| | 173 | -- ORDER BY stock_market_value / NULLIF(total_holdings_value, 0) DESC ( |
| | 174 | -- stock_weights ⋈ sw_portfolio_id = pt_portfolio_id portfolio_totals ) |
| | 175 | |
| | 176 | π user_id ← u.id, |
| | 177 | username ← u.username, |
| | 178 | portfolio_id ← p.id, |
| | 179 | portfolio_cash_balance ← p.balance, |
| | 180 | total_holdings_value ← COALESCE(pt.total_holdings_value, 0), |
| | 181 | total_portfolio_value ← p.balance + COALESCE(pt.total_holdings_value, 0), |
| | 182 | num_distinct_stocks ← COALESCE(pt.num_stocks, 0), |
| | 183 | total_lots ← COALESCE(pt.total_lots, 0), |
| | 184 | unrealized_pnl ← COALESCE(pt.total_unrealized_pnl, 0), |
| | 185 | unrealized_pnl_pct ← ROUND(unrealized_pnl / NULLIF(total_holdings_value - unrealized_pnl, 0) * 100, 2), |
| | 186 | hhi_score ← ROUND(COALESCE(hhi.hhi, 0), 4), |
| | 187 | risk_classification ← CASE(hhi ≥ 0.25 → 'HIGH CONCENTRATION RISK', |
| | 188 | hhi ≥ 0.10 → 'MEDIUM CONCENTRATION RISK', |
| | 189 | else → 'WELL DIVERSIFIED'), |
| | 190 | dominant_stock_symbol ← COALESCE(ds.dominant_symbol, 'N/A'), |
| | 191 | dominant_stock_weight_pct ← COALESCE(ds.dominant_weight_pct, 0), |
| | 192 | max_single_weight_pct ← COALESCE(hhi.max_weight_pct, 0), |
| | 193 | min_single_weight_pct ← COALESCE(hhi.min_weight_pct, 0), |
| | 194 | diversification_rank ← RANK() OVER ORDER BY COALESCE(hhi.hhi, 1) ASC ( |
| | 195 | |
| | 196 | τ hhi_score ASC, total_portfolio_value DESC ( |
| | 197 | |
| | 198 | σ role = 'USER' (users) |
| | 199 | ⋈ u.id = p.user_id portfolios |
| | 200 | ⟕ p.id = pt.pt_portfolio_id portfolio_totals |
| | 201 | ⟕ p.id = hhi.hhi_portfolio_id hhi_calc |
| | 202 | ⟕ p.id = ds.ds_portfolio_id dominant_stock |
| | 203 | |
| | 204 | ) |
| | 205 | ) |
| | 206 | }}} |
| | 207 | |
| | 208 | ---- |
| | 209 | |
| | 210 | == Детекција на волатилност и трговски активност по акција == |
| | 211 | |
| | 212 | '''Опис:''' Извештајот за секоја акција ги пресметува историската волатилност (стандардна девијација на дневни приноси пресметана со LAG прозорска функција), просечниот ценовен опсег во периодот, бројот на активни watchlist корисници, бројот на pending трговски барања и trend индикатор (UPTREND / DOWNTREND / SIDEWAYS) врз основа на наклон на линеарна регресија (least-squares slope) на историските цени. На крај се пресметуваат композитен activity_score и рангирање по волатилност и активност. Наменет за квартален/полугодишен мониторинг на добри акции наспроти лоши. |
| | 213 | |
| | 214 | '''SQL:''' |
| | 215 | {{{ |
| | 216 | CREATE OR REPLACE FUNCTION report_stock_volatility_and_activity( |
| | 217 | p_from_date TIMESTAMP DEFAULT NULL, |
| | 218 | p_to_date TIMESTAMP DEFAULT NULL |
| | 219 | ) |
| | 220 | RETURNS TABLE ( |
| | 221 | stock_id INTEGER, |
| | 222 | symbol TEXT, |
| | 223 | stock_name TEXT, |
| | 224 | current_price NUMERIC, |
| | 225 | period_min_price NUMERIC, |
| | 226 | period_max_price NUMERIC, |
| | 227 | price_range_pct NUMERIC, |
| | 228 | avg_daily_price NUMERIC, |
| | 229 | historical_volatility_pct NUMERIC, |
| | 230 | total_history_points BIGINT, |
| | 231 | total_txn_volume NUMERIC, |
| | 232 | total_txn_count BIGINT, |
| | 233 | buy_txn_count BIGINT, |
| | 234 | sell_txn_count BIGINT, |
| | 235 | buy_sell_ratio NUMERIC, |
| | 236 | active_watchlist_users BIGINT, |
| | 237 | pending_trade_requests BIGINT, |
| | 238 | trend_direction TEXT, |
| | 239 | trend_slope NUMERIC, |
| | 240 | activity_score NUMERIC, |
| | 241 | volatility_rank BIGINT, |
| | 242 | activity_rank BIGINT |
| | 243 | ) AS $$ |
| | 244 | BEGIN |
| | 245 | RETURN QUERY |
| | 246 | WITH |
| | 247 | -- Volatility: stddev of daily returns computed with LAG window function |
| | 248 | -- computing daily return per row using LAG window function |
| | 249 | daily_returns AS ( |
| | 250 | SELECT |
| | 251 | sh.stock_id AS dr_stock_id, |
| | 252 | sh.price AS dr_price, |
| | 253 | (sh.price - LAG(sh.price) OVER ( |
| | 254 | PARTITION BY sh.stock_id ORDER BY sh.timestamp |
| | 255 | )) / NULLIF(LAG(sh.price) OVER ( |
| | 256 | PARTITION BY sh.stock_id ORDER BY sh.timestamp |
| | 257 | ), 0) AS dr_daily_return |
| | 258 | FROM stock_history sh |
| | 259 | WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date) |
| | 260 | AND (p_to_date IS NULL OR sh.timestamp <= p_to_date) |
| | 261 | ), |
| | 262 | -- aggregate per stock |
| | 263 | price_stats AS ( |
| | 264 | SELECT |
| | 265 | dr.dr_stock_id AS ps_stock_id, |
| | 266 | COUNT(*) AS ps_total_points, |
| | 267 | MIN(dr.dr_price) AS ps_min_price, |
| | 268 | MAX(dr.dr_price) AS ps_max_price, |
| | 269 | AVG(dr.dr_price) AS ps_avg_price, |
| | 270 | COALESCE(STDDEV(dr.dr_daily_return) * 100, 0) AS ps_volatility_pct |
| | 271 | FROM daily_returns dr |
| | 272 | GROUP BY dr.dr_stock_id |
| | 273 | ), |
| | 274 | -- trend least-squares linear regression slope on (row_number, price) |
| | 275 | |
| | 276 | price_ordered AS ( |
| | 277 | SELECT |
| | 278 | sh.stock_id AS po_stock_id, |
| | 279 | ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp) AS po_rn, |
| | 280 | sh.price AS po_price |
| | 281 | FROM stock_history sh |
| | 282 | WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date) |
| | 283 | AND (p_to_date IS NULL OR sh.timestamp <= p_to_date) |
| | 284 | ), |
| | 285 | trend_calc AS ( |
| | 286 | SELECT |
| | 287 | po.po_stock_id AS tc_stock_id, |
| | 288 | ROUND( |
| | 289 | ((COUNT(*) * SUM(po.po_rn * po.po_price) - SUM(po.po_rn) * SUM(po.po_price)) |
| | 290 | / NULLIF(COUNT(*) * SUM(po.po_rn * po.po_rn) - SUM(po.po_rn) * SUM(po.po_rn), 0))::NUMERIC |
| | 291 | , 6) AS tc_slope |
| | 292 | FROM price_ordered po |
| | 293 | GROUP BY po.po_stock_id |
| | 294 | ), |
| | 295 | -- transctikomn stats per stock in period |
| | 296 | txn_stats AS ( |
| | 297 | SELECT |
| | 298 | t.stock_id AS ts_stock_id, |
| | 299 | SUM(t.price * t.quantity) AS ts_total_volume, |
| | 300 | COUNT(*) AS ts_total_count, |
| | 301 | COUNT(*) FILTER (WHERE t.type = 'BUY') AS ts_buy_count, |
| | 302 | COUNT(*) FILTER (WHERE t.type = 'SELL') AS ts_sell_count |
| | 303 | FROM transactions t |
| | 304 | WHERE (p_from_date IS NULL OR t.timestamp >= p_from_date) |
| | 305 | AND (p_to_date IS NULL OR t.timestamp <= p_to_date) |
| | 306 | GROUP BY t.stock_id |
| | 307 | ), |
| | 308 | -- watchlist interest stock |
| | 309 | watchlist_stats AS ( |
| | 310 | SELECT |
| | 311 | w.stock_id AS ws_stock_id, |
| | 312 | COUNT(DISTINCT w.user_id) AS ws_active_watchers |
| | 313 | FROM watchlist w |
| | 314 | GROUP BY w.stock_id |
| | 315 | ), |
| | 316 | -- pending trade requests per stock |
| | 317 | pending_stats AS ( |
| | 318 | SELECT |
| | 319 | s.id AS pst_stock_id, |
| | 320 | COUNT(tr.id) AS pst_pending_count |
| | 321 | FROM trade_request tr |
| | 322 | JOIN stock s ON tr.stock_symbol = s.symbol |
| | 323 | WHERE tr.status = 'PENDING' |
| | 324 | GROUP BY s.id |
| | 325 | ) |
| | 326 | |
| | 327 | SELECT |
| | 328 | s.id::INTEGER AS stock_id, |
| | 329 | s.symbol::TEXT, |
| | 330 | s.name::TEXT AS stock_name, |
| | 331 | s.current_price::NUMERIC, |
| | 332 | COALESCE(ps.ps_min_price, s.current_price)::NUMERIC AS period_min_price, |
| | 333 | COALESCE(ps.ps_max_price, s.current_price)::NUMERIC AS period_max_price, |
| | 334 | ROUND( |
| | 335 | ((COALESCE(ps.ps_max_price, s.current_price) |
| | 336 | - COALESCE(ps.ps_min_price, s.current_price)) |
| | 337 | / NULLIF(COALESCE(ps.ps_min_price, s.current_price), 0) * 100)::NUMERIC |
| | 338 | , 2)::NUMERIC AS price_range_pct, |
| | 339 | COALESCE(ps.ps_avg_price, s.current_price)::NUMERIC AS avg_daily_price, |
| | 340 | COALESCE(ps.ps_volatility_pct, 0)::NUMERIC AS historical_volatility_pct, |
| | 341 | COALESCE(ps.ps_total_points, 0)::BIGINT AS total_history_points, |
| | 342 | COALESCE(ts.ts_total_volume, 0)::NUMERIC AS total_txn_volume, |
| | 343 | COALESCE(ts.ts_total_count, 0)::BIGINT AS total_txn_count, |
| | 344 | COALESCE(ts.ts_buy_count, 0)::BIGINT AS buy_txn_count, |
| | 345 | COALESCE(ts.ts_sell_count, 0)::BIGINT AS sell_txn_count, |
| | 346 | ROUND( |
| | 347 | (COALESCE(ts.ts_buy_count, 0)::NUMERIC |
| | 348 | / NULLIF(COALESCE(ts.ts_sell_count, 0), 0))::NUMERIC |
| | 349 | , 2)::NUMERIC AS buy_sell_ratio, |
| | 350 | COALESCE(ws.ws_active_watchers, 0)::BIGINT AS active_watchlist_users, |
| | 351 | COALESCE(pst.pst_pending_count, 0)::BIGINT AS pending_trade_requests, |
| | 352 | CASE |
| | 353 | WHEN tc.tc_slope > 0.01 THEN 'UPTREND' |
| | 354 | WHEN tc.tc_slope < -0.01 THEN 'DOWNTREND' |
| | 355 | ELSE 'SIDEWAYS' |
| | 356 | END::TEXT AS trend_direction, |
| | 357 | COALESCE(tc.tc_slope, 0)::NUMERIC AS trend_slope, |
| | 358 | ROUND( |
| | 359 | (COALESCE(ws.ws_active_watchers, 0) * 2.0 |
| | 360 | + COALESCE(ts.ts_total_count, 0) * 1.0 |
| | 361 | + COALESCE(pst.pst_pending_count, 0) * 3.0)::NUMERIC |
| | 362 | , 2)::NUMERIC AS activity_score, |
| | 363 | RANK() OVER ( |
| | 364 | ORDER BY COALESCE(ps.ps_volatility_pct, 0) DESC |
| | 365 | )::BIGINT AS volatility_rank, |
| | 366 | RANK() OVER ( |
| | 367 | ORDER BY ( |
| | 368 | COALESCE(ws.ws_active_watchers, 0) * 2.0 |
| | 369 | + COALESCE(ts.ts_total_count, 0) * 1.0 |
| | 370 | + COALESCE(pst.pst_pending_count, 0) * 3.0 |
| | 371 | ) DESC |
| | 372 | )::BIGINT AS activity_rank |
| | 373 | FROM stock s |
| | 374 | LEFT JOIN price_stats ps ON s.id = ps.ps_stock_id |
| | 375 | LEFT JOIN trend_calc tc ON s.id = tc.tc_stock_id |
| | 376 | LEFT JOIN txn_stats ts ON s.id = ts.ts_stock_id |
| | 377 | LEFT JOIN watchlist_stats ws ON s.id = ws.ws_stock_id |
| | 378 | LEFT JOIN pending_stats pst ON s.id = pst.pst_stock_id |
| | 379 | ORDER BY historical_volatility_pct DESC, activity_score DESC; |
| | 380 | END; |
| | 381 | $$ LANGUAGE plpgsql; |
| | 382 | }}} |
| | 383 | |
| | 384 | '''Релациона алгебра:''' |
| | 385 | {{{ |
| | 386 | -- price_stats = γ sh.stock_id; |
| | 387 | -- ps_stock_id ← sh.stock_id, |
| | 388 | -- ps_total_points ← COUNT(*), |
| | 389 | -- ps_min_price ← MIN(sh.price), |
| | 390 | -- ps_max_price ← MAX(sh.price), |
| | 391 | -- ps_avg_price ← AVG(sh.price), |
| | 392 | -- ps_volatility_pct ← STDDEV(daily_return) * 100, |
| | 393 | -- каде daily_return = (price_t - price_{t-1}) / price_{t-1} |
| | 394 | -- пресметан преку LAG прозорска функција ( |
| | 395 | -- σ timestamp ∈ [from, to] (stock_history sh) ) |
| | 396 | |
| | 397 | -- price_ordered = π po_stock_id ← sh.stock_id, |
| | 398 | -- po_rn ← ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp), |
| | 399 | -- po_price ← sh.price ( |
| | 400 | -- σ timestamp ∈ [from, to] (stock_history sh) ) |
| | 401 | |
| | 402 | -- trend_calc = γ po.po_stock_id; |
| | 403 | -- tc_stock_id ← po_stock_id, |
| | 404 | -- tc_slope ← (n·Σ(po_rn·po_price) − Σpo_rn·Σpo_price) / (n·Σpo_rn² − (Σpo_rn)²) ( |
| | 405 | -- price_ordered po ) |
| | 406 | |
| | 407 | -- txn_stats = γ t.stock_id; |
| | 408 | -- ts_stock_id ← t.stock_id, |
| | 409 | -- ts_total_volume ← SUM(t.price * t.quantity), |
| | 410 | -- ts_total_count ← COUNT(*), |
| | 411 | -- ts_buy_count ← COUNT(* | type = 'BUY'), |
| | 412 | -- ts_sell_count ← COUNT(* | type = 'SELL') ( |
| | 413 | -- σ timestamp ∈ [from, to] (transactions t) ) |
| | 414 | |
| | 415 | -- watchlist_stats = γ w.stock_id; |
| | 416 | -- ws_stock_id ← w.stock_id, |
| | 417 | -- ws_active_watchers ← COUNT(DISTINCT w.user_id) (watchlist w) |
| | 418 | |
| | 419 | -- pending_stats = γ s.id; |
| | 420 | -- pst_stock_id ← s.id, |
| | 421 | -- pst_pending_count ← COUNT(tr.id) ( |
| | 422 | -- σ status = 'PENDING' (trade_request tr) ⋈ trade_stock_symbol = symbol stock s ) |
| | 423 | |
| | 424 | π stock_id ← s.id, symbol ← s.symbol, stock_name ← s.name, current_price ← s.current_price, |
| | 425 | period_min_price ← COALESCE(ps_min_price, current_price), |
| | 426 | period_max_price ← COALESCE(ps_max_price, current_price), |
| | 427 | price_range_pct ← (ps_max_price - ps_min_price) / NULLIF(ps_min_price, 0) * 100, |
| | 428 | avg_daily_price ← COALESCE(ps_avg_price, current_price), |
| | 429 | historical_volatility_pct ← COALESCE(ps_volatility_pct, 0), |
| | 430 | total_history_points ← COALESCE(ps_total_points, 0), |
| | 431 | total_txn_volume ← COALESCE(ts_total_volume, 0), |
| | 432 | total_txn_count ← COALESCE(ts_total_count, 0), |
| | 433 | buy_txn_count ← COALESCE(ts_buy_count, 0), |
| | 434 | sell_txn_count ← COALESCE(ts_sell_count, 0), |
| | 435 | buy_sell_ratio ← COALESCE(ts_buy_count, 0) / NULLIF(ts_sell_count, 0), |
| | 436 | active_watchlist_users ← COALESCE(ws_active_watchers, 0), |
| | 437 | pending_trade_requests ← COALESCE(pst_pending_count, 0), |
| | 438 | trend_direction ← CASE(tc_slope > 0.01 → 'UPTREND', tc_slope < -0.01 → 'DOWNTREND', else → 'SIDEWAYS'), |
| | 439 | trend_slope ← COALESCE(tc_slope, 0), |
| | 440 | activity_score ← ws_active_watchers*2 + ts_total_count*1 + pst_pending_count*3, |
| | 441 | volatility_rank ← RANK() OVER ORDER BY ps_volatility_pct DESC, |
| | 442 | activity_rank ← RANK() OVER ORDER BY activity_score DESC ( |
| | 443 | |
| | 444 | τ historical_volatility_pct DESC, activity_score DESC ( |
| | 445 | |
| | 446 | stock s |
| | 447 | ⟕ s.id = ps.ps_stock_id price_stats ps |
| | 448 | ⟕ s.id = tc.tc_stock_id trend_calc tc |
| | 449 | ⟕ s.id = ts.ts_stock_id txn_stats ts |
| | 450 | ⟕ s.id = ws.ws_stock_id watchlist_stats ws |
| | 451 | ⟕ s.id = pst.pst_stock_id pending_stats pst |
| | 452 | |
| | 453 | ) |
| | 454 | ) |
| | 455 | }}} |
