2017.04.13
第1回 2つの値の比率を計算する
データ分析では、複数の値を集約して1つの値にまとめたり、さまざまな値を比較する場面が多々あります。値を操作する目的を整理し、レコードに含まれる異なる値を組み合わせて、新しい値を計算する手法には「文字列の連結」「複数の値の比較」「2つの値の距離の計算」などがありますが、本項では、1レコードに含まれる値を組み合わせて、割合を計算する手法を紹介します。
この項で登場するSQLは、除算、CAST構文、CASE式、NULLIF関数です。
下記に示すデータ例の広告統計情報（advertising_stats）テーブルは、1日ごとの広告のインプレッション数とクリック数を集計しています。2017-04-02 のad_id:001 のデータは広告自体が表示されず、インプレッション数とクリック数が0 となっています。このテーブルをサンプルデータとして解説を進めます。
dt | ad_id | impressions | clicks ------------+-------+-------------+-------- 2017-04-01 | 001 | 100000 | 3000 2017-04-01 | 002 | 120000 | 1200 2017-04-01 | 003 | 500000 | 10000 2017-04-02 | 001 | 0 | 0 2017-04-02 | 002 | 130000 | 1400 2017-04-02 | 003 | 620000 | 15000
整数型のデータの除算を行う
次に示すコード例では、1日分（2017-04-01）のデータで各広告のCTR（Click Through Rate）を 計算します。CTRの定義は、「クリック数／インプレッション数」です。
1レコードに含まれる値の除算は、SELECT句中に「/」を使用します。ただし、PostgreSQLの場合は、 advertising_statsテーブルのclicksとimpressionsカラムが整数型のため、計算結果も整数型となり値が0となります。整数型同士の除算で小数点以下も取得する場合は、事前に小数点を扱える型に変換する必要があります。CAST関数を用いてclicksをdouble precision型（倍精度浮動小数点数型）に変換すると、除算の結果もdouble precision型になります。
また、結果をパーセント表記にする場合は、ctrカラムの結果に100を掛けることでも取得できますが、 ctr_as_percentカラムに示す通り、clicksに100.0を掛けることで、型変換も同時に実行できるため、クエリが簡潔になります。
SELECT
dt
, ad_id
-- ■ Hive, Redshift, BigQuery, SparkSQLの場合、
-- 整数の割り算でも結果が自動的に実数へ変換される
, clicks / impressions AS ctr
-- ■ PostgreSQLの場合、整数の割り算では小数点が切り捨てられてしまうので、明示的に型を変換する
-- , CAST(clicks AS double precision) / impressions AS ctr
-- 実数の定数を先頭で掛け算すると、暗黙的に型変換がされる
, 100.0 * clicks / impressions AS ctr_as_percent
FROM
advertising_stats
WHERE
dt = '2017-04-01'
ORDER BY
dt, ad_id
;
dt | ad_id | ctr | ctr_as_percent ------------+-------+------+---------------- 2017-04-01 | 001 | 0.03 | 3.00 2017-04-01 | 002 | 0.01 | 1.00 2017-04-01 | 003 | 0.02 | 2.00
0 除算を避ける
2017-04-02のデータにはimpressionsが0となるレコードが存在するため、上記のコード例に示したクエリをそのまま適用すると、0除算エラーとなってしまいます。
0除算を回避する方法の1つは、CASE式を用いてimpressionsが0かどうかに応じて場合分けする方法です。次に示すコード例のctr_as_percent_by_caseカラムでは、impressionsが0より大きい場合にはCTRを計算し、それ以外の場合はNULLを返します。
また、SQLにおけるNULLの伝搬を利用する方法でも0除算を回避できます。NULLの伝搬とは、NULLを含むデータの演算結果がすべてNULLとなる、SQLの性質です。次のコード例のctr_as_percent_by_nullの中で、NULLIF(impressions, 0)の部分はimpressionsの値が0の場合にNULL となります。その結果、impressionsの値が0の場合には、NULLの伝搬によりCTRの値もNULLとなり、CASE式を用いた方法と同じ値を得られます。
SELECT
dt
, ad_id
-- CASE式で分母が0の場合を分岐して、0除算を避ける方法
, CASE
WHEN impressions > 0 THEN 100.0 * clicks / impressions
END AS ctr_as_percent_by_case
-- 分母が0の場合はNULLに変換し、0除算を避ける方法
-- ■ PostgreSQL, Redshift, BigQuery, SparkSQLの場合、NULLIF関数が利用できる
, 100.0 * clicks / NULLIF(impressions, 0) AS ctr_as_percent_by_null
-- ■ Hiveの場合、NULLIFの代わりにCASE式を用いる
-- , 100.0 * clicks
-- / CASE WHEN impressions = 0 THEN NULL ELSE impressions END
-- AS ctr_as_percent_by_null
FROM
advertising_stats
ORDER BY
dt, ad_id
;
dt | ad_id | ctr_as_percent_by_case | ctr_as_percent_by_null ------------+-------+------------------------+------------------------ 2017-04-01 | 001 | 3.00 | 3.00 2017-04-01 | 002 | 1.00 | 1.00 2017-04-01 | 003 | 2.00 | 2.00 2017-04-02 | 001 | | 2017-04-02 | 002 | 1.07 | 1.07 2017-04-02 | 003 | 2.41 | 2.41
ワンポイント
本項で紹介した比率も、データ分析では頻出する計算ですが、整数のまま除算を実行したり、0で割ってしまうなどの失敗は、クエリの構文自体には間違いがないため見落としてしまいがちです。注意すべき点をしっかりと押さえておきましょう。
