読者です 読者をやめる 読者になる 読者になる

argius note

プログラミング関連

開発しています



SQLの集合関数の応用

database

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


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

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

書いてみると、簡単にできそうな気がしますが、最初はもっと漠然としたものをイメージしていて、おまけにSQLではやらずに取ってきてから集計しようと思ってました。
データはこんなので*1Oracleでも試しましたが、サンプル採取は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:ちなみに、今回初めて表組みを使いました。