Postgres 15

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

select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table trajectory(
  user_id int,
  session_id int, 
  lat double precision,
  lon double precision,
  ordering_id int generated always as identity);

INSERT INTO trajectory(user_id, session_id, lat, lon)
VALUES (1, 25304,39.984702, 116.318417),
   (1, 25304, 39.984683, 116.31845), (1, 25304, 39.984686, 116.318417),
  (1, 25304, 39.984688, 116.318385), (1, 20959,40.008304, 116.319876),
  (1, 20959,40.008413, 116.319962), (1, 20959,40.007171, 116.319458),
  (2, 55305, 39.984094, 116.319236), (2, 55305, 39.984198, 116.319322), 
  (2, 55305, 39.984224, 116.319402), (2, 34104, 40.013867, 116.306473),
  (2, 34104, 40.013907, 116.306488);

CREATE TABLE



INSERT 0 12


select t.*, 
    count(*) 
        filter(where session_id is distinct from lag_session_id) 
        over(partition by user_id order by ordering_id) trip_id
from (
    select t.*, 
        lag(session_id) over(partition by user_id order by ordering_id) lag_session_id
    from trajectory t
) t


user_id
session_id
lat
lon
ordering_id
lag_session_id
trip_id


1
25304
39.984702
116.318417
1
null
1

1
25304
39.984683
116.31845
2
25304
1

1
25304
39.984686
116.318417
3
25304
1

1
25304
39.984688
116.318385
4
25304
1

1
20959
40.008304
116.319876
5
25304
2

1
20959
40.008413
116.319962
6
20959
2

1
20959
40.007171
116.319458
7
20959
2

2
55305
39.984094
116.319236
8
null
1

2
55305
39.984198
116.319322
9
55305
1

2
55305
39.984224
116.319402
10
55305
1

2
34104
40.013867
116.306473
11
55305
2

2
34104
40.013907
116.306488
12
34104
2



SELECT 12

user_id	session_id	lat	lon	ordering_id	lag_session_id	trip_id
1	25304	39.984702	116.318417	1	null	1
1	25304	39.984683	116.31845	2	25304	1
1	25304	39.984686	116.318417	3	25304	1
1	25304	39.984688	116.318385	4	25304	1
1	20959	40.008304	116.319876	5	25304	2
1	20959	40.008413	116.319962	6	20959	2
1	20959	40.007171	116.319458	7	20959	2
2	55305	39.984094	116.319236	8	null	1
2	55305	39.984198	116.319322	9	55305	1
2	55305	39.984224	116.319402	10	55305	1
2	34104	40.013867	116.306473	11	55305	2
2	34104	40.013907	116.306488	12	34104	2