האם גאפים במניות תמיד נסגרים? ניתוח נתונים

WITH sessions AS
(
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS d,
        argMin(toFloat64(open), window_start)                AS session_open,
        argMax(toFloat64(close), window_start)               AS session_close,
        toFloat64(max(high))                                 AS session_high,
        toFloat64(min(low))                                  AS session_low
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'JPM', 'KO', 'WMT', 'XOM')
      AND window_start >= '2021-01-01'
      AND window_start <  '2026-07-01'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
    GROUP BY ticker, d
),
gapped AS
(
    SELECT
        d,
        session_open,
        session_high,
        session_low,
        lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY d ASC
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_close
    FROM sessions
),
measured AS
(
    SELECT
        abs(100 * (session_open / prior_close - 1)) AS gap_pct,
        toUInt8(if(session_open > prior_close,
                   session_low  <= prior_close,
                   session_high >= prior_close))    AS filled_same_session
    FROM gapped
    WHERE prior_close > 0
      AND d <= toDate('2026-02-28')
      AND abs(100 * (session_open / prior_close - 1)) >= 0.25
)
SELECT
    multiIf(gap_pct < 0.5, '0.25 to 0.5%',
            gap_pct < 1,   '0.5 to 1%',
            gap_pct < 2,   '1 to 2%',
            gap_pct < 4,   '2 to 4%',
                           '4% or more')         AS gap_bucket,
    count()                                      AS gap_days,
    round(100 * avg(filled_same_session), 1)     AS same_session_fill_pct
FROM measured
GROUP BY gap_bucket
ORDER BY min(gap_pct) ASC

WITH sessions AS
(
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS d,
        argMin(toFloat64(open), window_start)                AS session_open,
        argMax(toFloat64(close), window_start)               AS session_close,
        toFloat64(max(high))                                 AS session_high,
        toFloat64(min(low))                                  AS session_low
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'JPM', 'KO', 'WMT', 'XOM')
      AND window_start >= '2021-01-01'
      AND window_start <  '2026-07-01'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
    GROUP BY ticker, d
),
paths AS
(
    SELECT
        d,
        session_open,
        session_high,
        session_low,
        lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY d ASC
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)                                                          AS prior_close,
        min(session_low)  OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING)   AS low_5,
        max(session_high) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING)   AS high_5,
        min(session_low)  OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 19 FOLLOWING)  AS low_20,
        max(session_high) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 19 FOLLOWING)  AS high_20,
        min(session_low)  OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 59 FOLLOWING)  AS low_60,
        max(session_high) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 59 FOLLOWING)  AS high_60
    FROM sessions
),
gaps AS
(
    SELECT
        session_open > prior_close AS gap_up,
        prior_close,
        session_low,
        session_high,
        low_5,
        high_5,
        low_20,
        high_20,
        low_60,
        high_60
    FROM paths
    WHERE prior_close > 0
      AND d <= toDate('2026-02-28')
      AND abs(100 * (session_open / prior_close - 1)) >= 1
),
flags AS
(
    SELECT arrayJoin([
        (1, '1 session',   toUInt8(if(gap_up, session_low <= prior_close, session_high >= prior_close))),
        (2, '5 sessions',  toUInt8(if(gap_up, low_5  <= prior_close, high_5  >= prior_close))),
        (3, '20 sessions', toUInt8(if(gap_up, low_20 <= prior_close, high_20 >= prior_close))),
        (4, '60 sessions', toUInt8(if(gap_up, low_60 <= prior_close, high_60 >= prior_close)))
    ]) AS f
    FROM gaps
)
SELECT
    tupleElement(f, 2)                        AS horizon,
    count()                                   AS gaps_measured,
    round(100 * avg(tupleElement(f, 3)), 1)   AS filled_pct
FROM flags
GROUP BY horizon
ORDER BY min(tupleElement(f, 1)) ASC

WITH sessions AS
(
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS d,
        argMin(toFloat64(open), window_start)                AS session_open,
        argMax(toFloat64(close), window_start)               AS session_close,
        toFloat64(max(high))                                 AS session_high,
        toFloat64(min(low))                                  AS session_low,
        sum(toFloat64(volume))                               AS session_volume
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'JPM', 'KO', 'WMT', 'XOM')
      AND window_start >= '2021-01-01'
      AND window_start <  '2026-07-01'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
    GROUP BY ticker, d
),
paths AS
(
    SELECT
        d,
        session_open,
        session_high,
        session_low,
        session_volume,
        lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY d ASC
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)                                                         AS prior_close,
        avg(session_volume) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN 21 PRECEDING AND 2 PRECEDING) AS normal_volume,
        min(session_low)  OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 19 FOLLOWING) AS low_20,
        max(session_high) OVER (PARTITION BY ticker ORDER BY d ASC ROWS BETWEEN CURRENT ROW AND 19 FOLLOWING) AS high_20
    FROM sessions
),
measured AS
(
    SELECT
        session_volume / normal_volume AS volume_ratio,
        toUInt8(if(session_open > prior_close, session_low <= prior_close, session_high >= prior_close)) AS filled_same_session,
        toUInt8(if(session_open > prior_close, low_20 <= prior_close, high_20 >= prior_close))           AS filled_20_sessions
    FROM paths
    WHERE prior_close > 0
      AND normal_volume > 0
      AND d >= toDate('2021-03-01')
      AND d <= toDate('2026-02-28')
      AND abs(100 * (session_open / prior_close - 1)) >= 1
)
SELECT
    multiIf(volume_ratio < 1.5, 'Under 1.5x normal',
            volume_ratio < 3,   '1.5x to 3x normal',
                                '3x or more')        AS volume_regime,
    count()                                          AS gap_days,
    round(100 * avg(filled_same_session), 1)         AS same_session_fill_pct,
    round(100 * avg(filled_20_sessions), 1)          AS within_20_sessions_fill_pct
FROM measured
WHERE isFinite(volume_ratio)
GROUP BY volume_regime
ORDER BY min(volume_ratio) ASC

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 15 MINUTE), '%H:%i') AS et_time,
    round(10000 * avg(toFloat64(high) / toFloat64(low) - 1), 1) AS avg_range_bps,
    round(avg(toFloat64(volume)) / 1000, 1)                     AS avg_volume_k
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'AMZN', 'JPM', 'KO', 'WMT', 'XOM')
  AND window_start >= '2025-01-01'
  AND window_start <  '2026-01-01'
  AND toFloat64(low) > 0
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
       + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
       + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
GROUP BY et_time
ORDER BY et_time ASC