10-258 分析関数で他行の合計を取得

SQLパズル

regxテーブル
 ID  condition  pattern
---  ---------  -----------------------------------
100  SPE00001   Error where item =%
200  SPE00001   Discard due to code =%on customer
300  SPE00001   Discard due to code =%on vendor =%
400  SPE00001   %
500  SPE00002   Error where item =%
600  SPE00002   Discard due to reason =%on customer
700  SPE00002   Discard due to code =%on invoice =%
800  SPE00002   %
900  SPE00003   %

tlogテーブル
condition  msg
---------  -------------------------------------------
SPE00001   Error where item = 1234
SPE00001   Error where item = 2345
SPE00001   Discard due to code = 123 on customer
SPE00001   Discard due to code = 234 on customer
SPE00001   Discard due to code = 345 on customer
SPE00001   Discard due to code = 123 on vendor = 456
SPE00001   Discard due to code = 234 on vendor = 567
SPE00001   Discard due to code = 345 on vendor = 678
SPE00001   Discard due to code = 456 on vendor = 789
SPE00001   Other kind of message
SPE00001   Another kind of message
SPE00001   Yet, another
SPE00002   Error where item = 1234
SPE00002   Error where item = 2345
SPE00002   Discard due to reason = 123 on customer
SPE00002   Discard due to reason = 234 on customer
SPE00002   Discard due to reason = 345 on customer
SPE00002   Discard due to code = 123 on invoice = 456
SPE00002   Discard due to code = 234 on invoice = 567
SPE00002   Discard due to code = 345 on invoice = 678
SPE00002   Discard due to code = 456 on invoice = 789
SPE00002   Discard due to code = 456 on invoice = 789
SPE00002   Discard due to code = 456 on invoice = 789
SPE00002   Other kind of message
SPE00002   Another kind of message
SPE00002   Yet, another
SPE00002   Yet, another
SPE00002   Yet, another
SPE00003   Error 1234
SPE00003   Error 2345
SPE00003   Reason = 123 on customer
SPE00003   Reason = 234 on customer
SPE00003   Other kind of message
SPE00003   Another kind of message

conditionが一致して、
msgがpatternのパターン指定に一致する行の数を求める。

ただし、patternが%の場合は、
同じconditionの行の中で、
一致することができなかった行の数を求める。

出力結果
 ID  CNT
---  ---
100    4
200    3
300    4
400   10
500    4
600    3
700    6
800   10
900   10

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

データ作成スクリプト

create table regx(
ID        number(3) primary key,
condition char(8),
pattern   varchar2(55));

insert into regx values(100, 'SPE00001', 'Error where item =%');
insert into regx values(200, 'SPE00001', 'Discard due to code =%on customer');
insert into regx values(300, 'SPE00001', 'Discard due to code =%on vendor =%');
insert into regx values(400, 'SPE00001', '%');
insert into regx values(500, 'SPE00002', 'Error where item =%');
insert into regx values(600, 'SPE00002', 'Discard due to reason =%on customer');
insert into regx values(700, 'SPE00002', 'Discard due to code =%on invoice =%');
insert into regx values(800, 'SPE00002', '%');
insert into regx values(900, 'SPE00003', '%');

create table tlog(
condition char(8),
msg       varchar2(55));

insert into tlog values('SPE00001', 'Error where item = 1234');
insert into tlog values('SPE00001', 'Error where item = 2345');
insert into tlog values('SPE00001', 'Discard due to code = 123 on customer');
insert into tlog values('SPE00001', 'Discard due to code = 234 on customer');
insert into tlog values('SPE00001', 'Discard due to code = 345 on customer');
insert into tlog values('SPE00001', 'Discard due to code = 123 on vendor = 456');
insert into tlog values('SPE00001', 'Discard due to code = 234 on vendor = 567');
insert into tlog values('SPE00001', 'Discard due to code = 345 on vendor = 678');
insert into tlog values('SPE00001', 'Discard due to code = 456 on vendor = 789');
insert into tlog values('SPE00001', 'Other kind of message');
insert into tlog values('SPE00001', 'Another kind of message');
insert into tlog values('SPE00001', 'Yet, another');
insert into tlog values('SPE00002', 'Error where item = 1234');
insert into tlog values('SPE00002', 'Error where item = 2345');
insert into tlog values('SPE00002', 'Discard due to reason = 123 on customer');
insert into tlog values('SPE00002', 'Discard due to reason = 234 on customer');
insert into tlog values('SPE00002', 'Discard due to reason = 345 on customer');
insert into tlog values('SPE00002', 'Discard due to code = 123 on invoice = 456');
insert into tlog values('SPE00002', 'Discard due to code = 234 on invoice = 567');
insert into tlog values('SPE00002', 'Discard due to code = 345 on invoice = 678');
insert into tlog values('SPE00002', 'Discard due to code = 456 on invoice = 789');
insert into tlog values('SPE00002', 'Discard due to code = 456 on invoice = 789');
insert into tlog values('SPE00002', 'Discard due to code = 456 on invoice = 789');
insert into tlog values('SPE00002', 'Other kind of message');
insert into tlog values('SPE00002', 'Another kind of message');
insert into tlog values('SPE00002', 'Yet, another');
insert into tlog values('SPE00002', 'Yet, another');
insert into tlog values('SPE00002', 'Yet, another');
insert into tlog values('SPE00003', 'Error 1234');
insert into tlog values('SPE00003', 'Error 2345');
insert into tlog values('SPE00003', 'Reason = 123 on customer');
insert into tlog values('SPE00003', 'Reason = 234 on customer');
insert into tlog values('SPE00003', 'Other kind of message');
insert into tlog values('SPE00003', 'Another kind of message');
commit;

SQL

select a.id,
case a.pattern
when '%' then count(*) - sum(decode(a.pattern,'%',0,count(*))) over()
         else count(*) end as cnt
  from regx a,tlog b
 where b.msg Like a.pattern
group by a.id,a.pattern
order by a.id;

解説

総数 = マッチした数 + マッチしなかった数
移項して、
マッチしなかった数 = 総数 - マッチした数

を使っています。

---