2017-08-31

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

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

それでは

2017-08-29

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();
?>

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

それでは

2017-08-24

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マスターの知恵ブログ

それでは

2017-08-22

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からデータを持ってくる処理を書いたら再度記載いたします。

それでは

2017-08-18

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

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

それでは

2017-08-10

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年以上の実績

それでは

2017-08-10

SQL小数点の表示とtimestamp

MySQL

SQLを扱っていて、小数点の表示とtimestampを扱ったので、使い方等記載いたします。

1.小数点の指定

データの平均を出す計算をするときなどで、小数第何位まで表示するか指定したいときがあると思うのですがそういったときに使えるのが[DATE_FORMAT]です。使い方としましては以下のようになります。

hoge = 27 fuga = 7

WHERE
        hoge/fuga
3.7142.......

//小数第1位まで表示する
WHERE
        FORMAT(hoge/fuga , 1)

3.7

//小数第3位まで表示する
WHERE
        FORMAT(hoge/fuga , 3)
3.714

こちらで小数点をいくつまで表示するかを指定できます。

2.timestampの使い方

SQLで日にちを扱う型でtimestampというものがあるのですが、こちらの構造は以下のようになっています。

yyyy-mm-dd hh:mm:ss

年月日と時分秒が入っています。なので例えばtimestamp型の[order_date]というフィールドがあったとして、注文日時が2017年1月1日～2017年6月30日までのデータを取得したいときに

WHERE
        order_date between '2017-01-01' and '2017-06-30'

という検索方法ですと、時間を無視しているので正しいデータを取得できません。

入力値を年月日時分秒にするか、どうしても年月日だけで検索したいときは [DATE_FORMAT]を使いましょう。

WHERE
        DATE_FORMAT(order_date , '%Y%m%d') between '2017-01-01' and '2017-06-30'

よく分からないという方は、まずは実際にSQLを動かして検証すると良いと思います。私もそうして理解しました。

それでは

1.挿入

1.更新

3.削除

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

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

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

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

1.小数点の指定

2.timestampの使い方

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