問題
部屋の入退室記録サービスを考えます。
部屋ごとにユーザーが入室時刻・退室時刻を記録します。このとき、以下のようなトランザクションデータが登録されると思います。
このデータから「部屋に誰も居ない時間帯」を取得したいと思います。
なお、全体の営業時間的なものを9:00から20:00とします。
| id | room_id | user_id | entry_at | exit_at |
|---|---|---|---|---|
| 1 | 1 | 1 | 2024/01/01 9:00:00 | 2024/01/01 10:00:00 |
| 2 | 1 | 2 | 2024/01/01 9:00:00 | 2024/01/01 11:00:00 |
| 3 | 1 | 3 | 2024/01/01 13:00:00 | 2024/01/01 14:00:00 |
| 4 | 1 | 1 | 2024/01/01 16:00:00 | 2024/01/01 17:00:00 |
| 5 | 1 | 4 | 2024/01/01 17:00:00 | 2024/01/01 18:00:00 |
| 6 | 2 | 4 | 2024/01/01 10:00:00 | 2024/01/01 12:00:00 |
| 7 | 2 | 5 | 2024/01/01 14:00:00 | 2024/01/01 17:00:00 |
| 8 | 2 | 6 | 2024/01/01 15:00:00 | 2024/01/01 16:00:00 |
| 9 | 2 | 7 | 2024/01/01 15:00:00 | 2024/01/01 20:00:00 |
テーブル定義 & テストデータ
CREATE TABLE room_access (
id SERIAL PRIMARY KEY,
room_id INT NOT NULL,
user_id INT NOT NULL,
entry_at TIMESTAMP NOT NULL,
exit_at TIMESTAMP NOT NULL
);
INSERT INTO room_access (room_id, user_id, entry_at, exit_at)
VALUES
(1,1,'2024-01-01 09:00:00','2024-01-01 10:00:00'),
(1,2,'2024-01-01 09:00:00','2024-01-01 11:00:00'),
(1,3,'2024-01-01 13:00:00','2024-01-01 14:00:00'),
(1,1,'2024-01-01 16:00:00','2024-01-01 17:00:00'),
(1,4,'2024-01-01 17:00:00','2024-01-01 18:00:00'),
(2,4,'2024-01-01 10:00:00','2024-01-01 12:00:00'),
(2,5,'2024-01-01 14:00:00','2024-01-01 17:00:00'),
(2,6,'2024-01-01 15:00:00','2024-01-01 16:00:00'),
(2,7,'2024-01-01 15:00:00','2024-01-01 20:00:00');
期待する出力
このケースで期待する出力は下記のような形となります。
| room_id | empty_from | empty_to |
|---|---|---|
| 1 | 2024-01-01 11:00:00 | 2024-01-01 13:00:00 |
| 1 | 2024-01-01 14:00:00 | 2024-01-01 16:00:00 |
| 1 | 2024-01-01 18:00:00 | 2024-01-01 20:00:00 |
| 2 | 2024-01-01 09:00:00 | 2024-01-01 10:00:00 |
| 2 | 2024-01-01 12:00:00 | 2024-01-01 14:00:00 |
解法
1. 入室時間と前の退室時間のペアを生成
営業時間内の入室時間に対して、前の退室時間をペアにして取得します。
各部屋で最初の入室はペアが取得されません。
-- 入室時間と前の退室時間のペアを生成
SELECT
room_id,
entry_at,
LAG(exit_at) OVER (PARTITION BY room_id ORDER BY entry_at) AS prev_exit_at
FROM
room_access
WHERE
entry_at >= '2024-01-01 09:00:00' AND exit_at <= '2024-01-01 20:00:00'
| room_id | entry_at | prev_exit_at |
|---|---|---|
| 1 | 2024/01/01 9:00:00 | |
| 1 | 2024/01/01 9:00:00 | 2024/01/01 10:00:00 |
| 1 | 2024/01/01 13:00:00 | 2024/01/01 11:00:00 |
| 1 | 2024/01/01 16:00:00 | 2024/01/01 14:00:00 |
| 1 | 2024/01/01 17:00:00 | 2024/01/01 17:00:00 |
| 2 | 2024/01/01 10:00:00 | |
| 2 | 2024/01/01 14:00:00 | 2024/01/01 12:00:00 |
| 2 | 2024/01/01 15:00:00 | 2024/01/01 17:00:00 |
| 2 | 2024/01/01 15:00:00 | 2024/01/01 16:00:00 |
2. 各入室時間の前の空き時間を取得
手順1で作成したペアを使用して、各入室時間 (entry_at) の前の空き時間を取得します。
前の退室時間 (prev_exit_at) から入室時間 (entry_at)までの間が空き時間となります。
前の退室時間がない場合(その日の最初の入室)、営業開始時間(9:00)が開始時間として使用されます。
WITH access_periods AS (
-- 入室時間と前の退室時間のペアを生成
SELECT
room_id,
entry_at,
LAG(exit_at) OVER (PARTITION BY room_id ORDER BY entry_at) AS prev_exit_at
FROM
room_access
WHERE
entry_at >= '2024-01-01 09:00:00' AND exit_at <= '2024-01-01 20:00:00'
)
-- 各入室時間の前の空き時間を取得
SELECT
room_id,
COALESCE(prev_exit_at, '2024-01-01 09:00:00') AS empty_start,
entry_at AS empty_end
FROM
access_periods
| room_id | empty_start | empty_end |
|---|---|---|
| 1 | 2024/01/01 9:00:00 | 2024/01/01 9:00:00 |
| 1 | 2024/01/01 10:00:00 | 2024/01/01 9:00:00 |
| 1 | 2024/01/01 11:00:00 | 2024/01/01 13:00:00 |
| 1 | 2024/01/01 14:00:00 | 2024/01/01 16:00:00 |
| 1 | 2024/01/01 17:00:00 | 2024/01/01 17:00:00 |
| 2 | 2024/01/01 9:00:00 | 2024/01/01 10:00:00 |
| 2 | 2024/01/01 12:00:00 | 2024/01/01 14:00:00 |
| 2 | 2024/01/01 17:00:00 | 2024/01/01 15:00:00 |
| 2 | 2024/01/01 16:00:00 | 2024/01/01 15:00:00 |
3. 各部屋の最後の空き時間を取得
各部屋の最後の退室時間を取得し、営業終了時間(20:00)と組み合わせて、最後の空き時間を取得します。
-- 各部屋の最後の空き時間を取得
SELECT
room_id,
MAX(exit_at) AS empty_start,
'2024-01-01 20:00:00' AS empty_end
FROM
room_access
WHERE
exit_at <= '2024-01-01 20:00:00'
GROUP BY room_id
| room_id | empty_start | empty_end |
|---|---|---|
| 1 | 2024/01/01 18:00:00 | 2024-01-01 20:00:00 |
| 2 | 2024/01/01 20:00:00 | 2024-01-01 20:00:00 |
4. 空き時間の抽出
手順2、3で取得した期間からempty_end > empty_startを条件に、実際に空いている時間帯を取得します。
WITH access_periods AS (
-- 入室時間と前の退室時間のペアを生成
SELECT
room_id,
entry_at,
LAG(exit_at) OVER (PARTITION BY room_id ORDER BY entry_at) AS prev_exit_at
FROM
room_access
WHERE
entry_at >= '2024-01-01 09:00:00' AND exit_at <= '2024-01-01 20:00:00'
),
empty_periods AS (
-- 各入室時間の前の空き時間を取得
SELECT
room_id,
COALESCE(prev_exit_at, '2024-01-01 09:00:00') AS empty_start,
entry_at AS empty_end
FROM
access_periods
UNION ALL
-- 各部屋の最後の空き時間を取得
SELECT
room_id,
MAX(exit_at) AS empty_start,
'2024-01-01 20:00:00' AS empty_end
FROM
room_access
WHERE
exit_at <= '2024-01-01 20:00:00'
GROUP BY room_id
)
SELECT
room_id,
empty_start,
empty_end
FROM
empty_periods
WHERE
-- 実際に空いている時間帯のみを抽出
empty_end > empty_start
ORDER BY
room_id, empty_start
| room_id | empty_start | empty_end |
|---|---|---|
| 1 | 2024/01/01 11:00:00 | 2024/01/01 13:00:00 |
| 1 | 2024/01/01 14:00:00 | 2024/01/01 16:00:00 |
| 1 | 2024/01/01 18:00:00 | 2024/01/01 20:00:00 |
| 2 | 2024/01/01 9:00:00 | 2024/01/01 10:00:00 |
| 2 | 2024/01/01 12:00:00 | 2024/01/01 14:00:00 |
まとめ
ウィンドウ関数が強すぎる。