7-18 時間内の特定レコードの存在チェック

SQLパズル

行動テーブル
ログインID   行動          時間
----------   ----------   ----------------
         1   ログイン     2005/10/20 10:00
         1   商品購入     2005/10/20 10:10
         1   ログアウト   2005/10/20 10:30
         2   ログイン     2005/10/20 10:00
         2   商品購入     2005/10/20 10:20
         2   ログアウト   2005/10/20 10:30
         3   ログイン     2005/10/20 10:00
         3   ログアウト   2005/10/20 10:10
         4   ログイン     2005/10/20 10:00
         5   ログイン     2005/10/20 10:00
         5   商品購入     2005/10/20 10:10
         5   ログアウト   2005/10/20 10:40

行動テーブルから、
ログインしてから10分以内に、商品を購入し、
ログインしてから30分以内に、ログアウトした
ログインIDと、ログイン時間と、商品購入時間と、ログアウト時間を出力する。

行動テーブルのプライマリキーは、ログインIDと行動とする。

出力結果
ログインID   行動          時間
----------   ----------   ----------------
         1   ログイン     2005/10/20 10:00
         1   商品購入     2005/10/20 10:10
         1   ログアウト   2005/10/20 10:30

データ作成スクリプト

create table 行動テーブル(
ログインID number(1),
行動       varchar2(10),
時間       date,
primary key(ログインID,行動));

insert into 行動テーブル values(1,'ログイン'  ,to_date('2005/10/20 10:00','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(1,'商品購入'  ,to_date('2005/10/20 10:10','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(1,'ログアウト',to_date('2005/10/20 10:30','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(2,'ログイン'  ,to_date('2005/10/20 10:00','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(2,'商品購入'  ,to_date('2005/10/20 10:20','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(2,'ログアウト',to_date('2005/10/20 10:30','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(3,'ログイン'  ,to_date('2005/10/20 10:00','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(3,'ログアウト',to_date('2005/10/20 10:10','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(4,'ログイン'  ,to_date('2005/10/20 10:00','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(5,'ログイン'  ,to_date('2005/10/20 10:00','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(5,'商品購入'  ,to_date('2005/10/20 10:10','YYYY/MM/DD HH24:MI'));
insert into 行動テーブル values(5,'ログアウト',to_date('2005/10/20 10:40','YYYY/MM/DD HH24:MI'));
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select ログインID,行動,to_char(時間,'YYYY/MM/DD HH24:MI') as 時間
from 行動テーブル a
where exists(select 1 from 行動テーブル b,行動テーブル c,行動テーブル d
              where b.ログインID=a.ログインID
                and c.ログインID=a.ログインID
                and d.ログインID=a.ログインID
                and b.行動='ログイン'
                and c.行動='商品購入'
                and d.行動='ログアウト'
                and c.時間-b.時間 <= 10/24/60
                and d.時間-b.時間 <= 30/24/60)
order by ログインID,時間;

--■■■分析関数を使う方法■■■
select ログインID,行動,to_char(時間,'YYYY/MM/DD HH24:MI') as 時間
from (select ログインID,行動,時間,
      max(decode(行動,'ログイン'  ,時間)) over(partition by ログインID) as ログイン時間,
      max(decode(行動,'商品購入'  ,時間)) over(partition by ログインID) as 商品購入時間,
      max(decode(行動,'ログアウト',時間)) over(partition by ログインID) as ログアウト時間
      from 行動テーブル)
where 商品購入時間   - ログイン時間 <= 10/24/60
  and ログアウト時間 - ログイン時間 <= 30/24/60
order by ログインID,時間;

解説

ログインしてから10分以内に商品を購入したかと、
ログインしてから30分以内に商品をログアウトしたかを、
where句でチェックしてます。

---