隐含波动率与贝塔：区别及含义

WITH
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY','AAPL','MSFT','NVDA','TSLA','COIN','KO','JNJ','XOM','GLD','TLT')
          AND date >= '2025-08-22'
          AND date <= '2026-08-21'
    ),
    daily_ret AS
    (
        SELECT
            ticker,
            date,
            close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
        FROM px
    ),
    weekly_px AS
    (
        SELECT
            ticker,
            toMonday(date)         AS week_start,
            argMax(close_px, date) AS week_close
        FROM px
        GROUP BY ticker, week_start
    ),
    weekly_ret AS
    (
        SELECT
            ticker,
            week_start,
            week_close / lagInFrame(week_close) OVER (PARTITION BY ticker ORDER BY week_start ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
        FROM weekly_px
    ),
    daily_beta AS
    (
        SELECT
            s.ticker                                           AS symbol,
            round(covarSamp(s.ret, i.ret) / varSamp(i.ret), 2) AS beta_daily,
            round(pow(corr(s.ret, i.ret), 2), 2)               AS r_squared,
            count()                                            AS return_count
        FROM daily_ret AS s
        INNER JOIN
        (
            SELECT date, ret FROM daily_ret WHERE ticker = 'SPY' AND isFinite(ret)
        ) AS i ON i.date = s.date
        WHERE s.ticker != 'SPY' AND isFinite(s.ret)
        GROUP BY symbol
    ),
    weekly_beta AS
    (
        SELECT
            s.ticker                                           AS symbol,
            round(covarSamp(s.ret, i.ret) / varSamp(i.ret), 2) AS beta_weekly
        FROM weekly_ret AS s
        INNER JOIN
        (
            SELECT week_start, ret FROM weekly_ret WHERE ticker = 'SPY' AND isFinite(ret)
        ) AS i ON i.week_start = s.week_start
        WHERE s.ticker != 'SPY' AND isFinite(s.ret)
        GROUP BY symbol
    )
SELECT
    d.symbol       AS symbol,
    d.beta_daily   AS beta_daily,
    w.beta_weekly  AS beta_weekly,
    d.r_squared    AS r_squared,
    d.return_count AS return_count
FROM daily_beta AS d
INNER JOIN weekly_beta AS w ON w.symbol = d.symbol
ORDER BY beta_daily DESC

SELECT
    underlying_symbol                                                       AS symbol,
    round(avg(toFloat64(implied_volatility)) * 100, 1)                      AS iv_pct,
    round(avg(toFloat64(implied_volatility)) * 100 * sqrt(30.0 / 365.0), 1) AS expected_move_30d_pct,
    count()                                                                 AS contract_count,
    formatDateTime(max(date), '%b %e')                                      AS iv_through
FROM global_markets.options_greeks
WHERE underlying_symbol IN ('SPY','AAPL','MSFT','NVDA','TSLA','COIN','KO','JNJ','XOM','GLD','TLT')
  AND date >= '2026-08-03'
  AND date <= '2026-08-21'
  AND iv_converged = 1
  AND volume > 0
  AND days_to_expiry BETWEEN 20 AND 45
  AND underlying_close > 0
  AND abs(toFloat64(strike_price) / toFloat64(underlying_close) - 1) < 0.05
GROUP BY symbol
HAVING contract_count >= 25
ORDER BY iv_pct DESC

WITH
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY','AAPL','MSFT','NVDA','TSLA','COIN','KO','JNJ','XOM','GLD','TLT')
          AND date >= '2025-08-22'
          AND date <= '2026-08-21'
    ),
    daily_ret AS
    (
        SELECT
            ticker,
            date,
            close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
        FROM px
    ),
    idx AS
    (
        SELECT date, ret AS index_ret
        FROM daily_ret
        WHERE ticker = 'SPY' AND isFinite(ret)
    )
SELECT
    s.ticker                                                     AS symbol,
    round(avgIf(abs(s.ret) * 100, abs(i.index_ret) < 0.0025), 2) AS move_on_flat_index_pct,
    round(avg(abs(s.ret) * 100), 2)                              AS move_full_sample_pct,
    countIf(abs(i.index_ret) < 0.0025)                           AS flat_index_count
FROM daily_ret AS s
INNER JOIN idx AS i ON i.date = s.date
WHERE s.ticker != 'SPY' AND isFinite(s.ret)
GROUP BY symbol
HAVING countIf(abs(i.index_ret) < 0.0025) > 0
ORDER BY move_on_flat_index_pct DESC

WITH
    px AS
    (
        SELECT
            ticker,
            date,
            toFloat64(close) AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('SPY','NVDA')
          AND date >= '2021-08-01'
          AND date <= '2026-08-21'
    ),
    daily_ret AS
    (
        SELECT
            ticker,
            date,
            close_px / lagInFrame(close_px) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - 1 AS ret
        FROM px
    ),
    paired AS
    (
        SELECT
            s.date AS d,
            s.ret  AS stock_ret,
            i.ret  AS index_ret
        FROM daily_ret AS s
        INNER JOIN
        (
            SELECT date, ret FROM daily_ret WHERE ticker = 'SPY' AND isFinite(ret)
        ) AS i ON i.date = s.date
        WHERE s.ticker = 'NVDA' AND isFinite(s.ret)
    ),
    anchors AS
    (
        SELECT DISTINCT toStartOfMonth(d) AS anchor
        FROM paired
        WHERE d >= '2022-09-01'
    )
SELECT
    formatDateTime(a.anchor, '%Y-%m')                                    AS month,
    formatDateTime(a.anchor, '%b %Y')                                    AS month_label,
    round(covarSamp(p.stock_ret, p.index_ret) / varSamp(p.index_ret), 2) AS rolling_beta_1y,
    round(pow(corr(p.stock_ret, p.index_ret), 2), 2)                     AS r_squared
FROM anchors AS a, paired AS p
WHERE p.d < a.anchor
  AND p.d >= subtractYears(a.anchor, 1)
GROUP BY a.anchor
ORDER BY a.anchor