MySQLの外部結合の条件について
SQLの制作で結合を使うのですが外部結合を使う際の注意と仕様を今一度まとめたいと思います。
以下のようなテーブルがあると仮定いたします。
customersテーブル 顧客名 +---+-------+ |id |name | +---+-------+ |1 |yamada | |2 |tanaka | |3 |satou | +---+-------+ customer_historiesテーブル 顧客id 媒体id +---+------------+---------+ |id |customer_id |media_id | +---+------------+---------+ |1 | 1 |1 | |2 | 2 |1 | |3 | 2 |3 | +---+------------+---------+ mediaテーブル 媒体名 +---+-------+ |id |name | +---+-------+ |1 |aaa | |2 |bbb | |3 |ccc | +---+-------+
この時customersテーブルとmediaテーブルを結合したいときにJOINを使うのですが 全ての顧客データもしくは全ての媒体データを出力する必要がある場合は LEFT JOIN もしくは RIGHT JOIN を使うことになるのですが その場合customersテーブルかmediaテーブルのどちらかのデータしか全てを取得することが出来ません。
SQL文は以下のようになります。
customersをすべて取得
SELECT * FROM customers LEFT JOIN customer_histories ON customer_histories.customer_id = customers.id LEFT JOIN media ON customer_histories.media_id = media.id
検索結果は以下になります。
顧客名 顧客id 媒体id 媒体名 +---+-------+---+------------+---------+---+-------+ |id |name |id |customer_id |media_id |id |name | +---+-------+---+------------+---------+---+-------+ |1 |yamada |1 | 1 |1 |1 |aaa | |2 |tanaka |2 | 2 |1 |1 |aaa | |2 |tanaka |3 | 2 |3 |3 |ccc | |3 |satou | | | | | | +---+-------+---+------------+---------+---+-------+
mediaテーブルをすべて取得
SELECT * FROM media LEFT JOIN customer_histories ON customer_histories.media_id = media.id LEFT JOIN customers ON customer_histories.customer_id = customers.id
検索結果は以下になります。
媒体名 顧客id 媒体id 顧客名 +---+-------+---+------------+---------+---+-------+ |id |name |id |customer_id |media_id |id |name | +---+-------+---+------------+---------+---+-------+ |1 |aaa |1 | 1 |1 |1 |yamada | |1 |aaa |2 | 2 |1 |2 |tanaka | |2 |bbb | | | | | | |3 |ccc |3 | 3 |3 |3 |satou | +---+-------+---+------------+---------+---+-------+
といった形になります。 必要なデータによって結合の順番やFROMに何を指定するかを変更する必要があります。
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
以上となります。
jQuery でセレクターの指定2
前回jQueryでセレクター指定の記事を書いたのですが まだセレクターについて書くことがあったので記載していきます。
前回の記事はこちらになります。 nonaka-katuma-hal.hatenablog.com
初期状態はこのようになっているものとします。
HTML
<ul> <li>テキストテキスト</li> <li>テキストテキスト</li> <li>テキストテキスト</li> <li>テキストテキスト</li> </ul>
1.ユニバーサルセレクター
全ての要素を選択するもので*(アスタリスク)で記述します。
HTML
<ul> <li><a>テキスト</a>テキスト</li> <li><a>テキストテキスト</a></li> <li><a>テキストテキスト</a>テキスト</li> <li>テキストテキスト</li> </ul>
$(function() { $("li *").css("color","red"); });
li要素内に含まれる全ての要素、つまりaタグで囲まれたものが赤色になります。
2.グループセレクター
複数のセレクターを,(カンマ)区切りで並べて指定できるのがグループセレクターです。 それぞれのセレクターに対する命令をまとめて記述出来ます。
HTML
<ul> <li id="first">テキストテキスト</li> <li id="second">テキストテキスト</li> <li id="third">テキストテキスト</li> <li id="fourth">テキストテキスト</li> </ul>
$(function() { $("#second , #fourth").css("color","red"); });
id属性がsecondの2番目とfourthの4番目のli要素が赤色になります。
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>
$(function() { $("#first > a").css("color","red"); });
1行目2行目のidがfirstの直下のaタグに囲まれた文字だけ赤色になります。 3行目のaタグに囲まれた文字はdivの直下になっているので赤色になりません。
4.隣接セレクター
特定の要素の次に出現する要素を選択できるのが「隣接セレクター」です。 隣接セレクターは隣接する要素を+(プラス)で指定します。
HTML
<ul> <li id="first">テキストテキスト</li> <li id="second">テキストテキスト</li> <li id="third">テキストテキスト</li> <li id="fourth">テキストテキスト</li> </ul>
$(function() { $("#second + li").css("color","red"); });
id属性がsecondの要素の次に出現するli要素、つまり3行目のli要素が赤色になります。
これらのセレクターをどんどん使っていきたいと思います。
jQuery でセレクターの指定
先日jQueryでアコーディオンの制作をして記事を書いたのですが その前にセレクターの指定から書くべきだなと思ったので今回はjQueryのセレクターについて記載していきます。
HTMLのどの要素を操作するかをセレクターで指定していきます。 本編に入る前に本記事内で
$("セレクター").css("color","red");
このような記載の仕方をしますが[.css()]とはcssの[color]プロパティの値を[red]にするという意味になります。
1.要素セレクター
まずは簡単に要素セレクターから説明していきます。 HTMLはこのようになっております。
<ul> <li>テキストテキスト</li> <li>テキストテキスト</li> <li>テキストテキスト</li> <li>テキストテキスト</li> </ul>
こちらに
$(function() { $("li").css("color","red"); });
要素の[li]をセレクターで指定しますとli要素の部分全てが変更されます
2.IDセレクター
続いてはid属性をもつ要素を対象にします。
HTML
<ul> <li id="first">テキストテキスト</li> <li id="second">テキストテキスト</li> <li id="third">テキストテキスト</li> <li id="fourth">テキストテキスト</li> </ul>
id属性の値に#を付けたものをセレクターとして利用します。
$(function() { $("#second").css("color","red"); });
id値にsecondが設定されたli要素が赤くなります。
3.クラスセレクター
続いてはクラス属性をもつ要素を対象にします。
HTML
<ul> <li class="first">テキストテキスト</li> <li class="second">テキストテキスト</li> <li class="third">テキストテキスト</li> <li class="second">テキストテキスト</li> </ul>
クラス属性の値に.(ドット)を付けたものをセレクターとして利用します。
$(function() { $(".second").css("color","red"); });
クラス属性にsecondが設定された複数のli要素が赤くなります。
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>
$(function() { $(".first p").css("color","red"); });
class属性がfirstで要素の内側にあるp要素だけが赤くなります。
といったところで一旦は終了したいと思います。 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; }
この状態でブラウザ確認するとこうなります
こちらを「タイトル〇」と書かれた部分を押して「コンテンツ~」を表示するようにいたします。
jsファイルは以下のように作ります。
//メニューのアコーディオン $(function() { //デフォルトは全てのタイトルを非表示にします $('#panel > dd').hide(); //メニューのクリックを取得します。 $('#panel > dt').click(function(){ //クリックしたタイトルを開閉します $('+dd', this).slideToggle(200); }); });
これで「タイトル〇」と書かれた部分をクリックすると「コンテンツ~」が 表示されるようになります、もう一度「タイトル〇」を押すと「コンテンツ~」が閉じます。
それでは
CakePHP cssが反映されない時の対処方法
CakePHPでアプリの制作していてcssを編集してブラウザで確認した際に編集点が反映されていないという事があり、 原因を調べて対応したので手順等を記載いたします。
まずは原因ですが もしかしたらブラウザがcssをキャッシュしているのではないか? という記事がいくつかあったので、参考にいたしました。
解決方法としまして、app/Config/core.phpの中に
Configure::write('Asset.timestamp', true);
という一文がありましてコメントアウトされているのですが このコメントアウトを外したところ、ブラウザのキャッシュを無効化されて cssの編集が反映されました。
もし急にcssが反映されずに困ったら参考にしていただければ幸いです。
以下のサイトを参考にいたしました。
それでは
SQL サブクエリの扱い方について復習
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'
今までのブログでは特に書かずに進めていたところなのですが 結構エラーが出たり、質問されたりしたので一度まとめてみました。
それでは