SQL查询能否使用类列表下标方式简化多时间点数据筛选条件

可行性说明 #

这种写法完全可以实现，在支持窗口函数的主流SQL环境（MySQL 8.0+、PostgreSQL、Oracle、SQL Server等）下，我们可以通过分组排序+数组聚合/偏移取值的方式，实现你想要的下标引用效果，无需多层嵌套EXISTS关联。

具体修改方案 #

你提到的下标逻辑对应时间降序排序后的取值：下标0=09:45、下标1=09:40、下标2=09:35，和你的判断条件完全匹配，两种常用实现方式如下：

方式1：支持数组的数据库（如PostgreSQL），可直接使用下标写法

注意PostgreSQL数组下标默认从1开始，因此判断条件的下标对应+1即可

WITH grouped_data AS (
    SELECT 
        script_id,
        -- 按时间降序聚合open、close字段生成数组
        array_agg(open ORDER BY date_time DESC) AS open_arr,
        array_agg(close ORDER BY date_time DESC) AS close_arr,
        -- 预存09:35的目标行数据用于返回
        array_agg(data ORDER BY date_time DESC)[3] AS target_row
    FROM data
    WHERE date_time IN (
        '2019-01-11 09:35:00',
        '2019-01-11 09:40:00',
        '2019-01-11 09:45:00'
    )
    GROUP BY script_id
    HAVING COUNT(*) = 3 -- 过滤掉缺失任意时间点的标的
)
SELECT (target_row).*
FROM grouped_data
-- 完全匹配你提到的下标判断逻辑
WHERE open_arr[2] < open_arr[3] 
  AND close_arr[1] > close_arr[3]
  AND open_arr[1] > close_arr[2];

方式2：通用窗口函数实现（兼容所有支持窗口函数的数据库）

如果数据库不支持数组，可以用LAG偏移函数实现同等逻辑，比原嵌套写法更简洁：

WITH ranked_data AS (
    SELECT 
        *,
        -- 按时间降序排序，rn=1对应09:45、rn=2对应09:40、rn=3对应09:35
        ROW_NUMBER() OVER (PARTITION BY script_id ORDER BY date_time DESC) AS rn,
        -- 取后1个时间点（更早）的字段值
        LAG(open,1) OVER (PARTITION BY script_id ORDER BY date_time DESC) AS open_1,
        LAG(close,1) OVER (PARTITION BY script_id ORDER BY date_time DESC) AS close_1,
        -- 取后2个时间点的字段值
        LAG(open,2) OVER (PARTITION BY script_id ORDER BY date_time DESC) AS open_2,
        LAG(close,2) OVER (PARTITION BY script_id ORDER BY date_time DESC) AS close_2
    FROM data
    WHERE date_time IN (
        '2019-01-11 09:35:00',
        '2019-01-11 09:40:00',
        '2019-01-11 09:45:00'
    )
)
SELECT open_2 AS open, close_2 AS close, script_id, '2019-01-11 09:35:00' AS date_time
FROM ranked_data
WHERE rn = 1 -- 只取09:45的行，该行已关联了后两个时间点的所有字段
  AND open_1 < open_2 -- 对应9:40的open < 9:35的open
  AND close > close_2 -- 对应9:45的close > 9:35的close
  AND open > close_1; -- 对应9:45的open > 9:40的close

两种方案的返回结果和你原有的嵌套EXISTS查询完全一致，且性能更优，仅需一次全表扫描即可完成计算，不需要多次子查询关联。

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