如何根据行程与分段表统计各行程的停留次数？

SQL查询语句 #

SELECT
    t.trip_id,
    t.distance,
    COUNT(s.segment_id) AS stop_count
FROM trips t
LEFT JOIN unnest(t.session_ids) AS sess(session_id) ON TRUE
LEFT JOIN segments s ON sess.session_id = s.session_id AND s.movement = 'f'
GROUP BY t.trip_id, t.distance
HAVING COUNT(s.segment_id) > 0
ORDER BY t.trip_id;

逻辑说明 #

• 用unnest(t.session_ids)将行程表中的session_id数组拆分为独立行，实现与分段表的关联匹配。
• 关联时过滤出movement = 'f'的停留分段数据。
• 按行程ID和距离分组，统计对应行程的停留段总数。
• 通过HAVING子句排除无有效停留段的行程（如trip_id=156），最终输出符合预期的结果。

内容的提问来源于stack exchange，提问作者Amina Umar