kis_bot/scratch/compare_kiwoom_kis_tick_gaps.py

#!/usr/bin/env python3
"""오늘 키움 vs 한투 종목별 틱 공백 비교 (1회)."""
from __future__ import annotations

import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
if str(ROOT) not in sys.path:
    sys.path.insert(0, str(ROOT))

from database import TradeDB

TT = """
STR_TO_DATE(
    CASE
        WHEN CHAR_LENGTH(tick_time) >= 14 THEN LEFT(tick_time, 14)
        WHEN CHAR_LENGTH(tick_time) = 6 THEN CONCAT(DATE_FORMAT(recv_ts, '%%Y%%m%%d'), tick_time)
        ELSE NULL
    END,
    '%%Y%%m%%d%%H%%i%%s'
)
"""

db = TradeDB()
cols = [r["Field"] for r in db.conn.execute("SHOW COLUMNS FROM ws_ticks").fetchall()]
need = {"code", "tick_time", "recv_ts", "source", "id"}
missing = need - set(cols)
if missing:
    raise SystemExit(f"ws_ticks missing {missing} have={cols}")

t0 = "2026-08-19 09:00:00"
t1 = "2026-08-19 09:35:00"
print("window", t0, "~", t1)

print("\n== source 전체 (recv 연속 공백 / tick_time 연속 공백) ==")
rows = db.conn.execute(
    f"""
    SELECT source,
           COUNT(*) n,
           COUNT(DISTINCT code) codes,
           MAX(recv_gap) max_recv_gap,
           AVG(recv_gap) avg_recv_gap,
           SUM(recv_gap >= 10) n_recv_ge10,
           SUM(recv_gap >= 20) n_recv_ge20,
           MAX(tt_gap) max_tt_gap,
           AVG(tt_gap) avg_tt_gap,
           SUM(tt_gap >= 10) n_tt_ge10,
           SUM(tt_gap >= 20) n_tt_ge20
    FROM (
        SELECT source, code,
               TIMESTAMPDIFF(SECOND, LAG(recv_ts) OVER (PARTITION BY source, code ORDER BY id), recv_ts) recv_gap,
               TIMESTAMPDIFF(SECOND, LAG({TT}) OVER (PARTITION BY source, code ORDER BY {TT}, id), {TT}) tt_gap
        FROM ws_ticks
        WHERE recv_ts >= %s AND recv_ts < %s AND source IN ('kiwoom','kis')
    ) x
    GROUP BY source
    """,
    (t0, t1),
).fetchall()
for r in rows:
    print(dict(r))

print("\n== 키움 recv 공백 TOP15 (같은 종목 한투 대비) ==")
rows = db.conn.execute(
    f"""
    SELECT k.code,
           k.n_kw n_kiwoom, s.n_kis,
           k.max_recv max_recv_kw, s.max_recv max_recv_kis,
           (k.max_recv - IFNULL(s.max_recv, 0)) delta_recv,
           k.max_tt max_tt_kw, s.max_tt max_tt_kis
    FROM (
        SELECT code, COUNT(*) n_kw,
               MAX(recv_gap) max_recv, MAX(tt_gap) max_tt
        FROM (
            SELECT code,
                   TIMESTAMPDIFF(SECOND, LAG(recv_ts) OVER (PARTITION BY code ORDER BY id), recv_ts) recv_gap,
                   TIMESTAMPDIFF(SECOND, LAG({TT}) OVER (PARTITION BY code ORDER BY {TT}, id), {TT}) tt_gap
            FROM ws_ticks
            WHERE source='kiwoom' AND recv_ts >= %s AND recv_ts < %s
        ) a
        GROUP BY code
    ) k
    LEFT JOIN (
        SELECT code, COUNT(*) n_kis,
               MAX(recv_gap) max_recv, MAX(tt_gap) max_tt
        FROM (
            SELECT code,
                   TIMESTAMPDIFF(SECOND, LAG(recv_ts) OVER (PARTITION BY code ORDER BY id), recv_ts) recv_gap,
                   TIMESTAMPDIFF(SECOND, LAG({TT}) OVER (PARTITION BY code ORDER BY {TT}, id), {TT}) tt_gap
            FROM ws_ticks
            WHERE source='kis' AND recv_ts >= %s AND recv_ts < %s
        ) b
        GROUP BY code
    ) s ON s.code = k.code
    ORDER BY k.max_recv DESC
    LIMIT 15
    """,
    (t0, t1, t0, t1),
).fetchall()
for r in rows:
    print(dict(r))

print("\n== 025980 단독 ==")
rows = db.conn.execute(
    f"""
    SELECT source, COUNT(*) n,
           MAX(recv_gap) max_recv, AVG(recv_gap) avg_recv,
           MAX(tt_gap) max_tt, AVG(tt_gap) avg_tt,
           SUM(recv_gap >= 10) n_recv_ge10
    FROM (
        SELECT source,
               TIMESTAMPDIFF(SECOND, LAG(recv_ts) OVER (PARTITION BY source ORDER BY id), recv_ts) recv_gap,
               TIMESTAMPDIFF(SECOND, LAG({TT}) OVER (PARTITION BY source ORDER BY {TT}, id), {TT}) tt_gap
        FROM ws_ticks
        WHERE code=%s AND recv_ts >= %s AND recv_ts < %s AND source IN ('kiwoom','kis')
    ) x
    GROUP BY source
    """,
    ("025980", t0, t1),
).fetchall()
for r in rows:
    print(dict(r))

print("\n== 키움 recv>=10초 공백이 있는 종목 수 vs 한투 ==")
for src in ("kiwoom", "kis"):
    r = db.conn.execute(
        f"""
        SELECT COUNT(*) codes_with_gap10 FROM (
            SELECT code, MAX(recv_gap) mx FROM (
                SELECT code,
                       TIMESTAMPDIFF(SECOND, LAG(recv_ts) OVER (PARTITION BY code ORDER BY id), recv_ts) recv_gap
                FROM ws_ticks
                WHERE source=%s AND recv_ts >= %s AND recv_ts < %s
            ) a
            GROUP BY code
            HAVING mx >= 10
        ) z
        """,
        (src, t0, t1),
    ).fetchone()
    print(src, dict(r))