特定の期間に人物とクラスが紐づいていないレコードの抽出をしたい

Question

実現したいこと

• 2022-04-01~2023-03-31の期間にクラスに属していない人物のレコードを抽出する

前提

学校の生徒、クラスを管理しようとしています。
学年とは別なので、同じクラスに複数回所属することもあります。

userテーブルで人の情報を管理、
classテーブルでクラス自体を管理、
class_userテーブルで人とクラスの紐づけを管理しています。

userテーブル
id, (userテーブルの一意なid)
number, (番号)
name, (氏名)
valid_start, (有効な期間の開始日)
valid_end (有効な期間の終了日)

classテーブル
id, (classテーブルの一意なid)
name, (クラス名)
valid_start, (有効な期間の開始日)
valid_end (有効な期間の終了日)

class_userテーブル
id, (class_userテーブルの一意なid)
user_id, (userテーブルのid)
class_id, (classテーブルのid)
valid_start, (有効な期間の開始日)
valid_end (有効な期間の終了日)

データ

userテーブル

id	number	name	valid_start	valid_end
1	0001	佐藤 太郎	2020-04-01	9999-12-31
2	0002	伊藤 花子	2020-04-01	9999-12-31
3	0003	高橋 次郎	2021-04-01	9999-12-31
4	0004	田中 幸子	2021-04-01	2022-03-31

classテーブル

id	name	valid_start	valid_end
1	手芸	2020-04-01	9999-12-31
2	園芸	2020-04-01	9999-12-31
3	折紙	2022-04-01	9999-12-31
4	編物	2020-04-01	9999-12-31

class_userテーブル

id	user_id	class_id	valid_start	valid_end
1	1	4	2020-04-01	9999-12-31
2	2	1	2020-04-01	2021-03-31
3	3	1	2021-04-01	2021-06-30
4	4	1	2021-04-01	2022-03-31
5	2	2	2021-04-01	2022-12-31
6	2	2	2023-04-01	2023-12-31
7	3	3	2023-04-01	9999-12-31

発生している問題・エラーメッセージ

2022-04-01~2023-03-31の期間にクラスに所属していないユーザがいるようでその人物を抽出したいです。
sqlを作成していましたが、sqlの知識が浅いためうまく抽出ができず…
↑のデータで言えば
「2023-01-01～2023-03-31」にクラスに属していない伊藤 花子さんと
「2021-07-07～2023-03-31」にクラスに属していない高橋 次郎さんを抽出対象にしたいです。
アドバイスをよろしくお願いいたします。

作成したsql

select
    users.id,
    users.number,
    users.name,
    class.id,
    class.name
from
    users
    left join
        class_user
    on  users.id = class_user.user_id
    left join
        class
    on  class.id = class_user.class_id
where
    class_user.valid_start not between '2022-04-01' and '2023-03-31'
;

上記のsqlを作成して実行しましたが、
「2020-04-01」からずっと属している佐藤 太郎さん等も抽出対象になってしまい、どうしたらうまく抽出できるのか考えあぐねているところです…
考え方等やsql文の記載についてアドバイスいただけますと幸いです。よろしくお願いいたします。

追記

mysqlのバージョンは5.7.35です。
mysqlのみで完結できたらと思っていましたが、Exce、phpl等を使って抽出できるのであればそのアドバイスもいただけますと幸いです。

Answer 1

期間内にクラスに所属したことある人のリストを作って not in するのはダメでしょうか？

SELECT
    users.id,
    users.number,
    users.name,
    class.id,
    class.name
FROM
    users
LEFT JOIN
    class_user ON users.id = class_user.user_id
LEFT JOIN
    class ON class.id = class_user.class_id
WHERE
    users.id NOT IN (
        SELECT DISTINCT user_id
        FROM class_user
        WHERE valid_start <= '2023-03-31' AND valid_end >= '2022-04-01'
    )
    OR class_user.user_id IS NULL;

※SQLは動作検証していません
※各レコードvalid_start < valid_end は保証されている前提です

Comments

[taiyo-2017]

回答ありがとうございます！
＞※各レコードvalid_start < valid_end は保証されている前提
これは保証されている前提で大丈夫です。

SQLは問題なく動作しました！ですが、クラスに1度も属していないユーザがいる（class_userテーブルにレコードを持たないユーザがいる）ため、そのユーザも対象となってしまっているようでした。
NOT INの使い方が曖昧なので調べてみますが、なんだかもう少しで抽出できそうな気がしてきました！
少し内容を変えて挑戦してみたいと思います。

Answer 2

• 2022-04-01~2023-03-31の期間に、1日でも有効だったユーザを対象とする
• 2022-04-01~2023-03-31の期間に、クラスに所属しなかった日が1日でもあるユーザを抽出したい

実現したいことは上記であっていますか? 無効なクラスに所属したりなど、不適切なデータは存在しないとすると、次のようなSQLで実現できるかと思います。

WITH RECURSIVE CAL (dt) AS (
    SELECT STR_TO_DATE('2022-04-01', '%Y-%m-%d')
  UNION ALL
    SELECT ADDDATE(dt, 1)
    FROM CAL
    WHERE dt < '2023-03-31'
)
SELECT *
FROM user U
WHERE '2022-04-01' <= U.valid_end AND U.valid_start <= '2023-03-31'
AND (
  SELECT COUNT(*)
  FROM CAL C
  WHERE EXISTS (
    SELECT *
    FROM class_user CU
    WHERE CU.user_id = U.id
    AND CU.valid_start <= C.dt AND C.dt <= CU.valid_end
  )
) < DATEDIFF('2023-03-31','2022-04-01') + 1;

Comments

[taiyo-2017]

記載ありがとうございます！
実現したい内容は記載の通りで間違いないです。

記載ができていなかったのですがmysqlのバージョンが5.7.35だったためWITH句が使えないようでした。
カレンダーを作成していると認識しましたので、先にコメントいただいたyambejp様のカレンダーを流用（call add_calendar('2022-04-01','2023-03-31')を実行）して検証させていただきました。
実行したところ、レコードが全く存在しないユーザは取得できたのですが所属が歯抜け状態になっているユーザは取得することができませんでした…
WITHでカレンダーを作成していないことが原因でしょうか？

[neko_the_shadow]

MySQL5にて検証しました。「calendarテーブルに2022-04-01~2023-03-31の日付のみが投入されている」という前提のもと、以下のSQLを実行したところ、伊藤 花子と高橋 次郎が結果として返されることが確認できました。

SELECT *
FROM user U
WHERE '2022-04-01' <= U.valid_end AND U.valid_start <= '2023-03-31'
AND (
SELECT COUNT(*)
FROM calendar C
WHERE EXISTS (
SELECT *
FROM class_user CU
WHERE CU.user_id = U.id
AND CU.valid_start <= C.d AND C.d <= CU.valid_end
)
) < DATEDIFF('2023-03-31','2022-04-01') + 1;

[taiyo-2017]

再度コメントありがとうございます！
改めてカレンダーをtruncateしてデータを入れ直して実行するとそれらしいデータを取得できました！
ですが、一部思うように取得できていないようです。
ユーザの有効期限が2020-04-01～9999-12-31となっており、
クラスに属している期間が
2021/4/1 2022/3/31
2023/4/1 2023/5/31
2023/6/1 9999/12/31
となっているユーザが取得できませんでした…。
2022-04-01~2023-03-31の1年間ずっとクラスに属していないユーザが取得できていないのかもしれません…。
sqlを修正してみようかと思いましたが、知識不足のため
「AND CU.valid_start <= C.d AND C.d <= CU.valid_end」
「) < DATEDIFF('2023-03-31','2022-04-01') + 1」
の箇所の意味合いがよくわからず…ご教示いただけると幸いです。

[neko_the_shadow]

MySQL5にて検証しました。以下のようなテストデータを投入し、上記SQLを実行したところ、id=5の高村良子のデータは取得できました。何か別の問題があるかもしれません…

INSERT INTO user(id,number,name,valid_start,valid_end) VALUES (5,'005','高村 良子','2020-04-01', '9999-12-31');
INSERT INTO class_user(id,user_id,class_id,valid_start,valid_end) VALUES ( 8,5,1, '2021-04-01', '2022-03-31');
INSERT INTO class_user(id,user_id,class_id,valid_start,valid_end) VALUES ( 9,5,1, '2023-04-01', '2023-05-31');
INSERT INTO class_user(id,user_id,class_id,valid_start,valid_end) VALUES (10,5,1, '2023-06-01', '9999-12-31');

[taiyo-2017]

再度回答ありがとうございます！
改めてclass_userのデータを確認したところ、データの誤りがあったようでした。
記載させていただいたユーザに'2022-04-01'～'2023-03-31'の期間のレコードがある状態だったようです。大変失礼いたしました。
件数が多いため全てのデータの確認はできていませんが、いくつか確認ができ、欲しい情報が抽出されていることを確認いたしました。
何度も確認、記載していただきありがとうございました！

Answer 3

今回の命題に関係なさそうなクラスは省略

create table user(
uid int primary key,
name varchar(20),
valid_start date,
valid_end date);

insert into user values
(1,'佐藤 太郎','2020-04-01','9999-12-31'),
(2,'伊藤 花子','2020-04-01','9999-12-31'),
(3,'高橋 次郎','2021-04-01','9999-12-31'),
(4,'田中 幸子','2021-04-01','2022-03-31');

create table class_user(
id int primary key,
uid int,
cid int,
valid_start date,
valid_end date);

insert into class_user values
(1,1,4,'2020-04-01','9999-12-31'),
(2,2,1,'2020-04-01','2021-03-31'),
(3,3,1,'2021-04-01','2021-06-30'),
(4,4,1,'2021-04-01','2022-03-31'),
(5,2,2,'2021-04-01','2022-12-31'),
(6,2,2,'2023-04-01','2023-12-31'),
(7,3,3,'2023-04-01','9999-12-31');

ここでカレンダーテーブルを作っておきます。
プロシージャを使ってデータ投入しますがカレンダーは余裕を持って日付設定をしておきます

create table calendar(d date primary key);

drop procedure if exists add_calendar;
delimiter //
create procedure add_calendar(IN from_date date,to_date date)
begin 
set @d:=from_date;
set @count:=0;
while @d<=to_date do
if @count=0 then
set @sql:='insert ignore into calendar values';
else
set @sql:=concat(@sql,",");
end if;
set @sql:=concat(@sql,"('",@d,"')");
set @count:=@count +1;
if @count>=1000 or @d=to_date then
set @count:=0;
prepare stmt from @sql;
execute stmt;
end if;
set @d:=@d + interval 1 day;
end while;
end
//
delimiter ;

データ投入

call add_calendar('2018-01-01','2025-12-31')

検索

select distinct t1.uid,max(t1.name) as name from user as t1
inner join (
select * from calendar
where d between '2020-04-01' and '2023-03-31'
) as t2 on 1
left join class_user as t3
on t1.uid=t3.uid 
and t2.d between t3.valid_start and t3.valid_end
or not t2.d between t1.valid_start and t1.valid_end
group by t1.uid ,t2.d
having count(id)=0

Comments

[yambejp]

とりあえずアップしましたが、検証が必要

[taiyo-2017]

記載ありがとうございます！
プロシージャでカレンダーを作成することが全く頭になかったのでとても勉強になります。
ユーザやクラスの件数が多かったため少し内容を変更して抽出期間を絞って検索してみましたが実行時間が長くタイムアウトしてしまいました…
検索対象を少なくしたいのですが、
カレンダーのselect時の「where d between '2020-04-01' and '2023-03-31'」を「where d between '2022-04-01' and '2022-04-01'」にすることで
『「'2022-04-01'」にクラスに属していない人』を抽出できるのでしょうか？
sqlの読み取りが拙いため教えていただけますと幸いです。

[yambejp]

タイムアウトするようじゃ実用的ではないですね・・・失礼しました
考え方を変えて、適用外期間をclass_userテーブルに登録してしまったほうが早いかもしれません
ちょっと宿題