2

I have a table trips with the following information

Row User Timestamp Event
1 1 29/06/2022 17:18:03 Walking
2 1 29/06/2022 17:18:02 Walking
3 1 29/06/2022 17:18:01 Start
4 1 28/06/2022 19:21:03 Walking
5 1 28/06/2022 19:21:02 Walking
6 1 28/06/2022 19:21:01 Start
7 2 27/06/2022 17:36:03 Walking
8 2 27/06/2022 17:36:02 Walking
9 2 27/06/2022 17:36:01 Start
10 2 27/06/2022 16:55:03 Walking
11 2 27/06/2022 16:55:02 Walking
12 2 27/06/2022 16:55:01 Start

I would like to add a column that groups the users by User, sorts by Timestamp descending, and then identifies different trips wherever 'Start' is followed by 'Walking'. The identified trips should then be allocated a trip number in Trip nr.

Row User Timestamp Event Trip Nr
1 1 29/06/2022 17:18:03 Walking 4
2 1 29/06/2022 17:18:02 Walking 4
3 1 29/06/2022 17:18:01 Start 4
4 1 28/06/2022 19:21:03 Walking 3
5 1 28/06/2022 19:21:02 Walking 3
6 1 28/06/2022 19:21:01 Start 3
7 2 27/06/2022 17:36:03 Walking 2
8 2 27/06/2022 17:36:02 Walking 2
9 2 27/06/2022 17:36:01 Start 2
10 2 27/06/2022 16:55:03 Walking 1
11 2 27/06/2022 16:55:02 Walking 1
12 2 27/06/2022 16:55:01 Start 1

How can I achieve this optimally?

Improve this question
1
  • Please provide insert scripts for the person helping you :)
    – Peter
    Dec 1, 2022 at 10:19

1 Answer 1

Reset to default
0

This is an example of a SQL pattern called gaps and islands. The goal is to associate a unique grouping ID with each "island", then you can use GROUP BY to separate them out.

The first step is to determine where a gap is between each island, in a SQL sense. For your case, you already flag each new island by calling it Start. The next step is to assign a unique ID to each block headed by a Start. To create this ID we use a rolling sum over a column that is 1 when the row is a Start and 0 otherwise. It turns out that the rolling sum column is identical to your Trip Nr column. We now have each trip uniquely identified.

Demonstration fiddle.

The final query is:

 SELECT s.user_id AS "User",
    s.ts AS "Timestamp",
    s.event AS "Event",
    s.trip_nr AS "Trip Nr"
   FROM ( SELECT t.user_id,
            t.ts,
            t.event,
            sum(t.start_indicator) OVER (ORDER BY t.user_id, t.ts) AS trip_nr
           FROM ( SELECT trips.user_id,
                    trips.ts,
                    trips.event,
                        CASE
                            WHEN trips.event = 'Start'::text THEN 1
                            ELSE 0
                        END AS start_indicator
                   FROM trips) t) s
  ORDER BY s.user_id ASC, s.ts DESC
Improve this answer
3
  • Hi, thanks. But the query doesn't work if there are overlapping or similar timestamps between users.
    – Ruan
    Dec 7, 2022 at 10:34
  • Oops, probably need to add user_id to the window function sort. I’ll look at it tonight.
    – dwhitemv
    Dec 7, 2022 at 15:40
  • Updated query, added user_id to window function sort term. It means that trip numbers become a bit more arbitrary, but that's mostly unavoidable in the face of overlapping times for different user IDs. You can always add a new outer query to rearrange them as needed.
    – dwhitemv
    Dec 8, 2022 at 3:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.