🐈

SQLで時間帯テーブルから空いてる時間帯を取得する

2022/10/24に公開約4,900字2件のコメント

やりたいこと

入室時刻・退室時刻の時間帯を登録するようなテーブルにおいて、「誰も居ない時間帯を取得したい」場合のやりかたについて考えます。
要は範囲レコードの隙間を取得する方法です。
今回は全体の営業時間的なものを9:00から20:00とし、その間で誰も居ない時間帯を取得します。
なお、今回は入退室記録を閉区間データとし、空き時間は開区間で取れるように考えます。

user_id entry_from entry_to
1 2022/10/24 10:00:00 2022/10/24 12:00:00
2 2022/10/24 11:00:00 2022/10/24 12:00:00
3 2022/10/24 11:00:00 2022/10/24 13:00:00
4 2022/10/24 16:00:00 2022/10/24 17:00:00
5 2022/10/24 16:00:00 2022/10/24 18:00:00

この場合は下記のようなデータが取得できればオッケーです。

vacancy_from vacancy_to
2022/10/24 09:00:00 2022/10/24 09:59:59
2022/10/24 13:00:01 2022/10/24 15:59:59
2022/10/24 18:00:01 2022/10/24 20:00:00

TL;DR

  • 退室時刻+1秒で他の入室者が居ない時刻は空き開始時刻となる。
  • 入室時刻-1秒で他の入室者が居ない時刻は空き終了時刻となる。
ソース全文
DROP TABLE IF EXISTS room_access;
CREATE TABLE room_access (
    id SERIAL PRIMARY KEY,
    user_id INT,
    entry_from TIMESTAMP,
    entry_to TIMESTAMP
);

INSERT INTO room_access (user_id, entry_from, entry_to)
VALUES
(1, '2022-10-24 10:00', '2022-10-24 12:00'),
(2, '2022-10-24 11:00', '2022-10-24 12:00'),
(3, '2022-10-24 11:00', '2022-10-24 13:00'),
(4, '2022-10-24 16:00', '2022-10-24 17:00'),
(5, '2022-10-24 16:00', '2022-10-24 18:00');

DROP FUNCTION IF EXISTS vacancy();

CREATE FUNCTION vacancy()
RETURNS TABLE (vacancy_from TIMESTAMP, vacancy_to TIMESTAMP)
AS $$ 
BEGIN
CREATE TEMP TABLE IF NOT EXISTS vf AS 	
	SELECT ra.entry_to + CAST('+1 seconds' AS INTERVAL) AS vacancy_from
	FROM room_access AS ra
	WHERE NOT EXISTS (
		SELECT * FROM room_access AS s
		WHERE s.entry_from <= ra.entry_to 
		AND s.entry_to > ra.entry_to		
	);
	
INSERT INTO vf (vacancy_from) VALUES ('2022-10-24 9:00');
	
CREATE TEMP TABLE IF NOT EXISTS vt AS 
	SELECT ra.entry_from + CAST('-1 seconds' AS INTERVAL) AS vacancy_to
	FROM room_access AS ra
	WHERE NOT EXISTS (
		SELECT * FROM room_access AS s
		WHERE s.entry_from < ra.entry_from 
		AND s.entry_to >= ra.entry_from		
	);
	
INSERT INTO vt (vacancy_to) VALUES ('2022-10-24 20:00');

RETURN QUERY
SELECT vf.vacancy_from, MIN(vt.vacancy_to)
FROM vf
LEFT JOIN vt ON vf.vacancy_from < vt.vacancy_to
GROUP BY vf.vacancy_from;

DROP TABLE vf;
DROP TABLE vt;

END
$$ LANGUAGE plpgsql;

SELECT * FROM vacancy();

2022/10/28 追記
コメントで提案いただいた手法が素晴らしかったので紹介します。
ストアド不要という点でも、たぶん私の案の上位互換なのでこっち使っとけばいいと思います。
PostgreSQLで動作確認して一部修正しています。

SELECT
    s.entry_to + CAST('+1second' AS INTERVAL) AS vacancy_from,
    MIN(t.entry_from) + CAST('-1second' AS INTERVAL) AS vacancy_to

-- 退室時刻
FROM (
    SELECT CAST('2022/10/24 08:59:59' AS TIMESTAMP) AS entry_to
    UNION
    SELECT entry_to FROM room_access
) AS s

-- sより後の入室時刻
JOIN (
    SELECT CAST('2022/10/24 20:00:01' AS TIMESTAMP) AS entry_from
    UNION
    SELECT entry_from FROM room_access
) AS t
    ON s.entry_to < t.entry_from

-- sの時点で他の入室者がいないこと
WHERE NOT EXISTS (
    SELECT *
    FROM room_access AS d -- (duplicatedのd)
    WHERE d.entry_from <= s.entry_to AND s.entry_to < d.entry_to
)

GROUP BY s.entry_to
ORDER BY vacancy_to

解法

退室時刻+1秒でレコードがないものを探す

退室時刻+1秒はユーザーが退室した直後なので、空き時間である可能性が高いです。
同じ時刻に入室中の他のユーザーが居なければ、その時刻が空き開始時刻となります。
結果は空き開始時刻リストとして、一時テーブルに格納しておきます。

CREATE TEMP TABLE IF NOT EXISTS vf AS 	
	SELECT ra.entry_to + CAST('+1 seconds' AS INTERVAL) AS vacancy_from
	FROM room_access AS ra
	WHERE NOT EXISTS (
		SELECT * FROM room_access AS s
		WHERE s.entry_from <= ra.entry_to 
		AND s.entry_to > ra.entry_to		
	);

営業開始時刻を空き開始時刻リストに追加する

上記の方法だと、「営業開始後、ユーザーが入室するまでの空き時間」が取れません。
そのため、空き開始時刻リストに営業開始時刻も追加します。

INSERT INTO vf (vacancy_from) VALUES ('2022-10-24 9:00');

入室時刻-1秒でレコードがないものを探す

同様に、入室時刻-1秒はまだユーザーが入室してないので、空き時間である可能性が高いです。
同じ時刻に入室中の他のユーザーが居なければ、その時刻が空き終了時間となります。
結果は空き終了時刻候補として、一時テーブルに格納しておきます。

CREATE TEMP TABLE IF NOT EXISTS vt AS 
	SELECT ra.entry_from + CAST('-1 seconds' AS INTERVAL) AS vacancy_to
	FROM room_access AS ra
	WHERE NOT EXISTS (
		SELECT * FROM room_access AS s
		WHERE s.entry_from < ra.entry_from 
		AND s.entry_to >= ra.entry_from		
	);

営業開始時刻を空き終了時刻リストに追加する

営業終了時刻を空き終了時刻リストに追加します。
ここでの意味は「最終ユーザー退室から営業終了までの空き時間の終了時刻」となります。

INSERT INTO vt (vacancy_to) VALUES ('2022-10-24 20:00');

空き開始時刻と空き終了時刻を結合する

vacancy_from
2022/10/24 09:00:00
2022/10/24 13:00:01
2022/10/24 18:00:01
vacancy_to
2022/10/24 09:59:59
2022/10/24 15:59:59
2022/10/24 20:00:00

これですべてのパーツがそろったので、あとはこれを開始時刻・終了時刻の形式になるように結合します。
このとき、開始時刻に対して紐づく終了時刻は、開始時刻<終了時刻を満たす最小の値になります。

RETURN QUERY
SELECT vf.vacancy_from, MIN(vt.vacancy_to)
FROM vf
LEFT JOIN vt ON vf.vacancy_from < vt.vacancy_to
GROUP BY vf.vacancy_from;

最後に

当初はBIツールからストアド使えない問題などもあったので、「1分単位のタイムテーブル作ってJOINして繋がらないところを取得する」アプローチでやってました。死にました。

このケースは要件として割とありがちな気がするけど、存在しないデータを取得する関係上、ちょっとめんどいのでこうやって書いときます。

他にもやり方あるとは思うので、ベストプラクティスみたいなのがあれば教えていただけると嬉しいです。

GitHubで編集を提案

Discussion

興味深く読ませてもらいました

ベストプラクティスかは分かりませんが、以下のことが気になりました
1つ目: 一時テーブルを使うのではなく、FROMなしのSELECT文とUNIONを取るほうが楽だと思います

例えば退室時刻からなるリレーションに「9時」を加えたものは:

SELECT CAST('2022/10/24 09:00:00' AS DATETIME) AS entry_to
UNION
SELECT entry_to FROM room_access

のように書けると思います

また、「退室時刻+1s」の時点で誰も入室していない状態なら、対応する「空き時間」の終了時刻は単に「入室時刻-1s」の最小値を取ることで求まります
つまり入室時刻側の重複検査は取り除けると思います

(理由: 退室時刻以前に入室した人はすべて退室済みで、退室時刻+1s〜MIN(入室時刻-1s)の間に他の入室者はいないから)


サンプル (ただしMySQLで試したコードを手直ししたものなので、PostgreSQLでそのまま動かないかもしれません)

-- 準備
CREATE TABLE room_access(
    id INT PRIMARY KEY,
    entry_from DATETIME NOT NULL,
    entry_to DATETIME NOT NULL
);

INSERT INTO room_access(id, entry_from, entry_to) VALUES
    (1, '2022/10/24 10:00:00', '2022/10/24 12:00:00'),
    (2, '2022/10/24 11:00:00', '2022/10/24 12:00:00'),
    (3, '2022/10/24 11:00:00', '2022/10/24 13:00:00'),
    (4, '2022/10/24 16:00:00', '2022/10/24 17:00:00'),
    (5, '2022/10/24 16:00:00', '2022/10/24 18:00:00');

クエリ:

SELECT
    s.entry_to + CAST('+1second' AS INTERVAL) AS vacancy_from,
    MIN(t.entry_from) + CAST('-1second' AS INTERVAL) AS vacancy_to

-- 退室時刻
FROM (
    SELECT CAST('2022/10/24 09:00:00' AS DATETIME) AS entry_to
    UNION
    SELECT entry_to FROM room_access
) AS s

-- sより後の入室時刻
JOIN (
    SELECT CAST('2022/10/24 20:00:01' AS DATETIME) AS entry_from
    UNION
    SELECT entry_from FROM room_access
) AS t
    ON s.entry_to < t.entry_from

-- sの時点で他の入室者がいないこと
WHERE NOT EXISTS (
    SELECT *
    FROM room_access AS d -- (duplicatedのd)
    WHERE d.entry_from <= s.entry_to AND s.entry_to < d.entry_to
)

GROUP BY s.entry_to
HAVING vacancy_from < vacancy_to
ORDER BY vacancy_to

ありがとうございます。素晴らしいクエリに脱帽です。
検索して辿り着いた人に有益な記事となるよう、いただいたクエリを一部修正して記事内に記載させていただきました。

改めて考えると納得というか、試行錯誤の中で考えが固まってしまってたのを痛感しますね。
レビュー大事

ログインするとコメントできます