SQLのCASE式可愛いって話 - Feedforce Developer Blog

こんにちは。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)
;