Changes between Version 2 and Version 3 of otherdevelopment


Ignore:
Timestamp:
03/04/26 21:36:34 (2 weeks ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v2 v3  
    99'''SQL:'''
    1010{{{
    11 -- Core query inside report_portfolio_diversification()
    12 WITH holding_values AS (
    13     SELECT ph.portfolio_id AS hv_portfolio_id,
    14            ph.stock_id     AS hv_stock_id,
    15            s.symbol        AS hv_symbol,
    16            ph.quantity::NUMERIC * s.current_price::NUMERIC                           AS market_value,
    17            ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC) AS unrealized_pnl
    18     FROM portfolio_holdings ph
    19     JOIN stock s ON ph.stock_id = s.id
    20     WHERE ph.quantity > 0
    21 ),
    22 stock_weights AS (
    23     SELECT hv.hv_portfolio_id,
    24            hv.hv_stock_id,
    25            hv.hv_symbol,
    26            SUM(hv.market_value)   AS stock_market_value,
    27            SUM(hv.unrealized_pnl) AS stock_unrealized_pnl,
    28            COUNT(*)               AS lots_count
    29     FROM holding_values hv
    30     GROUP BY hv.hv_portfolio_id, hv.hv_stock_id, hv.hv_symbol
    31 ),
    32 portfolio_totals AS (
    33     SELECT sw.hv_portfolio_id,
    34            SUM(sw.stock_market_value)    AS total_holdings_value,
    35            SUM(sw.stock_unrealized_pnl)  AS total_unrealized_pnl,
    36            COUNT(DISTINCT sw.hv_stock_id) AS num_stocks,
    37            SUM(sw.lots_count)            AS total_lots
    38     FROM stock_weights sw
    39     GROUP BY sw.hv_portfolio_id
    40 )
    41 SELECT u.id, u.username, p.id, p.balance, ...
    42 FROM users u
    43 JOIN portfolios p ON u.id = p.user_id
    44 LEFT JOIN portfolio_totals pt  ON p.id = pt.hv_portfolio_id
    45 WHERE u.role = 'USER'
    46 ORDER BY hhi_score ASC, total_portfolio_value DESC;
     11CREATE OR REPLACE FUNCTION report_portfolio_diversification()
     12    RETURNS TABLE (
     13        user_id                   INTEGER,
     14        username                  TEXT,
     15        portfolio_id              INTEGER,
     16        portfolio_cash_balance    NUMERIC,
     17        total_holdings_value      NUMERIC,
     18        total_portfolio_value     NUMERIC,
     19        num_distinct_stocks       BIGINT,
     20        total_lots                BIGINT,
     21        unrealized_pnl            NUMERIC,
     22        unrealized_pnl_pct        NUMERIC,
     23        hhi_score                 NUMERIC,
     24        risk_classification       TEXT,
     25        dominant_stock_symbol     TEXT,
     26        dominant_stock_weight_pct NUMERIC,
     27        max_single_weight_pct     NUMERIC,
     28        min_single_weight_pct     NUMERIC,
     29        diversification_rank      BIGINT
     30    ) AS $$
     31BEGIN
     32    RETURN QUERY
     33        WITH
     34        holding_values AS (
     35            SELECT
     36                ph.portfolio_id                                                             AS hv_portfolio_id,
     37                ph.stock_id                                                                 AS hv_stock_id,
     38                s.symbol                                                                    AS hv_symbol,
     39                ph.quantity::NUMERIC * s.current_price::NUMERIC                            AS market_value,
     40                ph.quantity::NUMERIC * (s.current_price::NUMERIC - ph.avg_price::NUMERIC)  AS unrealized_pnl
     41            FROM portfolio_holdings ph
     42            JOIN stock s ON ph.stock_id = s.id
     43            WHERE ph.quantity > 0
     44        ),
     45        stock_weights AS (
     46            SELECT
     47                hv.hv_portfolio_id,
     48                hv.hv_stock_id,
     49                hv.hv_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.hv_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.hv_stock_id) AS num_stocks,
     62                SUM(sw.lots_count)             AS total_lots
     63            FROM stock_weights sw
     64            GROUP BY sw.hv_portfolio_id
     65        ),
     66        hhi_calc AS (
     67            SELECT
     68                sw.hv_portfolio_id AS hhi_portfolio_id,
     69                SUM(POWER(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC), 2::NUMERIC)) AS hhi,
     70                MAX(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC)     AS max_weight_pct,
     71                MIN(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC)     AS min_weight_pct
     72            FROM stock_weights sw
     73            JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
     74            GROUP BY sw.hv_portfolio_id
     75        ),
     76        dominant_stock AS (
     77            SELECT DISTINCT ON (sw.hv_portfolio_id)
     78                sw.hv_portfolio_id AS ds_portfolio_id,
     79                sw.hv_symbol       AS dominant_symbol,
     80                ROUND(sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) * 100::NUMERIC, 2) AS dominant_weight_pct
     81            FROM stock_weights sw
     82            JOIN portfolio_totals pt ON sw.hv_portfolio_id = pt.hv_portfolio_id
     83            ORDER BY sw.hv_portfolio_id,
     84                     sw.stock_market_value / NULLIF(pt.total_holdings_value, 0::NUMERIC) DESC
     85        )
     86        SELECT
     87            u.id::INTEGER,
     88            u.username::TEXT,
     89            p.id::INTEGER,
     90            p.balance::NUMERIC,
     91            COALESCE(pt.total_holdings_value, 0::NUMERIC),
     92            (p.balance::NUMERIC + COALESCE(pt.total_holdings_value, 0::NUMERIC)),
     93            COALESCE(pt.num_stocks, 0)::BIGINT,
     94            COALESCE(pt.total_lots, 0)::BIGINT,
     95            COALESCE(pt.total_unrealized_pnl, 0::NUMERIC),
     96            ROUND(COALESCE(pt.total_unrealized_pnl, 0::NUMERIC)
     97                / NULLIF(pt.total_holdings_value - COALESCE(pt.total_unrealized_pnl, 0::NUMERIC), 0::NUMERIC) * 100::NUMERIC, 2),
     98            ROUND(COALESCE(hhi.hhi, 0::NUMERIC), 4),
     99            CASE
     100                WHEN hhi.hhi >= 0.25 THEN 'HIGH CONCENTRATION RISK'
     101                WHEN hhi.hhi >= 0.10 THEN 'MEDIUM CONCENTRATION RISK'
     102                ELSE                      'WELL DIVERSIFIED'
     103            END::TEXT,
     104            COALESCE(ds.dominant_symbol, 'N/A')::TEXT,
     105            COALESCE(ds.dominant_weight_pct, 0::NUMERIC),
     106            COALESCE(hhi.max_weight_pct, 0::NUMERIC),
     107            COALESCE(hhi.min_weight_pct, 0::NUMERIC),
     108            RANK() OVER (ORDER BY COALESCE(hhi.hhi, 1::NUMERIC) ASC)::BIGINT
     109        FROM users u
     110        JOIN portfolios            p   ON u.id = p.user_id
     111        LEFT JOIN portfolio_totals pt  ON p.id = pt.hv_portfolio_id
     112        LEFT JOIN hhi_calc         hhi ON p.id = hhi.hhi_portfolio_id
     113        LEFT JOIN dominant_stock   ds  ON p.id = ds.ds_portfolio_id
     114        WHERE u.role = 'USER'
     115        ORDER BY hhi_score ASC, total_portfolio_value DESC;
     116END;
     117$$ LANGUAGE plpgsql;
    47118}}}
    48119
    … …  
    50121'''EXPLAIN ANALYZE без индекси:'''
    51122{{{
    52 EXPLAIN ANALYZE
    53 SELECT ph.portfolio_id, ph.stock_id, ph.quantity, s.current_price, s.symbol
    54 FROM portfolio_holdings ph
    55 JOIN stock s ON ph.stock_id = s.id
    56 WHERE ph.quantity > 0;
     123EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification();
    57124
    58125-- резултат:
    59 Hash Join  (cost=11.57..34.17 rows=313 width=544) (actual time=0.105..0.116 rows=4 loops=1)
    60   Hash Cond: (ph.stock_id = s.id)
    61   ->  Seq Scan on portfolio_holdings ph  (cost=0.00..21.75 rows=313 width=20) (actual time=0.034..0.042 rows=4 loops=1)
    62         Filter: (quantity > 0)
    63   ->  Hash  (cost=10.70..10.70 rows=70 width=532) (actual time=0.036..0.037 rows=4 loops=1)
    64         Buckets: 1024  Batches: 1  Memory Usage: 9kB
    65         ->  Seq Scan on stock s  (cost=0.00..10.70 rows=70 width=532) (actual time=0.019..0.020 rows=4 loops=1)
    66 Planning Time: 1.315 ms
    67 Execution Time: 0.180 ms
    68 }}}
     126QUERY PLAN                                                                                                                           |
     127-------------------------------------------------------------------------------------------------------------------------------------+
     128Function Scan on report_portfolio_diversification  (cost=0.25..10.25 rows=1000 width=416) (actual time=10.842..10.843 rows=2 loops=1)|
     129Planning Time: 0.084 ms                                                                                                              |
     130Execution Time: 12.265 ms                                                                                                            |
     131
     132}}}
     133
     134
    69135
    70136'''Индекси:'''
    … …  
    117183'''EXPLAIN ANALYZE со индекси:'''
    118184{{{
    119 EXPLAIN ANALYZE
    120 SELECT ph.portfolio_id, ph.stock_id, ph.quantity, s.current_price, s.symbol
    121 FROM portfolio_holdings ph
    122 JOIN stock s ON ph.stock_id = s.id
    123 WHERE ph.quantity > 0;
     185EXPLAIN ANALYZE SELECT * FROM report_portfolio_diversification();
    124186
    125187--  резултат:
    126 Nested Loop  (cost=0.14..9.44 rows=1 width=544) (actual time=0.034..0.042 rows=4 loops=1)
    127   ->  Seq Scan on portfolio_holdings ph  (cost=0.00..1.05 rows=1 width=20) (actual time=0.015..0.017 rows=4 loops=1)
    128         Filter: (quantity > 0)
    129   ->  Index Scan using stock_pkey on stock s  (cost=0.14..8.16 rows=1 width=532) (actual time=0.005..0.005 rows=1 loops=4)
    130         Index Cond: (id = ph.stock_id)
    131 Planning Time: 0.724 ms
    132 Execution Time: 0.086 ms
     188                                                                                                                       
     189-----------------------------------------------------------------------------------------------------------------------------------+
     190Function Scan on report_portfolio_diversification  (cost=0.25..10.25 rows=1000 width=416) (actual time=4.145..4.146 rows=2 loops=1)|
     191Planning Time: 0.070 ms                                                                                                            |
     192Execution Time: 4.212 ms                                                                                                           |
    133193}}}
    134194