あるカラムの値を、別のカラムの値によって、結果を表示する欄を変えたい。

Question

MySQL 8.0.25を使用

１つのカラムが、他のカラムによって意味が決定するといったテーブルがあります。
ここでの区分は入退場を表していて、１が入場、２が退場を意味します。
時間のカラムは区分とセットで意味が決定します。

useStationテーブル

ユーザID	日付	駅	時間	区分
A001	9/21	AAA	11:28	1
A001	9/21	BBB	12:20	2
A001	9/22	CCC	23:26	1
A001	9/23	DDD	00:09	2

これをSQLで、ユーザ毎に以下のようなデータになるよう検索をしています。

select distinct
    userID,date,entrance.station,entrance.time,exit.station,exit.time
from (
    select userID,date
    from useStation
    where userID = "A001"
) as mainKey
left join (
    select userID,date,station,time
    from useStation
    where class = '1' 
) as entrance
on 
    Mainkey.userId = entrance.userId and
    mainKey.date = entrance.date
left join (
    select userID,date,station,time
    from useStation
    where class = '2' 
) as exit
on 
    mainkey.userId = exit.userId 
    and mainkey.date = exit.date 

|ユーザID|日付|入場駅|入場時間|退場駅|退場時間|
|:--:|:--:|:--:|:--:|:--:|
|A001|9/21|AAA|11:28|BBB|12:20|
|A001|9/22|CCC|23:26||
|A001|9/23|||DDD|00:09|

区分が１の時間と、区分が２の時間をLEFTJOINで結合しています。
出来上がった後にふと、このSQLは冗長で、CASEやらIFやらを使えば結合を使わなくてもできるのではないかと思いました。
しかし、どうしてもTHENで結果の欄を制御する方法が分からず質問いたしました。

自分が作成したSQLでも動作自体は問題ないとは思うのですが、
分かる方、もっといい方法を知っている方がいましたら、後学の為にもよろしくお願いいたします。

---

追記
同じユーザが同じ日に複数回の入退場を行ったときについて、
多い方のデータ分行が増えていく様な想定です。
理想は以下のようになる事でした。
|ユーザID|日付|入場駅|入場時間|退場駅|退場時間|
|:--:|:--:|:--:|:--:|:--:|
|A001|9/21|AAA|11:28|BBB|12:20|
|A001|9/21|BBB|23:26||
|A001|9/22|AAA|08:53|AAA|00:18|
|A001|9/22|||BBB|09:45|

が、自分の実装では値を表示する時のロジックで処理する前提として、
以下のようになっています。

|ユーザID|日付|入場駅|入場時間|退場駅|退場時間|
|:--:|:--:|:--:|:--:|:--:|
|A001|9/21|AAA|11:28|BBB|12:20|
|A001|9/21|BBB|23:26|BBB|12:20|
|A001|9/22|AAA|08:53|AAA|00:18|
|A001|9/22|AAA|08:53|BBB|09:45|

Comments

[takanaweb5]

同じ人が同じ日に複数回入退場することは考慮しなくてよろしいのでしょうか？

[sazi]

同じ日に入退場を繰り返した時はどのように表現するのですか？
また、その場合入退場のペアは時間の前後で判断しないと駄目だと思えます。

[sazi]

> 自分の実装では値を表示する時のロジックで処理する前提として、以下のようになっています。

入退の時間の大小で判断しているとしたら、同じ日に複数の入退場がある場合の判断が付かないのでは？

Answer 1

MySQL 8.0.25 なら window関数が使えます。

window関数は比較的新しい構文なので、SQLに詳しい人でも食わず嫌いで、使いこなす人が少ないように思います。
ただし、window関数を使えば従来の方法では、自己結合などを駆使して可読性もパフォーマンスもあまり良くないSQLを簡潔にかつ、高パフォーマンスで記述することが可能です。
ぜひ、この機会にwindow関数を勉強されることをおすすめします。

それではwindows関数を使ったSQLを紹介します。

前提として、退場記録については、必ず対になる入場記録があることととします。
ただし、入場記録があっても、退場記録がないケースはあることとします。

-- スキーマ定義
create table tbl(uid varchar(10),d date,station varchar(20),t time,status tinyint);
insert into tbl values
('A001','2021-09-21','AAA','11:28',1),
('A001','2021-09-21','BBB','12:20',2),
('A001','2021-09-21','BBB','13:00',1), -- 同一日に２回目の入場
('A001','2021-09-21','AAA','13:30',2),
('A001','2021-09-22','CCC','23:26',1),
('A001','2021-09-23','DDD','00:09',2), -- 入場から日をまたいで退場
('A001','2021-09-23','DDD','01:00',1), -- 退場記録がない
-- ↓別のユーザ
('A002','2021-09-21','AAA','01:00',1), 
('A002','2021-09-21','CCC','01:30',2);

select
  uid
, status 
, d 
, t 
, station 
-- window関数のlead()関数で、uidごとに時系列でソートした、次のレコードの値を取得します
, lead(status)  over(partition by uid order by d,t) as lead_status
, lead(d)       over(partition by uid order by d,t) as lead_d
, lead(t)       over(partition by uid order by d,t) as lead_t
, lead(station) over(partition by uid order by d,t) as lead_station
from tbl

結果

uid	status	d	t	station	lead_status	lead_d	lead_t	lead_station
A001	1	2021/09/21	11:28:00	AAA	2	2021/09/21	12:20:00	BBB
A001	2	2021/09/21	12:20:00	BBB	1	2021/09/21	13:00:00	BBB
A001	1	2021/09/21	13:00:00	BBB	2	2021/09/21	13:30:00	AAA
A001	2	2021/09/21	13:30:00	AAA	1	2021/09/22	23:26:00	CCC
A001	1	2021/09/22	23:26:00	CCC	2	2021/09/23	00:09:00	DDD
A001	2	2021/09/23	00:09:00	DDD	1	2021/09/23	01:00:00	DDD
A001	1	2021/09/23	01:00:00	DDD	NULL	NULL	NULL	NULL
A002	1	2021/09/21	01:00:00	AAA	2	2021/09/21	01:30:00	CCC
A002	2	2021/09/21	01:30:00	CCC	NULL	NULL	NULL	NULL

上記の結果から、必要な値だけを表示するようにします

with sub as (
select
  uid
, status 
, d 
, t 
, station 
, lead(status)  over(partition by uid order by d,t) as lead_status
, lead(d)       over(partition by uid order by d,t) as lead_d
, lead(t)       over(partition by uid order by d,t) as lead_t
, lead(station) over(partition by uid order by d,t) as lead_station
from tbl
)

select 
  uid
, d as 入場日
, t as 入場時間
, station as 入場駅
, case when lead_status = 2 then lead_d       end as 出場日
, case when lead_status = 2 then lead_t       end as 出場時間
, case when lead_status = 2 then lead_station end as 出場駅
from sub
where status = 1;

結果

uid	入場日	入場時間	入場駅	出場日	出場時間	出場駅
A001	2021/09/21	11:28:00	AAA	2021/09/21	12:20:00	BBB
A001	2021/09/21	13:00:00	BBB	2021/09/21	13:30:00	AAA
A001	2021/09/22	23:26:00	CCC	2021/09/23	00:09:00	DDD
A001	2021/09/23	01:00:00	DDD	NULL	NULL	NULL
A002	2021/09/21	01:00:00	AAA	2021/09/21	01:30:00	CCC

なお以下のように、window部分を共通化して記述することも可能です。

with sub as (
select
  uid
, status 
, d
, t
, station
, lead(status)  over w as lead_status
, lead(d)       over w as lead_d
, lead(t)       over w as lead_t
, lead(station) over w as lead_station
from tbl
window w as (partition by uid order by d,t)
)

select 
  uid
, d as 入場日
, t as 入場時間
, station as 入場駅
, case when lead_status = 2 then lead_d end as 出場日
, case when lead_status = 2 then lead_t end as 出場時間
, case when lead_status = 2 then lead_station end as 出場駅
from sub
where status = 1;

Comments

[takanaweb5]

日をまたいで入退場したケースは質問文では、日を分けて２レコードに編集していますが、この回答では、１レコードにまとめて出力しています。
本来は、１レコードにまとめたかったのではと勝手に想像してそうしましたが、どちらがお望みなのでしょうか？

[Koto_2131]

ありがとうございます！
自分の勉強不足故、Window関数なるものを知りませんでした。
今急いで詰め込んだ感想なのですが、サブクエリを使わなくてもSUMの結果をカラムに使えるみたいな感じなのでしょうか。これを使いこなせられたら１つ見える世界が変わりそうな気がします。Windows関数達はこれからしっかりお勉強していきたいと思います。

日をまたぐ入退場については、自分の技術的な理由で１日１レコードにしていましたが、可能ならペアになる入退場は１レコードにまとめる事が望ましかったです。

[takanaweb5]

>サブクエリを使わなくてもSUMの結果をカラムに使えるみたいな感じなのでしょうか。これを使いこなせられたら１つ見える世界が変わりそうな気がします。

私の経験からは応用次第で、自己テーブルを使用する相関サブクエリや自己結合で実現しているSQLはほとんど何でも代替出来てしまいます。

>日をまたぐ入退場については、自分の技術的な理由で１日１レコードにしていましたが、可能ならペアになる入退場は１レコードにまとめる事が望ましかったです。

想像通りで良かったです。

Answer 2

create table tbl(uid varchar(10),d date,station varchar(20),t time,status tinyint);
insert into tbl values
('A001','2021-09-21','AAA','11:28',1),
('A001','2021-09-21','BBB','12:20',2),
('A001','2021-09-22','CCC','23:26',1),
('A001','2021-09-23','DDD','00:09',2);

select uid
,d 
,max(case status when 1 then station end) stat_1
,max(case status when 1 then t end) time_1
,max(case status when 2 then station end) stat_2
,max(case status when 2 then t end) time_2
from tbl
group by uid,d;