集計関数の小ネタ

SQL Server Advent Calendar 23日目 です。

今日は集計関数の小ネタです。 集計関数 (Transact-SQL)

Null を無視する

大体の集計関数は、Null を無視します。Null でも含めるのは、count(*) 位かな？
こんなテーブルとデータがあった時に

create table [成績] ( 
  [テスト] nvarchar(10), 
  [名前] nvarchar(10), 
  [点数] int
)

insert into [成績] ( [テスト], [名前], [点数] ) values
 ( '国語', '田中', 75 ), 
 ( '国語', '佐藤', 80 ), 
 ( '国語', '鈴木', 89 ), 
 ( '算数', '佐藤', 80 ), 
 ( '算数', '鈴木', 98 ), 
 ( '算数', '西岡', 65 ), 
 ( null, null, null )
go

select 
  count(*) as [全件] -- 7
  , count([点数]) as [点数の件数] -- 6 
  , sum([点数]) as [点数の合計] -- 481
  , avg([点数]) as [点数の平均] -- 481 / 6 = 80
  , avg(isnull([点数], 0)) as [点数の平均2] -- 481 / 7 = 68
from 
  [成績]

データが無い場合や、全ての値が Null の時は、Null を返します。

select sum([点数]) -- null
from [成績] 
where [点数] > 100
-- where [点数] is null -- この条件でも null が返る。

重複を無視する

重複を無視する場合は、distinct を指定する。

select 
  count(distinct [テスト]) as [テストの種類] -- 2
  , count(distinct [名前]) as [受講者の人数] -- 4
  , sum(distinct [点数]) as [重複除いた点数合計] -- 401
from 
  [成績]

Over を付けて Window 関数

大体は、ORDER BY 句 (Transact-SQL) と一緒に使うと思いますが、OVER 句 (Transact-SQL) を使うこともできます。

-- group by を使って、テスト毎の最高点/最低点/平均点を表示
select 
  [テスト] 
  , max([点数]) as [最高点] 
  , min([点数]) as [最低点] 
  , avg([点数]) as [平均点]
from 
  [成績] 
where 
  [テスト] is not null 
group by 
  [テスト] 
-- over (partition by) を使って、受講者の点数と(テスト毎の)平均点との差を表示
select 
  [テスト] 
  , [名前] 
  , [点数] 
  , avg([点数]) over (partition by [テスト]) as [平均点] 
  , [点数] - avg([点数]) over (partition by [テスト]) as [平均点との差]
from 
  [成績] 
where 
  [テスト] is not null