MySQL关联子查询在AWS Redshift报错，求无嵌套子查询替代方案

问题分析 #

你遇到的[0A000] ERROR: This type of correlated subquery pattern is not supported yet错误，是因为Redshift对JOIN条件中带ORDER BY和LIMIT 1的相关子查询支持有限。原逻辑是当search表的region_id匹配不到region表记录时，查找距离当前search经纬度在:dis范围内、type=1的最近region。

改写方案 #

我们可以用窗口函数+预计算距离的方式替代原相关子查询，具体步骤如下：

1. 预计算所有search和符合条件（type=1）的region之间的距离
2. 用ROW_NUMBER()窗口函数给每个search的候选region按距离升序排名
3. 只保留每个search排名第一的最近region
4. 将结果与原表关联，实现原逻辑

改写后的SQL #

WITH candidate_regions AS (
    SELECT
        s.id AS search_id,
        r.id AS region_id,
        -- 计算距离，和原逻辑一致
        acos(
            sin(radians(s.latitude)) * sin(radians(r.latitude))
            + cos(radians(s.latitude)) * cos(radians(r.latitude)) * cos(radians(r.longitude) - radians(s.longitude))
        ) * 3959 AS distance,
        -- 按search分组，距离升序排名
        ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY 
            acos(
                sin(radians(s.latitude)) * sin(radians(r.latitude))
                + cos(radians(s.latitude)) * cos(radians(r.latitude)) * cos(radians(r.longitude) - radians(s.longitude))
            ) * 3959 ASC
        ) AS rn
    FROM search s
    CROSS JOIN region r
    WHERE
        s.user IS NOT NULL
        AND r.type IN (1)
        AND acos(
            sin(radians(s.latitude)) * sin(radians(r.latitude))
            + cos(radians(s.latitude)) * cos(radians(r.latitude)) * cos(radians(r.longitude) - radians(s.longitude))
        ) * 3959 < :dis
),
nearest_regions AS (
    SELECT search_id, region_id
    FROM candidate_regions
    WHERE rn = 1 -- 只保留每个search的最近region
)
SELECT
    COALESCE(r.id, nr.region_id) AS region_id
FROM search s
LEFT JOIN region r ON s.region_id = r.id
LEFT JOIN nearest_regions nr ON s.id = nr.search_id
WHERE s.user IS NOT NULL
ORDER BY s.date_created DESC;

说明 #

• candidate_regions CTE：通过CROSS JOIN关联所有符合条件的search和region，计算距离并筛选出距离小于:dis的记录，同时用窗口函数给每个search的候选区域排名。
• nearest_regions CTE：过滤出每个search排名第一的最近区域。
• 主查询：将原表与nearest_regions关联，用COALESCE优先取原匹配的region_id，无匹配时取最近的区域ID，和原逻辑完全一致。

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