SQLite中如何关联价格列首次触发阈值的后续值？

核心思路 #

对每条价格记录，遍历其后续按ID排序的所有价格，找到第一个触发以下任一阈值的记录：

• 上阈值：后续价格 ≥ 当前价格 × 1.2
• 下阈值：后续价格 ≤ 当前价格 × 0.8

根据触发的阈值类型，返回关联价格或标记（1代表上阈值触发，-1代表下阈值触发）。

假设表结构 #

假设你的表名为price_data，包含用于排序的整数ID列和价格列：

CREATE TABLE price_data (
    id INT PRIMARY KEY,
    price NUMERIC(10,2) NOT NULL
);

具体实现 #

方法1：使用横向关联（PostgreSQL/MySQL 8.0+，性能更优） #

通过LATERAL JOIN（PostgreSQL）或CROSS APPLY（SQL Server）为每条记录筛选出后续第一个触发阈值的记录：

SELECT
    pd.id,
    pd.price AS current_price,
    first_trigger.price AS trigger_price,
    CASE
        WHEN first_trigger.price >= pd.price * 1.2 THEN 1
        WHEN first_trigger.price <= pd.price * 0.8 THEN -1
    END AS trigger_type
FROM price_data pd
LEFT JOIN LATERAL (
    SELECT price
    FROM price_data
    WHERE id > pd.id  -- 仅取当前记录之后的行
    AND (price >= pd.price * 1.2 OR price <= pd.price * 0.8)
    ORDER BY id ASC  -- 按ID排序，取首个触发阈值的记录
    LIMIT 1
) first_trigger ON TRUE
WHERE first_trigger.price IS NOT NULL;  -- 排除无后续触发的记录（如示例中的7）

方法2：使用窗口函数（通用SQL兼容） #

利用ROW_NUMBER()窗口函数标记后续触发阈值的记录，再筛选出每个当前记录的第一条：

WITH ranked_triggers AS (
    SELECT
        pd.id AS current_id,
        pd.price AS current_price,
        pd2.price AS trigger_price,
        CASE
            WHEN pd2.price >= pd.price * 1.2 THEN 1
            WHEN pd2.price <= pd.price * 0.8 THEN -1
        END AS trigger_type,
        ROW_NUMBER() OVER (PARTITION BY pd.id ORDER BY pd2.id ASC) AS rn
    FROM price_data pd
    JOIN price_data pd2 ON pd2.id > pd.id
    WHERE pd2.price >= pd.price * 1.2 OR pd2.price <= pd.price * 0.8
)
SELECT current_id, current_price, trigger_price, trigger_type
FROM ranked_triggers
WHERE rn = 1;

结果验证 #

针对示例数据：

执行上述SQL后，会得到符合需求的结果：

注意事项 #

• 若使用MySQL 8.0以下版本，需将LATERAL JOIN替换为CROSS JOIN结合子查询；
• 确保id列是严格递增的排序依据，若排序依赖其他字段（如时间列），需调整ORDER BY条件；
• 若存在多条记录同时触发阈值，LIMIT 1或rn=1会取ID最小的那条，符合“首个触发”的要求。

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