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

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

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

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

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

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

customer_analyseテーブル
      顧客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_analyse
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_analyse
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で記事を投稿するアプリケーションを作っていて 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年以上の実績

それでは

SQL小数点の表示とtimestamp

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

1.小数点の指定

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

hoge = 27 fuga = 7

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

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

3.7

//小数第3位まで表示する
WHERE
        DATE_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を動かして検証すると良いと思います。 私もそうして理解しました。

それでは

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

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

それでは

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

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

それでは

HTML5/CSS3を基礎から学ぶ

HTML5/CSS3の使い方等確認

現在HTML5やCSS3のコーディングの学習をしていまして そこで改めて学んだ知識等記載していきたいと思います。

ボックスモデル

CSSのボックスモデルについて記載致します。 HTML文書内に配置された要素は4つの領域をもち、階層があります。 一番内側から説明していきます。

まずはコンテンツエリアです。 単純にコンテンツ(内容)が表示されるエリアになります。

続いてパディングエリアです。 パディングエリアは要素の内側の余白までのことで、要素の外側とコンテンツとの間隔を調整できます。

続いてボーダーエリアです。 ボーダーエリアは要素の枠線までを含んだエリアです。 要素に背景色や背景画像を指定した場合、このボーダーエリアまで背景が適用されます。

最後にマージンエリアです。 マージンエリアは要素の外側の余白のことで、隣接するほかの要素との間隔を調整できます。

とても基本なところですが、最近のアプリ制作で意識していなかったので 自戒の念を込めて記載いたしました。

マージンの詳細

ボックスモデルの中でもmarginについて詳しく説明を記載いたします。

marginプロパティは、上下左右の余白をまとめて指定できます。 値を1つから4つまで指定出来て、値と個数の順番によって適用される位置が変わります。

//上下左右のmarginが10px
margin: 10px;

//上下のmarginが10px、左右のmarginが20px
margin: 10px 20px;

//上のmarginが10px、左右のmarginが20px、下のmarginが30px
margin: 10px 20px 30px;

//上のmarginが10px、右のmarginが20px、下のmarginが30px、左のmarginが40px
margin: 10px 20px 30px 40px;

こちらも基本的なことなのですが値が三つの場合はどうなるのかなど忘れていました。

このように基本の基本が抜けたままCakePHPでの制作などをしており UIの制作などで苦労していたので 初歩から学ぶことの大切さを実感致しました。

それでは

公開用アプリケーションのGit管理方法

サーバーのアプリをGitで管理する

サーバーでアプリを公開しているのですが、今後アプリをブラッシュアップをしていく予定なので リモートで制作したものをリポジトリに上げて、サーバーのアプリはpullするだけにしたいと思います。

1.サーバーのでGitHubの準備をする

サーバーでPuttyなどを使い、gitをインストールします。

$ git init

続いて公開鍵の作成を致します。

$ ssh-keygen -t rsa -C "[メールアドレス]"
Enter file in which to save the key (/c/fuga/hoge/.ssh/id_rsa): [左に出ているパスで良ければEnter]
Enter passphrase (empty for no passphrase):[任意のパスフレーズ入力]
Enter same passphrase again:[もう一度パスフレーズを入力]

鍵が出来ているか確認します。

$cd ~/.ssh
$ls ←.sshの中身を確認

中に「id_rsa.pub」というものが出来ていると思いますが、これが公開鍵です。 これの中身をコピーしてGitHubに登録します。

公開鍵の中身をコピーする方法としてほかのサイトでは

$ clip < ~/.ssh/id_rsa.pub

というコマンドが紹介されていたのですが、何故か私の環境では実行できなかったので

vim id_rsa.pub

で開いて内容を直接コピーしました。

2.GitHubで設定する

GitHubで右上のアイコンをクリックして「settings」を選択しましょう、続いて「SSH and GPG keys」を押します

右上の「new SSH key」で公開鍵の登録が出来るので Titleに任意のタイトルをつけて keyに先ほどの公開鍵の内容をペーストします「Add SSH key」で登録完了です。 f:id:nonaka-katuma-hal:20170725214658j:plain

3.サーバーでリポジトリからプロジェクトをCloneする

GitHubでサーバーにアップしたいプロジェクトのページで 「Clone or download」を押して表示されるテキストをコピーします。 f:id:nonaka-katuma-hal:20170725215438j:plain

続いてサーバーに戻りプロジェクトをCloneします。

$ git clone [テキストをペースト]

これでプロジェクトをサーバーに入れることが出来ますので 今後はリモートで制作したものをリポジトリにpushしてサーバーでpullしていきましょう。

それでは