10-124 ブール代数の分配法則

SQLパズル

以下のSQLのcase式のwhen句を
同値変形を使って簡略化する

select ColA,ColB,ColC,
case when ColA = 5
 and not(ColB = 50 or ColC < 3000)
  or ColC < 3000
 and ColB != 50
then 1 else 0 end as "条件式"
from (select 5 as ColA,50 as ColB,2000 as ColC from dual
union select 5,50,4000 from dual
union select 5,99,2000 from dual
union select 5,99,4000 from dual
union select 9,50,2000 from dual
union select 9,50,4000 from dual
union select 9,99,2000 from dual
union select 9,99,4000 from dual);

SQL

select ColA,ColB,ColC,
case when ColA = 5
 and not(ColB = 50 or ColC < 3000)
  or ColC < 3000
 and ColB != 50
then 1 else 0 end as "条件式1",
case when ColB != 50 and (ColA = 5 or ColC < 3000)
then 1 else 0 end as "条件式2"
from (select 5 as ColA,50 as ColB,2000 as ColC from dual
union select 5,50,4000 from dual
union select 5,99,2000 from dual
union select 5,99,4000 from dual
union select 9,50,2000 from dual
union select 9,50,4000 from dual
union select 9,99,2000 from dual
union select 9,99,4000 from dual);

解説

条件式 ColA = 5    を A
条件式 ColB = 50   を B
条件式 ColC < 3000 を C
とおきます

すると
ColA = 5
 and not(ColB = 50 or ColC < 3000)
  or ColC < 3000
 and ColB != 50
は
  _____     _
A*(B+C) + C*B
とおけます

ブール代数で同値変形して
  _____     _
A*(B+C) + C*B
  _ _ _
A*B*C+B*C  ドモルガンの法則
_    _
B*(A*C+C)  分配法則
_          _
B*(C+A)*(C+C)  分配法則
_
B*(C+A) 補元法則
_
B*(A+C) 交換法則

よって
ColB != 50 and (ColA = 5 or ColC < 3000)

数の計算の分配法則にない、
ブール代数の分配法則を使ってます
A+B*C = (A+B)*(A+C)

---