axjack's blog

axjack is said to be an abbreviation for An eXistent JApanese Cool Klutz.

集約関数

ちょっとgroup byと集約関数と仲良くする必要があったので練習してみた記録です。SQL FiddleのMS SQL Server 2017を使います。

その1

DDL

create table t (
  col1 int 
  ,col2 int
  ,col3 int
  ,col4 int
 )

insert into t values 
(1,1,1,0)
,(1,1,1,0)
,(1,1,1,0)
,(1,1,2,1)
,(1,1,2,0)
,(1,1,2,0)
,(1,1,3,0)
,(1,1,3,0)
,(1,1,3,0)
,(4,5,6,1)
,(4,5,6,-1)
,(5,5,6,1)
,(5,5,6,1)
,(6,5,6,0)
,(6,5,6,null) 

DML

-- --------------------------------------------------------------------------------
-- http://mickindex.sakura.ne.jp/database/celko/celko_tis.html
-- col1、col2、col3 の三列でグループ化したときに
-- col4 がすべて 0 であるような行を一意に取得せよ。
-- --------------------------------------------------------------------------------
select 
  col1,col2,col3
from 
  t
group by 
  col1,col2,col3
having 
  max(col4) = min(col4) -- group 内全てのcol4が同一な値である
  and min(col4) = 0 -- 最小値が0である
  and count(*) = count(col4)  -- col4はnullを含んでいない
;

補足

最小値の抑え込み

  and min(col4) = 0 -- 最小値が0である

がないと、

,(5,5,6,1)
,(5,5,6,1)
,(6,5,6,0)
,(6,5,6,null) 

が抽出されてしまう。

nullと集約関数

  • MAX
  • MIN

は中身がnullだとエラーになったり省略されたりする*1。なので、ISNULLcoalesceでnullを抑え込むのもあり。

その2

count(*)count(X)count(distinct(X))の違いをみてみます。なお、count(distinct(X))は奥が深い問題*2のようです。

DDL

create table t(
  grp char(1) null 
   , val nvarchar(10) null
)

insert into t values 
('A',null)
,('B','bbb')
,('B','bbb')
,('B','ccc')
,('C','ddd')
,('C','eee')
,('D','fff')
,('D',null)
,('D',null)

DML

select
  grp
  ,max(val) as max_val
  ,min(val) as min_val
  ,count(*) as count_aster
  ,count(val) as count_val
  ,count(distinct(val)) as count_distinct_val
from t
  group by grp

実行結果

grp max_val min_val count_aster count_val count_distinct_val
A (null) (null) 1 0 0
B ccc bbb 3 3 2
C eee ddd 2 2 2
D fff fff 3 1 1

*1:「警告 : NULL 値は集計またはその他の SET 演算で削除されました」が出たりする

*2:https://en.wikipedia.org/wiki/Count-distinct_problem