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を動かして検証すると良いと思います。私もそうして理解しました。

それでは

2017-08-03

MySQLで日付に関する処理

日付の処理の実装

MySQLでとある月から〇ヶ月後のデータを取得するなどを行ったので手順書等を記載いたします。

1.DATE_FORMAT

日付データから特定の数値だけ必要な時はDATE_FORMATを使います。例えばフィールド[birthday]に[1994-05-23]という形式で誕生日のデータが入っていて年と月の情報だけが欲しい場合以下のように記述することで年と月のデータを取得できます。

(DATE_FORMAT(birthday,'%Y%m')

これで先ほどのデータが [1994-05]となります。

例えばこれを結合条件で利用するとなると以下のようになります。

(DATE_FORMAT(hoges.day,'%Y%m') = (DATE_FORMAT(customers.birthday,'%Y%m')

まあ誕生日を結合条件にすることがあるかは疑問ですが、、、

2.DATE_ADD

とある日付のデータがあり、その〇日後や〇ヶ月後のデータを取得したいときにDATE_ADDを使用します。先ほどと同じように、フィールド[purchase_date]に[2017-06-01]という形式で購入日データが入っているとします使い方は以下のようになります。

・3日後を取得したい。

DATE_ADD(purchase_date, INTERVAL 3 DAY)

DAYで日にち計算をします。データは[2017-06-04]になります。

・5ヶ月前を取得したい

DATE_ADD(purchase_date, INTERVAL -5 MONTH)

MONTHで月計算をします。データは[2017-01-01]になります

・来月を取得したい

DATE_ADD(LAST_DAY(CURDATE()),INTERVAL 1 DAY)

LAST_DAYで月末の日付を取得できます CURDATE()で現在の年月日を(0000-00-00)の形式で取得できます 1 DAYで次の日なので翌月のデータを取得できます。

.3 DATE_FORMAT と DATE_ADD の組合せ

さて続いては2ヶ月後のデータで月だけを取得したい時の処理を記載いたします。

 DATE_FORMAT(DATE_ADD(purchase_date , INTERVAL 2 MONTH),'%m')

DATE_ADDはデータが[0000-00-00]の形式である必要があるらしく、まず先に2ヶ月後のデータを取得してそのデータを DATE_FRMATで月のデータのみにいたします。

・来月の月のデータだけを取得するには以下のようになります。

 DATE_FORMAT( DATE_ADD(LAST_DAY(CURDATE()),INTERVAL 1 DAY) , '%m')

来月が誕生日の人のデータをとるときなどに使えます。

それでは

2017-08-01

MySQLでデータの結合と月ごとのデータの出力

MySQLでサブクエリやDATE_FORMATを使用したので、手順書等を記載いたします。

1.月毎にデータを出力する

データを月毎の結果で出力したい場合は DATE_FORMTというものを使います。

使い方としましては

SELECT
    DATE_FORMAT(fuga_time, '%Y-%m'),
    COUNT(*)
FROM
    hoges
GROUP BY
    DATE_FORMAT(fuga_time, '%Y%m');

といった形式で使用します。例えば期間を指定すると、その期間で結果が表示されます。

SELECT
    DATE_FORMAT(fuga_time, '%Y-%m'),count(*)
FROM
    hoges
WHERE
    fuga_time between '2016-01-01' and '2016-12-31'
GROUP BY
    DATE_FORMAT(fuga_time, '%Y-%m')

こちらですと2016年1月から12月のデータが月毎で表示されます。

2.条件指定したテーブルを結合する

続いてはテーブルをWHEREで検索したものを他のテーブルに結合する手順を紹介いたします。まずは単純にテーブルで検索を行います。

SELECT
    id
FROM
    customers
WHERE
    birthday between '1970-01-01' and '1999-12-31'

こちらの検索結果のテーブル情報を他のテーブルに結合したいときはサブクエリを使用します。

SELECT
    c.name , count(*) , sum(orders.price)
FROM
    orders
INNER JOIN
( SELECT
    id
FROM
    customers
WHERE
    birthday between '1970-01-01' and '1999-12-31') AS c
ON
    orders.customer_id = c.id

このようにすることでまとまったデータを他のテーブルに結合することが出来ます。

それでは

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

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

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

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

1.小数点の指定

2.timestampの使い方

日付の処理の実装

1.DATE_FORMAT

2.DATE_ADD

.3 DATE_FORMAT と DATE_ADD の組合せ

1.月毎にデータを出力する

2.条件指定したテーブルを結合する

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