ちょっとgroup byと集約関数と仲良くする必要があったので練習してみた記録です。SQL FiddleのMS SQL Server 2017を使います。
その1
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)
select
col1,col2,col3
from
t
group by
col1,col2,col3
having
max(col4) = min(col4)
and min(col4) = 0
and count(*) = count(col4)
;
補足
最小値の抑え込み
and min(col4) = 0
がないと、
,(5,5,6,1)
,(5,5,6,1)
,(6,5,6,0)
,(6,5,6,null)
が抽出されてしまう。
nullと集約関数
は中身がnullだとエラーになったり省略されたりする*1。なので、ISNULL
やcoalesce
でnullを抑え込むのもあり。
その2
count(*)
とcount(X)
とcount(distinct(X))
の違いをみてみます。なお、count(distinct(X))
は奥が深い問題*2のようです。
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)
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 |