如何编写SQLite查询筛选符合多时间框架SMA9条件的股票代码

核心逻辑 #

针对每个时间框架的指标表，执行以下判断：

• 先过滤掉close或sma9为NaN的记录（SQLite中用IS NOT NULL判断）
• 按时间倒序取最近3条有效记录
• 如果有效记录数不足3条，直接豁免该框架的条件
• 如果有3条及以上有效记录，检查这3条的close是否全部大于对应的sma9

查询语句 #

SELECT sl.stock_code
FROM stocks_list sl
LEFT JOIN (
    SELECT 
        stock_code,
        -- 判断4H框架是否满足条件：有效记录不足3条，或最近3条close都>sma9
        CASE 
            WHEN COUNT(*) < 3 THEN 1
            WHEN SUM(CASE WHEN close > sma9 THEN 1 ELSE 0 END) = 3 THEN 1
            ELSE 0
        END AS pass_4H
    FROM (
        -- 取4H表中无NaN的最近3条记录
        SELECT stock_code, close, sma9
        FROM indicators_4H
        WHERE close IS NOT NULL AND sma9 IS NOT NULL
        ORDER BY record_time DESC
        LIMIT 3
    ) t
    GROUP BY stock_code
) ih4 ON sl.stock_code = ih4.stock_code
LEFT JOIN (
    SELECT 
        stock_code,
        CASE 
            WHEN COUNT(*) < 3 THEN 1
            WHEN SUM(CASE WHEN close > sma9 THEN 1 ELSE 0 END) = 3 THEN 1
            ELSE 0
        END AS pass_1D
    FROM (
        SELECT stock_code, close, sma9
        FROM indicators_1D
        WHERE close IS NOT NULL AND sma9 IS NOT NULL
        ORDER BY record_time DESC
        LIMIT 3
    ) t
    GROUP BY stock_code
) ih1d ON sl.stock_code = ih1d.stock_code
LEFT JOIN (
    SELECT 
        stock_code,
        CASE 
            WHEN COUNT(*) < 3 THEN 1
            WHEN SUM(CASE WHEN close > sma9 THEN 1 ELSE 0 END) = 3 THEN 1
            ELSE 0
        END AS pass_1W
    FROM (
        SELECT stock_code, close, sma9
        FROM indicators_1W
        WHERE close IS NOT NULL AND sma9 IS NOT NULL
        ORDER BY record_time DESC
        LIMIT 3
    ) t
    GROUP BY stock_code
) ih1w ON sl.stock_code = ih1w.stock_code
LEFT JOIN (
    SELECT 
        stock_code,
        CASE 
            WHEN COUNT(*) < 3 THEN 1
            WHEN SUM(CASE WHEN close > sma9 THEN 1 ELSE 0 END) = 3 THEN 1
            ELSE 0
        END AS pass_1M
    FROM (
        SELECT stock_code, close, sma9
        FROM indicators_1M
        WHERE close IS NOT NULL AND sma9 IS NOT NULL
        ORDER BY record_time DESC
        LIMIT 3
    ) t
    GROUP BY stock_code
) ih1m ON sl.stock_code = ih1m.stock_code
WHERE 
    -- 处理框架无数据的情况（pass字段为NULL时视为豁免）
    COALESCE(ih4.pass_4H, 1) = 1
    AND COALESCE(ih1d.pass_1D, 1) = 1
    AND COALESCE(ih1w.pass_1W, 1) = 1
    AND COALESCE(ih1m.pass_1M, 1) = 1;

关键说明 #

1. 子查询结构：每个时间框架的子查询先过滤无效数据（含NaN的记录），再取最近3条，最后通过CASE判断是否满足条件或豁免。
2. COALESCE函数：处理股票在某个时间框架无任何有效数据的情况，此时pass_*字段为NULL，用COALESCE将其转为1（视为豁免）。
3. 分组与聚合：通过GROUP BY stock_code聚合每个股票的判断结果，确保单股票单结果。

内容的提问来源于stack exchange，提问作者mhiytham