Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE trail
(
    time     timestamptz NOT NULL default now(),
    user_id  int         not null,
    position geography(POINT, 4326)
);

INSERT INTO trail VALUES
('2012-01-03T12:42:23', 1, ST_GeomFromText('POINT(-71.0 48.4)', 4326)),
('2012-01-03T12:54:23', 1, ST_GeomFromText('POINT(-72.0 48.4)', 4326)),
('2012-01-03T12:59:23', 1, ST_GeomFromText('POINT(-72.0 50.4)', 4326)),
  
('2012-02-03T12:42:23', 1, ST_GeomFromText('POINT(-71.0 48.4)', 4326)),
('2012-02-03T12:54:23', 1, ST_GeomFromText('POINT(-72.0 48.4)', 4326)),
('2012-02-03T12:59:23', 1, ST_GeomFromText('POINT(-72.0 50.4)', 4326)),
  
('2012-02-03T12:42:23', 2, ST_GeomFromText('POINT(-61.0 28.4)', 4326)),
('2012-02-03T12:54:23', 2, ST_GeomFromText('POINT(-62.0 28.4)', 4326)),
('2012-02-03T12:59:23', 2, ST_GeomFromText('POINT(-62.0 30.4)', 4326));

SELECT * FROM trail

CREATE TABLE



INSERT 0 9




time
user_id
position


2012-01-03 12:42:23+00
1
0101000020E61000000000000000C051C03333333333334840

2012-01-03 12:54:23+00
1
0101000020E610000000000000000052C03333333333334840

2012-01-03 12:59:23+00
1
0101000020E610000000000000000052C03333333333334940

2012-02-03 12:42:23+00
1
0101000020E61000000000000000C051C03333333333334840

2012-02-03 12:54:23+00
1
0101000020E610000000000000000052C03333333333334840

2012-02-03 12:59:23+00
1
0101000020E610000000000000000052C03333333333334940

2012-02-03 12:42:23+00
2
0101000020E61000000000000000804EC06666666666663C40

2012-02-03 12:54:23+00
2
0101000020E61000000000000000004FC06666666666663C40

2012-02-03 12:59:23+00
2
0101000020E61000000000000000004FC06666666666663E40



SELECT 9


SELECT
    *,
    lag(position) OVER (PARTITION BY user_id ORDER BY time) as prev_position
FROM trail
WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours' 


time
user_id
position
prev_position


2012-02-03 12:42:23+00
1
0101000020E61000000000000000C051C03333333333334840
null

2012-02-03 12:54:23+00
1
0101000020E610000000000000000052C03333333333334840
0101000020E61000000000000000C051C03333333333334840

2012-02-03 12:59:23+00
1
0101000020E610000000000000000052C03333333333334940
0101000020E610000000000000000052C03333333333334840

2012-02-03 12:42:23+00
2
0101000020E61000000000000000804EC06666666666663C40
null

2012-02-03 12:54:23+00
2
0101000020E61000000000000000004FC06666666666663C40
0101000020E61000000000000000804EC06666666666663C40

2012-02-03 12:59:23+00
2
0101000020E61000000000000000004FC06666666666663E40
0101000020E61000000000000000004FC06666666666663C40



SELECT 6


SELECT
    *,
    ST_Distance(
        position, 
        lag(position) OVER (PARTITION BY user_id ORDER BY time)
    ) as distance_to_prev_position
FROM trail
WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours' 


time
user_id
position
distance_to_prev_position


2012-02-03 12:42:23+00
1
0101000020E61000000000000000C051C03333333333334840
null

2012-02-03 12:54:23+00
1
0101000020E610000000000000000052C03333333333334840
74046.12952794

2012-02-03 12:59:23+00
1
0101000020E610000000000000000052C03333333333334940
222434.92794088

2012-02-03 12:42:23+00
2
0101000020E61000000000000000804EC06666666666663C40
null

2012-02-03 12:54:23+00
2
0101000020E61000000000000000004FC06666666666663C40
97995.98031436

2012-02-03 12:59:23+00
2
0101000020E61000000000000000004FC06666666666663E40
221684.90508034



SELECT 6


SELECT
    *,
    SUM(distance_to_prev_position) OVER (PARTITION BY user_id ORDER BY time) as cum_distance
FROM (
    SELECT
        *,
        ST_Distance(
            position, 
            lag(position) OVER (PARTITION BY user_id ORDER BY time)
        ) as distance_to_prev_position
    FROM trail
    WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours' 
) s


time
user_id
position
distance_to_prev_position
cum_distance


2012-02-03 12:42:23+00
1
0101000020E61000000000000000C051C03333333333334840
null
null

2012-02-03 12:54:23+00
1
0101000020E610000000000000000052C03333333333334840
74046.12952794
74046.12952794

2012-02-03 12:59:23+00
1
0101000020E610000000000000000052C03333333333334940
222434.92794088
296481.05746882

2012-02-03 12:42:23+00
2
0101000020E61000000000000000804EC06666666666663C40
null
null

2012-02-03 12:54:23+00
2
0101000020E61000000000000000004FC06666666666663C40
97995.98031436
97995.98031436

2012-02-03 12:59:23+00
2
0101000020E61000000000000000004FC06666666666663E40
221684.90508034
319680.8853947



SELECT 6


SELECT DISTINCT ON (user_id)
    *,
    SUM(distance_to_prev_position) OVER (PARTITION BY user_id ORDER BY time) as cum_distance
FROM (
    SELECT
        *,
        ST_Distance(
            position, 
            lag(position) OVER (PARTITION BY user_id ORDER BY time)
        ) as distance_to_prev_position
    FROM trail
    WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours' 
) s
ORDER BY user_id, time DESC


time
user_id
position
distance_to_prev_position
cum_distance


2012-02-03 12:59:23+00
1
0101000020E610000000000000000052C03333333333334940
222434.92794088
296481.05746882

2012-02-03 12:59:23+00
2
0101000020E61000000000000000004FC06666666666663E40
221684.90508034
319680.8853947



SELECT 2


CREATE MATERIALIZED VIEW distance_24 AS
  
SELECT DISTINCT ON (user_id)
    *,
    SUM(distance_to_prev_position) OVER (PARTITION BY user_id ORDER BY time) as cum_distance
FROM (
    SELECT
        *,
        ST_Distance(
            position, 
            lag(position) OVER (PARTITION BY user_id ORDER BY time)
        ) as distance_to_prev_position
    FROM trail
    WHERE time > '2012-02-03T23:00:00'::timestamp - interval '24 hours' 
) s
ORDER BY user_id, time DESC;

SELECT * FROM distance_24

SELECT 2




time
user_id
position
distance_to_prev_position
cum_distance


2012-02-03 12:59:23+00
1
0101000020E610000000000000000052C03333333333334940
222434.92794088
296481.05746882

2012-02-03 12:59:23+00
2
0101000020E61000000000000000004FC06666666666663E40
221684.90508034
319680.8853947



SELECT 2

time	user_id	position
2012-01-03 12:42:23+00	1	0101000020E61000000000000000C051C03333333333334840
2012-01-03 12:54:23+00	1	0101000020E610000000000000000052C03333333333334840
2012-01-03 12:59:23+00	1	0101000020E610000000000000000052C03333333333334940
2012-02-03 12:42:23+00	1	0101000020E61000000000000000C051C03333333333334840
2012-02-03 12:54:23+00	1	0101000020E610000000000000000052C03333333333334840
2012-02-03 12:59:23+00	1	0101000020E610000000000000000052C03333333333334940
2012-02-03 12:42:23+00	2	0101000020E61000000000000000804EC06666666666663C40
2012-02-03 12:54:23+00	2	0101000020E61000000000000000004FC06666666666663C40
2012-02-03 12:59:23+00	2	0101000020E61000000000000000004FC06666666666663E40

time	user_id	position	prev_position
2012-02-03 12:42:23+00	1	0101000020E61000000000000000C051C03333333333334840	null
2012-02-03 12:54:23+00	1	0101000020E610000000000000000052C03333333333334840	0101000020E61000000000000000C051C03333333333334840
2012-02-03 12:59:23+00	1	0101000020E610000000000000000052C03333333333334940	0101000020E610000000000000000052C03333333333334840
2012-02-03 12:42:23+00	2	0101000020E61000000000000000804EC06666666666663C40	null
2012-02-03 12:54:23+00	2	0101000020E61000000000000000004FC06666666666663C40	0101000020E61000000000000000804EC06666666666663C40
2012-02-03 12:59:23+00	2	0101000020E61000000000000000004FC06666666666663E40	0101000020E61000000000000000004FC06666666666663C40

time	user_id	position	distance_to_prev_position	cum_distance
2012-02-03 12:59:23+00	1	0101000020E610000000000000000052C03333333333334940	222434.92794088	296481.05746882
2012-02-03 12:59:23+00	2	0101000020E61000000000000000004FC06666666666663E40	221684.90508034	319680.8853947