SQLの集合関数の応用

数種類の集計を効率的にできないかな、と調べてみたら、あっさり見つかってしまいました。おまけに、DBMS固有のでも無く、最新の仕様よりも前からサポートされていたりするものもあるようです。SQLってたまに使うと便利ですね。
これから紹介するのは、それほど特別なことではありません。ちゃんとSQLを勉強されている方には常識レベルだと思います。私があまり真面目に仕様を勉強していないだけです。

今回、私は3種類の集計を同時に(SQLを1文で)行おうとしていました。

1. 列の値の重複を除いた件数
2. 列の値がnullでない件数
3. 列の値がある条件を満たしている件数

書いてみると、簡単にできそうな気がしますが、最初はもっと漠然としたものをイメージしていて、おまけにSQLではやらずに取ってきてから集計しようと思ってました。
データはこんなので*1。Oracleでも試しましたが、サンプル採取はDerbyで行いました。

ID	NAME	VOLUME	SIGN
AAA	あああ	4	K
AAA	あああ	2	null
BBB	あああ	3	X

まず、最初のはDISTINCTのようなことがやりたいんだけど、集計の時はどうすれば良いのだろう、と考えました。調べてみたら、そのままなのでした。

SELECT COUNT(DISTINCT ID) FROM TABLE1

COUNT(DISTINCT ID)
2

次に、nullでないカウント。これも知らなかったのですが、ごく簡単です。

SELECT COUNT(SIGN) FROM TABLE1

COUNT(SIGN)
2

普通に書いただけです。そもそもCOUNTは、列の値がnull以外のレコードを数えるようです。私は、プライマリキーでしか使ったことなかったので、それを意識したことがありませんでした。

最後。ここでは「VOLUMEが3以上」という条件にします。これまでのよりは応用っぽいですが、単純にSUM+CASEを使うだけです。

SELECT SUM(CASE WHEN VOLUME>=3 THEN 1 ELSE 0 END) FROM TABLE1

SUM(CASE...)
2

COUNTでもできるが、変則的かも。

SELECT COUNT(CASE WHEN VOLUME>=3 THEN '1' ELSE NULL END) FROM TABLE1

COUNT+CASE版では、システムのNULLの扱いに注意が必要です。Derbyだと、THENの後ろを数字の1にすると、NULLがCHARなので互換性が無いと怒られます。Oracleは(今試せないのですが)、'1'を''にしたら、NULL扱いになるのかどうか分かりません。

*1:ちなみに、今回初めて表組みを使いました。