Snowflake SQL：如何计算对象在同一位置的驻留时长及首次到达时间

该需求属于经典的「间隙与岛屿（gaps and islands）」序列问题，可通过窗口函数分组计算实现，具体方案如下：

核心思路 #

• 按对象ID、采集时间排序，对比相邻两条记录的经纬度，标记位置发生变化的节点
• 对变化标记做累加，得到每个连续驻留周期的唯一分组ID
• 按对象、分组ID、经纬度聚合，计算每个周期的开始时间和驻留时长

完整SQL代码 #

WITH raw_data AS (
    -- 此处为样例测试数据，实际使用时替换为你的业务表即可
    SELECT * FROM VALUES
    (23, '2021-05-02 12:00'::TIMESTAMP, 38.09, 20.99),
    (23, '2021-05-02 16:00'::TIMESTAMP, 40.11, 30.34),
    (23, '2021-05-02 23:00'::TIMESTAMP, 40.11, 30.34),
    (23, '2021-05-03 12:00'::TIMESTAMP, 40.11, 30.34),
    (23, '2021-05-03 16:00'::TIMESTAMP, 40.11, 30.34),
    (23, '2021-05-03 23:00'::TIMESTAMP, 39.88, 29.00),
    (23, '2021-05-04 12:00'::TIMESTAMP, 39.88, 29.00),
    (23, '2021-05-04 16:00'::TIMESTAMP, 20.77, 11.66),
    (23, '2021-05-04 23:00'::TIMESTAMP, 40.11, 30.34),
    (23, '2021-05-05 12:00'::TIMESTAMP, 40.11, 30.34),
    (23, '2021-05-05 16:00'::TIMESTAMP, 20.77, 11.66)
    t(ObjectID, DateTime, Lat, Lon)
),
-- 步骤1：获取上一条记录的经纬度
prev_loc AS (
    SELECT 
        ObjectID,
        DateTime,
        Lat,
        Lon,
        LAG(Lat) OVER (PARTITION BY ObjectID ORDER BY DateTime) AS prev_lat,
        LAG(Lon) OVER (PARTITION BY ObjectID ORDER BY DateTime) AS prev_lon
    FROM raw_data
),
-- 步骤2：标记位置变化的节点，生成连续驻留周期的分组ID
session_group AS (
    SELECT 
        *,
        SUM(CASE WHEN Lat = prev_lat AND Lon = prev_lon THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ObjectID ORDER BY DateTime) AS session_id
    FROM prev_loc
)
-- 步骤3：聚合计算每个驻留周期的结果
SELECT 
    ObjectID,
    MIN(DateTime) AS StartTime,
    Lat,
    Lon,
    -- 计算驻留时长并转换为HH:MI格式，若需直接取小时数可替换为DATEDIFF('hour', MIN(DateTime), MAX(DateTime))
    TO_CHAR(DATEADD('second', DATEDIFF('second', MIN(DateTime), MAX(DateTime)), '1970-01-01'::DATE), 'HH24:MI') AS HoursInLocation
FROM session_group
-- 过滤仅保留有连续驻留（至少2条采集记录）的周期，和样例输出规则一致
GROUP BY ObjectID, session_id, Lat, Lon
HAVING COUNT(*) >= 2
ORDER BY StartTime;

逻辑说明 #

• 多对象兼容：所有窗口函数都按ObjectID分区，可同时处理多个对象的驻留数据
• 采集频率适配：无需固定采集间隔，仅以实际采集到的同位置最早、最晚记录计算时长，符合两次采集间隔位置未知的业务规则
• 重复位置识别：相同经纬度如果中间出现其他位置，会被判定为不同的驻留周期，独立计算时长
• 输出格式适配：HoursInLocation默认输出为HH:MI格式，可根据业务需要调整为小时、分钟等数值格式

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