BigQuery(BQ) で条件分岐をする際には CASE や IF を用います。これらを活用することで抽出したカラムを条件をもとにラベリングすることが可能です。データ分析では頻発する表現になります。
こちらの記事では BigQuery 初心者向けに CASE / IF の使い方やその応用を解説します!
条件分岐とは?
例えば、
- ABテストをする際にIDが奇数のユーザーと偶数のユーザーで分類したい
- 年齢を10代、20代、30代、のように一定のくくりで分類したい
というような状況の際に「Aという条件だったらBにする」という処理を条件分岐といいます。
EXCELではIF関数や、COUNTIF関数、SUMIF関数などで処理することが多いでが、BigQueryでは CASE や IF を用いて処理していきます。
以降、実際にBigQuery(BQ)における条件分岐のやり方を見ていきましょう!
CASEの使い方
CASEの構文は以下になります。
CASE
WHEN {条件句} THEN {条件にマッチした場合に割り振る値}
[ ... ]
[ ELSE {いずれの条件にも当てはまらなかった場合の値} ]
END
実例を見たほうがイメージが湧くと思うので、サンプルを見ていきましょう。
サンプルクエリ
使用するデータ
WITH
data AS (
SELECT "田中" AS name, 11 AS age
UNION ALL
SELECT "高橋" AS name, 22 AS age
UNION ALL
SELECT "鈴木" AS name, 25 AS age
UNION ALL
SELECT "加藤" AS name, 31 AS age
UNION ALL
SELECT "山本" AS name, 38 AS age
)
SELECT * FROM data
実行結果すると以下の結果が返ってきます。
name | age |
---|---|
田中 | 11 |
高橋 | 22 |
鈴木 | 25 |
加藤 | 31 |
山本 | 38 |
年代のラベルを付ける
CASEを用いて10代や20代などのラベルをつけます。
WITH
data AS (
SELECT "田中" AS name, 11 AS age
UNION ALL
SELECT "高橋" AS name, 22 AS age
UNION ALL
SELECT "鈴木" AS name, 25 AS age
UNION ALL
SELECT "加藤" AS name, 31 AS age
UNION ALL
SELECT "山本" AS name, 38 AS age
)
SELECT
name,
age,
CASE
WHEN age BETWEEN 10 AND 19 THEN '10代'
WHEN age BETWEEN 20 AND 29 THEN '20代'
WHEN age BETWEEN 30 AND 39 THEN '30代'
ELSE '他' END AS age_segment
FROM
data
以下の結果が出力されます。年齢を条件にして、各年代のラベルを割り振りました。
name | age | age_segment |
---|---|---|
田中 | 11 | 10代 |
高橋 | 22 | 20代 |
鈴木 | 25 | 20代 |
加藤 | 31 | 30代 |
山本 | 38 | 30代 |
実際に分析する際はこのようにセグメントで分類して集計する事が多いので利用する頻度が高い表現です。
IFの使い方
同様のことをIFを用いて処理できます。使い方はEXCELのIF関数と同じです。
IF({条件句}, {条件に一致した場合に割り振る値}, {条件に一致しなかった場合に割り振る値})
サンプルクエリ
WITH
data AS (
SELECT "田中" AS name, 11 AS age
UNION ALL
SELECT "高橋" AS name, 22 AS age
UNION ALL
SELECT "鈴木" AS name, 25 AS age
UNION ALL
SELECT "加藤" AS name, 31 AS age
UNION ALL
SELECT "山本" AS name, 38 AS age
)
SELECT
name,
age,
IF(age BETWEEN 10 AND 19, '10代',
IF(age BETWEEN 20 AND 29, '20代',
IF(age BETWEEN 30 AND 39, '30代',
'他'))) AS age_segment
FROM
data
以下の結果が出力されます。
name | age | age_segment |
---|---|---|
田中 | 11 | 10代 |
高橋 | 22 | 20代 |
鈴木 | 25 | 20代 |
加藤 | 31 | 30代 |
山本 | 38 | 30代 |
IFNULLの使い方(COALESCE)
IFNULLもしくはCOALESCEを用いることでカラムが空だった場合に指定した値を入れることができます。
サンプルクエリ(IFNULL)
WITH
data AS (
SELECT "田中" AS name, 11 AS age
UNION ALL
SELECT "高橋" AS name, 22 AS age
UNION ALL
SELECT "鈴木" AS name, 25 AS age
UNION ALL
SELECT "加藤" AS name, 31 AS age
UNION ALL
SELECT null AS name, 39 AS age
)
SELECT
name,
age,
IFNULL(name, "名前不明") AS age_if_null
FROM
data
出力結果は以下になります。nameが空だったレコードに対して別の値を入れることができました。
name | age | age_if_null |
---|---|---|
田中 | 11 | 田中 |
高橋 | 22 | 高橋 |
鈴木 | 25 | 鈴木 |
加藤 | 31 | 加藤 |
null | 39 | 名前不明 |
サンプルクエリ(COALESCE)
IFNULLと同様のことをCOALESCEを用いて処理することができます。
WITH
data AS (
SELECT "田中" AS name, 11 AS age
UNION ALL
SELECT "高橋" AS name, 22 AS age
UNION ALL
SELECT "鈴木" AS name, 25 AS age
UNION ALL
SELECT "加藤" AS name, 31 AS age
UNION ALL
SELECT null AS name, 39 AS age
)
SELECT
name,
age,
COALESCE(name, "名前不明") AS age_if_null
FROM
data
出力結果は以下です。
name | age | age_if_null |
---|---|---|
田中 | 11 | 田中 |
高橋 | 22 | 高橋 |
鈴木 | 25 | 鈴木 |
加藤 | 31 | 加藤 |
null | 39 | 名前不明 |
IFNULLやCOALESCEの処理はCASE文を用いても同様のことができますが、かなり省略した記法で書くことができるので便利です。
【応用】集計関数内で条件を使う
集計関数を用いる際に、特定条件のみを集計することもCASEやIFを用いることで簡略化して書くことができます。
サンプルクエリ
20代のみのレコードの数をカウントしてみましょう。COUNT()の中にCASE文を記載します。
WITH
data AS (
SELECT "田中" AS name, 11 AS age
UNION ALL
SELECT "高橋" AS name, 22 AS age
UNION ALL
SELECT "鈴木" AS name, 25 AS age
UNION ALL
SELECT "加藤" AS name, 31 AS age
UNION ALL
SELECT null AS name, 39 AS age
)
SELECT
COUNT(CASE WHEN age BETWEEN 20 AND 29 THEN name END) AS age_20_count
FROM
data
出力結果は以下です。COUNT()内でageが20~29のレコードのみ値を返して、それ以外は値を返さずに集計しているので、20代のレコードのみが集計されます。
age_20_count |
---|
2 |
この記法をすることでサブクエリを量産しなくて良いのでかなり楽になります。
まとめ
こちらの記事では、
- BigQuery における条件句(CASE / IF) の基本的な使い方
- よく使う応用的な記法
について解説しました。
条件分岐はデータ分析で利用頻度が高いので、是非 BigQuery を使用する際は活用してみてください!
コメント