tl;dr

使用lead（）按用户和日期查找下一个日志条目。加入下一个日志条目，并使用ST_LengthSperoid（）和age（）计算时间和距离增量。使用此增量计算速度并过滤数据。

说来话长

让我们创建一些演示数据：

--DROP TABLE IF EXISTS tracking;

CREATE TABLE tracking(
    created timestamp,
    user_id integer,
    lat numeric,
    lng numeric
);

--TRUNCATE TABLE tracking;

INSERT INTO tracking VALUES
-- user 1
('2023-05-02 8:00', 1, 50.01, 8.02),
('2023-05-02 8:30', 1, 50.02, 8.03),
('2023-05-02 8:55', 1, 50.03, 8.01),
-- user 1 violation
('2023-05-02 8:05', 1, 53.00, 7.00),
-- user 2
('2023-05-02 9:00', 2, 40.01, 8.03),
('2023-05-02 9:30', 2, 40.02, 8.02),
('2023-05-02 9:55', 2, 40.03, 8.03);

我猜lead()可以做到这一点，因为它返回以下条目的时间戳，按时间戳排序：

SELECT 
    user_id,
    created as t1_created,
    lat as t1_lat,
    lng as t1_lng,
    
    LEAD(created,1) OVER(
        PARTITION BY user_id
        ORDER BY created
    ) as t2_created 
FROM 
    tracking
-- also possible to check for M and N at this stage
;

这导致（没有双关语😉）

然后，您可以连接数据：

WITH next_timestamp as (
SELECT 
    user_id,
    created as t1_created,
    lat as t1_lat,
    lng as t1_lng,
    
    LEAD(created,1) OVER(
        PARTITION BY user_id
        ORDER BY created
    ) as t2_created
FROM 
    tracking
-- also possible to check for M and N at this stage
)

SELECT 
    next_timestamp.*,
    t2.lng as t2_lng,
    t2.lat as t2_lat
FROM
    next_timestamp
JOIN
    tracking t2
ON 
    next_timestamp.t2_created = t2.created AND
    next_timestamp.user_id = t2.user_id

其返回

做点数学题

WITH next_timestamp as (
SELECT 
    user_id,
    created as t1_created,
    lat as t1_lat,
    lng as t1_lng,
    
    LEAD(created,1) OVER(
        PARTITION BY user_id
        ORDER BY created
    ) as t2_created
    
FROM 
    tracking
),

next_location AS (
    SELECT 
        next_timestamp.*,
        t2.lng as t2_lng,
        t2.lat as t2_lat
    FROM
        next_timestamp
    JOIN
        tracking t2
    ON 
        next_timestamp.t2_created = t2.created AND
        next_timestamp.user_id = t2.user_id
),

with_deltas AS (

    SELECT
        *,
        age(t2_created, t1_created) as delta_time,
        ST_LengthSpheroid(
            ST_MakeLine(
                ST_Point(t1_lng, t1_lat),
                ST_Point(t2_lng, t2_lat)
            ),
            'SPHEROID["GRS_1980",6378137,298.257222101]'
            )/1000 as delta_distance_km

    FROM
        next_location   
)

SELECT 
    *,
    EXTRACT(epoch FROM delta_time)/3600 as delta_time_hours,
    delta_distance_km / (EXTRACT(epoch FROM delta_time)/3600) as speed_kmh
FROM 
    with_deltas

它返回所需的结构：

此时，您可以添加一个过滤器，如

-- include CTEs from above

SELECT 
    *,
    EXTRACT(epoch FROM delta_time)/3600 as delta_time_hours,
    delta_distance_km / (EXTRACT(epoch FROM delta_time)/3600) as speed_kmh
FROM 
    with_deltas
WHERE
     age(now(), t1_created) < '24 hour'::interval AND -- test for M
     age(t2_created, t1_created) < '10 Minute'::interval AND --test for N
     delta_distance_km / (EXTRACT(epoch FROM delta_time)/3600) > 120 --test for S

获取所需的user_id：

-- include CTEs from above

SELECT 
    DISTINCT(user_id)
FROM 
    with_deltas
WHERE
     age(now(), t1_created) < '24 hour'::interval AND -- test for M
     age(t2_created, t1_created) < '10 Minute'::interval AND --test for N
     delta_distance_km / (EXTRACT(epoch FROM delta_time)/3600) > 120 --test for S