MySQLで特定期間のデータを抽出する

ある期間に始めて購入があった顧客を取り出して、購入した月から3か月間の累計購入金額を 出力するという処理を作ったので手順等記載いたします。

今回のブログでは3つのテーブルを使います(SQL文では2つ) 構造を記載いたします。

customersテーブル
     顧客名
+---+-------+
|id |name   |
+---+-------+
|1  |tanaka |
|2  |yamada |
+---+-------+

customer_analysesテーブル
      顧客id      最新注文日     累計購入回数
+---+------------+--------------+-------------------+
|id |customer_id |purchase_time |purchase_frequency |
+---+------------+--------------+-------------------+
|1  | 2          |2017-01-02    | 1                 |
|2  | 1          |2017-01-06    | 1                 |
+---+------------+--------------+-------------------+

ordersテーブル
      顧客id      注文日         購入金額
+---+------------+--------------+-------------+
|id |customer_id |order_date    |total_amount |
+---+------------+--------------+-------------+
|1  | 2          |2017-01-02    |10000        |
|2  | 1          |2017-01-06    |2000         |
+---+------------+--------------+-------------+

まずは特定期間を指定するサブクエリを作ります。 このサブクエリで出した顧客データと注文のデータを紐づけます

DATE_ADDとDATE_FORMATを使うのですがそれぞれの使い方に 関しましては以前記載したブログをお読みください。 MySQLで日付に関する処理 - 情弱エンジニアのなかのblog

( SELECT
    -- サブクエリ外で利用するフィールドをSELECTで指定する
    -- 顧客のIDと購入の年月日
    customer_id,
        purchase_time
FROM
    customer_analyses
WHERE
        -- 2017年の1月に購入があった顧客
    purchase_time between '2017-0-01' and '2017-01-31'
        -- 購入が1回目(初回購入)
    AND 
        purchase_frequency = 1 ) AS ca

注文に関するテーブルに結合します

FROM
    orders
INNER JOIN
(先ほど作ったサブクエリはここに入ります) AS ca
ON
    orders.customer_id = ca.customer_id

続いて初回購入月から3ヶ月間のデータを取得するために DATE_ADDを使って3ヵ月後の月を取得します その後にordersにある注文の日付と比較して三か月後より前かつ 初回購入月より後のデータを取り出します。 月のデータが欲しいのでDATE_FORMATを使います。

WHERE
    DATE_FORMAT(orders.order_date,'%Y%m') <= DATE_FORMAT(DATE_ADD(ca.purchase_time, INTERVAL 2 MONTH),'%Y%m')

すべて合わせると以下のようになります。

SELECT
        orders.customer_id AS '顧客ID',
        sum(orders.total_amount) AS '売上金額'
FROM
    orders
INNER JOIN
( SELECT
    customer_id,purchase_time
FROM
    customer_analyses
WHERE
    purchase_time between '2017-01-01' and '2017-01-31'
    AND
        purchase_frequency = 1 ) AS ca
ON
    orders.customer_id = ca.customer_id
WHERE
    DATE_FORMAT(orders.order_date,'%Y%m') <= DATE_FORMAT(DATE_ADD(ca.purchase_time, INTERVAL 2 MONTH),'%Y%m')

サブクエリの中の日付を変更することで任意の期間を指定して抽出できます。

それでは