開源量化交易書籍：完整學習路徑與美股應用

WITH june_week AS (
    SELECT toYear(toTimeZone(window_start, 'America/New_York')) AS year,
           ticker
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE toMonth(toTimeZone(window_start, 'America/New_York')) = 6
      AND toDayOfMonth(toTimeZone(window_start, 'America/New_York')) BETWEEN 10 AND 16
      AND toYear(toTimeZone(window_start, 'America/New_York')) BETWEEN 2016 AND 2026
      AND volume > 0
    GROUP BY year, ticker
),
survivors AS (
    SELECT ticker FROM june_week WHERE year = 2026
)
SELECT year,
       uniqExact(ticker) AS tickers_traded,
       uniqExactIf(ticker, ticker IN (SELECT ticker FROM survivors)) AS still_trading_2026,
       round(100 * uniqExactIf(ticker, ticker IN (SELECT ticker FROM survivors))
             / uniqExact(ticker), 1) AS still_trading_pct
FROM june_week
GROUP BY year
ORDER BY year

WITH sessions AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMin(open, window_start) AS session_open,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'NVDA')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2024-08-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session
),
gaps AS (
    SELECT ticker,
           session,
           toFloat64(session_open) AS open_px,
           toFloat64(lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY session)) AS prev_close
    FROM sessions
)
SELECT formatDateTime(toStartOfMonth(session), '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(toStartOfMonth(session), 'MMMM yyyy') AS month_label,
       round(avgIf(abs(open_px / prev_close - 1) * 10000, ticker = 'SPY'), 1) AS spy_gap_bps,
       round(avgIf(abs(open_px / prev_close - 1) * 10000, ticker = 'NVDA'), 1) AS nvda_gap_bps
FROM gaps
WHERE prev_close > 0
GROUP BY month, month_label
HAVING countIf(ticker = 'SPY') > 0 AND countIf(ticker = 'NVDA') > 0
ORDER BY month

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMax(close, window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'KO', 'JNJ', 'MSFT', 'AAPL', 'NVDA', 'TSLA', 'COIN')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2025-08-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-07-31')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session
),
rets AS (
    SELECT ticker,
           toFloat64(session_close) AS close_px,
           toFloat64(lagInFrame(session_close) OVER (PARTITION BY ticker ORDER BY session)) AS prev_close
    FROM daily
)
SELECT ticker,
       round(stddevSamp(close_px / prev_close - 1) * sqrt(252) * 100, 1) AS annualized_vol_pct,
       round(abs(min(close_px / prev_close - 1)) * 100, 2) AS worst_day_pct
FROM rets
WHERE prev_close > 0
GROUP BY ticker
ORDER BY annualized_vol_pct DESC

WITH quotes AS (
    SELECT ticker,
           toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), INTERVAL 30 MINUTE) AS bucket,
           toFloat64(ask_price - bid_price) / toFloat64((ask_price + bid_price) / 2) * 10000 AS spread_bps
    FROM global_markets.cache_stocks_quotes
    WHERE ticker IN ('AAPL', 'KO')
      AND sip_timestamp >= toDateTime('2026-07-17 13:00:00')
      AND sip_timestamp < toDateTime('2026-07-17 21:00:00')
      AND bid_price > 0
      AND ask_price > bid_price
)
SELECT formatDateTime(bucket, '%H:%i') AS et_time,
       round(avgIf(spread_bps, ticker = 'AAPL'), 2) AS aapl_spread_bps,
       round(avgIf(spread_bps, ticker = 'KO'), 2) AS ko_spread_bps
FROM quotes
WHERE spread_bps > 0 AND spread_bps < 500
GROUP BY et_time
HAVING countIf(ticker = 'AAPL') > 0 AND countIf(ticker = 'KO') > 0
ORDER BY et_time