基于PostgreSQL+PostGIS检测账号共享违规的SQL查询实现需求

核心逻辑 #

通过自连接同一用户的请求记录，对比时间间隔与理论可移动距离，筛选出不符合物理移动规律的用户。

完整SQL示例（以参数N=24、M=10、S=120为例） #

WITH recent_records AS (
    SELECT
        user_id,
        created,
        -- 将经纬度转换为PostGIS地理坐标（WGS84坐标系）
        ST_SetSRID(ST_MakePoint(lng, lat), 4326)::geography AS geom
    FROM tracking
    -- 筛选最近N小时的记录
    WHERE created >= NOW() - INTERVAL '24 hours'
)
-- 去重得到违规用户ID
SELECT DISTINCT r1.user_id
FROM recent_records r1
JOIN recent_records r2
    ON r1.user_id = r2.user_id
    -- 确保第二条记录晚于第一条，且间隔不超过M分钟
    AND r2.created > r1.created
    AND r2.created <= r1.created + INTERVAL '10 minutes'
WHERE
    -- 计算两点实际球面距离（单位：米）
    ST_Distance(r1.geom, r2.geom) > 
    -- 计算该时间内以S km/h能移动的最大距离（单位：米）
    120 * 1000 * EXTRACT(EPOCH FROM (r2.created - r1.created)) / 3600;

关键细节说明 #

1. 地理坐标转换：用ST_MakePoint将经纬度转为点对象，再通过::geography转换为地理类型，确保计算的是球面真实距离，而非平面距离。
2. 时间与距离校验：
   • 右侧公式：把速度（km/h）转为米/小时，乘以时间差（小时），得到这段时间内的理论最大移动距离。
   • 若实际球面距离超过该值，说明用户不可能在这段时间内完成移动，存在账号共享嫌疑。
3. CTE的作用：先过滤出最近N小时的记录，减少后续自连接的数据量，提升查询效率。

性能优化建议 #

• 创建复合索引：CREATE INDEX idx_tracking_user_created ON tracking(user_id, created);，加速用户分组和时间范围筛选。
• 创建空间索引：CREATE INDEX idx_tracking_geom ON tracking USING GIST(ST_SetSRID(ST_MakePoint(lng, lat), 4326));，大幅提升距离计算的速度。

参数替换指南 #

将SQL中的以下部分替换为你的实际业务参数：

• INTERVAL '24 hours' → 替换为INTERVAL '{N} hours'（N为最近小时数）
• INTERVAL '10 minutes' → 替换为INTERVAL '{M} minutes'（M为时间间隔阈值）
• 120 → 替换为你的最大移动速度{S}（单位：km/h）

内容的提问来源于stack exchange，提问作者vitaly-t