SQLは集計に適しています。数を数えたり、合計したり、平均したりすることは、SQLでは簡単な作業です。分布を見るときには、文脈を理解し、全体の各数値を相対比較することが効果的です。
各マーケティングソースから獲得したユーザーの数を追跡したいとします。そのためには、ソース別のユーザー数を計算する簡単なクエリを書きます：
select
date_trunc('week',created_at) as date
, source
, count(1)
from
users
group by
date
, source
このデータをプロットして、ソースごとのユーザー数の増加を時系列で追跡することができます：
それぞれのソースから何人のユーザーが来ているかを知るのは達成しましたが、ある週にどのくらいの割合のソースから来ているかを知りたい場合はどうしたらいいでしょうか？見てみましょう。
Redshiftには便利なratio_to_report window関数があります。この関数は、現在の値を、指示された区分内のすべての値の合計で割り算します。今回のクエリでは、1つのソースからのユーザー数を、すべてのソースからのユーザー数で割りたいと思います。
select
date_trunc('week',created_at) as date
, source
, count(1) as count
, ratio_to_report(count(1)) over (
partition by date_trunc('week',created_at)
) as proportion
from
users
group by
date, source
Postgresには同じようなratio_to_report関数はありませんが、sum()overをwindow関数の中で使用することで同様の結果を得ることができます。まず、ユーザの数を持つ共通のテーブル式を作成します。
with users_by_source as (
select
date_trunc('week',created_at) as date
, source
, count(1) as count
from
users
group by
date
, source
)
次に、users_by_source CTEを使用して、sum()over関数で各週の合計で割ることができます。
select
date
, source
, 1.0 * count / sum(count) over (partition by date)
from users_by_source
データベースがMySQLの場合やウィンドウ関数を使いたくない場合はいくつかの簡単なサブクエリでこれを行うことができます。まず、各日付の合計を計算したいと思います:
select
yearweek(created_at) as date
, count(1) as count
from
users
group by
date
次に、ソース別のユーザーを求めます：
select
yearweek(created_at) as date
, source
, count(1) as count
from
users
group by
date, source
最終的には、結合したり、分割したりします：
select
T1.date
, t1.source
, t1.count / t2.count
from
(
select
yearweek(created_at) as date
, source
, count(1) as count
from
Users
group by
date
, source
)
as t1
join(
select
yearweek(created_at) as date
, count(1) as count
from
Users
group by
date
)
as t2 on
t1.date = t2.date
比率を棒グラフ化することで、データを視覚的に表現することができます：
ピボットテーブルを使えば、データを表形式で表示するための列のパーセンテージを素早く計算することができます。
これで、クエリを書く時間を減らし、データの結果を分析する時間を増やすことができます。
※本記事は、「Calculating Proportional Values in SQL」を翻訳・加筆修正したものです。