🔍 信号分析查询手册

-- 信号数据健康检查
WITH health_metrics AS (
    SELECT
        COUNT(*) as total_records,
        COUNT(DISTINCT symbol) as symbol_count,
        COUNT(DISTINCT date) as date_count,
        MIN(date) as start_date,
        MAX(date) as end_date,
        COUNT(CASE WHEN signal NOT IN ('B','S','H','E') OR signal IS NULL THEN 1 END) as invalid_signals
    FROM trade_signals
)
SELECT
    '数据规模' as check_item,
    total_records || ' records, ' || symbol_count || ' symbols' as result,
    CASE WHEN total_records > 0 THEN '✅ 正常' ELSE '❌ 无数据' END as status
FROM health_metrics
UNION ALL
SELECT
    '时间范围',
    start_date || ' to ' || end_date || ' (' || date_count || ' days)',
    CASE WHEN date_count > 0 THEN '✅ 正常' ELSE '❌ 无数据' END
FROM health_metrics
UNION ALL
SELECT
    '信号有效性',
    CASE WHEN invalid_signals = 0 THEN 'All signals are valid (B/S/H/E)'
         ELSE invalid_signals || ' invalid signals found' END,
    CASE WHEN invalid_signals = 0 THEN '✅ 正常' ELSE '❌ 发现无效信号' END
FROM health_metrics;

-- 信号切换逻辑验证
WITH signal_transitions AS (
    SELECT
        date,
        symbol,
        signal as current_signal,
        LAG(signal) OVER (PARTITION BY symbol ORDER BY date) as prev_signal
    FROM trade_signals
),
transition_analysis AS (
    SELECT
        prev_signal || ' → ' || current_signal as transition,
        COUNT(*) as count,
        CASE
            -- 异常切换
            WHEN prev_signal = 'H' AND current_signal = 'E' THEN '❌ 异常: 持有直接空仓(应经过卖出)'
            WHEN prev_signal = 'B' AND current_signal = 'S' THEN '❌ 异常: 买入直接卖出'
            WHEN prev_signal = 'E' AND current_signal = 'S' THEN '❌ 异常: 空仓时卖出'
            WHEN prev_signal = 'S' AND current_signal = 'B' THEN '❌ 异常: 卖出直接买入'
            -- 正常切换
            WHEN prev_signal = 'E' AND current_signal = 'B' THEN '✅ 正常: 空仓买入'
            WHEN prev_signal = 'B' AND current_signal = 'H' THEN '✅ 正常: 买入后持有'
            WHEN prev_signal = 'H' AND current_signal = 'S' THEN '✅ 正常: 持有后卖出'
            WHEN prev_signal = 'S' AND current_signal = 'E' THEN '✅ 正常: 卖出后空仓'
            WHEN prev_signal = 'H' AND current_signal = 'B' THEN '✅ 正常: 定投加仓'
            WHEN prev_signal = current_signal THEN '⚪ 无变化: 状态保持'
            ELSE '❓ 其他: ' || prev_signal || ' → ' || current_signal
        END as logic_check
    FROM signal_transitions
    WHERE prev_signal IS NOT NULL
    GROUP BY prev_signal, current_signal
)
SELECT
    transition,
    count,
    ROUND(count * 100.0 / (SELECT SUM(count) FROM transition_analysis), 2) as percentage,
    logic_check
FROM transition_analysis
WHERE count > 0
ORDER BY
    CASE WHEN logic_check LIKE '❌%' THEN 1
         WHEN logic_check LIKE '❓%' THEN 2
         WHEN logic_check LIKE '✅%' THEN 3
         ELSE 4 END,
    count DESC
LIMIT 50;

-- 信号分布和策略特征分析
WITH signal_stats AS (
    SELECT
        signal,
        COUNT(*) as count,
        ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM trade_signals), 2) as percentage
    FROM trade_signals
    GROUP BY signal
),
activity_summary AS (
    SELECT
        SUM(CASE WHEN signal IN ('B', 'S') THEN count ELSE 0 END) as active_count,
        SUM(CASE WHEN signal IN ('H', 'E') THEN count ELSE 0 END) as passive_count,
        SUM(count) as total_count
    FROM signal_stats
)
SELECT
    signal || ' (' ||
    CASE
        WHEN signal = 'B' THEN 'Buy'
        WHEN signal = 'S' THEN 'Sell'
        WHEN signal = 'H' THEN 'Hold'
        WHEN signal = 'E' THEN 'Empty'
        ELSE 'Unknown'
    END || ')' as signal_type,
    count,
    percentage || '%' as percentage_str,
    CASE
        WHEN signal IN ('B', 'S') THEN '🔥 Active Trading'
        WHEN signal IN ('H', 'E') THEN '💤 Passive Holding'
        ELSE '❓ Unknown'
    END as activity_style
FROM signal_stats
UNION ALL
SELECT
    '--- 策略风格评估 ---',
    NULL,
    ROUND(active_count * 100.0 / total_count, 2) || '% Active, ' ||
    ROUND(passive_count * 100.0 / total_count, 2) || '% Passive',
    CASE
        WHEN active_count * 100.0 / total_count > 10 THEN '🔥 激进型策略'
        WHEN active_count * 100.0 / total_count > 2 THEN '⚖️ 平衡型策略'
        ELSE '💤 保守型策略'
    END
FROM activity_summary
ORDER BY count DESC NULLS LAST;

-- 最近信号状态查询
SELECT
    date,
    symbol,
    signal,
    CASE
        WHEN signal = 'B' THEN '🟢 Buy'
        WHEN signal = 'S' THEN '🔴 Sell'
        WHEN signal = 'H' THEN '🟡 Hold'
        WHEN signal = 'E' THEN '⚪ Empty'
        ELSE '❓ Unknown'
    END as signal_status
FROM trade_signals
WHERE date >= (SELECT date(MAX(date), '-7 days') FROM trade_signals)
ORDER BY date DESC, symbol
LIMIT 30;