Snowflake中同表用户地理坐标距离计算及查询无结果问题解决

现有数据表 #

原查询语句问题 #

你编写的CTE查询存在核心错误：

• group_A和group_B中的left join table b未指定连接条件，会导致无意义的笛卡尔积或直接报错，这是无结果返回的主要原因
• 完全不需要定义两个重复的CTE，直接对原表做自连接即可实现需求

正确实现方案 #

1. 基础自连接计算所有用户对的距离 #

如果你的数据库支持haversine函数（如PostgreSQL的地理函数、部分云数据库内置实现），可直接执行以下查询：

SELECT
    a.User AS User_1,
    b.User AS User_2,
    ROUND(haversine(a.LATITUDE, a.LONGITUDE, b.LATITUDE, b.LONGITUDE), 3) AS Distance
FROM
    your_table_name a
JOIN
    your_table_name b ON a.User != b.User
-- 若需避免重复用户对（如A-B和B-A只保留一条），添加以下条件
-- WHERE a.User < b.User
ORDER BY
    Distance;

2. 自定义Haversine函数（无内置函数时） #

如果数据库没有内置Haversine函数，以MySQL为例自定义实现：

-- 创建返回米为单位的Haversine函数
DELIMITER //
CREATE FUNCTION haversine(lat1 DECIMAL(10,6), lon1 DECIMAL(10,6), lat2 DECIMAL(10,6), lon2 DECIMAL(10,6))
RETURNS DECIMAL(10,3)
DETERMINISTIC
BEGIN
    DECLARE R INT DEFAULT 6371000; -- 地球半径（米），改6371则为公里
    DECLARE phi1, phi2, delta_phi, delta_lon, a, c, d DECIMAL(10,6);
    SET phi1 = RADIANS(lat1);
    SET phi2 = RADIANS(lat2);
    SET delta_phi = RADIANS(lat2 - lat1);
    SET delta_lon = RADIANS(lon2 - lon1);
    SET a = SIN(delta_phi/2) * SIN(delta_phi/2) + COS(phi1) * COS(phi2) * SIN(delta_lon/2) * SIN(delta_lon/2);
    SET c = 2 * ATAN2(SQRT(a), SQRT(1-a));
    SET d = R * c;
    RETURN d;
END //
DELIMITER ;

-- 执行距离计算查询
SELECT
    a.User AS User_1,
    b.User AS User_2,
    ROUND(haversine(a.LATITUDE, a.LONGITUDE, b.LATITUDE, b.LONGITUDE), 3) AS Distance
FROM
    your_table_name a
JOIN
    your_table_name b ON a.User != b.User
ORDER BY
    Distance;

3. 找出每个用户的最近位置 #

若需求是获取每个用户距离最近的其他用户，用窗口函数实现：

WITH user_distances AS (
    SELECT
        a.User AS User_1,
        b.User AS User_2,
        ROUND(haversine(a.LATITUDE, a.LONGITUDE, b.LATITUDE, b.LONGITUDE), 3) AS Distance,
        ROW_NUMBER() OVER (PARTITION BY a.User ORDER BY haversine(a.LATITUDE, a.LONGITUDE, b.LATITUDE, b.LONGITUDE)) AS rn
    FROM
        your_table_name a
    JOIN
        your_table_name b ON a.User != b.User
)
SELECT User_1, User_2, Distance
FROM user_distances
WHERE rn = 1;

注意事项 #

• 替换your_table_name为实际表名
• 可通过修改地球半径参数调整距离单位（米/公里）
• 若不需要双向用户对，添加WHERE a.User < b.User过滤重复数据

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