如何复用同一SELECT查询结果删除多表中符合条件的数据？

我的表结构及数据示例如下：

name | version | processed | processing | updated  | ref_time 
------+---------+-----------+------------+----------+----------
 abc  |       1 | t         | f          | 27794395 | 27794160
 def  |       1 | t         | f          | 27794395 | 27793440
 ghi  |       1 | t         | f          | 27794395 | 27793440
 jkl  |       1 | f         | f          | 27794395 | 27794160
 mno  |       1 | t         | f          | 27794395 | 27793440
 pqr  |       1 | f         | t          | 27794395 | 27794160

我编写了生成待删除ref_time列表的查询：

WITH main AS
(
    SELECT ref_time,
        ROUND(AVG(processed::int) * 100, 1) percent
    FROM status_table
    GROUP BY ref_time ORDER BY ref_time DESC, percent DESC
)
SELECT ref_time FROM main WHERE percent=100 OFFSET 2;

该查询返回结果示例：

ref_time 
----------
 27794880
 27794160

并通过以下语句删除了status_table中符合条件的数据：

DELETE FROM status_table
WHERE ref_time IN 
(
    WITH main AS
    (
        SELECT ref_time,
            ROUND(AVG(processed::int) * 100, 1) percent
        FROM status_table
        GROUP BY ref_time ORDER BY ref_time DESC, percent DESC
    )
    SELECT ref_time FROM main WHERE percent=100 OFFSET 2
);

现在需要基于同一组ref_time值删除data_table中的对应数据，如何避免重复编写生成ref_time列表的查询？

方法1：共享CTE一次性删除多表数据（PostgreSQL适用） #

将生成目标ref_time的逻辑放在外层CTE中，后续两次删除操作直接复用该CTE的结果：

WITH target_ref_times AS (
    SELECT ref_time
    FROM (
        SELECT ref_time,
               ROUND(AVG(processed::int) * 100, 1) AS percent,
               ROW_NUMBER() OVER (ORDER BY ref_time DESC, percent DESC) AS rn
        FROM status_table
        GROUP BY ref_time
    ) sub
    WHERE percent = 100 AND rn > 2
)
-- 删除status_table数据
DELETE FROM status_table st
USING target_ref_times trt
WHERE st.ref_time = trt.ref_time;

-- 删除data_table数据
DELETE FROM data_table dt
USING target_ref_times trt
WHERE dt.ref_time = trt.ref_time;

注：这里用ROW_NUMBER()替代原查询的OFFSET 2，逻辑完全等价，且在CTE中更易维护。

方法2：用临时表存储目标ref_time #

如果需要多次复用这组ref_time，或者数据库不支持跨删除语句共享CTE，可以先将结果存入临时表：

-- 创建临时表存储待删除的ref_time
CREATE TEMP TABLE temp_ref_times AS
WITH main AS
(
    SELECT ref_time,
        ROUND(AVG(processed::int) * 100, 1) percent
    FROM status_table
    GROUP BY ref_time ORDER BY ref_time DESC, percent DESC
)
SELECT ref_time FROM main WHERE percent=100 OFFSET 2;

-- 删除status_table数据
DELETE FROM status_table
WHERE ref_time IN (SELECT ref_time FROM temp_ref_times);

-- 删除data_table数据
DELETE FROM data_table
WHERE ref_time IN (SELECT ref_time FROM temp_ref_times);

-- 临时表会在会话结束后自动清理，也可手动删除
DROP TABLE temp_ref_times;

方法3：封装成自定义函数复用逻辑 #

如果该ref_time筛选逻辑会频繁使用，可封装成函数：

CREATE OR REPLACE FUNCTION get_old_ref_times()
RETURNS TABLE(ref_time INT) AS $$
BEGIN
    RETURN QUERY
    WITH main AS
    (
        SELECT ref_time,
            ROUND(AVG(processed::int) * 100, 1) percent
        FROM status_table
        GROUP BY ref_time ORDER BY ref_time DESC, percent DESC
    )
    SELECT ref_time FROM main WHERE percent=100 OFFSET 2;
END;
$$ LANGUAGE plpgsql;

-- 删除数据时直接调用函数
DELETE FROM status_table WHERE ref_time IN (SELECT * FROM get_old_ref_times());
DELETE FROM data_table WHERE ref_time IN (SELECT * FROM get_old_ref_times());

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