3-41 regr_count関数

SQLパズル

引数が2つともnullでない行数をカウントするマイナーな集合関数
regr_count関数の紹介です。
count関数の引数が2つ版と覚えるといいかもしれない・・・

データ作成スクリプト

create table regr_cT(SK,ColA,ColB) as
select 1,   1,   2 from dual union
select 2,   1,   2 from dual union
select 3,   1,null from dual union
select 4,null,   2 from dual union
select 5,null,null from dual union
select 6,   1,   2 from dual union
select 7,null,null from dual union
select 8,   1,   2 from dual union
select 9,null,null from dual;

SQL

select SK,ColA,ColB,regr_count(ColA,ColB) over(order by SK) as regC1,
count(case when ColA is not null
            and ColB is not null
           then 1 end) over(order by SK) as regC2
  from regr_cT
order by SK;

SK  ColA  ColB  regC1  regC2
--  ----  ----  -----  -----
 1     1     2      1      1
 2     1     2      2      2
 3     1  null      2      2
 4  null     2      2      2
 5  null  null      2      2
 6     1     2      3      3
 7  null  null      3      3
 8     1     2      4      4
 9  null  null      4      4

select regr_count(ColA,ColB) as regC1,
count(case when ColA is not null
            and ColB is not null
           then 1 end) as regC2
  from regr_cT;

regC1  regC2
-----  -----
    4      4

select regr_count(ColA,ColB) as regC1,
count(case when ColA is not null
            and ColB is not null
           then 1 end) as regC2
  from regr_cT
 where 1=0;

regC1  regC2
-----  -----
    0      0

解説

忘れたころに使うかもしれませんねぇ

---