- 投稿日：2019-05-06T23:02:23+09:00
MySQLでwindow関数②
はじめに
前回の続きです
色々なwindow関数
一通りリストで書いて、後ほど一つづつ説明していきます。
const, max, min, sum, avgのおなじみの関数の説明は省きます。
関数名 説明 count 行数をカウント max 最大値 min 最小値 sum 合計値 avg 平均値 row_number 行番号 rank 順位づけ(同着後に飛ばす) dense_rank 順位づけ(同着後に飛ばさない) Lag 前の行の値 Lead 後ろの行の値 First_Value 指定したソートキーでの最初の行の値 Last_Value 指定したソートキーでの最後の行の値 row_number
初めの行から現在の行数を返します。
SELECT product_name, amount, date, row_number() OVER() as number FROM product_orders; 結果 +--------------+--------+------------+------------+ | product_name | amount | date | row_bumber | +--------------+--------+------------+------------+ | banana | 3 | 2019-03-01 | 1 | | banana | 2 | 2019-04-01 | 2 | | banana | 4 | 2019-05-01 | 3 | | apple | 8 | 2019-03-01 | 4 | | apple | 2 | 2019-04-01 | 5 | | apple | 5 | 2019-05-01 | 6 | | orange | 4 | 2019-03-01 | 7 | | orange | 1 | 2019-04-01 | 8 | | orange | 3 | 2019-05-01 | 9 | +--------------+--------+------------+------------+PARTITION BYで区切ったら分割されたwindowごとに行数を返します。
SELECT product_name, amount, date, Row_Number() OVER( PARTITION BY product_name ) as sum_amount FROM product_orders; +--------------+--------+------------+------------+ | product_name | amount | date | sum_amount | +--------------+--------+------------+------------+ | apple | 8 | 2019-03-01 | 1 | | apple | 2 | 2019-04-01 | 2 | | apple | 5 | 2019-05-01 | 3 | | banana | 3 | 2019-03-01 | 1 | | banana | 2 | 2019-04-01 | 2 | | banana | 4 | 2019-05-01 | 3 | | orange | 4 | 2019-03-01 | 1 | | orange | 1 | 2019-04-01 | 2 | | orange | 3 | 2019-05-01 | 3 | +--------------+--------+------------+------------+rank, dense_rank
どちらも現在の行の順位を返す関数です。
違いは一つ上の順位のものが複数あった場合に、rankは次の順位を飛ばし、dense_rankは飛ばしません。実際にみてみましょう。
新しいテーブルを作ります。CREATE TABLE stocks ( product_name varchar(255), amount int ); INSERT INTO stocks VALUES ('banana', 3), ('orange', 2), ('apple', 4), ('grape', 8), ('pineapple', 2), ('peach', 5), ('pear', 8), ('melon', 4);rank
商品の個数が多い順に並び替えます。
SELECT product_name, amount, rank() OVER( order by amount DESC ) as grade FROM stocks; 結果 +--------------+--------+-------+ | product_name | amount | grade | +--------------+--------+-------+ | grape | 8 | 1 | | pear | 8 | 1 | | peach | 5 | 3 | | apple | 4 | 4 | | melon | 4 | 4 | | banana | 3 | 6 | | orange | 2 | 7 | | pineapple | 2 | 7 | +--------------+--------+-------+peachやbananaを見ていただければわかると思いますが、一つ上の順位が2つあるのでpeachは3位、bananaは6位になっています。
次にdense_rankを試してみます。
SELECT product_name, amount, dense_rank() OVER( order by amount DESC ) as grade FROM stocks; 結果 +--------------+--------+-------+ | product_name | amount | grade | +--------------+--------+-------+ | grape | 8 | 1 | | pear | 8 | 1 | | peach | 5 | 2 | | apple | 4 | 3 | | melon | 4 | 3 | | banana | 3 | 4 | | orange | 2 | 5 | | pineapple | 2 | 5 | +--------------+--------+-------+rankと違い、同着後のgradeも連番になっていることがわかります。
lag, lead
lagは前の行の指定した値を返します。
下のクエリは一つ前のフルーツのamountを返すクエリです。SELECT product_name, amount, lag(amount) OVER( order by amount DESC ) as before_amount FROM stocks; 結果 +--------------+--------+---------------+ | product_name | amount | before_amount | +--------------+--------+---------------+ | grape | 8 | NULL | | pear | 8 | 8 | | peach | 5 | 8 | | apple | 4 | 5 | | melon | 4 | 4 | | banana | 3 | 4 | | orange | 2 | 3 | | pineapple | 2 | 2 | +--------------+--------+---------------+こんな感じになります。
現在の行のフルーツのamountが次の行のbefore_amountになっています。
grapeは前の行がないのでnullを返しています。一方でleadはというと、
SELECT product_name, amount, lead(amount) OVER( order by amount DESC ) as before_amount FROM stocks; +--------------+--------+---------------+ | product_name | amount | before_amount | +--------------+--------+---------------+ | grape | 8 | 8 | | pear | 8 | 5 | | peach | 5 | 4 | | apple | 4 | 4 | | melon | 4 | 3 | | banana | 3 | 2 | | orange | 2 | 2 | | pineapple | 2 | NULL | +--------------+--------+---------------+結果はlagの逆になります。
また、この二つの関数は第二引数に数字を取ることによってその分だけ前もしくは後ろの行の値を取りことができます。SELECT product_name, amount, lag(amount, 2) OVER( order by amount DESC ) as before_amount FROM stocks; +--------------+--------+---------------+ | product_name | amount | before_amount | +--------------+--------+---------------+ | grape | 8 | NULL | | pear | 8 | NULL | | peach | 5 | 8 | | apple | 4 | 8 | | melon | 4 | 5 | | banana | 3 | 4 | | orange | 2 | 4 | | pineapple | 2 | 3 | +--------------+--------+---------------+first_value, last_value
指定したソートキーでの最初もしくは最後の行の値を返します。
SELECT product_name, amount, date,first_value(amount) OVER( PARTITION BY product_name order by amount DESC ) as before_amount FROM product_orders; +--------------+--------+------------+---------------+ | product_name | amount | date | before_amount | +--------------+--------+------------+---------------+ | apple | 8 | 2019-03-01 | 8 | | apple | 5 | 2019-05-01 | 8 | | apple | 2 | 2019-04-01 | 8 | | banana | 4 | 2019-05-01 | 4 | | banana | 3 | 2019-03-01 | 4 | | banana | 2 | 2019-04-01 | 4 | | orange | 4 | 2019-03-01 | 4 | | orange | 3 | 2019-05-01 | 4 | | orange | 1 | 2019-04-01 | 4 | +--------------+--------+------------+---------------+参考にしたサイト
第89回 WINDOW関数を使ってみる MySQL道普請便り
【MySQL8】【新機能】Window関数がMySQLでも使えるようになったよ。
MySQL王国に黒船（Window関数）がやってきた！
- 投稿日：2019-05-06T22:58:27+09:00
MySQLでwindow関数①
はじめに
先日Udemyでmysqlの基礎講座を受けてSQLの基本的な構文はある程度かけるようになった者です。
次は下の本を読んでもっと勉強するぞー！と意気込んでいましたが、第1章でwindow関数というみたことのないSQLに出会って早速つまづきました...達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
そこで今回はwindow関数について色々調べてみたのでまとめていきたいなーと思います。
window関数とは？
簡単に言いますと、集合関数と同じ集計動作をそれぞれの行に制限範囲で実行するものです。
集約関数とは、GroupBy句で区切られた集合に対しての処理で、「分割」と「集約」をやっています。
一方でwidow関数は分割のみ行って集約はやりません。どういうことか簡単なテーブルを用いて解説していきます。
まず適当にテーブルを作り、レコードを入れます。
CREATE TABLE product_orders ( product_name varchar(255), amount int, date DATE ); INSERT INTO product_orders VALUES ('banana', 3, '2019-03-01'), ('banana', 2, '2019-04-01'), ('banana', 4, '2019-05-01'), ('apple', 8, '2019-03-01'), ('apple', 2, '2019-04-01'), ('apple', 5, '2019-05-01'), ('orange', 4, '2019-03-01'), ('orange', 1, '2019-04-01'), ('orange', 3, '2019-05-01');集約関数とwindow関数の比較
まずは普通にGroup By句で集約してみましょう。
SELECT product_name, sum(amount) FROM product_orders group by product_name; 結果 +--------------+-------------+ | product_name | sum(amount) | +--------------+-------------+ | banana | 9 | | apple | 15 | | orange | 8 | +--------------+-------------+はい。特に不思議なことはありませんね。
group byでproduct_nameをグループ化し、そのamountの合計を出力しています。次にwindow関数を使ってみましょう。
SELECT product_name, sum(amount) OVER( PARTITION BY product_name ) as amount FROM product_orders;これがwindow関数の基本形です。
関数() OVER()の形をとります。
OVER()の中にあるPARTITION BYは分割するカラムを指定します。これを実行した結果がこちらです
+--------------+--------+ | product_name | amount | +--------------+--------+ | apple | 15 | | apple | 15 | | apple | 15 | | banana | 9 | | banana | 9 | | banana | 9 | | orange | 8 | | orange | 8 | | orange | 8 | +--------------+--------+group by句を使ったクエリとの出力結果の違いはすぐにわかりますね。
group by句と同じく、amountはそのフルーツの売れた個数ですが、出力結果はproduct_nameを1つに集約しているのに対し、こちらは集約していません。これがはじめに言った違いの、集約関数は「分割」と「集約」を行い、window関数は「分割」のみ行うということです。
over句について
over句はPartition By , Order By, Frameの三つの方法で集計の範囲指定をすることができます。
一つづつみていきましょうPartition By
上でちょろっと説明しましたね。
Windowをどのように分けるのかを指定します。
集合関数でいうGroup Byと同じような動きをします。
指定しなければWindowで区切られない状態(つまり全体)で関数が実行されます。Order By
従来のOrder By句はASCやDESCで出力結果を昇順や降順に並び替えるものとして使っていましたが、window関数のOrder By句は少し違います。
window関数のOrder By句は、指定したキーで並び替え、初めの行から現在の行までを集計します。
実際にクエリを叩いてみましょう。SELECT product_name, amount, date,sum(amount) OVER( PARTITION BY product_name ORDER BY date ) as sum_amount FROM product_orders; 結果 +--------------+--------+------------+------------+ | product_name | amount | date | sum_amount | +--------------+--------+------------+------------+ | apple | 8 | 2019-03-01 | 8 | | apple | 2 | 2019-04-01 | 10 | | apple | 5 | 2019-05-01 | 15 | | banana | 3 | 2019-03-01 | 3 | | banana | 2 | 2019-04-01 | 5 | | banana | 4 | 2019-05-01 | 9 | | orange | 4 | 2019-03-01 | 4 | | orange | 1 | 2019-04-01 | 5 | | orange | 3 | 2019-05-01 | 8 | +--------------+--------+------------+------------+こんな感じになります。
sum_amountに注目してください。
何が起きているかというと、PARTITION BYでproduct_nameごとに集計をしていて、各product_nameの1行目は1行目のみ、2行目は1行目との合計、3行目は2行目までの合計との合計を出力しています。まとめ
長くなるので2回に分けます。
第二回では色々なwindow関数の使い方について書いていきたいなーと思います。参考にしたサイト
第89回 WINDOW関数を使ってみる MySQL道普請便り
【MySQL8】【新機能】Window関数がMySQLでも使えるようになったよ。
MySQL王国に黒船（Window関数）がやってきた！
- 投稿日：2019-05-06T22:09:01+09:00
PDOを使ってデータベースに接続する方法について解説！
はじめに
- データベースに接続する方法について本やネットの情報から調べて理解したことをまとめました。
- もし、書いていることに何か間違いがある場合はご指摘いただけると嬉しいです。
接続するデータベースとテーブル
- 以下のようなデータベースとテーブルがあると仮定して実行します。
- PDOを利用してデータベースに接続します。
データベース名： test_db1
テーブル名： user
name てすと太郎 test-taro@test.test PDOとは
- 「PHP Data Objects」の略で、PHPからデータベースへ接続するためのクラスのことです。
データベース（MySQL）に接続する方法
- データベースに接続するには、どこにある何というデータベースにどのユーザーが接続するのか情報を記述します。
- PDOクラスをインスタンス化するときに、引数にデータベースの接続に必要な情報を記述することで接続できます。
書き方
インスタンス名 = new PDO("データベースの種類:host=接続先アドレス, dbname=データベース名,charset=文字エンコード" "ユーザー名", "パスワード", オプション)引数
引数について解説します。
データベースの種類
- 使用したいデータベースを指定
ホスト名(host)
- 接続先アドレス
データベース名(dbname)
- 使用したいデータベース名
文字コード(charset)
- 文字コード（utf8）
ユーザー名
- データベースにログインするユーザー名
パスワード
- データベースにログインするパスワード
オプション
- オプションは連想配列で指定します。
- オプションはデータベースに接続する時に様々な機能を使うことができます。
コード例
$user = "ここにユーザー名が入ります"; $password = "ここにパスワードが入ります"; $dbh = new PDO("mysql:host=localhost; dbname=test_db1; charset=utf8", "$user", "$password");
- 作成したインスタンスを$dbhという変数に代入しています。
- dbhはデータベースハンドラの略です。
データベースに接続した後にオプションを指定する方法
- データベースに接続した後にオプションを指定するには PDO::setAttributeメソッドを使用します。
書き方
$dbh->setAttribute(属性 , 値);
- PDO::setAttributeメソッドは属性をセットするメソッドです。
- 第1引数に属性を、第2引数に値を指定します。
コード例
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)とは
PDO::ATTR_ERRMODEという属性でPDO::ERRMODE_EXCEPTIONの値を設定することでエラーが発生したときに、PDOExceptionの例外を投げてくれます。データを取得する
- ここまでデータベース接続ができたので、次からデータを取得する方法について紹介します。
コード例
$stmt = $dbh->query('SELECT * FROM user'); $result = $stmt->fetchAll(PDO::FETCH_ASSOC);PDO::queryメソッドとは
$stmt = $dbh->query('SELECT * FROM user');
- PDO::queryメソッドを実行するとクエリを実行します。
PDOStatement::fetchAllメソッドとは
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
- PDOStatement::fetchAllメソッドは該当する全てのデータを配列として取得します。
FETCH_ASSOCでカラム名をキーとする連想配列で返します。データベースの接続を閉じる方法
- データベースの接続を閉じるには
$dbh = null;を使います。データベースに接続するコード例
コード例<?php $user = "ここにユーザー名が入ります"; $password = "ここにパスワードが入ります"; $dbh = new PDO("mysql:host=localhost; dbname=test_db1; charset=utf8", "$user", "$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $dbh->query('SELECT * FROM user'); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); var_dump($result); $dbh = null; ?>実行結果array(1) { [0]=> array(2) { ["name"]=> string(15) "てすと太郎" ["email"]=> string(19) "test-taro@test.test" } }
- 投稿日：2019-05-06T22:09:01+09:00
PDOを使ってデータベースに接続する方法を解説！
はじめに
- データベースに接続する方法について本やネットの情報から調べて理解したことをまとめました。
- もし、書いていることに何か間違いがある場合はご指摘いただけると嬉しいです。
接続するデータベースとテーブル
- 以下のようなデータベースとテーブルがあると仮定して実行します。
- PDOを利用してデータベースに接続します。
データベース名： test_db1
テーブル名： user
name てすと太郎 test-taro@test.test PDOとは
- 「PHP Data Objects」の略で、PHPからデータベースへ接続するためのクラスのことです。
データベース（MySQL）に接続する方法
- データベースに接続するには、どこにある何というデータベースにどのユーザーが接続するのか情報を記述します。
- PDOクラスをインスタンス化するときに、引数にデータベースの接続に必要な情報を記述することで接続できます。
書き方
インスタンス名 = new PDO("データベースの種類:host=接続先アドレス, dbname=データベース名,charset=文字エンコード" "ユーザー名", "パスワード", オプション)引数
引数について解説します。
データベースの種類
- 使用したいデータベースを指定
ホスト名(host)
- 接続先アドレス
データベース名(dbname)
- 使用したいデータベース名
文字コード(charset)
- 文字コード（utf8）
ユーザー名
- データベースにログインするユーザー名
パスワード
- データベースにログインするパスワード
オプション
- オプションは連想配列で指定します。
- オプションはデータベースに接続する時に様々な機能を使うことができます。
コード例
$user = "ここにユーザー名が入ります"; $password = "ここにパスワードが入ります"; $dbh = new PDO("mysql:host=localhost; dbname=test_db1; charset=utf8", "$user", "$password");
- 作成したインスタンスを$dbhという変数に代入しています。
- dbhはデータベースハンドラの略です。
データベースに接続した後にオプションを指定する方法
- データベースに接続した後にオプションを指定するには PDO::setAttributeメソッドを使用します。
書き方
$dbh->setAttribute(属性 , 値);
- PDO::setAttributeメソッドは属性をセットするメソッドです。
- 第1引数に属性を、第2引数に値を指定します。
コード例
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)とは
PDO::ATTR_ERRMODEという属性でPDO::ERRMODE_EXCEPTIONの値を設定することでエラーが発生したときに、PDOExceptionの例外を投げてくれます。データを取得する
- ここまでデータベース接続ができたので、次からデータを取得する方法について紹介します。
コード例
$stmt = $dbh->query('SELECT * FROM user'); $result = $stmt->fetchAll(PDO::FETCH_ASSOC);PDO::queryメソッドとは
$stmt = $dbh->query('SELECT * FROM user');
- PDO::queryメソッドを実行するとクエリを実行します。
PDOStatement::fetchAllメソッドとは
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
- PDOStatement::fetchAllメソッドは該当する全てのデータを配列として取得します。
FETCH_ASSOCでカラム名をキーとする連想配列で返します。データベースの接続を閉じる方法
- データベースの接続を閉じるには
$dbh = null;を使います。データベースに接続するコード例
コード例<?php $user = "ここにユーザー名が入ります"; $password = "ここにパスワードが入ります"; $dbh = new PDO("mysql:host=localhost; dbname=test_db1; charset=utf8", "$user", "$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $dbh->query('SELECT * FROM user'); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); var_dump($result); $dbh = null;実行結果array(1) { [0]=> array(2) { ["name"]=> string(15) "てすと太郎" ["email"]=> string(19) "test-taro@test.test" } }
- 投稿日：2019-05-06T22:09:01+09:00
データベースに接続する方法について解説！
はじめに
- データベースに接続する方法について本やネットの情報から調べて理解したことをまとめました。
- もし、書いていることに何か間違いがある場合はご指摘いただけると嬉しいです。
接続するデータベースとテーブル
- 以下のようなデータベースとテーブルがあると仮定して実行します。
- PDOを利用してデータベースに接続します。
データベース名： test_db1
テーブル名： user
name てすと太郎 test-taro@test.test PDOとは
- 「PHP Data Objects」の略で、PHPからデータベースへ接続するためのクラスのことです。
データベース（MySQL）に接続する方法
- データベースに接続するには、どこにある何というデータベースにどのユーザーが接続するのか情報を記述します。
- PDOクラスをインスタンス化するときに、引数にデータベースの接続に必要な情報を記述することで接続できます。
書き方
インスタンス名 = new PDO("データベースの種類:host=接続先アドレス, dbname=データベース名,charset=文字エンコード" "ユーザー名", "パスワード", オプション)引数
引数について解説します。
データベースの種類
- 使用したいデータベースを指定
ホスト名(host)
- 接続先アドレス
データベース名(dbname)
- 使用したいデータベース名
文字コード(charset)
- 文字コード（utf8）
ユーザー名
- データベースにログインするユーザー名
パスワード
- データベースにログインするパスワード
オプション
- オプションは連想配列で指定します。
- オプションはデータベースに接続する時に様々な機能を使うことができます。
コード例
$user = "ここにユーザー名が入ります"; $password = "ここにパスワードが入ります"; $dbh = new PDO("mysql:host=localhost; dbname=test_db1; charset=utf8", "$user", "$password");
- 作成したインスタンスを$dbhという変数に代入しています。
- dbhはデータベースハンドラの略です。
データベースに接続した後にオプションを指定する方法
- データベースに接続した後にオプションを指定するには PDO::setAttributeメソッドを使用します。
書き方
$dbh->setAttribute(属性 , 値);
- PDO::setAttributeメソッドは属性をセットするメソッドです。
- 第1引数に属性を、第2引数に値を指定します。
コード例
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION)とは
PDO::ATTR_ERRMODEという属性でPDO::ERRMODE_EXCEPTIONの値を設定することでエラーが発生したときに、PDOExceptionの例外を投げてくれます。データを取得する
- ここまでデータベース接続ができたので、次からデータを取得する方法について紹介します。
コード例
$stmt = $dbh->query('SELECT * FROM user'); $result = $stmt->fetchAll(PDO::FETCH_ASSOC);PDO::queryメソッドとは
$stmt = $dbh->query('SELECT * FROM user');
- PDO::queryメソッドを実行するとクエリを実行します。
PDOStatement::fetchAllメソッドとは
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
- PDOStatement::fetchAllメソッドは該当する全てのデータを配列として取得します。
FETCH_ASSOCでカラム名をキーとする連想配列で返します。データベースの接続を閉じる方法
- データベースの接続を閉じるには
$dbh = null;を使います。データベースに接続するコード例
コード例<?php $user = "ここにユーザー名が入ります"; $password = "ここにパスワードが入ります"; $dbh = new PDO("mysql:host=localhost; dbname=test_db1; charset=utf8", "$user", "$password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $dbh->query('SELECT * FROM user'); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); var_dump($result); $dbh = null; ?>実行結果array(1) { [0]=> array(2) { ["name"]=> string(15) "てすと太郎" ["email"]=> string(19) "test-taro@test.test" } }
- 投稿日：2019-05-06T21:12:25+09:00
MYSQLのエラー関連のエラーってわかりにくいよね
エラー発生：SQLSTATE[HY093]
Invalid parameter number: number of bound variables does not match number of tokens
例外処理時のExceptionにてキャッチした際に発生したエラー。
catch(Exception $e){
debug('エラー発生：'.$e->getMessage());
}debug()関数でエラー文をエラー時に別ファイルに作成
エラーの考えられる原因
1.SQL文のミス
主にINSERT INTOの対象となるパラメーター以外の記述はしてはいけない（と思う）
対象プレースホルダーに注意。
- 投稿日：2019-05-06T19:39:23+09:00
Ruby on railsを学習してる者たちよ、SQLを学べ！
SQLを学ぼうと思ったきっかけ
Railsで自作アプリを作っているときにN+1問題に直面した。
includesやleft_joinとかを学習してるときに、SQLの動きをちゃんとわかってないとやばくね？って思った。
Railsはあくまでフレームワークだ。本来SQL直書きするところを簡単にしてくれている。
なのでSQLをある程度わかっていたら、もし違うプログラミング言語で組むことになったとき、フレームワークであれば関連づけて覚えやすいし、直書きなら普通に書ける。
実際にSQLを勉強してみたら意外とすんなり頭に入ってくれた。何も怖がることはなかったので勉強しておくとあとあと絶対いいはず！
この記事では、結合とグループ化について書いていく。
テーブルの結合
Usersテーブル +----+--------+ | id | name | +----+--------+ | 1 | user１ | | 2 | user2 | +----+------- + Articlesテーブル +----+--------+-------+---------+ | id | title | body | user_id | +----+--------+-------+---------+ | 1 | title1 | body１ | 1 | | 2 | title２ | body２ | 1 | | 3 | title３ | body３ | 2 | | 4 | title４ | body４ | | +----+--------+-------+---------+上記の２つのテーブルがあるとします。
INNER JOIN（内部結合）
articleのuser_idとusersのidを紐づけている。
select articles.*, users.name from articles inner join users on articles.user_id = users.id;出力結果
+----+--------+-------+---------+-------+ | id | title | body | user_id | name | +----+--------+-------+---------+-------+ | 1 | title1 | body1 | 1 | user1 | | 2 | title2 | body2 | 1 | user1 | | 3 | title3 | body3 | 2 | user2 | +----+--------+-------+---------+-------+articles.user_idとusers.idが一致しているデータだけを出力している。
なので、Articlesテーブルのid = 4のデータはuser_idを持っていないので出力されていない。
LEFT JOIN（外部結合）
LEFT OUTER JOINはLEFT JOINと同じ意味。SQL
select articles.*, users.name from articles left join users on articles.user_id = users.id;出力結果
+----+--------+-------+---------+-------+ | id | title | body | user_id | name | +----+--------+-------+---------+-------+ | 1 | title1 | body1 | 1 | user1 | | 2 | title2 | body2 | 1 | user1 | | 3 | title3 | body3 | 2 | user2 | | 4 | title4 | body4 | | | +----+--------+-------+---------+-------+articlesテーブルとuserテーブルを並べるようなイメージ。
左のテーブル(articlesテーブル)を基準に結合している。
articlesテーブルはwhereの条件にあてはまる全件が出力され、それに紐づくusersのデータが出力されている。
ちなみにwhere句の条件なしの表現は
where 1 = 1RIGHT JOIN（外部結合）
LEFT JOINの逆なので省略
グループ化
Teamsテーブル +----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamA | 3 | | 2 | teamB | 2 | | 3 | teamA | 5 | | 4 | teamB | 4 | | 5 | teamC | 10 | | 6 | teamC | 20 | +----+-------+-------+上記のテーブルがあるとします。
Group by
SQL
select team, sum(point) from teams group by team;出力結果
+----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamA | 8 | | 2 | teamB | 6 | | 3 | teamC | 30 | +----+-------+-------+teamカラムを基準にpointを合計している。
今回扱ったのはsum()合計やけど他にも色々あるで。例：
- COUNT(team)
select team, sum(point) from teams group by team;レコードのカウント数 +----+-------+-------+ | id | team | COUNT | +----+-------+-------+ | 1 | teamA | 2 | | 2 | teamB | 2 | | 3 | teamC | 2 | +----+-------+-------+
- avg(point)では平均pointが出力される。
where句の位置
SQL
select team, sum(point) from teams where team != "teamB" group by team;出力結果
+----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamA | 8 | | 2 | teamC | 30 | +----+-------+-------+Having句
group byした結果をさらに条件をつける。
SQL
select team, sum(point) from teams where team != "teamB" group by team having sum(point) > 10;出力結果
+----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamC | 30 | +----+-------+-------+何をしているかというと、
team != "teamB"の条件でグループ化
グループ化した結果をさらにsum(point) > 10の条件で検索
Havingはグループ化した後のwhere句みたいなかんじ。
余計わかりにくいかな。
おわり
他にもいろいろあるけど、とりあえず今回は結合とグループ化までで。
これがわかっただけでもRailsで走っているSQLだいたい読めると思う。
記事書いてるときたまたま見つけたサイト。
https://www.1keydata.com/jp/sql/sql-intersect.php
じゃ。
- 投稿日：2019-05-06T19:39:23+09:00
Railsを学習してる者たちよ、SQLを学べ！
SQLを学ぼうと思ったきっかけ
Railsで自作アプリを作っているときにN+1問題に直面した。
includesやleft_joinとかを学習してるときに、SQLの動きをちゃんとわかってないとやばくね？って思った。
Railsはあくまでフレームワークだ。本来SQL直書きするところを簡単にしてくれている。
なのでSQLをある程度わかっていたら、もし違うプログラミング言語で組むことになったとき、フレームワークであれば関連づけて覚えやすいし、直書きなら普通に書ける。
実際にSQLを勉強してみたら意外とすんなり頭に入ってくれた。何も怖がることはなかったので勉強しておくとあとあと絶対いいはず！
この記事では、結合とグループ化について書いていく。
テーブルの結合
Usersテーブル +----+--------+ | id | name | +----+--------+ | 1 | user１ | | 2 | user2 | +----+------- + Articlesテーブル +----+--------+-------+---------+ | id | title | body | user_id | +----+--------+-------+---------+ | 1 | title1 | body１ | 1 | | 2 | title２ | body２ | 1 | | 3 | title３ | body３ | 2 | | 4 | title４ | body４ | | +----+--------+-------+---------+上記の２つのテーブルがあるとします。
INNER JOIN（内部結合）
articleのuser_idとusersのidを紐づけている。
select articles.*, users.name from articles inner join users on articles.user_id = users.id;出力結果
+----+--------+-------+---------+-------+ | id | title | body | user_id | name | +----+--------+-------+---------+-------+ | 1 | title1 | body1 | 1 | user1 | | 2 | title2 | body2 | 1 | user1 | | 3 | title3 | body3 | 2 | user2 | +----+--------+-------+---------+-------+articles.user_idとusers.idが一致しているデータだけを出力している。
なので、Articlesテーブルのid = 4のデータはuser_idを持っていないので出力されていない。
LEFT JOIN（外部結合）
LEFT OUTER JOINはLEFT JOINと同じ意味。SQL
select articles.*, users.name from articles left join users on articles.user_id = users.id;出力結果
+----+--------+-------+---------+-------+ | id | title | body | user_id | name | +----+--------+-------+---------+-------+ | 1 | title1 | body1 | 1 | user1 | | 2 | title2 | body2 | 1 | user1 | | 3 | title3 | body3 | 2 | user2 | | 4 | title4 | body4 | | | +----+--------+-------+---------+-------+articlesテーブルとuserテーブルを並べるようなイメージ。
左のテーブル(articlesテーブル)を基準に結合している。
articlesテーブルはwhereの条件にあてはまる全件が出力され、それに紐づくusersのデータが出力されている。
ちなみにwhere句の条件なしの表現は
where 1 = 1RIGHT JOIN（外部結合）
LEFT JOINの逆なので省略
グループ化
Teamsテーブル +----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamA | 3 | | 2 | teamB | 2 | | 3 | teamA | 5 | | 4 | teamB | 4 | | 5 | teamC | 10 | | 6 | teamC | 20 | +----+-------+-------+上記のテーブルがあるとします。
Group by
SQL
select team, sum(point) from teams group by team;出力結果
+----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamA | 8 | | 2 | teamB | 6 | | 3 | teamC | 30 | +----+-------+-------+teamカラムを基準にpointを合計している。
今回扱ったのはsum()合計やけど他にも色々あるで。例：
- COUNT(team)
select team, sum(point) from teams group by team;レコードのカウント数 +----+-------+-------+ | id | team | COUNT | +----+-------+-------+ | 1 | teamA | 2 | | 2 | teamB | 2 | | 3 | teamC | 2 | +----+-------+-------+
- avg(point)では平均pointが出力される。
where句の位置
SQL
select team, sum(point) from teams where team != "teamB" group by team;出力結果
+----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamA | 8 | | 2 | teamC | 30 | +----+-------+-------+Having句
group byした結果をさらに条件をつける。
SQL
select team, sum(point) from teams where team != "teamB" group by team having sum(point) > 10;出力結果
+----+-------+-------+ | id | team | point | +----+-------+-------+ | 1 | teamC | 30 | +----+-------+-------+何をしているかというと、
team != "teamB"の条件でグループ化
グループ化した結果をさらにsum(point) > 10の条件で検索
Havingはグループ化した後のwhere句みたいなかんじ。
余計わかりにくいかな。
おわり
他にもいろいろあるけど、とりあえず今回は結合とグループ化までで。
これがわかっただけでもRailsで走っているSQLだいたい読めると思う。
記事書いてるときたまたま見つけたサイト。
https://www.1keydata.com/jp/sql/sql-intersect.php
じゃ。
- 投稿日：2019-05-06T16:54:34+09:00
Railsのポリモーフィック関連の挙動確認
はじめに
公式ドキュメントのポリモーフィック関連の項目を見た際に、挙動がイメージしにくかったので、ポリモーフィック関連を持つモデルを作成しつつ、DBのデータやコンソールで動きを確認します。
環境
- OS : Ubuntu 17.04
- Ruby : 2.6.3
- Rails: 5.2.3
- MySQL: 5.7.20
ポリモーフィズム(多様性)
ポリモーフィックと聞くと、オブジェクト指向プログラミングで出てくるポリモーフィズムを思い出す人が多いはずです。
ポリモーフィズムとは、プログラミング言語の持つ性質の一つで、ある関数やメソッドなどが、引数や返り値の数やデータ型などの異なる複数の実装を持ち、呼び出し時に使い分けるようにできること。
(略)
オブジェクト指向プログラミング言語では親クラスから派生（継承）した子クラスがメソッドの内容を上書き（オーバーライド）したり、インターフェースで定義されたメソッドを実装することによりこれを実現している。オブジェクト指向プログラミングの、ポリモーフィズムでは、継承やインターフェース1を使って同名のメソッドを複数のクラスで定義します。それによって同名のメソッドでも、インスタンスごとに振る舞いを変える(多様性2が生まれる)というものです。
ただ、ActiveRecordのポリモーフィック関連は、継承やインターフェース1を用いるのではなく、ActiveRecordで定義されている関連付けの機能を使用します。
(用途は異なりますが継承を用いるシングルテーブル継承 （STI）というものもあります)
ポリモーフィック関連を持つモデルの作成
- ActiveRecordマイグレーション - Railsガイド 2.2 モデルを生成する
- ActiveRecordの関連付け(アソシエーション) - Railsガイド 2.9 ポリモーフィック関連付け
Railsガイドを元に、ポリモーフィック関連付けのモデルを作成します。
作成するモデルは、Picture/Employee/Productの3つです。
ここでは、モデルPictureと複数のモデルEmployee/Productとの関連を、imageableという関連1つで表現します。$ bin/rails generate model Product name:string Running via Spring preloader in process 21448 invoke active_record create db/migrate/20190504040556_create_products.rb create app/models/product.rb invoke test_unit create test/models/product_test.rb create test/fixtures/products.yml $ bin/rails generate model Employee name:string # 省略 $ bin/rails generate model Picture name:string # 省略PictureのMigrationファイルを修正します。
class CreatePictures < ActiveRecord::Migration[5.2] def change create_table :pictures do |t| t.string :name t.integer :imageable_id t.string :imageable_type t.timestamps end add_index :pictures, [:imageable_type, :imageable_id] end end作成した、それぞれのモデルに関連を記載します。
# app/models/picture.rb class Picture < ApplicationRecord belongs_to :imageable, polymorphic: true end # app/models/employee.rb class Employee < ApplicationRecord has_many :pictures, as: :imageable end # app/models/product.rb class Product < ApplicationRecord has_many :pictures, as: :imageable endマイグレーションを実行します。
$ bin/rails db:migrate == 20190504040556 CreateProducts: migrating =================================== -- create_table(:products) -> 0.0588s == 20190504040556 CreateProducts: migrated (0.0590s) ========================== == 20190504040742 CreateEmployees: migrating ================================== -- create_table(:employees) -> 0.0648s == 20190504040742 CreateEmployees: migrated (0.0650s) ========================= == 20190504041111 CreatePictures: migrating =================================== -- create_table(:pictures) -> 0.0630s -- add_index(:pictures, [:imageable_type, :imageable_id]) -> 0.0678s == 20190504041111 CreatePictures: migrated (0.1311s) ==========================MySQLのデータを確認
DBeaver をつかってデータを確認します。
ポリモーフィック関連を実現するために、テーブルpicturesに、imageable_id/imageable_typeというカラムが追加されてます。
imageableという単語は、モデルEmployee/Productで定義されていました。has_many :pictures, as: :imageable説明をすると、「複数のpictureを持ち、その関連はimageableとする」といったところでしょうか。
モデルの作成
動きを確認するために、モデルを作成します。
$ bin/rails c > product = Product.create(name: '商品1') => #<Product id: 1, name: "商品1", created_at: "2019-05-04 04:41:12", updated_at: "2019-05-04 04:41:12"> > employee = Employee.create(name: '田中一郎') => #<Employee id: 1, name: "田中一郎", created_at: "2019-05-04 04:42:23", updated_at: "2019-05-04 04:42:23"> > product.pictures => #<ActiveRecord::Associations::CollectionProxy []> > employee.pictures => #<ActiveRecord::Associations::CollectionProxy []>ここは
has_manyの動きです。まだPictureを作成していないのでデータがヒットしません。product.pictures.create(name: '商品1-1.jpg') => #<Picture id: 1, name: "商品1-1.jpg", imageable_id: 1, imageable_type: "Product", created_at: "2019-05-04 04:54:46", updated_at: "2019-05-04 04:54:46"> employee.pictures.create(name: '田中一郎_1.jpg') => #<Picture id: 2, name: "田中一郎_1.jpg", imageable_id: 1, imageable_type: "Employee", created_at: "2019-05-04 04:59:24", updated_at: "2019-05-04 04:59:24">それぞれのProduct/Employeeで、picturesを作成したところ、こちらで指定していないのにもかかわらずimageable_idとimageable_typeに値が入っています。
今度はPictureから、Product/Employeeの関連を見てみましょう。
> pictures = Picture.all Picture Load (0.9ms) SELECT `pictures`.* FROM `pictures` LIMIT 11 => #<ActiveRecord::Relation [#<Picture id: 1, name: "商品1-1.jpg", imageable_id: 1, imageable_type: "Product", created_at: "2019-05-04 04:54:46", updated_at: "2019-05-04 04:54:46">, #<Picture id: 2, name: "田中一郎_1.jpg", imageable_id: 1, imageable_type: "Employee", created_at: "2019-05-04 04:59:24", updated_at: "2019-05-04 04:59:24">]> > pictures.first.imageable Picture Load (0.8ms) SELECT `pictures`.* FROM `pictures` ORDER BY `pictures`.'id' ASC LIMIT 1 Product Load (1.1ms) SELECT `products`.* FROM `products` WHERE `products`.'id' = 1 LIMIT 1 => #<Product id: 1, name: "商品1", created_at: "2019-05-04 04:41:12", updated_at: "2019-05-04 04:41:12"> > pictures.last.imageable Picture Load (0.8ms) SELECT `pictures`.* FROM `pictures` ORDER BY `pictures`.'id' DESC LIMIT 1 Employee Load (1.2ms) SELECT `employees`.* FROM `employees` WHERE `employees`.'id' = 1 LIMIT 1 => #<Employee id: 1, name: "田中一郎", created_at: "2019-05-04 04:42:23", updated_at: "2019-05-04 04:42:23">(省略していた、SQL文のログも記載してます)
Pictureでは、Product/Employeeの値を参照する際に、Product/Employeeで定義していたimageableを使用して参照することができます。
ログから察するに、1回目のSQLでテーブルpicturesのデータを取得、2回目のSQLでpicturesのimageable_id/imageable_typeの値を使用して、検索するテーブルと、id値を決めているようです。
MySQLのデータを確認
コンソールで確認したように、それぞれのテーブルにデータが格納されています。
注意する点としてはpicturesのimageable_id/imageable_typeはRails(アプリ側)だと、ポリモーフィック関連を提供するカラムとして認識されますが、DB側にはポリモーフィック関連を表現する機能がないため「インデックスが張られているカラム」という認識しかありません。
例えば、アプリ側でEmployeeもしくはProductのデータを削除した際に
dependentを使い、Pictureのimageable_id/imageable_typeのデータに手を加え関連の整合性を保つことができます。しかし、DB側でemployeeもしくはproductsを削除しても外部キー制約などを設定できない(このカラムでは外部キーとなるテーブルが明確でない)ため、picturesとの関連の整合性が取れなくなります。まとめ
- ActiveRecordのポリモーフィック関連は、ポリモーフィズムと目的は同様
- しかし、オブジェクト指向プログラミングなどと手法が異なる
- x_id/x_typeといったカラムを追加して、対象のオブジェクト(テーブル)名と主キーのidを格納する
- 1つの関連で、複数のオブジェクトの関連を表す事ができる
- ポリモーフィック関連はActiveRecordの機能であり、DBはポリモーフィックの整合性を担保しない
ここでいうインターフェースは、Javaの実装におけるインターフェースを想定しているような気がします。 ↩
オブジェクト指向プログラミングでよく例に挙げられるものは、厳密には「ポリモーフィズムの部分型付け」を指すようです。Wikipedia ポリモーフィズム ↩
