基于位置数据生成new_idle_moving列的SQL优化需求

问题分析 #

原SQL错误地通过相邻位置分组取MAX(idle_or_moving)来判断，逻辑完全偏离需求——它无法正确识别设备下所有重复的经纬度对，导致错误将所有值置为idle。

修正逻辑 #

核心思路：

• 按rake_device分区、created_date降序排列
• 标记每条记录的经纬度对在分区内是否存在重复（即与全列位置对对比后，该经纬度对不是唯一的）
• 若存在重复且原状态为moving，则将new_idle_moving设为idle，否则保持原状态

修正后的SQL代码 #

WITH DuplicateCheck AS (
  SELECT
    created_date,
    rake_device,
    rounded_geo_lat,
    rounded_geo_lng,
    idle_or_moving,
    -- 统计当前经纬度对在对应设备内的总出现次数，判断是否存在重复
    CASE 
      WHEN COUNT(*) OVER (PARTITION BY rake_device, rounded_geo_lat, rounded_geo_lng) > 1 
      THEN 1 
      ELSE 0 
    END AS has_duplicate_location
  FROM YourTable
)
SELECT
  created_date,
  rake_device,
  rounded_geo_lat,
  rounded_geo_lng,
  idle_or_moving,
  -- 按规则生成目标列
  CASE 
    WHEN has_duplicate_location = 1 AND idle_or_moving = 'moving' 
    THEN 'idle' 
    ELSE idle_or_moving 
  END AS new_idle_moving
FROM DuplicateCheck
ORDER BY created_date DESC;

可选场景：仅关注相邻位置重复 #

如果需求是仅当当前记录的前后相邻经纬度对相同时才触发修改，可使用以下版本：

WITH AdjacentCheck AS (
  SELECT
    created_date,
    rake_device,
    rounded_geo_lat,
    rounded_geo_lng,
    idle_or_moving,
    -- 检查前一条或后一条记录是否有相同经纬度对
    CASE 
      WHEN (LAG(rounded_geo_lat) OVER (PARTITION BY rake_device ORDER BY created_date DESC) = rounded_geo_lat
            AND LAG(rounded_geo_lng) OVER (PARTITION BY rake_device ORDER BY created_date DESC) = rounded_geo_lng)
           OR (LEAD(rounded_geo_lat) OVER (PARTITION BY rake_device ORDER BY created_date DESC) = rounded_geo_lat
               AND LEAD(rounded_geo_lng) OVER (PARTITION BY rake_device ORDER BY created_date DESC) = rounded_geo_lng)
      THEN 1 
      ELSE 0 
    END AS has_adjacent_duplicate
  FROM YourTable
)
SELECT
  created_date,
  rake_device,
  rounded_geo_lat,
  rounded_geo_lng,
  idle_or_moving,
  CASE 
    WHEN has_adjacent_duplicate = 1 AND idle_or_moving = 'moving' 
    THEN 'idle' 
    ELSE idle_or_moving 
  END AS new_idle_moving
FROM AdjacentCheck
ORDER BY created_date DESC;

说明 #

• 第一个版本通过COUNT()窗口函数实现全量位置对比，适配增量数据场景下的全局重复判断
• 第二个版本仅检查相邻记录，适用于仅关注连续位置重复的业务场景
• 两种版本均修复了原SQL的分组逻辑错误，精准匹配需求规则

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