Changes between Version 5 and Version 6 of otherdevelopment


Ignore:
Timestamp:
03/09/26 01:09:36 (10 days ago)
Author:
231020
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • otherdevelopment

    v5 v6  
    134134'''SQL:'''
    135135{{{
    136 CREATE OR REPLACE FUNCTION report_stock_volatility_and_activity(
    137     p_from_date TIMESTAMP DEFAULT NULL,
    138     p_to_date   TIMESTAMP DEFAULT NULL
    139 )
    140     RETURNS TABLE (
    141         stock_id                  INTEGER,
    142         symbol                    TEXT,
    143         stock_name                TEXT,
    144         current_price             NUMERIC,
    145         period_min_price          NUMERIC,
    146         period_max_price          NUMERIC,
    147         price_range_pct           NUMERIC,
    148         avg_daily_price           NUMERIC,
    149         historical_volatility_pct NUMERIC,
    150         total_history_points      BIGINT,
    151         total_txn_volume          NUMERIC,
    152         total_txn_count           BIGINT,
    153         buy_txn_count             BIGINT,
    154         sell_txn_count            BIGINT,
    155         buy_sell_ratio            NUMERIC,
    156         active_watchlist_users    BIGINT,
    157         pending_trade_requests    BIGINT,
    158         trend_direction           TEXT,
    159         trend_slope               NUMERIC,
    160         activity_score            NUMERIC,
    161         volatility_rank           BIGINT,
    162         activity_rank             BIGINT
    163     ) AS $$
     136CREATE OR REPLACE FUNCTION test_stock_volatility()
     137RETURNS void AS $$
     138DECLARE
     139  start_time timestamptz;
     140  end_time   timestamptz;
     141  duration   int;
    164142BEGIN
    165     RETURN QUERY
    166         WITH
    167         -- Volatility: stddev of daily returns computed with LAG window function
    168         -- computing daily return per row using LAG window function
    169         daily_returns AS (
    170             SELECT
    171                 sh.stock_id                AS dr_stock_id,
    172                 sh.price                   AS dr_price,
    173                 (sh.price - LAG(sh.price) OVER (
    174                     PARTITION BY sh.stock_id ORDER BY sh.timestamp
    175                 )) / NULLIF(LAG(sh.price) OVER (
    176                     PARTITION BY sh.stock_id ORDER BY sh.timestamp
    177                 ), 0)                      AS dr_daily_return
    178             FROM stock_history sh
    179             WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date)
    180               AND (p_to_date   IS NULL OR sh.timestamp <= p_to_date)
    181         ),
    182         -- aggregate per stock
    183         price_stats AS (
    184             SELECT
    185                 dr.dr_stock_id             AS ps_stock_id,
    186                 COUNT(*)                   AS ps_total_points,
    187                 MIN(dr.dr_price)           AS ps_min_price,
    188                 MAX(dr.dr_price)           AS ps_max_price,
    189                 AVG(dr.dr_price)           AS ps_avg_price,
    190                 COALESCE(STDDEV(dr.dr_daily_return) * 100, 0) AS ps_volatility_pct
    191             FROM daily_returns dr
    192             GROUP BY dr.dr_stock_id
    193         ),
    194         -- trend least-squares linear regression slope on (row_number, price)
    195      
    196         price_ordered AS (
    197             SELECT
    198                 sh.stock_id            AS po_stock_id,
    199                 ROW_NUMBER() OVER (PARTITION BY sh.stock_id ORDER BY sh.timestamp) AS po_rn,
    200                 sh.price                   AS po_price
    201             FROM stock_history sh
    202             WHERE (p_from_date IS NULL OR sh.timestamp >= p_from_date)
    203               AND (p_to_date   IS NULL OR sh.timestamp <= p_to_date)
    204         ),
    205         trend_calc AS (
    206             SELECT
    207                 po.po_stock_id             AS tc_stock_id,
    208                 ROUND(
    209                     ((COUNT(*) * SUM(po.po_rn * po.po_price) - SUM(po.po_rn) * SUM(po.po_price))
    210                     / NULLIF(COUNT(*) * SUM(po.po_rn * po.po_rn) - SUM(po.po_rn) * SUM(po.po_rn), 0))::NUMERIC
    211                 , 6)                       AS tc_slope
    212             FROM price_ordered po
    213             GROUP BY po.po_stock_id
    214         ),
    215         -- transctikomn stats per stock in period
    216         txn_stats AS (
    217             SELECT
    218                 t.stock_id                 AS ts_stock_id,
    219                 SUM(t.price * t.quantity)  AS ts_total_volume,
    220                 COUNT(*)                   AS ts_total_count,
    221                 COUNT(*) FILTER (WHERE t.type = 'BUY')  AS ts_buy_count,
    222                 COUNT(*) FILTER (WHERE t.type = 'SELL') AS ts_sell_count
    223             FROM transactions t
    224             WHERE (p_from_date IS NULL OR t.timestamp >= p_from_date)
    225               AND (p_to_date   IS NULL OR t.timestamp <= p_to_date)
    226             GROUP BY t.stock_id
    227         ),
    228         -- watchlist interest  stock
    229         watchlist_stats AS (
    230             SELECT
    231                 w.stock_id                 AS ws_stock_id,
    232                 COUNT(DISTINCT w.user_id)  AS ws_active_watchers
    233             FROM watchlist w
    234             GROUP BY w.stock_id
    235         ),
    236         -- pending trade requests per stock
    237         pending_stats AS (
    238             SELECT
    239                 s.id                       AS pst_stock_id,
    240                 COUNT(tr.id)               AS pst_pending_count
    241             FROM trade_request tr
    242             JOIN stock s ON tr.stock_symbol = s.symbol
    243             WHERE tr.status = 'PENDING'
    244             GROUP BY s.id
    245         )
    246 
    247         SELECT
    248             s.id::INTEGER                                                AS stock_id,
    249             s.symbol::TEXT,
    250             s.name::TEXT                                                 AS stock_name,
    251             s.current_price::NUMERIC,
    252             COALESCE(ps.ps_min_price, s.current_price)::NUMERIC         AS period_min_price,
    253             COALESCE(ps.ps_max_price, s.current_price)::NUMERIC         AS period_max_price,
    254             ROUND(
    255                 ((COALESCE(ps.ps_max_price, s.current_price)
    256                  - COALESCE(ps.ps_min_price, s.current_price))
    257                 / NULLIF(COALESCE(ps.ps_min_price, s.current_price), 0) * 100)::NUMERIC
    258             , 2)::NUMERIC                                                AS price_range_pct,
    259             COALESCE(ps.ps_avg_price, s.current_price)::NUMERIC         AS avg_daily_price,
    260             COALESCE(ps.ps_volatility_pct, 0)::NUMERIC                  AS historical_volatility_pct,
    261             COALESCE(ps.ps_total_points, 0)::BIGINT                     AS total_history_points,
    262             COALESCE(ts.ts_total_volume, 0)::NUMERIC                    AS total_txn_volume,
    263             COALESCE(ts.ts_total_count,  0)::BIGINT                     AS total_txn_count,
    264             COALESCE(ts.ts_buy_count,    0)::BIGINT                     AS buy_txn_count,
    265             COALESCE(ts.ts_sell_count,   0)::BIGINT                     AS sell_txn_count,
    266             ROUND(
    267                 (COALESCE(ts.ts_buy_count, 0)::NUMERIC
    268                 / NULLIF(COALESCE(ts.ts_sell_count, 0), 0))::NUMERIC
    269             , 2)::NUMERIC                                                AS buy_sell_ratio,
    270             COALESCE(ws.ws_active_watchers, 0)::BIGINT                  AS active_watchlist_users,
    271             COALESCE(pst.pst_pending_count, 0)::BIGINT                  AS pending_trade_requests,
    272             CASE
    273                 WHEN tc.tc_slope >  0.01 THEN 'UPTREND'
    274                 WHEN tc.tc_slope < -0.01 THEN 'DOWNTREND'
    275                 ELSE                          'SIDEWAYS'
    276             END::TEXT                                                    AS trend_direction,
    277             COALESCE(tc.tc_slope, 0)::NUMERIC                           AS trend_slope,
    278             ROUND(
    279                 (COALESCE(ws.ws_active_watchers, 0) * 2.0
    280                 + COALESCE(ts.ts_total_count,    0) * 1.0
    281                 + COALESCE(pst.pst_pending_count, 0) * 3.0)::NUMERIC
    282             , 2)::NUMERIC                                                AS activity_score,
    283             RANK() OVER (
    284                 ORDER BY COALESCE(ps.ps_volatility_pct, 0) DESC
    285             )::BIGINT                                                    AS volatility_rank,
    286             RANK() OVER (
    287                 ORDER BY (
    288                     COALESCE(ws.ws_active_watchers, 0) * 2.0
    289                     + COALESCE(ts.ts_total_count,    0) * 1.0
    290                     + COALESCE(pst.pst_pending_count, 0) * 3.0
    291                 ) DESC
    292             )::BIGINT                                                    AS activity_rank
    293         FROM stock s
    294         LEFT JOIN price_stats     ps  ON s.id = ps.ps_stock_id
    295         LEFT JOIN trend_calc      tc  ON s.id = tc.tc_stock_id
    296         LEFT JOIN txn_stats       ts  ON s.id = ts.ts_stock_id
    297         LEFT JOIN watchlist_stats ws  ON s.id = ws.ws_stock_id
    298         LEFT JOIN pending_stats   pst ON s.id = pst.pst_stock_id
    299         ORDER BY historical_volatility_pct DESC, activity_score DESC;
     143  start_time := clock_timestamp();
     144
     145  PERFORM
     146    sh.stock_id,
     147    sh.price,
     148    (sh.price - LAG(sh.price) OVER (
     149        PARTITION BY sh.stock_id ORDER BY sh.timestamp))
     150    / NULLIF(LAG(sh.price) OVER (
     151        PARTITION BY sh.stock_id ORDER BY sh.timestamp), 0) AS daily_return
     152  FROM stock_history sh
     153  WHERE sh.timestamp >= '2024-01-01'
     154    AND sh.timestamp <= '2024-12-31';
     155
     156  end_time := clock_timestamp();
     157  duration := round(1000 * (extract(epoch FROM end_time)
     158                           - extract(epoch FROM start_time)));
     159  RAISE NOTICE 'Query executed in: % ms', duration;
    300160END;
    301161$$ LANGUAGE plpgsql;
    302 }}}
    303 
    304 
    305 
    306 '''EXPLAIN ANALYZE без индекси:'''
    307 {{{
    308 EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31');
    309 -- резултат:
    310 QUERY PLAN                                                                                                                               |
    311 -----------------------------------------------------------------------------------------------------------------------------------------+
    312 Function Scan on report_stock_volatility_and_activity  (cost=0.25..10.25 rows=1000 width=484) (actual time=13.601..13.602 rows=4 loops=1)|
    313 Planning Time: 0.094 ms                                                                                                                  |
    314 Execution Time: 15.248 ms                                                                                                                |
    315 }}}
     162
     163
     164DROP INDEX IF EXISTS idx_sh_stock_id_timestamp;
     165DROP INDEX IF EXISTS idx_tx_stock_id_timestamp;
     166DROP INDEX IF EXISTS idx_watchlist_stock_id;
     167DROP INDEX IF EXISTS idx_tr_symbol_status;
     168
     169-- run 1: no index
     170SELECT test_stock_volatility();
     171
     172-- run 2: + idx_sh_stock_id_timestamp (composite)
     173CREATE INDEX idx_sh_stock_id_timestamp ON stock_history(stock_id, timestamp);
     174ANALYZE stock_history;
     175SELECT test_stock_volatility();
     176
     177-- run 3: + idx_tx_stock_id_timestamp (composite)
     178CREATE INDEX idx_tx_stock_id_timestamp ON transactions(stock_id, timestamp);
     179ANALYZE transactions;
     180SELECT test_stock_volatility();
     181
     182-- run 4: + idx_watchlist_stock_id
     183CREATE INDEX idx_watchlist_stock_id ON watchlist(stock_id);
     184ANALYZE watchlist;
     185SELECT test_stock_volatility();
     186
     187-- run 5: + idx_tr_symbol_status (composite)
     188CREATE INDEX idx_tr_symbol_status ON trade_request(stock_symbol, status);
     189ANALYZE trade_request;
     190SELECT test_stock_volatility();
     191
     192DROP FUNCTION test_stock_volatility();
     193}}}
     194
    316195
    317196
    … …  
    362241---
    363242
    364 '''EXPLAIN ANALYZE со индекси:'''
    365 {{{
    366 EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31');
    367 -- резултат:
    368 QUERY PLAN                                                                                                                             |
    369 ---------------------------------------------------------------------------------------------------------------------------------------+
    370 Function Scan on report_stock_volatility_and_activity  (cost=0.25..10.25 rows=1000 width=484) (actual time=6.624..6.625 rows=4 loops=1)|
    371 Planning Time: 0.111 ms                                                                                                                |
    372 Execution Time: 6.693 ms                                                                                                               |
    373 }}}
    374243
    375244'''Заклучок:'''
     245
     246Без индекси: 102ms
     247
     248Со индекси: 40ms
     249
    376250Имаме забрзување.
     251
    377252----
    378253