こんにちは。id:kano-e です。
先日、社内で SQL の CASE について使い所を説明するという機会がありました。
2回くらい。
あれ、意外と CASE って使われてない……?
こんなに可愛いのに……!
みたいな気持ちになったので、今日は CASE がいかに可愛いのか話したいと思います。
SQL 可愛いよ!
CASE の使い方
CASE の使い方、ご存知の方も多いと思いますが、以下の通りです。
case category when 1 then 'カテゴリー1' when 2 then 'カテゴリー2' else 'その他' end
あるいは
case when category = 1 then 'カテゴリー1' when category = 2 then 'カテゴリー2' else 'その他' end
上記2つは、どちらも同じ結果を返します。
後者の使い方をすると、例えば category
以外のカラムを使って条件式を書いたり、複数の条件を組み合わせるなどもできます。
よしなに使い分けてください。
BigQuery のレガシーSQL (BigQuery SQL) では、後者の書き方しかできなくて焦ったという記憶があるので、お気をつけください。
SELECT で CASE を使う
さて、先日遭遇したのは、
- nginx のアクセスログが BigQuery に投入されている
- ログの情報から API の利用者が特定できる
- API のエンドポイントから利用されている API の種類が特定できる
「このログから、API の利用者毎に、A という API と B という API がどれだけ使われているか一覧で見たい」という状況でした。
ログから API の利用者と API の種類を抜き出すのはすでにできていたので、具体的には、
id | api_name |
---|---|
111 | A |
222 | B |
333 | A |
111 | B |
222 | C |
... | ... |
このようなテーブルを
id | api_a_count | api_b_count |
---|---|---|
111 | 5 | 2 |
222 | 0 | 3 |
333 | 8 | 2 |
... | ... | ... |
このような形で見たいという。
これはまさしく CASE 式の出番ですね!
では、早速 SQL 書いていきましょう。
こういう時に、自分はまず SELECT から書くことが多いです。
最終的な出力がわかると、ゴールがイメージしやすくなるので。
この時点で FROM もわかってるのでつけてしまいましょう。
今回ならこう。
select id , api_a_count , api_b_count from (今回の対象のテーブルとかサブクエリとか) ;
次は id
毎にカウントなので GROUP BY しましょう。
件数をカウントしたいので、とりあえず COUNT もしてみます。
select id , count(*) as api_a_count , count(*) as api_b_count from (今回の対象のテーブルとかサブクエリとか) group by id ;
さて、ここで実行結果を見て考えます。
今の実行結果は形式こそ欲しい形だけど、その数は欲しいものと違います。
api_a_count
では api_name
が A
のものだけカウントしたい。
api_b_count
では api_name
が B
のものだけカウントしたい。
はい、満を辞して CASE の登場です。
COUNT 部分を以下のように書き換えます。
select id , count( case when api_name = "A" then 1 else null end ) as api_a_count , count( case when api_name = "B" then 1 else null end ) as api_b_count from (今回の対象のテーブルとかサブクエリとか) group by id ;
api_a_count
の COUNT の中では api_name
が "A"
の時は 1
に、それ以外の時は null
になるような CASE を書いています。
COUNT には null が含まれないので、これでめでたく A という API の数だけがカウントできます。
うわ CASE 可愛い!
null が気持ち悪い時は COUNT ではなく SUM を使っても良いです。
select id , sum( case when api_name = "A" then 1 else 0 end ) as api_a_count , sum( case when api_name = "B" then 1 else 0 end ) as api_b_count from (今回の対象のテーブルとかサブクエリとか) group by id ;
これも、最終的に得られる結果は同じになります。
UPDATE で CASE を使う
CASE の使い所は SELECT だけではありません。
例えば、
- category が 3 のものを 30 に
- category の 10 と 20 を取り替える (10 のものを 20 に、20 のものを 10 に)
みたいな更新も CASE でまとめて実行可能です。
update table_name set category = ( case category when 3 then 30 when 10 then 20 when 20 then 10 else category -- WHERE で絞り込んでいれば不要なはずなのですが ELSE ないと不安です end ) -- CASE で ELSE 書いてるので WHERE なくても問題ないはずですが、更新系は不安になりますよね where category in (3, 10, 20) ;
まとめて1回で更新できるの便利ですね。
とはいえ、こういった更新処理が必要になるタイミングがどの程度あるかはわかりません。
CASE だとこういうこともできるんだよ!可愛いよ!とアピールするために、社内でこんな話をしたのでした。
CASE 可愛いので、皆さんもぜひ戯れてください。