国内予選B

少しSQLの勉強をしてるので，練習に今年の国内予選BをSQLでといてみる．

まず，利用記録は下のようにloginテーブルに格納されているものとする

time(時間)	pid(pc番号)	sid(学生番号)	in(1:ログイン, 0:ログアウト)
775	1	1	1
780	4	2	1
790	2	1	1
800	2	1	0
810	3	1	1
820	1	1	0
825	3	1	0
860	1	1	1
870	4	2	0
880	1	1	0

なお，(time,pid)を主キーとする．問題は期間(t_s,t_e)に学生mがどれだけPCを利用していたかを問い合わせ(t_s,t_e,m)に関して答えろというものなので，まずログインとログアウトをペアにしたビューを作る．

create view loginout AS
select L1.time AS login , min(L2.time) AS  logout, L1.sid AS sid 
from (login as L1 , login as L2)
where l1.pid = l2.pid and l1.sid = l2.sid 
and l1.in = 1 and l2.in = 0 and l1.time < l2.time
group by l1.time , l1.pid

各loginに対して一番近いlogoutを割り当ててるだけ，上の表に対してviewは下のようになる

login	logout	sid
775	820	1
780	870	2
790	800	1
810	825	1
860	880	1

後は，このビューからt_s-t_eに入っている部分の合計を重複なく求めたいのだけど，今回はminuteという540から1260までの連番を入れた補助テーブルを使うことにする(連番の列名はiとする)，すると

select count(*) from minute
where t_s <= minute.i  and minute.i < t_e and 
exists(select * from loginout where sid=m and login <= minute.i and minute.i < logout)

で問い合わせに対する答えが出る．やってることはt_sからt_eまでの各iについてiが入っているような区間があるかどうか判定しているだけです．