情弱エンジニアのなかのblog

一人前のエンジニアになる為のブログです

SQL　データの集計に関して

MySQL

売上の計算をする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つです。

  1. まずは初回購入の媒体ごとに分かれている

  2. 登録月ごとに分かれているので登録月が3月の customers.id 3[satou]と4[ito]の2人が3行目で合算されています。総売上も合算されています。

  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になります。

それでは

SQL　JOINの注意点について

初回購入商品ごとに顧客を出力して顧客たちの初回購入月の総売上を出力するSQLを作っていたのですが 問題点があったので記載いたします。

まず今回このようなデータが入っているものとします。

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

customer_historiesテーブル
      顧客id         注文日          購入回数         注文id
+---+------------+----------------+------------------+---------+
|id |customer_id |date_of_purchase|number_of_purchase|order_id |
+---+------------+----------------+------------------+---------+
|1  | 1          |2017-05-01      | 1                |1        |
|2  | 1          |2017-05-15      | 2                |2        |
|3  | 1          |2017-05-20      | 3                |3        |
|4  | 2          |2017-05-02      | 1                |4        |
+---+------------+----------------+------------------+---------+

ordersテーブル
      顧客id      注文日          金額
+---+------------+--------------+--------------+
|id |customer_id |order_date    | total_amount |
+---+------------+--------------+--------------+
|1  | 1          |2017-05-01    | 100          |
|2  | 1          |2017-05-15    | 500          |
|3  | 1          |2017-05-20    | 300          |
|4  | 2          |2017-05-02    | 100          |
+---+------------+--------------+--------------+

order_detailsテーブル
      注文id    商品id
+---+---------+---------+
|id |order_id |item_id  |
+---+---------+---------+
|1  | 1       |1        |
|2  | 2       |2        |
|3  | 2       |3        |
|4  | 3       |3        |
|5  | 4       |1        |
+---+---------+---------+

itemsテーブル(商品テーブル)
     　商品名　　値段
+---+----------+-----+
|id |name      |price|
+---+----------+-----+
|1  |Pepper    |100  |
|2  |salt      |200  |
|3  |mayonnaise|300  |
|4  |ketchup   |400  |
+---+----------+-----+

まず初めに以下のようなSQLを作りました サブクエリやDATE_FORMATやFORMATの使い方に関しては以前の記事を参考にしてください。

MySQLで特定期間のデータを抽出する - 情弱エンジニアのなかのblog

MySQLで日付に関する処理 - 情弱エンジニアのなかのblog

SQL小数点の表示とtimestamp - 情弱エンジニアのなかのblog

SELECT
    ch.name AS '商品名',
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.total_amount ),0) AS '1月間の総売上'
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id,
        customer_histories.date_of_purchase,
        items.id,
        items.name
    FROM
        customer_histories
    WHERE
        date_of_purchase between '2017-05-01' and '2017-05-31'
        AND
        number_of_purchase = 1
    ) AS ch
ON
    orders.customer_id = ch.customer_id
INNER JOIN
    order_details
ON
    orders.id = order_details.order_id
INNER JOIN
    items
ON
    order_details.item_id = items.id
WHERE
    DATE_FORMAT(orders.order_date , '%Y%m') <= DATE_FORMAT(ch.date_of_purchase , '%Y%m')
GROUP BY
    items.id

こちらですと問題点として まず初回購入以外の商品データもGROUP BYの集計に入ってしまいます。

あとordersテーブルとorder_detailsテーブルを見るとわかるのですが、 order.id = 2で[tanaka]さんが同時に2つの商品を購入しているのでordersとorder_detailsの結合時に order.id = 2が2行分作られます。その状態で

select
    sum(orders.charged_amount)

をするとorder.id = 2 の total_amount = 500が2回集計されて間違ったデータになってしまします。

以下が正しい修正バージョンです。

SELECT
    ch.name AS '商品名',
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.total_amount),0) AS '1月間の総売上'
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id,
        customer_histories.date_of_purchase,
        items.id,
        items.name
    FROM
        customer_histories
    INNER JOIN
        order_details
    ON
        customer_histories.order.id = order_details.order_id
    INNER JOIN
        items
    ON
        order_details.item_id = items.id
    WHERE
        date_of_purchase between '2017-05-01' and '2017-05-31'
        AND
        number_of_purchase = 1
    ) AS ch
ON
    orders.customer_id = ch.customer_id
WHERE
    DATE_FORMAT(orders.order_date , '%Y%m') <= DATE_FORMAT(ch.date_of_purchase , '%Y%m')
GROUP BY
    ch.id

今回の問題点は無駄な結合を行っていたことと、集計の軸を間違えていたことです。

それでは

PHPでDBのデータを扱う

PHP MySQL

PHPのアプリケーションを制作していてDBを扱う方法を学んだので記載いたします。 今回はDBの挿入と更新と削除を行う方法に絞って説明いたします。

1.挿入

挿入の処理は以下になります

$stmt = $db->prepare("INSERT INTO テーブル名 (name, value) VALUES (:name, :value)");

$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':value', 1, PDO::PARAM_INT);

$stmt->execute();

bindParam は PDO::PARAM_INT を指定しても文字列として扱われて、変数を入れないとエラーが出ます。

bindValue は値を直接入れても、変数を入れても問題ありません。値を数値で指定します。

1.更新

更新は以下になります。

<?php
$sql = 'update テーブル名 set name =:name where id = :value';
$stmt = $db->prepare($sql);

$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':value', 1, PDO::PARAM_INT);

$stmt->execute();
?>

3.削除

削除は以下になります。

<?php
$sql = 'DELETE FROM テーブル名 where id = :delete_id';
$stmt = $db->prepare($sql);
$stmt -> bindParam(':delete_id', $value, PDO::PARAM_INT);
$stmt -> execute();
?>

以上が、挿入、更新、削除の方法になります。

それでは

MySQLで大きい数値の表示とGROUP BYで複数指定の方法

MySQL

MySQLを使っていて覚えた技術を記載いたします。

1.表示する数値を3桁でカンマ区切りにする

総売上など、ある程度大きい数値を出力するSQLを作った際に、そのまま表示すると見にくくなります。

SELECT
        total_sales as '総売上'

総売上
10000000

表示する数値を3桁でカンマ区切りに表示する方法を調べました。 使うのはFORMATです。FORMATを使うだけで3桁区切りになります。 小数点以下がなければ第二引数は0にしましょう。

SELECT
        FORMAT(total_sales , 0) AS '総売上'

総売上
10,000,000

2.GROUP BYを2つ使う　○○ごとで○○ごとのデータを出したいとき

年別で都道府県ごとの売上を表示するSQL文を作ることになったとします。

まず以下のようなテーブルがあると仮定します。

customersテーブル
     顧客名   都道府県ID
+---+-------+--------------+
|id |name   |prefecture_id |
+---+-------+--------------+
|1  |tanaka | 1            |
|2  |yamada | 2            |
+---+-------+--------------+

prefecturesテーブル
+---+-------+
|id |name   |
+---+-------+
|1  |北海道 |
|2  |青森   |
+---+-------+

ordersテーブル
      顧客id      注文日          合計金額
+---+------------+--------------+-------------+
|id |customer_id |order_date    |total_amount |
+---+------------+--------------+-------------+
|1  | 2          |2016-05-02    | 10000       |
|2  | 1          |2017-08-24    | 2000        |
+---+------------+--------------+-------------+

では方法ですが、 GROUP BYに複数のフィールドを指定することができます。GROUP BYの後にフィールドをカンマ区切りで記載します。 例： [GROUP BY hoge , fuga]

実際に書くと以下のようになります。

SELECT
        DATE_FORMAT(orders.order_date,'%Y') AS '注文日_年',
        prefectures.name AS '都道府県',
        sum(orders.total_amount) AS '売上'
FROM
    orders
INNER JOIN
    customers
ON
    orders.customer_id = customers.id
INNER JOIN
    prefectures
ON
    customers.prefecture_id = prefectures.id
GROUP BY
        --年ごとなのでorder_dateをフォーマットしています
        DATE_FORMAT(orders.order_date,'%Y'),
        prefectures.id

結果は以下のようになります。

+----------+------------+--------------+
|注文年     |都道府県    |売上          |
+----------+------------+--------------+
|2016      | 北海道     | 10000        |
|2016      | 青森       | 20000        |
|               ～                     |
|2016      | 沖縄       | 8000         |
|2017      | 北海道     | 30000        |
|2017      | 青森       | 25000        |
|               ～                     |
|2017      | 沖縄       | 12000        |
+----------+------------+--------------+

以下のサイトを参考にいたしました。

FORMAT：数値を整形する

【MySQL】GROUP BY句に複数のカラムを指定する方法 | WEBマスターの知恵ブログ

それでは

SQL　WHERE 1の意味について

MySQL

SQL文でときどき

SELECT
    fuga
FROM
    hoges
WHERE
    1

といった書き方を見かけたのですがどういった意味なのか分からなかったので、調べたので記載いたします。

まず上記のSQL文は

SELECT
    fuga
FROM
    hoges

と結果は変わりません。 [where 1]に関してですが、MySQLでは整数の1はTRUEと同じになります。

そしてWHEREで指定する条件が真ならば、すべての行が選択されるので WHEREを指定しない、[SELECT * FROM hoges]と結果は同じになります。

ではなぜ結果が同じにもかかわらず、[where 1]などする必要があるのかですが、 まずメリットとしましてWHEREに追加する条件を「AND」で書き始めることができます。

以下のような書き方が出来ます。

SELECT
    fuga
FROM
    hoges
WHERE 1
    AND id = 10
    AND rank = 5

このように書くと[WHERE]の条件の編集が楽になります。 WHERE句の条件を何度も変更して試行錯誤するような場合はこう書いておくといいと思います。

そしてもう1つはPHPスクリプトでSQLを組み立てるときに楽になるというところです。 SQLを組み立てるときに「１つ目の条件ならWHEREで指定する」という書き方が面倒なので、 あらかじめ WHERE = 1 という全ての行を選択する条件をつけておいて、 それ以降の条件を足していくようにしておいたほうが楽になります。

以下のサイトを参考にいたしました。

SQL: WHERE 1 の意味 - エラーの向こうへ

phpMyAdminのSELECT文に付加される「WHERE 1」とは

現在CakePHPでデータを検索して表示するアプリを作っているので スクリプトでDBからデータを持ってくる処理を書いたら再度 記載いたします。

それでは

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')

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

それでは

CakePHPでSession情報を取得する

CakePHP

CakePHPで記事を投稿するアプリケーションを作っていて Authを使ったユーザーログイン機能を実装したのですがログイン中ユーザーが記事を投稿する事が出来ておらず、 今回記事を投稿する際Sessionの情報からユーザーを判断する処理を作ったので手順書等を記載いたします。

つまりログインしているユーザーが記事を投稿するようにいたしました。 Authでログイン機能を作っている前提で進めていきます。

1.Sessionを確認する、idを取得する

まずログインしている状態でdebugkitの「Session」を確認すると[Auth][User]にユーザー情報が入っています。 記事投稿の際にこの情報をとってきて使用したいと思います。

Session情報を取得するには以下の方法があります

$this->Session->read('session_name');

ユーザーのidを取得してログイン中のユーザーを判断したいときはSessionの中の[id]を取得する必要があります。 Sessionの中身が階層構造になっていて[Auth][User][id]という階層の場合は

$user_id = $this->Session->read('Auth.User.id');

というふうに書くことでidを取得できます。

2.記事投稿の際にログイン中ユーザーのidを代入する

記事のControlerのaddで 直接idにログイン中ユーザーのidを代入していきます。

//ログイン中ユーザーのidを取得する
$user_id = $this->Session->read('Auth.User.id');
//記事データのユーザーIDにログイン中ユーザーのidを代入する
$this->request->data['Article']['user_id'] = $user_id;

以上でログインしているユーザーが記事を投稿できるようになりました。

以下のサイトを参考にいたしました。 CakePHPでSessionを使用する方法 « CakePHP « 技術ブログ « 株式会社プロネット | WEBシステム開発20年以上の実績

それでは