メモ残しておかんと、青冷めるので残しておく。
WITH with_stop AS (
SELECT
l.psg_id,
l.ride_time,
l.on_off,
-- stop_tsubame.name 先頭の「番号＋(． or .)」を落として、
-- stop_num から "N. " を付け直す（=> "1. 平高山" 形式）
(s.stop_num::text || '. ' ||
regexp_replace(s.name, '^[0-9]+[．\.]\s*', '')
) AS stop_name
FROM log_pass_tsubame l
JOIN LATERAL (
SELECT stop_num, name, geom
FROM stop_tsubame
ORDER BY l.geom <-> geom
LIMIT 1
) s ON true
),
seq AS (
SELECT
psg_id,
ride_time,
on_off,
stop_name,
-- 乗車(1)が出たら trip_no を +1（psg_id 内で乗車ごとにトリップ区切り）
SUM(CASE WHEN on_off = 1 THEN 1 ELSE 0 END)
OVER (PARTITION BY psg_id ORDER BY ride_time) AS trip_no
FROM with_stop
),
od AS (
SELECT
psg_id,
trip_no,
-- 同一 trip_no の最初の乗車停留所
(array_agg(stop_name ORDER BY ride_time)
FILTER (WHERE on_off = 1))[1] AS "from",
-- 同一 trip_no の最後の降車停留所（降車が複数あっても最後を採用）
(
array_agg(stop_name ORDER BY ride_time)
FILTER (WHERE on_off = 0)
)[
array_length(
array_agg(stop_name ORDER BY ride_time)
FILTER (WHERE on_off = 0)
, 1)
] AS "to"
FROM seq
WHERE trip_no > 0
GROUP BY psg_id, trip_no
)
SELECT
"from",
"to",
COUNT(*) AS trips
FROM od
WHERE "from" IS NOT NULL
AND "to" IS NOT NULL
AND "from" <> "to"
GROUP BY "from", "to"
ORDER BY "from", "to";これで直る点
-
表示が
1. 1．平高山のように重複せず、1. 平高山形式になります -
「降車→次の降車」ではなく、「乗車→降車」OD になります（提示されたCSVの形に一致するはず）
もし「期間指定」も当時入れていたなら、with_stop に条件を足します（例）。
WHERE l.ride_time >= '2025-01-01'::timestamp
AND l.ride_time < '2025-02-01'::timestamp