S-JIS[2010-01-27/2010-07-19] 変更履歴

集計関数COUNT

Oracleの集計関数COUNTのメモ。

概要
ALLとDISTINCT [/2010-07-19]
COUNT(値)とCOUNT(*)のどちらを使うべきか？
別SQLで代替する方法

Oracle Database SQL言語リファレンスのCOUNT

はじめに

集計関数COUNTは、レコード数（データ件数）を算出する関数。
基本的には、SELECTのWHERE条件を満たしたレコードが何件あったのかを数えるもの。
件数を返すので、必ず数値が返る。（レコードが無ければ0が返る。NULLが返ってくるような事は無い）

ネット上で使用例を見ると、COUNT(列名)やCOUNT(定数)（null以外の値）やCOUNT(ROWID)とCOUNT(*)（アスタリスクをそのまま記述）というパターンで例が載っていることが多い。

しかし文法的には、COUNT(式)とCOUNT(*)の2種類しか無い。
つまりテーブルの列名（項目名）や定数を指定するのは、「式」の特別な（単純な）使用法に過ぎない。

COUNT(式)とCOUNT(*)の一番大きな違いは、前者は各レコードの式の値（計算結果）がNULLだった場合はカウントに含めないのに対し、後者COUNT(*)はそういった考慮は無しに全レコードをカウント対象とすること。
（COUNT(*)は式を計算するわけではないので、何かと比較しようと思っても、何を対象にすればいいんだ？ってことだと思う。
ちなみに自分は昔、「COUNT(*)はレコードの全項目がNULLだったらカウントされない」と思っていたが、それは間違い(苦笑)）
→式がNULL以外の場合、COUNT(式)とCOUNT(*)のどちらを使うべきか？

COUNT(式)の場合、さらにALLとDISTINCTという指定をすることが出来る。
→ALLとDISTINCTの例

COUNT(*)の場合はALLやDISTINCTといった指定は（文法上は）無い。（何も指定しなくてもALLと同じ意味）
しかし、COUNT(ALL *)と書くことは出来るみたい。COUNT(DISTINCT *)と書くとエラーになるが。

ALLとDISTINCTの例

COUNT(式)の場合、ALLとDISTINCTという指定をすることが出来る。
COUNT(ALL 式)やCOUNT(DISTINCT 式)といった具合。
ALLは、（式の値がNULL以外の）レコード数をカウントする。
DISTINCTは、式の値が（NULL以外で）重複するものは1回しかカウントしない。いわばデータ（式の値）の種類数をカウントする事になる。
省略した場合はALL扱い。

COUNTの引数には1つの式しか指定できないので、複数項目にまたがった重複データを排除したい場合にCOUNT(DISTINCT 項目A, 項目B)といった書き方をすることは出来ない。
複数項目にまたがって重複データを排除したい場合、それらの項目が固定長でNULLにならないならば、文字列結合して一項目にすればよい。
COUNT(DISTINCT 項目A || 項目B)

データ例 SQL例結果備考

TBL
COL1	COL2
`'ABC'`	`'123'`
`'ABC'`	`'123'`
`'DEF'`	`'123'`
`'DEF'`	`'456'`
`NULL`	`'456'`

SELECT COUNT(COL1) FROM TBL;

値がNULLのものはカウントされない。

SELECT COUNT(COL2) FROM TBL; 5 COL2にはNULLが無いので、全レコード数と等しい。

SELECT COUNT(ALL COL1) FROM TBL; 4 ALLを指定してみた。

SELECT COUNT(DISTINCT COL1) FROM TBL; 2 DISTINCTを指定すると重複データは1回しかカウントされない。
この例では「ABC」と「DEF」の2種類なので、2となる。

SELECT COUNT(DISTINCT COL1 || COL2) FROM TBL; 4 文字列結合してみた。
「ABC123」が2レコード→重複排除で1カウント。
「DEF123」「DEF456」「456」が1カウントずつ。
合計で4になる。
もしCOL1=456・COL2=nullのデータがあったとすると、結合すると「456」。
COL1=null・COL2=456の結合結果「456」と同じなので、
重複していると認識されてしまう。（だからNULLは不可）

count(distinct COL1 || COL2)だと、「COL1=456・COL2=null」「COL1=null・COL2=456」というデータは同じと見なされてしまう。[2010-07-19]
以下のようなSQLなら、NULL値が含まれていても別々にカウントできる。

select count(*) from (
	select distinct COL1, COL2 from TBL
);

COUNT(値)よりCOUNT(*)を使うべき

ネット上の集計関数COUNTの例では、COUNT(1)やCOUNT(0)、COUNT(ROWID)、あるいはNOT NULL項目に対してCOUNT(項目)といった使用例が見られる。

1や0といったNULL以外の定数は、必ずNULL以外になる。
擬似列ROWIDも常にNULL以外になる。
NOT NULL制約のついた項目は、当然NULL以外になる。
これらを指定したCOUNT(式)は、式が常にNULL以外になることにより、レコード数が常にカウントされる事となる。
つまりCOUNT(*)と全く同じ状態であり、同じ結果が得られる。

ではなぜこういった例が色々あるかというと、「どちらの方が（処理速度が）速いか？（パフォーマンスが良いか？）」という（実装上の）現実問題がある為。
たぶん「*」という指定を見ると「SELECT *」を連想してカウント時に全項目を取ってくるように見えるので（あるいは昔の自分のように全項目がNULL以外かどうかチェックするように勘違いしているので）、それは無駄だから何か1項目だけ指定しよう。という発想になるのだと思う。
→NULLになり得る項目を指定すると計算結果は変わってくるので、NOT NULL項目を指定しよう。
→レコード取得で必ず使われるし一番高速なのはOracleの擬似列ROWID。これは当然NULLにならないんだからROWIDを指定しよう。
→でもそもそもNOT NULLなんだから、固定値でいいじゃん。定数（1や0）を指定しよう。
といった考えだろうか。

また、インデックスの第1項目となる項目を指定するという話もあるようだ。
インデックスは値がNULLになるものは含まれないし、テーブル走査でなくインデックス走査でデータ取得できるから（読み込むデータのバイト数が減るので）高速になるという事らしい。
（COUNT(式)はNULL以外のものだけをカウントするので、NULLのデータはインデックスに含まれないという仕様と合致している）
（インデックスの第2項目以降の項目だとそのインデックスを使用したスキャンは出来ないから意味が無い）
（WHERE条件により別のインデックスを使った方が高速になるような場合にはCOUNTで指定した項目のインデックスとは無関係な為、意味が無い）

しかしこれらの話は、オプティマイザー（実行時の最適化）がどれだけ賢いかによって変わってくる。

基本的に、COUNT(式)ではレコード毎にNULLチェックを行う必要があるので、それを行わないCOUNT(*)より遅い。
（COUNT(*)であればレコードの有無だけでカウントできるが、COUNT(項目)だと値を取ってくる必要がある。COUNT(計算式)だと計算を行う必要がある）
COUNT(定数)やCOUNT(NotNull項目)であればNULLチェックを行う必要が無いので、
オプティマイザーがそう判断すればCOUNT(*)と同じロジックで処理すると思われる。つまりCOUNT(式)の場合、指定された式がNOT NULLかどうかを判断する処理が最初に入る。まぁ最初の一回だけだから大した差ではないだろうけど。
DBMSがそういう判断を行う実装になっていなければ、NULLチェックをレコード毎に常に行ってしまうだろう。
COUNT(式)に指定された項目がインデックスを使えるという場合は、COUNT(*)にしていたとしても同じインデックスを使えば同じ速さになる。
COUNT(式)であってもCOUNT(*)であっても、どういう走査方法をするか（どのインデックスを使うか）はオプティマイザーの判断による（WHERE条件も勘案して）。
（Oracle9iやOracle10gでは、COUNT(*)でもインデックスを使用してくれた）
（なお、index_ffsヒントを指定することで、COUNT(*)の時でもどのインデックスを使用するかを指定できるらしい）

つまり、Oracleのバージョンによって、あるいは状態（ルールベースかコストベースか等）によってオプティマイザーによる最適化が左右されるので、
古いバージョンでオプティマイザーが賢くない場合は、インデックス項目を指定するようなやり方は有効なのかもしれない。
が、新しいバージョンでは、COUNT(式)を使用する方が無駄になる可能性が高そうだ。
（使用する環境で実行計画を見て確認しないと一概には言えない）

結論としては、仕様的にNULLを除外したいのでない限り、COUNT(*)を使用する方が良い。
（SQL上にCOUNT(*)と書いてあれば、NULL判断をしない事が“人間が見て”明らかなので）
（少なくともCOUNT(項目名)の場合、項目指定が間違っているとカウント結果が違ってくるので、COUNT(*)を使う方が良い。
NotNull項目だからと思ってCOUNT(項目)としていたら、NotNull制約を外してNULLを入れたりすると結果が変わってきちゃう！そんな変更はまずしないだろうけど(苦笑)）

COUNTを別のSQLで表現

条件に応じたレコード数をカウントしたい場合、条件はWHERE句で指定する。

select count(*) from テーブル where 条件;

ただしこの書き方では、同じテーブルに対して複数の条件でカウントしたい場合は別々のSQLを発行することになってしまう。
指定したwhere条件でスキャンする範囲があまり絞り込めないような場合は、1回のフルスキャンで複数の条件を指定してカウントしたいところ。

WHERE句を使わない常套手段としては、decode()を使う方法がある。

select sum(  decode(項目, 条件値, 1, 0   )) from テーブル;
select count(decode(項目, 条件値, 1, null)) from テーブル;

decode(項目, 条件値, 値1, 値2)は、項目の値＝条件値の場合は値1、それ以外の場合は値2になる。
decode(項目, 条件値, 1, 0)であれば、条件が一致した場合は1、それ以外は0になるので、それを合計（sum）すれば件数と等しくなる。
decode(項目, 条件値, 1, null)であれば、条件が一致した場合は1（null以外であれば何でもよい）、それ以外はnullになるので、それをcountするとnull以外だけをカウントするので条件に合致したレコードの件数と等しくなる。

ただし、decode()はOracle独自の関数なので、今はCASE式を使う方が良いらしい。
decode()は条件が項目＝値という一致条件でしか使えないが、CASE式ではWHERE句と同様の条件を指定できる。

select sum(  case when 条件 then 1 else 0    end) from テーブル;
select count(case when 条件 then 1 else null end) from テーブル;

集計関数SUMを使う方式は、COUNT(ALL)と同等になる。
COUNT(DISTINCT)の場合はSUMでは難しいと思う。

select count(distinct 式) from テーブル where 条件;
↓↑
select count(distinct case when 条件 then 式 else null end) from テーブル;

例

-- ct1はdata3>100かつdata1>100の件数、ct2はdata3>100かつdata2>100の件数
select
	count(distinct case when data1 > 100 then key1 else null end) ct1,
	count(distinct case when data2 > 100 then key1 else null end) ct2
from テーブル
where data3 > 100
;

-- countの中のwhenでexistsを使って副問い合わせを使用することも出来る
select
	count(distinct case
		when
			exists (select * from テーブル2 e where e.key = m.key)
		then key
		else null end
	) ct
from テーブル1 m
;

ある条件でレコードが存在しているかどうかを、COUNTを使ってチェックすることが出来る。

select count(*) from テーブル where 条件;

で、レコードが存在していなければ0が返るし、存在していれば0より大きい値が返る。なので0かどうかで存在有無を判断できる。
が。
ちょっと考えれば分かる通り、テーブルの件数が多い場合は（WHERE条件でインデックスが使えてよほど絞り込めるのでない限りは）フルスキャンになるので、とても遅い。

存在有無のチェックは、擬似列ROWNUMを併用するのが良い。

select *        from テーブル where 条件 and ROWNUM <= 1;
select count(*) from テーブル where 条件 and ROWNUM <= 1;

前者の場合、レコードが存在していれば最初に見つかったデータが1件だけ返る。存在していなければ何も返ってこない。
後者の場合、レコードが存在していれば1が返る。存在していなければ0が返る。
存在していればその時点でスキャンが終わるので速い。（レコードが無ければ結局全部探しに行くことにはなるんだろうけど）

SQL> set autotrace on exp
SQL> select count(*) from emp where ROWNUM<=1;

  COUNT(*)
----------
         1


実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14)

Oracle関数へ戻る / SQLメモへ戻る / Oracle目次へ戻る / 技術メモへ戻る

メールの送信先：ひしだま