SQL データの集計に関して
売上の計算をするSQLを制作していまして、特定条件で絞り込んだ顧客の初回購入から〇ヵ月間の売上を取得する という処理を制作したので、手順等を記載いたします。
まず前提として以下のようなテーブルがあるとします。
customersテーブル
顧客名 登録日（顧客として登録された年月日）
+---+-------+-----------+
|id |name |created |
+---+-------+-----------+
|1 |tanaka |2017-01-01 |
|2 |yamada |2017-02-02 |
|3 |satou |2017-03-03 |
|4 |ito |2017-03-04 |
|5 |katou |2017-02-05 |
+---+-------+-----------+
customer_historiesテーブル
顧客id 注文日 購入回数 注文id 媒体id
+---+------------+----------------+------------------+---------+---------+
|id |customer_id |date_of_purchase|number_of_purchase|order_id |media_id |
+---+------------+----------------+------------------+---------+---------+
|1 | 1 |2017-01-05 | 1 |1 |1 |
|1 | 1 |2017-02-05 | 2 |2 |1 |
|1 | 1 |2017-03-05 | 3 |3 |1 |
|1 | 2 |2017-04-01 | 1 |4 |1 |
|1 | 3 |2017-05-01 | 1 |5 |1 |
|1 | 4 |2017-06-01 | 1 |6 |1 |
|1 | 5 |2017-07-01 | 1 |7 | |
+---+------------+----------------+------------------+---------+---------+
ordersテーブル
顧客id 注文日 金額
+---+------------+--------------+--------------+
|id |customer_id |order_date | total_amount |
+---+------------+--------------+--------------+
|1 | 1 |2017-01-05 | 100 |
|2 | 1 |2017-02-05 | 200 |
|3 | 1 |2017-03-05 | 300 |
|4 | 2 |2017-04-01 | 100 |
|5 | 3 |2017-05-01 | 100 |
|6 | 4 |2017-06-01 | 100 |
|7 | 5 |2017-07-01 | 500 |
+---+------------+--------------+--------------+
mediasテーブル
媒体名
+---+--------+
|id |name |
+---+--------+
|1 |blog |
|2 |twitter |
+---+--------+
まず条件として2つの軸で集計いたします。初回流入媒体と登録日です。 初回流入媒体ごとに分けて更に登録月別に分けます。 登録月別に別れた顧客の初回購入から3ヵ月間の購入の売上を計算します。 以下のようなSQLになります。
select
ch.id as '媒体ID',
ch.name as '媒体名',
DATE_FORMAT(ch.created , '%Y%m') as '顧客登録月',
FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
FORMAT(sum(orders.total_amount),0) AS '総売上'
FROM
orders
INNER JOIN
--サブクエリで初回購入のテーブルを作ります
(
SELECT
customer_histories.customer_id,
customer_histories.date_of_purchase,
medias.id,
medias.name,
customers.created
FROM
customer_histories
INNER JOIN
--登録年月日を出すためcustomersテーブルを結合します
customers
ON
customers.id = customer_histories.customer_id
INNER JOIN
medias
ON
customer_histories.media_id = medias.id
WHERE
--初回購入の条件
customer_histories.number_of_purchase = 1
) AS ch
ON
orders.customer_id = ch.customer_id
WHERE
--初回の注文年月から 2 MONTH つまり2ヵ月間のデータに絞り込みます
DATE_FORMAT(orders.order_date ,'%Y%m') <= DATE_FORMAT(DATE_ADD(ch.date , INTERVAL 2 MONTH),'%Y%m')
GROUP BY
--まず初回流入媒体ごとに分けます
ch.id,
--次に登録年月ごとに分けます
DATE_FORMAT(ch.created ,'%Y%m')
ORDER BY
--medias.idの昇順に並び替えます
ch.id
上記のようになりまして結果は以下のようになります。
+-----------+------------+-----------+-------+--------+ |媒体ID |媒体名 |顧客登録月 |顧客数 |総売上 | +-----------+------------+-----------+-------+--------+ |1 | blog |2017-01 | 1 |600 | |1 | blog |2017-02 | 1 |100 | |1 | blog |2017-03 | 2 |200 | +-----------+------------+-----------+-------+--------+
確認すべき点は3つです。
まずは初回購入の媒体ごとに分かれている
登録月ごとに分かれているので登録月が3月の customers.id 3[satou]と4[ito]の2人が3行目で合算されています。総売上も合算されています。
購入時にメディアIDのなかった customers.id 5[katou]が分析に含まれていません。
続いてWHEREの[2 MONTH]を[1 MONTH]に変えてみます。 そうすると初回購入から2ヵ月間の売上を出力します。
+-----------+------------+-----------+-------+--------+ |媒体ID |媒体名 |顧客登録月 |顧客数 |総売上 | +-----------+------------+-----------+-------+--------+ |1 | blog |2017-01 | 1 |300 | |1 | blog |2017-02 | 1 |100 | |1 | blog |2017-03 | 2 |200 | +-----------+------------+-----------+-------+--------+
細かい違いですが、1行目の総売上が300になっています。 これは2ヵ月間の分析に変更したのでcustomers.id 1[tanaka]の3月の購入が 除外されたことが原因です。
これらが特定条件で絞り込んだ顧客の初回購入から〇ヵ月間の売上を取得するSQLになります。
それでは
