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
create table Losses(
cust_nbr smallint NOT NULL primary key,
ValA smallint,
ValB smallint,
ValC smallint,
ValD smallint,
ValE smallint);
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 smallint NOT NULL,
criteria text NOT NULL,
crit_val smallint 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);
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 cross join PolicyCriteria b
group by a.cust_nbr,b.criteria_id) a
where (ANotExist+AIn)*(BNotExist+BIn)*(CNotExist+CIn)*(DNotExist+DIn)*(ENotExist+EIn) >= 1) a
where policies = maxPolicies;