如何实现SQL查询在条件触发时提前终止？附文件累加场景

问题背景 #

我有一张存储文件基础信息的表：

CREATE TABLE filez (
    id     INTEGER NOT NULL,
    name   TEXT NOT NULL,
    size   BIGINT NOT NULL
);
CREATE UNIQUE INDEX filez__id__idx ON filez USING (id);

我需要选取总大小不超过指定THRESHOLD的文件集合，目前使用的查询如下（可使用NON MATERIALIZED CTE，但此处不影响）：

SELECT id, name
FROM (
        SELECT id, name,
            SUM(size) OVER w AS total_size -- 累计文件大小
        FROM filez
        WINDOW w AS (ORDER BY id ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        ORDER BY id ASC
    ) AS t
WHERE total_size <= THRESHOLD

当前查询的问题是：无论阈值是否达到，都会遍历所有记录。我希望查询仅遍历足够数量的记录（按已建索引的id字段排序），在累计大小超过阈值时立即停止执行。

补充说明：或许提前终止查询的问题可以泛化。假设有一个stop_query(BOOL):BOOL函数，能基于任意条件提前终止查询，示例用法如下：

SELECT f1, f2
FROM tab1
WHERE stop_query(<some condition>)

或者：

SELECT f1, f2
FROM tab1
WHERE CASE WHEN <some condition> THEN stop_query() ELSE TRUE END

针对文件累计大小的优化方案 #

你的核心需求是按id顺序累加大小，一旦超过阈值就停止扫描。窗口函数的方式会先全表扫描计算所有累计值再过滤，效率低下。可以用递归CTE实现提前终止，它能在每一步判断累计值是否超过阈值，一旦满足就停止递归：

WITH RECURSIVE selected_files AS (
    -- 初始步骤：获取第一条记录，计算初始累计大小
    SELECT 
        id, 
        name, 
        size AS total_size
    FROM filez
    ORDER BY id ASC
    LIMIT 1
    UNION ALL
    -- 递归步骤：取下一条记录，累加大小，仅当累计值未超过阈值时继续
    SELECT 
        f.id, 
        f.name, 
        s.total_size + f.size AS total_size
    FROM selected_files s
    JOIN filez f ON f.id = (SELECT MIN(id) FROM filez WHERE id > s.id)
    WHERE s.total_size + f.size <= THRESHOLD
)
SELECT id, name FROM selected_files;

这个方案的优势：

• 利用filez__id__idx索引快速定位下一条记录，无需全表扫描
• 每一步递归都会检查累计大小，一旦加上下一个文件的大小超过阈值，立即停止递归，不再扫描后续记录

泛化提前终止的实现方式 #

主流SQL数据库（如PostgreSQL）没有内置的stop_query函数直接终止查询，但可以通过以下两种方式实现类似逻辑：

1. 递归CTE（适用于有序迭代场景） #

如上面的文件查询示例，通过递归步骤中的条件判断，控制是否继续迭代，天然实现提前终止。

2. 游标（适用于复杂终止条件） #

在过程化SQL（如PostgreSQL的PL/pgSQL）中使用游标，逐行读取数据并判断终止条件，一旦满足就关闭游标。示例：

CREATE OR REPLACE FUNCTION select_until_threshold(threshold BIGINT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
DECLARE
    cur CURSOR FOR SELECT id, name, size FROM filez ORDER BY id ASC;
    rec RECORD;
    total BIGINT := 0;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        
        total := total + rec.size;
        IF total > threshold THEN
            EXIT;
        END IF;
        
        RETURN NEXT rec;
    END LOOP;
    CLOSE cur;
END;
$$ LANGUAGE plpgsql;

-- 调用函数获取结果
SELECT * FROM select_until_threshold(1000000);

这种方式完全由你控制终止时机，适合复杂的自定义终止条件，但需要编写过程化函数。

SQL本身是声明式语言，优化器通常会自行选择最优执行计划，但如果需要精确控制提前终止，过程化方式或递归CTE是可行的解决方案。

内容的提问来源于stack exchange，提问作者oᴉɹǝɥɔ