9-42 存在有無のブール値の応用

SQLパズル

Lossesテーブル
cust_nbr  ValA  ValB  ValC  ValD  ValE
--------  ----  ----  ----  ----  ----
      99     5    10    15  null  null
      98    10    20    30    40    50

PolicyCriteriaテーブル
criteria_id  criteria  crit_val
-----------  --------  -------
        1     ValA          5
        1     ValA          9
        1     ValA         14
        1     ValB          4
        1     ValB         10
        1     ValB         20
        2     ValB         10
        2     ValB         19
        3     ValA          5
        3     ValB         10
        3     ValB         30
        3     ValC          3
        3     ValC         15
        4     ValA          5
        4     ValB         21
        4     ValB         22
        5     ValA         10
        5     ValB         20
        5     ValC         30
        5     ValD         40
        5     ValE         90
        6     ValA         10
        6     ValB         20

以下の仕様で、cust_nbrごとに、
最も多くの条件を満たすcriteria_idと満たした条件の数を出力する

■■■↓↓↓引用させていただいた文章↓↓↓■■■
In English, this means that:
Policy 1 has criteria A = (5, 9, 14), B = (4, 10, 20).
Policy 2 has criteria B = (10, 19).
Policy 3 has criteria A = 5, B = (10, 30), C = (3, 15).
Policy 4 has criteria A = 5, B = (21, 22).
The Losses data for customer 99 has A = 5, B = 10, C = 15.
Policy 1 has criteria A in (5, 9, 14) and B in (4, 10, 20).
Policy 2 has criteria B in (10, 19).
Policy 3 has criteria A = 5 and B in (10, 30) and C in (3, 15).
Policy 4 has criteria A = 5 and B in (21, 22).

Therefore, the customer could be offered policies 1, 2, and 3, but not 4.
Policy 3 should be ranked the highest,
because it matches the most criteria and returned as the answer.
Policy 1 should be second highest,
and Policy 2 should be last,
■■■↑↑↑引用させていただいた文章↑↑↑■■■

出力結果
CUST_NBR  CRITERIA_ID   POLICIES
--------  -----------  ---------
      98            6          2
      99            3          3

SQLパズル 第2版のパズル39 [保険損失] を参考にさせていただきました

データ作成スクリプト

create table Losses(
cust_nbr number(2) NOT NULL primary key,
ValA number(2),
ValB number(2),
ValC number(2),
ValD number(2),
ValE number(2));

insert into Losses values(99, 5,10,15,NULL,NULL);
insert into Losses values(98,10,20,30,  40,  50);

create table PolicyCriteria(
criteria_id number(1) NOT NULL,
criteria  varchar2(5) NOT NULL,
crit_val    number(2) NOT NULL,
primary key(criteria_id, criteria, crit_val));

insert into PolicyCriteria values(1,'ValA', 5);
insert into PolicyCriteria values(1,'ValA', 9);
insert into PolicyCriteria values(1,'ValA',14);
insert into PolicyCriteria values(1,'ValB', 4);
insert into PolicyCriteria values(1,'ValB',10);
insert into PolicyCriteria values(1,'ValB',20);
insert into PolicyCriteria values(2,'ValB',10);
insert into PolicyCriteria values(2,'ValB',19);
insert into PolicyCriteria values(3,'ValA', 5);
insert into PolicyCriteria values(3,'ValB',10);
insert into PolicyCriteria values(3,'ValB',30);
insert into PolicyCriteria values(3,'ValC', 3);
insert into PolicyCriteria values(3,'ValC',15);
insert into PolicyCriteria values(4,'ValA', 5);
insert into PolicyCriteria values(4,'ValB',21);
insert into PolicyCriteria values(4,'ValB',22);
insert into PolicyCriteria values(5,'ValA',10);
insert into PolicyCriteria values(5,'ValB',20);
insert into PolicyCriteria values(5,'ValC',30);
insert into PolicyCriteria values(5,'ValD',40);
insert into PolicyCriteria values(5,'ValE',90);
insert into PolicyCriteria values(6,'ValA',10);
insert into PolicyCriteria values(6,'ValB',20);
commit;

SQL

select cust_nbr,criteria_id,policies
from (select cust_nbr,criteria_id,
      AIn+BIn+CIn+DIn+EIn as policies,
      max(AIn+BIn+CIn+DIn+EIn) over(partition by cust_nbr) as maxPolicies
      from (select a.cust_nbr,b.criteria_id,
            min(case when b.criteria = 'ValA' then 0 else 1 end) as ANotExist,
            max(case when b.criteria = 'ValA' and a.ValA = b.crit_val then 1 else 0 end) as AIn,
            min(case when b.criteria = 'ValB' then 0 else 1 end) as BNotExist,
            max(case when b.criteria = 'ValB' and a.ValB = b.crit_val then 1 else 0 end) as BIn,
            min(case when b.criteria = 'ValC' then 0 else 1 end) as CNotExist,
            max(case when b.criteria = 'ValC' and a.ValC = b.crit_val then 1 else 0 end) as CIn,
            min(case when b.criteria = 'ValD' then 0 else 1 end) as DNotExist,
            max(case when b.criteria = 'ValD' and a.ValD = b.crit_val then 1 else 0 end) as DIn,
            min(case when b.criteria = 'ValE' then 0 else 1 end) as ENotExist,
            max(case when b.criteria = 'ValE' and a.ValE = b.crit_val then 1 else 0 end) as EIn
            from Losses a,PolicyCriteria b
            group by a.cust_nbr,b.criteria_id)
      where (ANotExist+AIn)*(BNotExist+BIn)*(CNotExist+CIn)*(DNotExist+DIn)*(ENotExist+EIn) >= 1)
where policies = maxPolicies;

解説

case式とmax関数、min関数を組み合わせて、
存在有無のブール値を求めて、

足し算で論理和を求めたり、掛け算で論理積を求めたり、
数値として加算したりしています。

■■■■■■■■■■■■■■■■■■■■■■■■■■■
max(case when 条件A then 1 else 0 end)は、
集合に、条件Aを満たす要素が存在すれば1、
集合に、条件Aを満たす要素が存在しなければ0

min(case when 条件A then 0 else 1 end)は、
集合に、条件Aを満たす要素が存在しなければ1、
集合に、条件Aを満たす要素が存在すれば0

といった考え方を使ってます。

---

CodeZine：分析関数の衝撃（完結編）

---