สโตแคสติก %K %D: สัญญาณจริงหรือสัญญาณรบกวน

WITH
    bars AS (
        SELECT
            date,
            argMax(toFloat64(close), _ingest_time) AS close,
            argMax(toFloat64(high),  _ingest_time) AS high,
            argMax(toFloat64(low),   _ingest_time) AS low
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'AAPL'
          AND date >= '2026-02-01'
          AND date <  '2026-09-01'
        GROUP BY date
    ),
    ranges AS (
        SELECT
            date,
            close,
            count()   OVER w14 AS n14,
            max(high) OVER w14 AS hh14,
            min(low)  OVER w14 AS ll14
        FROM bars
        WINDOW w14 AS (ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
    ),
    fast AS (
        SELECT
            date,
            if(hh14 > ll14, 100 * (close - ll14) / (hh14 - ll14), 50) AS k_fast
        FROM ranges
        WHERE n14 = 14
    ),
    slow AS (
        SELECT
            date,
            k_fast,
            avg(k_fast) OVER w3 AS k_slow,
            count()     OVER w3 AS n3
        FROM fast
        WINDOW w3 AS (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    ),
    lines AS (
        SELECT
            date,
            k_fast,
            k_slow,
            avg(k_slow) OVER w3b AS d_slow,
            count()     OVER w3b AS n3b
        FROM slow
        WHERE n3 = 3
        WINDOW w3b AS (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    )
SELECT
    date,
    round(k_fast, 1) AS k_fast_pct,
    round(k_slow, 1) AS k_slow_pct,
    round(d_slow, 1) AS d_slow_pct
FROM lines
WHERE n3b = 3
  AND date >= '2026-05-01'
ORDER BY date

WITH
    bars AS (
        SELECT
            ticker,
            date,
            argMax(toFloat64(close), _ingest_time) AS close,
            argMax(toFloat64(high),  _ingest_time) AS high,
            argMax(toFloat64(low),   _ingest_time) AS low
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','XOM','PG','KO','WMT','HD','UNH','CVX','PEP','MRK','CSCO','ORCL','MCD')
          AND date >= '2015-01-01'
          AND date <  '2026-09-01'
        GROUP BY ticker, date
    ),
    ranges AS (
        SELECT
            ticker, date, close,
            count()   OVER w14 AS n14,
            max(high) OVER w14 AS hh14,
            min(low)  OVER w14 AS ll14
        FROM bars
        WINDOW w14 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
    ),
    fast AS (
        SELECT
            ticker, date, close,
            if(hh14 > ll14, 100 * (close - ll14) / (hh14 - ll14), 50) AS k_fast
        FROM ranges
        WHERE n14 = 14
    ),
    slow AS (
        SELECT
            ticker, date, close,
            avg(k_fast) OVER w3 AS k_slow,
            count()     OVER w3 AS n3
        FROM fast
        WINDOW w3 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    ),
    fwd AS (
        SELECT
            ticker, date, close, k_slow,
            lagInFrame(k_slow, 1)  OVER wl AS k_prev,
            leadInFrame(close, 20) OVER wf AS c20
        FROM slow
        WHERE n3 = 3
        WINDOW
            wl AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
            wf AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    )
SELECT
    toYear(date)                                        AS year,
    countIf((k_prev >= 80) AND (k_slow < 80))           AS cross_down_80_count,
    countIf((k_prev <= 20) AND (k_slow > 20))           AS cross_up_20_count
FROM fwd
WHERE date >= '2016-01-01'
  AND c20 > 0
GROUP BY year
ORDER BY year

WITH
    bars AS (
        SELECT
            ticker,
            date,
            argMax(toFloat64(close), _ingest_time) AS close,
            argMax(toFloat64(high),  _ingest_time) AS high,
            argMax(toFloat64(low),   _ingest_time) AS low
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','XOM','PG','KO','WMT','HD','UNH','CVX','PEP','MRK','CSCO','ORCL','MCD')
          AND date >= '2015-01-01'
          AND date <  '2026-09-01'
        GROUP BY ticker, date
    ),
    ranges AS (
        SELECT
            ticker, date, close,
            count()   OVER w14 AS n14,
            max(high) OVER w14 AS hh14,
            min(low)  OVER w14 AS ll14
        FROM bars
        WINDOW w14 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
    ),
    fast AS (
        SELECT
            ticker, date, close,
            if(hh14 > ll14, 100 * (close - ll14) / (hh14 - ll14), 50) AS k_fast
        FROM ranges
        WHERE n14 = 14
    ),
    slow AS (
        SELECT
            ticker, date, close,
            avg(k_fast) OVER w3 AS k_slow,
            count()     OVER w3 AS n3
        FROM fast
        WINDOW w3 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    ),
    fwd AS (
        SELECT
            ticker, date, close, k_slow,
            lagInFrame(k_slow, 1)  OVER wl AS k_prev,
            leadInFrame(close, 5)  OVER wf AS c5,
            leadInFrame(close, 10) OVER wf AS c10,
            leadInFrame(close, 20) OVER wf AS c20
        FROM slow
        WHERE n3 = 3
        WINDOW
            wl AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
            wf AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    ),
    tagged AS (
        SELECT
            100 * (c5  / close - 1)             AS fwd5_pct,
            100 * (c10 / close - 1)             AS fwd10_pct,
            100 * (c20 / close - 1)             AS fwd20_pct,
            cityHash64(ticker, toString(date))  AS h,
            arrayJoin(arrayConcat(
                if((k_prev >= 80) AND (k_slow < 80), ['%K ตัดลงต่ำกว่า 80'], emptyArrayString()),
                if((k_prev <= 20) AND (k_slow > 20), ['%K ตัดขึ้นเหนือ 20'], emptyArrayString()),
                ['ค่าฐาน: ทุกวันทำการ']
            ))                                  AS signal_group
        FROM fwd
        WHERE date >= '2016-01-01'
          AND c5 > 0 AND c10 > 0 AND c20 > 0
    )
SELECT
    signal_group,
    round(quantileDeterministic(0.5)(fwd5_pct,  h), 2) AS median_fwd_5d_pct,
    round(quantileDeterministic(0.5)(fwd10_pct, h), 2) AS median_fwd_10d_pct,
    round(quantileDeterministic(0.5)(fwd20_pct, h), 2) AS median_fwd_20d_pct
FROM tagged
GROUP BY signal_group
ORDER BY indexOf(['%K ตัดลงต่ำกว่า 80', '%K ตัดขึ้นเหนือ 20', 'ค่าฐาน: ทุกวันทำการ'], signal_group)

WITH
    bars AS (
        SELECT
            ticker,
            date,
            argMax(toFloat64(close), _ingest_time) AS close,
            argMax(toFloat64(high),  _ingest_time) AS high,
            argMax(toFloat64(low),   _ingest_time) AS low
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','XOM','PG','KO','WMT','HD','UNH','CVX','PEP','MRK','CSCO','ORCL','MCD')
          AND date >= '2015-01-01'
          AND date <  '2026-09-01'
        GROUP BY ticker, date
    ),
    ranges AS (
        SELECT
            ticker, date, close,
            count()   OVER w14 AS n14,
            max(high) OVER w14 AS hh14,
            min(low)  OVER w14 AS ll14
        FROM bars
        WINDOW w14 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW)
    ),
    fast AS (
        SELECT
            ticker, date, close,
            if(hh14 > ll14, 100 * (close - ll14) / (hh14 - ll14), 50) AS k_fast
        FROM ranges
        WHERE n14 = 14
    ),
    slow AS (
        SELECT
            ticker, date, close,
            avg(k_fast) OVER w3 AS k_slow,
            count()     OVER w3 AS n3
        FROM fast
        WINDOW w3 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    ),
    fwd AS (
        SELECT
            ticker, date, close, k_slow,
            lagInFrame(k_slow, 1)  OVER wl AS k_prev,
            leadInFrame(close, 5)  OVER wf AS c5,
            leadInFrame(close, 10) OVER wf AS c10,
            leadInFrame(close, 20) OVER wf AS c20
        FROM slow
        WHERE n3 = 3
        WINDOW
            wl AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
            wf AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    ),
    tagged AS (
        SELECT
            100 * (c5  / close - 1) AS fwd5_pct,
            100 * (c10 / close - 1) AS fwd10_pct,
            100 * (c20 / close - 1) AS fwd20_pct,
            arrayJoin(arrayConcat(
                if((k_prev >= 80) AND (k_slow < 80), ['%K ตัดลงต่ำกว่า 80'], emptyArrayString()),
                if((k_prev <= 20) AND (k_slow > 20), ['%K ตัดขึ้นเหนือ 20'], emptyArrayString()),
                ['ค่าฐาน: ทุกวันทำการ']
            ))                      AS signal_group
        FROM fwd
        WHERE date >= '2016-01-01'
          AND c5 > 0 AND c10 > 0 AND c20 > 0
    )
SELECT
    signal_group,
    round(100 * countIf(fwd5_pct  > 0) / count(), 1) AS win_rate_5d_pct,
    round(100 * countIf(fwd10_pct > 0) / count(), 1) AS win_rate_10d_pct,
    round(100 * countIf(fwd20_pct > 0) / count(), 1) AS win_rate_20d_pct,
    count()                                          AS observation_count
FROM tagged
GROUP BY signal_group
ORDER BY indexOf(['%K ตัดลงต่ำกว่า 80', '%K ตัดขึ้นเหนือ 20', 'ค่าฐาน: ทุกวันทำการ'], signal_group)

WITH
    bars AS (
        SELECT
            ticker,
            date,
            argMax(toFloat64(close), _ingest_time) AS close,
            argMax(toFloat64(high),  _ingest_time) AS high,
            argMax(toFloat64(low),   _ingest_time) AS low
        FROM global_markets.stocks_daily_aggs
        WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','XOM','PG','KO','WMT','HD','UNH','CVX','PEP','MRK','CSCO','ORCL','MCD')
          AND date >= '2015-01-01'
          AND date <  '2026-09-01'
        GROUP BY ticker, date
    ),
    ranges AS (
        SELECT
            ticker, date, close,
            count()    OVER w14  AS n14,
            max(high)  OVER w14  AS hh14,
            min(low)   OVER w14  AS ll14,
            count()    OVER w200 AS n200,
            avg(close) OVER w200 AS ma200
        FROM bars
        WINDOW
            w14  AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 13 PRECEDING AND CURRENT ROW),
            w200 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW)
    ),
    fast AS (
        SELECT
            ticker, date, close, n200, ma200,
            if(hh14 > ll14, 100 * (close - ll14) / (hh14 - ll14), 50) AS k_fast
        FROM ranges
        WHERE n14 = 14
    ),
    slow AS (
        SELECT
            ticker, date, close, n200, ma200,
            avg(k_fast) OVER w3 AS k_slow,
            count()     OVER w3 AS n3
        FROM fast
        WINDOW w3 AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    ),
    fwd AS (
        SELECT
            ticker, date, close, n200, ma200, k_slow,
            lagInFrame(k_slow, 1)  OVER wl AS k_prev,
            leadInFrame(close, 10) OVER wf AS c10
        FROM slow
        WHERE n3 = 3
        WINDOW
            wl AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
            wf AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
    ),
    split AS (
        SELECT
            100 * (c10 / close - 1)            AS fwd10_pct,
            cityHash64(ticker, toString(date)) AS h,
            concat(
                if((k_prev >= 80) AND (k_slow < 80), 'ตัดลง 80', 'ตัดขึ้น 20'),
                ' / ',
                if(close >= ma200, 'ราคาเหนือ MA200', 'ราคาใต้ MA200')
            )                                  AS signal_group
        FROM fwd
        WHERE date >= '2016-01-01'
          AND c10 > 0
          AND n200 = 200
          AND (((k_prev >= 80) AND (k_slow < 80)) OR ((k_prev <= 20) AND (k_slow > 20)))
    )
SELECT
    signal_group,
    round(quantileDeterministic(0.5)(fwd10_pct, h), 2) AS median_fwd_10d_pct,
    round(100 * countIf(fwd10_pct > 0) / count(), 1)   AS win_rate_10d_pct,
    count()                                            AS signal_count
FROM split
GROUP BY signal_group
ORDER BY indexOf([
    'ตัดลง 80 / ราคาเหนือ MA200',
    'ตัดลง 80 / ราคาใต้ MA200',
    'ตัดขึ้น 20 / ราคาเหนือ MA200',
    'ตัดขึ้น 20 / ราคาใต้ MA200'
], signal_group)