Context Navigation

Changes between Version 3 and Version 4 of otherdevelopment

Timestamp:: 03/04/26 21:40:01 (2 weeks ago)
Author:: 231020
Comment:: --

Legend:

: Unmodified
: Added
: Removed
: Modified

otherdevelopment

-              v3
+              v4
 '''SQL:'''
 {{{
+-- Core query inside report_stock_volatility_and_activity()
+WITH 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 sh.timestamp >= p_from_date
+      AND sh.timestamp <= p_to_date
+),
+price_stats AS (
+    SELECT dr.dr_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
+),
+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 t.timestamp >= p_from_date
+      AND t.timestamp <= p_to_date
+    GROUP BY t.stock_id
+CREATE OR REPLACE FUNCTION report_stock_volatility_and_activity(
+    p_from_date TIMESTAMP DEFAULT NULL,
+    p_to_date   TIMESTAMP DEFAULT NULL
+)
+SELECT s.id, s.symbol, s.name, s.current_price, ...
+FROM stock s
+LEFT JOIN price_stats ps ON s.id = ps.dr_stock_id
+LEFT JOIN txn_stats   ts ON s.id = ts.ts_stock_id
+ORDER BY historical_volatility_pct DESC, activity_score DESC;
+    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;
 }}}
 …
 '''EXPLAIN ANALYZE без индекси:'''
 {{{
+EXPLAIN ANALYZE
+SELECT sh.stock_id, sh.price, sh.timestamp
+FROM stock_history sh
+WHERE sh.timestamp >= '2024-01-01'
+  AND sh.timestamp <= '2024-12-31'
+ORDER BY sh.stock_id, sh.timestamp;
+EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31');
 -- резултат:
+Sort  (cost=31.85..31.87 rows=7 width=20) (actual time=0.051..0.052 rows=0 loops=1)
+  Sort Key: stock_id, "timestamp"
+  Sort Method: quicksort  Memory: 25kB
+  ->  Seq Scan on stock_history sh  (cost=0.00..31.75 rows=7 width=20) (actual time=0.018..0.018 rows=0 loops=1)
+        Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))
+        Rows Removed by Filter: 8
+Planning Time: 0.417 ms
+Execution Time: 0.077 ms
+QUERY PLAN                                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------------------+
+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)|
+Planning Time: 0.094 ms                                                                                                                  |
+Execution Time: 15.248 ms                                                                                                                |
 }}}
 …
 '''EXPLAIN ANALYZE со индекси:'''
 {{{
+EXPLAIN ANALYZE
+SELECT sh.stock_id, sh.price, sh.timestamp
+FROM stock_history sh
+WHERE sh.timestamp >= '2024-01-01'
+  AND sh.timestamp <= '2024-12-31'
+ORDER BY sh.stock_id, sh.timestamp;
+EXPLAIN ANALYZE SELECT * FROM report_stock_volatility_and_activity('2024-01-01', '2024-12-31');
 -- резултат:
+Sort  (cost=1.13..1.14 rows=1 width=20) (actual time=0.024..0.025 rows=0 loops=1)
+  Sort Key: stock_id, "timestamp"
+  Sort Method: quicksort  Memory: 25kB
+  ->  Seq Scan on stock_history sh  (cost=0.00..1.12 rows=1 width=20) (actual time=0.017..0.017 rows=0 loops=1)
+        Filter: (("timestamp" >= '2024-01-01'::date) AND ("timestamp" <= '2024-12-31'::date))
+        Rows Removed by Filter: 8
+Planning Time: 0.397 ms
+Execution Time: 0.054 ms
+QUERY PLAN                                                                                                                             |
+---------------------------------------------------------------------------------------------------------------------------------------+
+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)|
+Planning Time: 0.111 ms                                                                                                                |
+Execution Time: 6.693 ms                                                                                                               |
 }}}