How to generate this summary from my tables-CodePudding

I have the following two tables:

CREATE TABLE segments(session_id INT, segment_id INT, 
          length DOUBLE PRECISION, movement BOOLEAN);
INSERT INTO segments (session_id, segment_id, length, movement)
VALUES (49,0,0,'f'),(49,1,180851,'t'),(49,2,1.31082,'f'),(49,3,1580.41,'t'),
(49,4,1.0059,'f'),(49,5,4.47451,'t'),(49,6,1.69973,'f'),(55,0,2.76848,'f'),
(55,1,2.60235,'t'),(55,2,3.807,'f'),(55,3,2.78887,'t'),(55,4,8.47029,'f');

CREATE TABLE trips (trip_id INT, session_ids INT[], distance DOUBLE PRECISION);
INSERT INTO trips (trip_id, session_ids, distance)
VALUES (153,'{49}', 183245.960),(155,'{55}',109.035),
      (156,'{59,60}',909.870);

Representing user trips and corresponding trip segments.

In the segments table column movement shows whether the user is moving t; or stopped f. So I want to count the number of stops (not moving segments) for each trip in my database.

Expected results:

trip_id | distance | stop_count
-------- ---------- -----------
  153   |183245.96 |    4
  155   | 109.035  |    3

NB: db<>fiddle

CodePudding user response：

I did it in db<>fiffle.

Still have few questions - you did not mention how is movement defined (is it just f - for stop?)? Why distance in trips differ from sum that comes from segment length?

CodePudding user response：

You can simply use this query for it. If you have other questions feel free to comment.

select
    t.trip_id,
    t.distance,
    count(*)
--  USE THIS IF YOU OPT FOR REMOVING SECOND CONDITION FROM JOIN
--  count(s.movement) filter(where movement = false)
from
    trips t
inner join
    segments s
on  s.session_id = any(t.session_ids)
and movement = false
group by
    t.trip_id,
    t.distance