2017-09-26

MySQLで初回購入後の顧客データ

SQLで初回購入の年月と初回流入の媒体を指定して顧客を分析してその顧客の月毎かつ媒体ごとの売上とユニークな顧客数を出すという処理を制作したので手順を記載いたします。今回は初回購入月と初回流入媒体を指定することを前提とします。

まずは以下のようにデータがあるものとします。

customersテーブル
     顧客名  
+---+-------+
|id |name   |
+---+-------+
|1  |yamada |
|2  |tanaka |
|3  |satou  |
|4  |katou  |
|5  |ito    |
+---+-------+


customer_historiesテーブル
      顧客id         注文日          購入回数         注文id      媒体id
+---+------------+----------------+------------------+---------+---------+
|id |customer_id |date_of_purchase|number_of_purchase|order_id |media_id |
+---+------------+----------------+------------------+---------+---------+
|1  | 5          |2016-12-01      | 1                |1        |1        |
|2  | 1          |2017-01-01      | 1                |2        |1        |
|3  | 2          |2017-01-01      | 1                |3        |1        |
|4  | 3          |2017-01-01      | 1                |4        |1        |
|5  | 4          |2017-01-01      | 1                |5        |2        |
|6  | 5          |2017-02-01      | 2                |6        |1        |
|7  | 1          |2017-02-01      | 2                |7        |2        |
|8  | 2          |2017-02-01      | 2                |8        |2        |
|9  | 4          |2017-02-01      | 2                |9        |2        |
|10 | 1          |2017-03-01      | 3                |10       |3        |
|11 | 1          |2017-03-01      | 4                |11       |         |
|12 | 3          |2017-03-01      | 2                |12       |3        |
|13 | 3          |2017-03-01      | 3                |13       |         |
|14 | 4          |2017-03-01      | 3                |14       |2        |
|15 | 3          |2017-03-01      | 4                |15       |3        |
+---+------------+----------------+------------------+---------+---------+


ordersテーブル
      顧客id         注文日          金額
+---+------------+--------------+--------------+
|id |customer_id |order_date    |  amount      |
+---+------------+--------------+--------------+
|1  | 5          |2016-12-01    | 100          |
|2  | 1          |2017-01-01    | 100          |
|3  | 2          |2017-01-01    | 100          |
|4  | 3          |2017-01-01    | 100          |
|5  | 4          |2017-01-01    | 100          |
|6  | 5          |2017-02-01    | 100          |
|7  | 1          |2017-02-01    | 100          |
|8  | 2          |2017-02-01    | 100          |
|9  | 4          |2017-02-01    | 100          |
|10 | 1          |2017-03-01    | 100          |
|11 | 1          |2017-03-01    | 100          |
|12 | 3          |2017-03-01    | 100          |
|13 | 3          |2017-03-01    | 100          |
|14 | 4          |2017-03-01    | 100          |
|15 | 3          |2017-03-01    | 100          |
+---+------------+--------------+--------------+

mediaテーブル

+---+-------+
|id |name   |
+---+-------+
|1  |aaa    |
|2  |bbb    |
|3  |ccc    |
+---+-------+

ではSQLの制作に入っていきますいくつかに分割して説明いたします。

1.サブクエリ

まずは初回購入の年月と初回流入の媒体を指定して顧客を絞り込むためのサブクエリを作ります

FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id
    FROM
        customer_histories
    INNER JOIN
        media
    ON
        customer_histories.media_id = media.id
    WHERE
       --2017年の1月に初回購入した顧客で絞ります
       customer_histories.date like '2017-01-%'
       AND
       --初回流入媒体がaaaの顧客に絞ります
       media.name = 'aaa'
       AND
       --初回購入に絞るためにnumber_of_purchase = 1にします
       customer_histories.number_of_purchase = 1
    ) AS ch
ON
orders.customer_id = ch.customer_id

2.表示条件

続いては絞り込んだ顧客の月毎かつ直接流入媒体ごとに出力する条件を作ります

LEFT JOIN
     --媒体なしの場合も含めるので外部結合にいたします。
     media
ON
     orders.media_id = media.id
GROUP BY
     --年月ごと
     DATE_FORMAT(orders.date ,'%Y%m'),
     --媒体ごと
     media.id

3.表示項目

最後に出力する項目（カラム）を指定します。

SELECT
    DATE_FORMAT(orders.date,"%Y-%m") AS '購入年月',
    media2.id AS '媒体id',
    media2.name AS '媒体名',
    --ユニークな顧客の人数を出すためにdistinctを使います。
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.amount),0) AS '総売上'

こちらすべてつなげると以下のようになります。

SELECT
    DATE_FORMAT(orders.date,"%Y-%m") AS '購入年月',
    media2.id AS '媒体id',
    media2.name AS '媒体名',
    FORMAT(count(distinct orders.customer_id),0) AS '顧客数',
    FORMAT(sum(orders.amount),0) AS '総売上'
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.customer_id
    FROM
        customer_histories
    LEFT JOIN
        media
    ON
        customer_histories.media_id = media.id
    WHERE
       customer_histories.date like '2017-01-%'
       AND
       media.name = 'aaa'
       AND
       customer_histories.number_of_purchase = 1
    ) AS ch
ON
orders.customer_id = ch.customer_id
LEFT JOIN
     media
ON
     orders.media_id = media.id
GROUP BY
     DATE_FORMAT(orders.date ,'%Y%m'),
     media.id

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

購入年月 媒体id    媒体名   顧客数   総売上
2017-1      1       aaa     3       300
2017-2      2       bbb     2       200
2017-3                      2       200
2017-3      3       ccc     2       300

以上となります。

2017-09-21

jQuery でセレクターの指定2

前回jQueryでセレクター指定の記事を書いたのですがまだセレクターについて書くことがあったので記載していきます。

前回の記事はこちらになります。 nonaka-katuma-hal.hatenablog.com

初期状態はこのようになっているものとします。

HTML

<ul>
  <li>テキストテキスト</li>
  <li>テキストテキスト</li>
  <li>テキストテキスト</li>
  <li>テキストテキスト</li>
</ul>

f:id:nonaka-katuma-hal:20170921173002j:plain

1.ユニバーサルセレクター

全ての要素を選択するもので*(アスタリスク)で記述します。

HTML

<ul>
  <li><a>テキスト</a>テキスト</li>
  <li><a>テキストテキスト</a></li>
  <li><a>テキストテキスト</a>テキスト</li>
  <li>テキストテキスト</li>
</ul>

jQuery

$(function() {
  $("li *").css("color","red");
});

li要素内に含まれる全ての要素、つまりaタグで囲まれたものが赤色になります。

f:id:nonaka-katuma-hal:20170921173800j:plain

2.グループセレクター

複数のセレクターを,(カンマ)区切りで並べて指定できるのがグループセレクターです。それぞれのセレクターに対する命令をまとめて記述出来ます。

HTML

<ul>
  <li id="first">テキストテキスト</li>
  <li id="second">テキストテキスト</li>
  <li id="third">テキストテキスト</li>
  <li id="fourth">テキストテキスト</li>
</ul>

jQuery

$(function() {
  $("#second , #fourth").css("color","red");
});

id属性がsecondの2番目とfourthの4番目のli要素が赤色になります。

f:id:nonaka-katuma-hal:20170921175959j:plain

3.子セレクター

特定の要素の直下に配置された要素を選択できるのが「子セレクター」です。子セレクターは親要素と子要素を「>」で結びます。

HTML

<ul>
  <li id="first"><a>テキスト</a>テキスト</li>
  <li id="first"><a>テキストテキスト</a></li>
  <li id="first"><div>テキスト<a>テキスト</a></div></li>
  <li id="second"><a>テキスト</a>テキスト</li>
</ul>

jQuery

$(function() {
  $("#first > a").css("color","red");
});

1行目2行目のidがfirstの直下のaタグに囲まれた文字だけ赤色になります。 3行目のaタグに囲まれた文字はdivの直下になっているので赤色になりません。

f:id:nonaka-katuma-hal:20170921183131j:plain

4.隣接セレクター

特定の要素の次に出現する要素を選択できるのが「隣接セレクター」です。隣接セレクターは隣接する要素を+（プラス）で指定します。

HTML

<ul>
  <li id="first">テキストテキスト</li>
  <li id="second">テキストテキスト</li>
  <li id="third">テキストテキスト</li>
  <li id="fourth">テキストテキスト</li>
</ul>

jQuery

$(function() {
  $("#second + li").css("color","red");
});

id属性がsecondの要素の次に出現するli要素、つまり3行目のli要素が赤色になります。

f:id:nonaka-katuma-hal:20170921182345j:plain

これらのセレクターをどんどん使っていきたいと思います。

2017-09-19

jQuery でセレクターの指定

先日jQueryでアコーディオンの制作をして記事を書いたのですがその前にセレクターの指定から書くべきだなと思ったので今回はjQueryのセレクターについて記載していきます。

HTMLのどの要素を操作するかをセレクターで指定していきます。本編に入る前に本記事内で

$("セレクター").css("color","red");

このような記載の仕方をしますが[.css()]とはcssの[color]プロパティの値を[red]にするという意味になります。

1.要素セレクター

まずは簡単に要素セレクターから説明していきます。 HTMLはこのようになっております。

<ul>
    <li>テキストテキスト</li>
    <li>テキストテキスト</li>
    <li>テキストテキスト</li>
    <li>テキストテキスト</li>
</ul>

f:id:nonaka-katuma-hal:20170919172257j:plain

こちらに

$(function() {
    $("li").css("color","red");
});

要素の[li]をセレクターで指定しますとli要素の部分全てが変更されます

f:id:nonaka-katuma-hal:20170919172730j:plain

2.IDセレクター

続いてはid属性をもつ要素を対象にします。

HTML

<ul>
    <li id="first">テキストテキスト</li>
    <li id="second">テキストテキスト</li>
    <li id="third">テキストテキスト</li>
    <li id="fourth">テキストテキスト</li>
</ul>

id属性の値に#を付けたものをセレクターとして利用します。

jQuery

$(function() {
    $("#second").css("color","red");
});

id値にsecondが設定されたli要素が赤くなります。

f:id:nonaka-katuma-hal:20170919173555j:plain

3.クラスセレクター

続いてはクラス属性をもつ要素を対象にします。

HTML

<ul>
    <li class="first">テキストテキスト</li>
    <li class="second">テキストテキスト</li>
    <li class="third">テキストテキスト</li>
    <li class="second">テキストテキスト</li>
</ul>

クラス属性の値に.(ドット)を付けたものをセレクターとして利用します。

jQuery

$(function() {
    $(".second").css("color","red");
});

クラス属性にsecondが設定された複数のli要素が赤くなります。

f:id:nonaka-katuma-hal:20170919174441j:plain

4.子孫セレクター

複数のセレクターをスペース区切りで指定することにより、特定の要素の中にある要素をさらに絞り込めます。

HTML

<ul>
  <li class="first"><p>テキストテキスト</p></li>
  <li class="second"><p>テキストテキスト</p></li>
  <li class="first"><p>テキストテキスト</p></li>
  <li class="first">テキストテキスト</li>
</ul>

jQuery

$(function() {
    $(".first p").css("color","red");
});

class属性がfirstで要素の内側にあるp要素だけが赤くなります。

f:id:nonaka-katuma-hal:20170919175323j:plain

といったところで一旦は終了したいと思います。 jQueryに関してはまだまだ勉強してブログを更新していく予定です

それでは

2017-09-14

jQueryでアコーディオンの制作

jQuery

アプリの制作をしていまして、アコーディオンを使ったメニューを制作したので手順等を記載いたします。

まずはHTMLは以下のように記載いたしました。

<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta charset="UTF-8">
        <title>アコーディオン</title>
        <script type="text/javascript" src="js/jquery-3.2.0.min.js"></script>
        <script type="text/javascript" src="js/menu.js"></script>
        <link rel="stylesheet" href="css/style.css">
    </head>
    <body>
        <div class="menu">
            <dl id="panel">
                <dt>タイトル1</dt>
                <dd>コンテンツコンテンツコンテンツコンテンツ</dd>
                <dt>タイトル2</dt>
                <dd>コンテンツコンテンツコンテンツコンテンツ</dd>
                <dt>タイトル3</dt>
                <dd>コンテンツコンテンツコンテンツコンテンツ</dd>
                <dt>タイトル4</dt>
                <dd>コンテンツコンテンツコンテンツコンテンツ</dd>
                <dt>タイトル5</dt>
                <dd>コンテンツコンテンツコンテンツコンテンツ</dd>
                <dt>タイトル6</dt>
                <dd>コンテンツコンテンツコンテンツコンテンツ</dd>
            </dl>
        </div>
    </body>
</html>

CSSは以下のようにしておきます。

.menu {
    border: solid #56df55;
}
.menu dt {
    background-color: #afeeee;
}
.menu dd {
    background-color: #e0ffff;
}

この状態でブラウザ確認するとこうなります

f:id:nonaka-katuma-hal:20170914175333j:plain

こちらを「タイトル〇」と書かれた部分を押して「コンテンツ～」を表示するようにいたします。

jsファイルは以下のように作ります。

//メニューのアコーディオン
$(function() {
    //デフォルトは全てのタイトルを非表示にします
    $('#panel > dd').hide();

    //メニューのクリックを取得します。
    $('#panel > dt').click(function(){
        //クリックしたタイトルを開閉します
        $('+dd', this).slideToggle(200);
    });
});

これで「タイトル〇」と書かれた部分をクリックすると「コンテンツ～」が表示されるようになります、もう一度「タイトル〇」を押すと「コンテンツ～」が閉じます。

f:id:nonaka-katuma-hal:20170914175717j:plain

それでは

2017-09-12

CakePHP cssが反映されない時の対処方法

CakePHP

CakePHPでアプリの制作していてcssを編集してブラウザで確認した際に編集点が反映されていないという事があり、原因を調べて対応したので手順等を記載いたします。

まずは原因ですがもしかしたらブラウザがcssをキャッシュしているのではないか？という記事がいくつかあったので、参考にいたしました。

解決方法としまして、app/Config/core.phpの中に

Configure::write('Asset.timestamp', true);

という一文がありましてコメントアウトされているのですがこのコメントアウトを外したところ、ブラウザのキャッシュを無効化されて cssの編集が反映されました。

もし急にcssが反映されずに困ったら参考にしていただければ幸いです。

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

normalblog.net

それでは

2017-09-08

SQL　サブクエリの扱い方について復習

MySQL

SQLを書いていてサブクエリを使うのですが基本的な使い方のおさらいをしておきたいと思ったので記載いたします。

今回はデータうんぬんではなく、サブクエリを使う際の注意になります。

まず以下のようなサブクエリの使い方をしているとします

SELECT
        customers.name,
        ch.birthday,
        ch.number_of_purchases
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.number_of_purchases,
        customers.name
    FROM
        customer_histories
    INNER JOIN
        customers
    ON
        customers.id = customer_histories.customer_id
    WHERE
        customers.birthday <= '1990-01-01'
    ) AS ch
ON
    orders.customer_id = ch.customer.id
WHERE
    orders.date >= '2017-05-01'

SQLの意味自体は一旦置いておいて、上記のSQLだとエラーが出ます。原因は2つありまして、どちらも最初のSELECTの中にあります。

まず一つ目は [customers.name]です。こちらエラーになる原因ですが、customersのnameがほしいんでしょうが customersの結合はサブクエリの中で行われています。書き方としましてはサブクエリの最後に　AS ch　とあるので [ch.name]が正しい書き方になります。

続いて2つ目は [ch.birthday]になりますこちらcustomersのbirthdayを表示仕様としているのですがサブクエリのSELECTにcustomers.birthdayがありません。なのでbirthdayを呼び出せずエラーになります。サブクエリのSELECTにcustomers.birthdayを入れるとオーケーです。

以下正しく修正したSQLになります。

SELECT
        ch.name,
        ch.birthday,
        ch.number_of_purchases
FROM
    orders
INNER JOIN
    (
    SELECT
        customer_histories.number_of_purchases,
        customers.name,
        customers.birthday
    FROM
        customer_histories
    INNER JOIN
        customers
    ON
        customers.id = customer_histories.customer_id
    WHERE
        customers.birthday <= '1990-01-01'
    ) AS ch
ON
    orders.customer_id = ch.customer.id
WHERE
    orders.date >= '2017-05-01'

今までのブログでは特に書かずに進めていたところなのですが結構エラーが出たり、質問されたりしたので一度まとめてみました。

それでは

2017-09-05

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つです。

まずは初回購入の媒体ごとに分かれている
登録月ごとに分かれているので登録月が3月の customers.id 3[satou]と4[ito]の2人が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になります。

それでは

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

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

MySQLで初回購入後の顧客データ

1.サブクエリ

2.表示条件

3.表示項目

jQuery でセレクターの指定2

1.ユニバーサルセレクター

2.グループセレクター

3.子セレクター

4.隣接セレクター

jQuery でセレクターの指定

1.要素セレクター

2.IDセレクター

3.クラスセレクター

4.子孫セレクター

jQueryでアコーディオンの制作

CakePHP cssが反映されない時の対処方法

SQL　サブクエリの扱い方について復習

SQL　データの集計に関して