10-174 全称命題と存在命題で論理積(partition版)

SQLパズル

personTable2
person  ID_State
------  --------
Carl           3
Carl           4
Joe            3
Mark           2  ←出力対象
Mark           3  ←出力対象
Mark           4  ←出力対象
Paul           1
Paul           2
Sam            2
Sam            3
Wendy          2  ←出力対象
Wendy          2  ←出力対象
Wendy          3  ←出力対象
Wendy          3  ←出力対象
Wendy          4  ←出力対象
John           1
John           2
John           2
John           3
Tom            1
Tom            2
Tom            3
Tom            4

personごとで、
ID_State=2の行が存在し、かつ、
ID_State=3の行が存在し、かつ、
ID_State=4の行が存在し、かつ、
全てのID_Stateが2,3,4のいずれか
であるpersonの行を出力する。

出力結果
person  ID_State
------  --------
Mark           2
Mark           3
Mark           4
Wendy          2
Wendy          2
Wendy          3
Wendy          3
Wendy          4

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table personTable2 as
select 'Carl' as person,3 as ID_State from dual
union all select 'Carl'  ,4 from dual
union all select 'Joe'   ,3 from dual
union all select 'Mark'  ,2 from dual
union all select 'Mark'  ,3 from dual
union all select 'Mark'  ,4 from dual
union all select 'Paul'  ,1 from dual
union all select 'Paul'  ,2 from dual
union all select 'Sam'   ,2 from dual
union all select 'Sam'   ,3 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,2 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,3 from dual
union all select 'Wendy' ,4 from dual
union all select 'John'  ,1 from dual
union all select 'John'  ,2 from dual
union all select 'John'  ,2 from dual
union all select 'John'  ,3 from dual
union all select 'Tom'   ,1 from dual
union all select 'Tom'   ,2 from dual
union all select 'Tom'   ,3 from dual
union all select 'Tom'   ,4 from dual;

SQL

--■■■ブール代数での演算結果に列別名(WillOut)をつける方法■■■
select person,ID_State
from (select person,ID_State,
        max(case when ID_STATE = 2 then 1 else 0 end) over(partition by person)
      * max(case when ID_STATE = 3 then 1 else 0 end) over(partition by person)
      * max(case when ID_STATE = 4 then 1 else 0 end) over(partition by person)
      * min(case when ID_STATE in(2,3,4) then 1 else 0 end) over(partition by person) as WillOut
      from personTable2)
where WillOut = 1;

--■■■列別名をつけてからブール代数で演算する方法■■■
select person,ID_State
from (select person,ID_State,
      max(case when ID_STATE = 2 then 1 else 0 end) over(partition by person) as "has2",
      max(case when ID_STATE = 3 then 1 else 0 end) over(partition by person) as "has3",
      max(case when ID_STATE = 4 then 1 else 0 end) over(partition by person) as "has4",
      min(case when ID_STATE in(2,3,4) then 1 else 0 end) over(partition by person) as "All2-3-4"
      from personTable2)
where "has2" * "has3" * "has4" * "All2-3-4" = 1;

解説

partitionを切って、もしくはgroup化しての
max(case when 条件P then 1 else 0 end) = 1 が∃X:P(X)に相当
min(case when 条件P then 1 else 0 end) = 1 が∀X:P(X)に相当
max(case when 条件P then 0 else 1 end) = 1 が∃X:¬P(X)に相当
min(case when 条件P then 0 else 1 end) = 1 が∀X:¬P(X)に相当
を使ってます。

---