Backtest กับ Forward Test ต่างกันอย่างไร

WITH px AS (
    SELECT
        ticker,
        date,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPY', 'MSFT', 'JPM', 'HD', 'PEP')
      AND date >= '2008-12-01'
      AND date <  '2026-10-01'
),
ma AS (
    SELECT
        ticker,
        date,
        c,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 19  PRECEDING AND CURRENT ROW) AS ma_20,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 49  PRECEDING AND CURRENT ROW) AS ma_50,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 99  PRECEDING AND CURRENT ROW) AS ma_100,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 149 PRECEDING AND CURRENT ROW) AS ma_150,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma_200
    FROM px
),
grid AS (
    SELECT
        ticker,
        date,
        c,
        g.1 AS label,
        g.2 AS ma_value
    FROM ma
    ARRAY JOIN [('MA 20', ma_20), ('MA 50', ma_50), ('MA 100', ma_100), ('MA 150', ma_150), ('MA 200', ma_200)] AS g
),
sig AS (
    SELECT
        label,
        ticker,
        date,
        c,
        any(c)                      OVER (PARTITION BY label, ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_c,
        any(if(c > ma_value, 1, 0)) OVER (PARTITION BY label, ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS held
    FROM grid
),
port AS (
    SELECT
        label,
        if(date < '2020-01-01', 'in_sample', 'forward') AS window_name,
        date,
        avg(held * (c / prev_c - 1)) AS r
    FROM sig
    WHERE prev_c > 0
      AND ((date >= '2010-01-01' AND date < '2020-01-01')
        OR (date >= '2020-09-01' AND date < '2026-10-01'))
    GROUP BY label, window_name, date
),
curve AS (
    SELECT
        label,
        window_name,
        date,
        exp(sum(log(1 + r)) OVER (PARTITION BY label, window_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS equity
    FROM port
),
metrics AS (
    SELECT
        label,
        window_name,
        round((pow(argMax(equity, date), 252.0 / count()) - 1) * 100, 2) AS cagr_pct
    FROM curve
    GROUP BY label, window_name
)
SELECT
    label,
    anyIf(cagr_pct, window_name = 'in_sample') AS cagr_in_sample_pct,
    anyIf(cagr_pct, window_name = 'forward')   AS cagr_forward_pct
FROM metrics
GROUP BY label
HAVING countIf(window_name = 'in_sample') > 0
   AND countIf(window_name = 'forward') > 0
ORDER BY cagr_in_sample_pct DESC

WITH px AS (
    SELECT
        ticker,
        date,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPY', 'MSFT', 'JPM', 'HD', 'PEP')
      AND date >= '2008-12-01'
      AND date <  '2026-10-01'
),
ma AS (
    SELECT
        ticker,
        date,
        c,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 19  PRECEDING AND CURRENT ROW) AS ma_20,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 49  PRECEDING AND CURRENT ROW) AS ma_50,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 99  PRECEDING AND CURRENT ROW) AS ma_100,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 149 PRECEDING AND CURRENT ROW) AS ma_150,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma_200
    FROM px
),
grid AS (
    SELECT
        ticker,
        date,
        c,
        g.1 AS label,
        g.2 AS ma_value
    FROM ma
    ARRAY JOIN [('MA 20', ma_20), ('MA 50', ma_50), ('MA 100', ma_100), ('MA 150', ma_150), ('MA 200', ma_200)] AS g
),
sig AS (
    SELECT
        label,
        ticker,
        date,
        c,
        any(c)                      OVER (PARTITION BY label, ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_c,
        any(if(c > ma_value, 1, 0)) OVER (PARTITION BY label, ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS held
    FROM grid
),
port AS (
    SELECT
        label,
        if(date < '2020-01-01', 'in_sample', 'forward') AS window_name,
        date,
        avg(held * (c / prev_c - 1)) AS r,
        avg(held)                    AS exposure
    FROM sig
    WHERE prev_c > 0
      AND ((date >= '2010-01-01' AND date < '2020-01-01')
        OR (date >= '2020-09-01' AND date < '2026-10-01'))
    GROUP BY label, window_name, date
),
curve AS (
    SELECT
        label,
        window_name,
        date,
        r,
        exposure,
        exp(sum(log(1 + r)) OVER (PARTITION BY label, window_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS equity
    FROM port
),
dd AS (
    SELECT
        label,
        window_name,
        r,
        exposure,
        equity / greatest(max(equity) OVER (PARTITION BY label, window_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1.0) - 1 AS drawdown
    FROM curve
),
metrics AS (
    SELECT
        label,
        window_name,
        round(abs(min(drawdown)) * 100, 2)                                                        AS max_dd_pct,
        round(100.0 * countIf(r > 0 AND exposure > 0) / greatest(countIf(exposure > 0), 1), 2)     AS hit_rate_pct
    FROM dd
    GROUP BY label, window_name
)
SELECT
    label,
    anyIf(max_dd_pct, window_name = 'in_sample')   AS max_dd_in_sample_pct,
    anyIf(max_dd_pct, window_name = 'forward')     AS max_dd_forward_pct,
    anyIf(hit_rate_pct, window_name = 'in_sample') AS hit_rate_in_sample_pct,
    anyIf(hit_rate_pct, window_name = 'forward')   AS hit_rate_forward_pct
FROM metrics
GROUP BY label
HAVING countIf(window_name = 'in_sample') > 0
   AND countIf(window_name = 'forward') > 0
ORDER BY max_dd_forward_pct DESC

WITH px AS (
    SELECT
        ticker,
        date,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('SPY', 'MSFT', 'JPM', 'HD', 'PEP')
      AND date >= '2008-12-01'
      AND date <  '2026-10-01'
),
ma AS (
    SELECT
        ticker,
        date,
        c,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 179 PRECEDING AND CURRENT ROW) AS ma_180,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 189 PRECEDING AND CURRENT ROW) AS ma_190,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma_200,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 209 PRECEDING AND CURRENT ROW) AS ma_210,
        avg(c) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 219 PRECEDING AND CURRENT ROW) AS ma_220
    FROM px
),
grid AS (
    SELECT
        ticker,
        date,
        c,
        g.1 AS label,
        g.2 AS ma_value
    FROM ma
    ARRAY JOIN [('MA 180', ma_180), ('MA 190', ma_190), ('MA 200', ma_200), ('MA 210', ma_210), ('MA 220', ma_220)] AS g
),
sig AS (
    SELECT
        label,
        ticker,
        date,
        c,
        any(c)                      OVER (PARTITION BY label, ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_c,
        any(if(c > ma_value, 1, 0)) OVER (PARTITION BY label, ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS held
    FROM grid
),
port AS (
    SELECT
        label,
        if(date < '2020-01-01', 'in_sample', 'forward') AS window_name,
        date,
        avg(held * (c / prev_c - 1)) AS r
    FROM sig
    WHERE prev_c > 0
      AND ((date >= '2010-01-01' AND date < '2020-01-01')
        OR (date >= '2020-09-01' AND date < '2026-10-01'))
    GROUP BY label, window_name, date
),
curve AS (
    SELECT
        label,
        window_name,
        date,
        exp(sum(log(1 + r)) OVER (PARTITION BY label, window_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS equity
    FROM port
),
metrics AS (
    SELECT
        label,
        window_name,
        round((pow(argMax(equity, date), 252.0 / count()) - 1) * 100, 2) AS cagr_pct
    FROM curve
    GROUP BY label, window_name
)
SELECT
    label,
    anyIf(cagr_pct, window_name = 'in_sample') AS cagr_in_sample_pct,
    anyIf(cagr_pct, window_name = 'forward')   AS cagr_forward_pct
FROM metrics
GROUP BY label
HAVING countIf(window_name = 'in_sample') > 0
   AND countIf(window_name = 'forward') > 0
ORDER BY label

WITH px AS (
    SELECT
        date,
        toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2019-10-01'
      AND date <  '2026-10-01'
),
ma AS (
    SELECT
        date,
        c,
        avg(c) OVER (ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS ma_200
    FROM px
),
sig AS (
    SELECT
        date,
        c,
        any(c)                    OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_c,
        any(if(c > ma_200, 1, 0)) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS held
    FROM ma
),
daily AS (
    SELECT
        date,
        held * (c / prev_c - 1) AS r_rule,
        c / prev_c - 1          AS r_hold
    FROM sig
    WHERE date >= '2020-09-01'
      AND prev_c > 0
),
cum AS (
    SELECT
        date,
        exp(sum(log(1 + r_rule)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS eq_rule,
        exp(sum(log(1 + r_hold)) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS eq_hold
    FROM daily
)
SELECT
    formatDateTime(toStartOfMonth(date), '%Y-%m') AS month,
    round(argMax(eq_rule, date) * 100, 1)         AS strategy_index,
    round(argMax(eq_hold, date) * 100, 1)         AS buy_hold_index
FROM cum
GROUP BY month
ORDER BY month