- 投稿日:2020-09-28T21:33:42+09:00
SQL のウィンドウ関数を使うサンプル: OVER, PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK
概要
- SQL のウィンドウ関数を使うサンプルを示す
- OVER 句, PARTITION BY 句, ORDER BY 句, ROW_NUMBER 関数, RANK 関数, DENSE_RANK 関数を使う
- 環境: MySQL Ver 8.0.21 for osx10.15 on x86_64 (Homebrew)
ウィンドウ関数の機能
ウィンドウ関数とは
ウィンドウ関数はテーブルから部分集合を切り出して処理をかける機能。
OVER 句にて、 PARTITION BY 句で部分集合を切り出し、部分集合内を ORDER BY 句でソートし、部分集合を順序関数または集約関数で処理する。SQL において、窓関数(まどかんすう)もしくはウィンドウ関数 (ウィンドウかんすう、英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されている。分析関数やOLAP機能と呼ばれる場合もある。
PARTITION BY 句
指定したカラムの値ごとに部分集合を作る。
ORDER BY 句
部分集合内を指定した条件でソートする。
ROW_NUMBER 関数
部分集合内のレコードに連番を割り当てる関数。
MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions
Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.
ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is nondeterministic.
RANK 関数
部分集合内のレコードに順位の数値を割り当てる関数。同じ順位の場合は同じ数値を割り当てて、その後に続く順位は +1 とはならず数値をスキップする。
MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions
Returns the rank of the current row within its partition, with gaps. Peers are considered ties and receive the same rank. This function does not assign consecutive ranks to peer groups if groups of size greater than one exist; the result is noncontiguous rank numbers.
This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers.
DENSE_RANK 関数
部分集合内のレコードに順位の数値を割り当てる関数。同じ順位の場合は同じ数値を割り当てて、その後に続く順位は +1 した数値になる。
MySQL :: MySQL 8.0 Reference Manual :: 12.21.1 Window Function Descriptions
Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. This function assigns consecutive ranks to peer groups; the result is that groups of size greater than one do not produce noncontiguous rank numbers. For an example, see the RANK() function description.
This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers.
サンプル
サンプルデータを準備
買ったお菓子テーブルを作成。
mysql> CREATE TABLE katta_okashi ( -> number INTEGER, -> name VARCHAR(128), -> okashi VARCHAR(128), -> price INTEGER -> ); Query OK, 0 rows affected (0.02 sec)買ったお菓子データを追加。
mysql> INSERT INTO katta_okashi VALUES -> (1, 'Alice', 'candy', 100), -> (2, 'Alice', 'candy', 50), -> (3, 'Alice', 'cookie', 30), -> (4, 'Bob', 'chocolate', 200), -> (5, 'Bob', 'chocolate', 20), -> (6, 'Bob', 'cookie', 20); Query OK, 6 rows affected (0.03 sec) Records: 6 Duplicates: 0 Warnings: 0買ったお菓子データ一覧を出力。
mysql> select * from katta_okashi; +--------+-------+-----------+-------+ | number | name | okashi | price | +--------+-------+-----------+-------+ | 1 | Alice | candy | 100 | | 2 | Alice | candy | 50 | | 3 | Alice | cookie | 30 | | 4 | Bob | chocolate | 200 | | 5 | Bob | chocolate | 20 | | 6 | Bob | cookie | 20 | +--------+-------+-----------+-------+ 6 rows in set (0.00 sec)OVER + ROW_NUMBER
PARTITION BY を使わない場合は部分集合を作らない。
1つの集合として扱い、ナンバリングする。
mysql> SELECT -> name, -> okashi, -> price, -> ROW_NUMBER() -> OVER () AS my_number -> FROM -> katta_okashi; +-------+-----------+-------+-----------+ | name | okashi | price | my_number | +-------+-----------+-------+-----------+ | Alice | candy | 100 | 1 | | Alice | candy | 50 | 2 | | Alice | cookie | 30 | 3 | | Bob | chocolate | 200 | 4 | | Bob | chocolate | 20 | 5 | | Bob | cookie | 20 | 6 | +-------+-----------+-------+-----------+ 6 rows in set (0.00 sec)OVER + PARTITION BY + ROW_NUMBER
name カラムの値ごとに部分集合を作り、それぞれの部分集合内でナンバリングする。
mysql> SELECT -> name, -> okashi, -> price, -> ROW_NUMBER() -> OVER ( -> PARTITION BY name -> ) AS my_number -> FROM -> katta_okashi; +-------+-----------+-------+-----------+ | name | okashi | price | my_number | +-------+-----------+-------+-----------+ | Alice | candy | 100 | 1 | | Alice | candy | 50 | 2 | | Alice | cookie | 30 | 3 | | Bob | chocolate | 200 | 1 | | Bob | chocolate | 20 | 2 | | Bob | cookie | 20 | 3 | +-------+-----------+-------+-----------+ 6 rows in set (0.00 sec)OVER + PARTITION BY 複数カラム指定 + ROW_NUMBER
name カラムと okashi カラムの値の組み合わせごとに部分集合を作り、それぞれの部分集合内でナンバリングする。
mysql> SELECT -> name, -> okashi, -> price, -> ROW_NUMBER() -> OVER ( -> PARTITION BY name, okashi -> ) AS my_number -> FROM -> katta_okashi; +-------+-----------+-------+-----------+ | name | okashi | price | my_number | +-------+-----------+-------+-----------+ | Alice | candy | 100 | 1 | | Alice | candy | 50 | 2 | | Alice | cookie | 30 | 1 | | Bob | chocolate | 200 | 1 | | Bob | chocolate | 20 | 2 | | Bob | cookie | 20 | 1 | +-------+-----------+-------+-----------+ 6 rows in set (0.02 sec)OVER + PARTITION BY + ORDER BY + ROW_NUMBER
name カラムの値ごとに部分集合を作り、それぞれの部分集合内にて price カラムでソートしてナンバリングする。
mysql> SELECT -> name, -> okashi, -> price, -> ROW_NUMBER() -> OVER ( -> PARTITION BY name -> ORDER BY price -> ) AS my_number -> FROM -> katta_okashi; +-------+-----------+-------+-----------+ | name | okashi | price | my_number | +-------+-----------+-------+-----------+ | Alice | cookie | 30 | 1 | | Alice | candy | 50 | 2 | | Alice | candy | 100 | 3 | | Bob | chocolate | 20 | 1 | | Bob | cookie | 20 | 2 | | Bob | chocolate | 200 | 3 | +-------+-----------+-------+-----------+ 6 rows in set (0.00 sec)OVER + PARTITION BY + ORDER BY + RANK
name カラムの値ごとに部分集合を作り、それぞれの部分集合内にて price カラムでソートしてランキングする。
同順位が存在する場合は次のランク数との間に隙間ができる。
例えば name ='Bob' の部分集合では price = 20 が2つあるため同順1位になり、次の price = 200 は2位ではなく3位になる。mysql> SELECT -> name, -> okashi, -> price, -> RANK() -> OVER ( -> PARTITION BY name -> ORDER BY price -> ) AS my_number -> FROM -> katta_okashi; +-------+-----------+-------+-----------+ | name | okashi | price | my_number | +-------+-----------+-------+-----------+ | Alice | cookie | 30 | 1 | | Alice | candy | 50 | 2 | | Alice | candy | 100 | 3 | | Bob | chocolate | 20 | 1 | | Bob | cookie | 20 | 1 | | Bob | chocolate | 200 | 3 | +-------+-----------+-------+-----------+ 6 rows in set (0.01 sec)OVER + PARTITION BY + ORDER BY + DENSE_RANK
name カラムの値ごとに部分集合を作り、それぞれの部分集合内にて price カラムでソートしてランキングする。
同順位が存在する場合は次のランク数との間に隙間ができない。
例えば name ='Bob' の部分集合では price = 20 が2つあるため同順1位になり、次の price = 200 は3位ではなく2位になる。mysql> SELECT -> name, -> okashi, -> price, -> DENSE_RANK() -> OVER ( -> PARTITION BY name -> ORDER BY price -> ) AS my_number -> FROM -> katta_okashi; +-------+-----------+-------+-----------+ | name | okashi | price | my_number | +-------+-----------+-------+-----------+ | Alice | cookie | 30 | 1 | | Alice | candy | 50 | 2 | | Alice | candy | 100 | 3 | | Bob | chocolate | 20 | 1 | | Bob | cookie | 20 | 1 | | Bob | chocolate | 200 | 2 | +-------+-----------+-------+-----------+ 6 rows in set (0.01 sec)OVER + PARTITION BY + RANK
MySQL の RANK 関数では ORDER BY を付けないとすべて1位になる。
mysql> SELECT -> name, -> okashi, -> price, -> RANK() -> OVER ( -> PARTITION BY name -> ) AS my_number -> FROM -> katta_okashi; +-------+-----------+-------+-----------+ | name | okashi | price | my_number | +-------+-----------+-------+-----------+ | Alice | candy | 100 | 1 | | Alice | candy | 50 | 1 | | Alice | cookie | 30 | 1 | | Bob | chocolate | 200 | 1 | | Bob | chocolate | 20 | 1 | | Bob | cookie | 20 | 1 | +-------+-----------+-------+-----------+ 6 rows in set (0.01 sec)参考資料
- 投稿日:2020-09-28T21:07:18+09:00
MySQL ORDER BY で降順に指定しテーブルに出力する
テーブルに投稿日時順に出力し最初の投稿を先に出力する
返信内容があったら間に出力するこれも日付け順groupby.php<?php require_once("func/header.php"); $pdo = new PDO('mysql:host=localhost;dbname=name_db;', 'name', 'pass', array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' )); echo "<table class='table'> <thead class='thead-light'> <tr> <th>コメント</th> <th>日付</th> </tr> </thead> "; $result = $pdo->query('SELECT `text_id`, `comment`, `datetime` FROM t_text WHERE reply_parent_id = 0 AND comment IS NOT NULL ORDER BY datetime DESC'); // print_r($result); //エラーチェック var_dump($pdo->errorCode()); var_dump($pdo->errorInfo()); foreach ($result as $row) { // print_r($row); echo "<tr>"; echo "<td>" . $row['comment'] . "</td>"; echo "<td>" . $row['datetime'] . "</td>"; echo "</tr>"; $reply_id = $row['text_id']; $result2 =$pdo->query('SELECT `comment`, `datetime` FROM t_text WHERE reply_parent_id = "' . $reply_id . '" AND comment IS NOT NULL ORDER BY datetime DESC'); foreach ($result2 as $row2) { echo "<tr>"; echo "<td>" . $row2['comment'] . "</td>"; echo "<td>" . $row2['datetime'] . "</td>"; echo "</tr>"; } } echo '</table>';1,text_id
2,reply_parent_id
3,reply_id
4,user_id
5,comment
6,datetime
7,deleteflg
が振られているtext.csv1,0,0,11,ないよう1,2020-09-24 07:09:07,0 2,0,0,11,ないよう2,2020-09-24 07:09:13,0 3,0,0,11,ないよう3,2020-09-24 07:09:21,0 4,0,0,11,ないよう4,2020-09-24 07:09:51,0 5,0,0,12,ないよう5,2020-09-24 07:09:32,1 6,0,0,12,ないよう6,2020-09-24 07:09:37,0 7,0,0,12,ないよう7,2020-09-24 07:09:45,0 8,7,7,12,7番目のuser3へ返信:ないよう8,2020-09-24 08:09:23,0 9,7,8,12,8番目のuser3へ返信:ないよう9,2020-09-24 09:09:13,0 10,6,6,11,6番目のuser3へ返信:へんしんないよう,2020-09-25 01:09:58,0 11,2,2,11,2番目のuser2へ返信:へんしんないよう2,2020-09-25 01:09:02,0 12,7,9,11,9番目のuser3へ返信:へんしんないよう3,2020-09-28 01:09:02,0 13,7,12,10,12番目のuser2へ返信:へんしんないよう4,2020-09-28 01:09:02,0参考にしたサイト
https://www.javadrive.jp/php/mysql/index6.htmlhttps://proengineer.internous.co.jp/content/columnfeature/6653
文字化けhttps://amg-solution.jp/blog/247
https://dekokun.github.io/posts/2014-05-31.html
https://rfs.jp/sb/sql/s03/03_2-2.html#WHERE
https://qiita.com/mpyw/items/b00b72c5c95aac573b71
PDO::query メソッドで直接クエリを実行する
PDO::exec メソッドで直接クエリを実行するforeachを三回回す返信内容2回目の返信内容までなら表示可能 返信内容分foreachが増えてしまう
なのでreply_parent_idに親元のtext_idが振られているカラムを追加
https://zenn.dev/naoki_mochizuki/articles/60603b2cdc273cd51c59
エラーおよびエラー処理
https://www.php.net/manual/ja/pdo.error-handling.phpPDOはデフォルトではエラー出力をしないです
PDO::ERRMODE_SILENT
https://unskilled.site/pdo%E3%81%AF%E3%83%87%E3%83%95%E3%82%A9%E3%83%AB%E3%83%88%E3%81%A7%E3%81%AF%E3%82%A8%E3%83%A9%E3%83%BC%E5%87%BA%E5%8A%9B%E3%82%92%E3%81%97%E3%81%AA%E3%81%84%E3%81%A7%E3%81%99/
PDO::errorInfo()ではエラーコード、エラー番号、エラーメッセージが配列で得られます。
デフォルトのエラー設定。エラーメッセージを出力しない。エラー内容は保持している。 エラ−を取得するにはPDO::errorCode()かPDO::errorInfo()を使います。PDO::ERRMODE_SILENTについてはデフォルトなのでsetAttribute()しなくてもいいのですが、今回は例を示すために明示的に記述しています。
https://www.dbonline.jp/mysql/select/index11.html
- 投稿日:2020-09-28T20:14:50+09:00
データベースのカラムなどを修正したい場合
db:migrateしたデータベースを修正したい場合
プログラミングスクールでのチーム開発学習中にデータベースの修正を行いたかったのですが、
細かい部分の知識が抜け落ちていたので、備忘録用にまとめてます。データベースの操作に慣れていない方や、これからデータベースを学習する方の参考にもなれば幸いです。
開発環境
DB: MySQL
Rails: 5.2.4.3まず自分が修正したいデータベース(テーブル)の確認を行いましょう
ターミナル% rails db:migrate:statusすると、テーブルがこの様に出てくると思います(出てくるテーブルの数や名前はそれぞれ違ってきます)
ターミナルStatus Migration ID Migration Name -------------------------------------------------- up 20200823051138 Devise create ----s up 20200824122031 Create -------s up 20200824122659 Add ancestry to ------s up 20200824123715 Create -----s up 20200829083145 Create -----s up 20200906141656 Create -----s 今回はこのテーブルを修正したい up 20200907114227 Create -----s down 20200927061950 Create -----s down 20200927065357 Create -----s ※----は自分で作成したテーブル名ここでupとdownに注目です。
マイグレーションの修正を行うには、statusをdownの状態にしておく必要があります。
次に自分が修正したいデータベース(テーブル)をdownにしましょう
downの状態にするにはターミナルでこの様なコマンドを実行しましょう
ターミナル% rails db:rollbackもう一度statusを確認してみましょう
ターミナル% rails db:migrate:statusターミナルStatus Migration ID Migration Name -------------------------------------------------- up 20200823051138 Devise create ----s up 20200824122031 Create -------s up 20200824122659 Add ancestry to ------s up 20200824123715 Create -----s up 20200829083145 Create -----s up 20200906141656 Create -----s 今回はこのテーブルを修正したい down 20200907114227 Create -----s down 20200927061950 Create -----s down 20200927065357 Create -----s ※----は自分で作成したテーブル名あれ?
一つ下しかdownに変わってません。
というのもrollbackコマンドは一つずつしかdownに変えられないのです。
なので、もう一度やってみましょう。
ターミナル% rails db:rollbackもう一度statusを確認してみましょう
ターミナル% rails db:migrate:statusターミナルStatus Migration ID Migration Name -------------------------------------------------- up 20200823051138 Devise create ----s up 20200824122031 Create -------s up 20200824122659 Add ancestry to ------s up 20200824123715 Create -----s up 20200829083145 Create -----s down 20200906141656 Create -----s 今回はこのテーブルを修正したい down 20200907114227 Create -----s down 20200927061950 Create -----s down 20200927065357 Create -----s ※----は自分で作成したテーブル名今度は無事に目的のテーブルをdownに出来ました。
修正が終わった後
今回はカラム名の修正を行いたかったので、この後にマイグレーションファイルのカラム名の変更を行いました。
最後に
ターミナル% rails db:migrateもう一度statusを確認しておきましょう
ターミナル% rails db:migrate:statusターミナルStatus Migration ID Migration Name -------------------------------------------------- up 20200823051138 Devise create ----s up 20200824122031 Create -------s up 20200824122659 Add ancestry to ------s up 20200824123715 Create -----s up 20200829083145 Create -----s up 20200906141656 Create -----s 修正したテーブル up 20200907114227 Create -----s up 20200927061950 Create -----s up 20200927065357 Create -----s ※----は自分で作成したテーブル名rails db:migrateコマンドの場合は、downのテーブルを全てupに変更します。
db:migrateは1度で全てupにするけど
db:rollbackは1つずつしかdownに出来ないんですね。一度にrollbackをまとめて行いたい場合
今回の様に複数回rollbackを行わないといけない場合にまとめて行える方法も紹介します
ターミナル% rails db:rollback STEP=2※STEP=2を入力する事でrollbackを2回分まとめて実行してくれます。
rollbackコマンドに慣れてきたら、STEPオプションも積極的に使って、作業性をあげていきましょう。
- 投稿日:2020-09-28T17:49:14+09:00
SQLの小ワザ(指定した日付から、指定した日数分の日付を取得する)
はじめに
ほぼタイトルで完結していますが、「指定した日付から、指定した日数分の日付を取得するSQL」をご紹介します。
やってる事は何てことない & あんまり使いどころないですが、何かの足しになればと。
※ MySQL5.6の環境で実行しています。コード
下記のような感じです。
この例では、2017-01-01
から、1000日分の日付を取得しています。select date_format(date_add('2016-12-31', interval numbers.generate_series day), '%Y-%m-%d') as date from ( SELECT 0 generate_series FROM DUAL WHERE (@num:=1-1)*0 UNION ALL SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 1000 ) as numbers ;結果
"2017-01-01" "2017-01-02" "2017-01-03" "2017-01-04" "2017-01-05" "2017-01-06" "2017-01-07" "2017-01-08" "2017-01-09" "2017-01-10" "2017-01-11" "2017-01-12" "2017-01-13" "2017-01-14" . .ちょっと解説
下記の部分で、1〜1000までの数字を取得しています。
SELECT 0 generate_series FROM DUAL WHERE (@num:=1-1)*0 UNION ALL SELECT @num:=@num+1 FROM `information_schema`.COLUMNS LIMIT 1000それを、
date_add
で日付に加算しているだけです。最後に
分かってしまえばなんて事はないコードですが、SQLにあまり触れてないエンジニアの方だと、ぱっと見意味不明だと思います。
正直、あんまり使うケースはない気もします。マスタデータ作成したり、データマート作る時に使ったりくらいですかね・・・。
SQL楽しいです。
- 投稿日:2020-09-28T15:09:28+09:00
【ウィンドウ関数】各グループごとの最大値行だけを取得する【Athena】
グループ毎に最大値を取ってくるのが難しい
特定カラムの最大値を取ってくるだけであればMAX関数を利用するだけで解決するので特に悩むところはないと思われます。mysql例
しかし、以下のようなテーブルからuser_id毎の最大値を取ってくる場合、一工夫いります。
user_id score 1 200 3 120 2 140 2 220 3 100 4 50 5 270 4 500 5 10 今回はAthenaで実行したのでcreate文が以下となっています。
create.sqlCREATE EXTERNAL TABLE IF NOT EXISTS tmp.max_test ( `user_id` int, `score` int ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ( 'serialization.format' = ',', 'field.delim' = ',' ) LOCATION 's3://example_bucket/' TBLPROPERTIES ('has_encrypted_data'='false');ウィンドウ関数の利用
この場合、ウィンドウ関数の
row_number関数
を利用して各グループ、今回であればuser_id毎にスコアに降順の順位ををつけてその上位1位を取得することで最大値行の一覧を引っ張ってこれます。select.sqlselect user_id, score from ( SELECT user_id, score, ROW_NUMBER() over(partition by user_id ORDER BY score desc) AS score_rank FROM tmp.max_test ) where score_rank = 1簡単に解説
サブクエリ内のROW_NUMBER関数のoverの括弧内partitionでグループを分けます。今回はuser_id毎に最大値を取得したいのでuser_idですが、複数のカラムを使ってpartitionを分けることも可能です。その場合はカンマでつなげます。
orderby句でそのpartition内の順番を決めます。今回はscoreを降順で並べたいのでscore_rank desc
とします。サブクエリの結果はこのようになります。
user_id score score_rank 1 200 1 1 150 2 2 220 1 2 140 2 3 120 1 3 100 2 4 500 1 4 50 2 5 270 1 5 10 2 これをみると後は
where score_rank = 1
で持ってくればいいというのがはっきりわかります。MySqlでも同様の関数がありました。Window Function Descriptions。
- 投稿日:2020-09-28T04:23:46+09:00
データベース用語集
この度、研修も終わりイルカさんチームに配属されたので、勉強がてらデータベースの用語を簡単にまとめてみようと思います。
(RDBMSメインになります)データベース
検索・追加・更新などがしやすいように整理された情報の集合。データベース管理システム(DBMS)やDBMS上で管理されるデータ群のことを指していわれることもある。
DBMS(Database Management System)
コンピュータ上でデータベースを構築・運用するためのシステム、およびソフトウェア。
データモデル
どのようにデータベースが構造化され利用されるかを定めた仕様。
階層型
データを木構造で表現したデータモデル。データは常に1対多の親子関係となるため冗長化が起こりやすい。
ネットワーク型
任意のデータ同士を繋げることができる網目状のデータモデル。多対多の関係性を表現できるが、データの検索や更新がデータ構造に強く依存し、かなりの習熟者でないとコーディングが煩雑になりやすい。
リレーショナル型
関係型モデルとも呼ばれ、データを行と列からなる2次元の表形式で表したデータモデル。行はデータのエントリー(レコード)を表し、列はエントリーが持つ属性(各項目)を示す。データ同士は複数の表(テーブル)の同士の関係によって関連付けられて、SQLによってユーザからの目的に応じた操作が可能となる。
RDBMS(Relational Database Management System)
リレーショナル型のデータモデルに基づいて設計、開発されたデータモデル管理システム。
代表的なものとして、OSSではMySQL, PostgreSQL, MariaDB、商用ではOracle Database, IBM DB2などがある。レコード
DBに記録する一件一件のデータの基本単位。通常複数の項目に対応する値を持つ。
フィールド
各レコードに含まれるデータ項目。
テーブル
列がフィールド、行がレコードからなる表。RDBにおけるテーブルはデータを格納する場所の基本単位で、原則としてすべてのデータは何らかのテーブルに保管される。
主キー(Primary Key)
テーブル内で各レコードを一意に識別するためのフィールド。
インデックス
データの検索効率を向上させるための仕組み。インデックス構造はB-Treeがよく用いられる。
ビュー
テーブルから必要なデータを取得し組み合わせて表された仮想的なテーブル。
スキーマ
データ構造や関連性、操作方法といったデータベースの設計図。
3層スキーマ
データベースのスキーマを3階層に分けて定義する方式。
内部スキーマ
概念スキーマで定義された論理データの物理的な格納方法を定義する。
概念スキーマ
データベース化する対象のデータ要素やデータ同士の関係性などのデータ構造を定義する。(テーブルに相当)
外部スキーマ
概念スキーマで定義された論理データから必要な部分を取り出したもの。(ビューに相当)
正規化
表構造を整理するために、データの冗長性がない状態(One Fact In One Place)となるよう整合性の取れたデータ構造を導き出す手法。
関数従属
ある2つのデータ項目間において、一方の値がもう一方の値を関数的に決定する性質。
部分関数従属
複数の候補キーの一部に関数従属している状態。(逆に全てに関数従属している状態を完全関数従属と呼ぶ)
推移的関数従属
関数従属性が推移的に導かれる状態、すなわち3つのデータA,B,Cに関してA→BかつB→Cかつ(A→Cではない)ときCはAに推移的関数従属していると言える。
非正規形
全く正規化が行われていない、繰り返し項目・集合属性を含んでいるテーブルの状態。
第1正規形
非正規形のテーブルから、繰り返し部分を排除した結果のテーブルの状態。
第2正規形
第1正規形のテーブルから、部分関数従属している項目を排除した結果のテーブルの状態。
第3正規形
第2正規形のテーブルから、主キーに推移的関数従属している項目を排除した結果同のテーブルの状態。
SQL(Structured Query Language)
RDBMSにおいて、データの操作や定義を行うためのデータベース言語、またドメイン固有言語。
DDL(Data Definition Language)
SQLにおいて、データを定義するための命令群。データベースとテーブルの作成(CREATE)・更新(ALTER)・削除(DROP)を行う。
DML(Data Manipulation Language)
SQLにおいて、データを操作するための命令群。テーブルのレコードの取得(SELECT)・追加(INSERT)・更新(UPDATE)・削除(DELETE)を行う。
DCL(Data Control Language)
SQLにおいて、トランザクションを制御するための命令群。トランザクション処理の開始(BEGIN)・確定(COMMIT)・取り消し(ROLLBACK)を行う。
トランザクション
関連する複数のクエリを処理単位としてまとめたもの。トランザクション処理はACID特性と呼ばれる4つの性質を求められる。
Atomicity(原子性/不可分性)
トランザクションに含まれる各処理は「すべて実行される」or「1つも実行されない」どちらかの結果になるという性質。例えばサーバーやネットワーク障害により、途中までしか実行されていなかった場合はトランザクションを開始する前の状態に戻される。
Consistency(一貫性/整合性)
トランザクションの前後でデータの整合性が保たれ、矛盾のない状態が継続される性質。トランザクションに関係するすべての条件が満足されることを保証する。例えば在庫を超える数の注文がされて在庫数がマイナスになってしまったり、UNIQUE制約のカラムにもかかわらず値が重複してしまうといったことが生じることを防ぐ。
Isolation(独立性/隔離性)
複数の実行中トランザクションの処理過程が、お互いの処理に影響を与えない性質。別のトランザクションの影響をどの程度隠蔽するかはトランザクション分離レベルによって異なる。
Durability(耐久性/永続性)
トランザクションが完了したとき、その結果は記録され、システム障害などが発生しても失われることがないという性質。データの更新履歴をログに保存しておくことにより、ストレージへの書き込みが中断された場合でもログを元に実行結果を再現することができる。
ロールバック
トランザクション中で、それより前のチェックポイント時点での状態に戻すこと。
ロールフォワード
障害などによりトランザクションが中断された際、記録されているチェックポイントの状態を再現した上でログファイルに残っているチェックポイント後の処理まで実行し障害直前の状態まで戻すこと。
ロック
データの取得・更新を制御すること。あるクエリが特定データを更新するとき、実行前にロックして他のクエリがそのデータを読み書きできないようにすることを排他制御という。
デッドロック
2つのトランザクションがそれじれロックしているデータのロック解除を互いに待っている状態。解除されないと次の処理に進まないため、何らかの回避策を施しておく必要がある。
トランザクション分離レベル
トランザクション間の分離度合いのこと。他のトランザクションから受ける影響によって幾つかの問題が発生する。
ダーティリード(Dirty Read)
他のトランザクションでコミットされていない結果を読み取ってしまう現象。
ファジーリード/ノンリピータブルリード(Fuzzy Read / Non-Repeatable Read)
あるデータを複数回読み取っている途中で、他のトランザクションがそのデータを更新したとき、その更新後の値を読み取ってしまう現象。
ファントムリード(Phantom Read)
ある一定の範囲のレコードに対して処理を行っている途中で、他のトランザクションがその範囲にレコードを追加・削除を行ったとき、その追加・削除が反映されてしまう現象。
READ-UNCOMMITTED
他のトランザクションのコミットされていない変更を参照できる。
READ-COMMITTED
他のトランザクションのコミットされた変更を参照できる。
REPEATABLE-READ
既存のレコードに関しては、トランザクション開始時の値を読み取るが、他のトランザクションによるコミットされた追加・削除を参照できる。
※ MySQLのデフォルト。InnoDBではギャップロックやネクストキーロックという仕組みによりファントムリードが発生しない。SERIALIZABLE
複数の並列して動くトランザクションそれぞれの結果が、逐次実行した場合と同じ結果になる。最も強い分離レベルであり、最も安全にデータを操作できるが、相対的に性能は低い。
レプリケーション
あるデータベースの複製を、別のコンピュータ上に展開しデータの内容を同期させる機能。
- 投稿日:2020-09-28T01:14:44+09:00
RDBMS特にDWHにおけるユーザー定義関数呼び出しオーバーヘッドによる性能劣化について
はじめに
多くの RDBMS では手続き型言語でユーザー定義関数(ストアドファンクション)を作成することができます。コード・ロジック再利用の観点からは有用ですが、SQL文からのユーザー定義関数の呼び出しはかなりオーバーヘッドがあり、呼び出し回数によっては性能劣化の原因になることが多いです。
この事は DWH 系システムでは特に顕著で、データモデルやシステムのアーキテクチャの設計方針に影響を与えるため、DB/DWH の技術者は大体知っているのですが、アプリ技術者には知らない人も多いようです。
そこで今回は、Oracle, SQL Server, MySQL, PostgreSQL において、ユーザー定義関数を利用した場合とそうでない場合でどれぐらい性能差があるかを実測してみたいと思います。
また、最後の章で回避策もいくつか紹介します。
実測環境
今回は Amazon EC2 のm5.large(vCPU 2コア、メモリ 8GB)上の CentOS 7 の環境で測定してます。利用している RDBMS のバージョンは以下です。
- Oracle:19.3.0.0
- SQL Server:2019 (15.0.4053.23)
- MySQL:8.0.21
- PostgreSQL:13.0
実測対象の処理ですが、以下のカラムを持つテーブル
orders
(注文テーブル)に対して、customer_age
(顧客年齢)列から算出した年代毎にorder_amt
(注文金額)の合計を集計する処理とします。
order_id
:注文IDcustomer_age
:顧客年齢order_amt
:注文金額実測の仕方についていくつか注釈を
- 同一の1,000万件のCSVデータを Oracle, SQL Server, MySQL, PostgreSQL の
orders
テーブルに読み込んでいます。- キャッシュサイズとソートに利用するメモリサイズは十分大きい値を設定しています。
- どの RDBMS においてもパラレルクエリは利用していません。
- 実行計画作成やディスクI/Oなどの影響を除くために、4回連続で処理を実施し、2~3回目の実行の経過時間の平均を評価します。
実測結果
結果だけ先に示します。ユーザー定義関数の利用ありで遅くなる程度は RDBMS により異なりますが、どのケースでも無視できない程度の性能劣化になります。
RDBMS ユーザー定義関数あり ユーザー定義なし 性能劣化 Oracle 18.61秒 1.71秒 10.9倍 SQL Server 42.49秒 1.89秒 22.5倍 MySQL 60.14秒 10.27秒 5.9倍 PostgreSQL 7.28秒 2.68秒 2.7倍 Oracle の場合
年代を算出するためのユーザー定義関数は以下になります。
CREATE OR REPLACE FUNCTION get_age_group(age NUMBER) RETURN VARCHAR AS BEGIN RETURN CASE WHEN age < 20 THEN '20歳未満' WHEN age < 30 THEN '20代' WHEN age < 40 THEN '30代' WHEN age < 50 THEN '40代' WHEN age < 60 THEN '50代' ELSE '60歳以上' END; END; /このユーザー定義関数を使った SQL 文は以下です。
SELECT get_age_group(customer_age) age_group, sum(order_amt) FROM orders GROUP BY get_age_group(customer_age);ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。
SELECT CASE WHEN customer_age < 20 THEN '20歳未満' WHEN customer_age < 30 THEN '20代' WHEN customer_age < 40 THEN '30代' WHEN customer_age < 50 THEN '40代' WHEN customer_age < 60 THEN '50代' ELSE '60歳以上' END age_group, sum(order_amt) FROM orders GROUP BY CASE WHEN customer_age < 20 THEN '20歳未満' WHEN customer_age < 30 THEN '20代' WHEN customer_age < 40 THEN '30代' WHEN customer_age < 50 THEN '40代' WHEN customer_age < 60 THEN '50代' ELSE '60歳以上' END;この2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が10.9倍遅いです。
処理 2回目 3回目 4回目 ユーザー定義関数あり 18.62 18.51 18.69 ユーザー定義関数なし 1.70 1.74 1.70 SQL Server の場合
年代を算出するためのユーザー定義関数は以下になります。
CREATE FUNCTION get_age_group(@age SMALLINT) RETURNS VARCHAR(10) AS BEGIN RETURN CASE WHEN @age < 20 THEN '20歳未満' WHEN @age < 30 THEN '20代' WHEN @age < 40 THEN '30代' WHEN @age < 50 THEN '40代' WHEN @age < 60 THEN '50代' ELSE '60歳以上' END; ENDこのユーザー定義関数を使った SQL 文は以下です。
SELECT dbo.get_age_group(customer_age) age_group, sum(order_amt) sum_order_amt FROM orders GROUP BY dbo.get_age_group(customer_age)ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。
SELECT CASE WHEN customer_age < 20 THEN '20歳未満' WHEN customer_age < 30 THEN '20代' WHEN customer_age < 40 THEN '30代' WHEN customer_age < 50 THEN '40代' WHEN customer_age < 60 THEN '50代' ELSE '60歳以上' END age_group, sum(order_amt) sum_order_amt FROM orders GROUP BY CASE WHEN customer_age < 20 THEN '20歳未満' WHEN customer_age < 30 THEN '20代' WHEN customer_age < 40 THEN '30代' WHEN customer_age < 50 THEN '40代' WHEN customer_age < 60 THEN '50代' ELSE '60歳以上' ENDこの2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が22.5倍遅いです。
処理 2回目 3回目 4回目 ユーザー定義関数あり 42.56 42.50 42.40 ユーザー定義関数なし 1.89 1.89 1.89 MySQL の場合
年代を算出するためのユーザー定義関数は以下になります。
CREATE FUNCTION get_age_group(age SMALLINT) RETURNS VARCHAR(10) DETERMINISTIC NO SQL RETURN CASE WHEN age < 20 THEN '10代' WHEN age < 30 THEN '20代' WHEN age < 40 THEN '30代' WHEN age < 50 THEN '40代' WHEN age < 60 THEN '50代' ELSE '60歳以上' END;このユーザー定義関数を使った SQL 文は以下です。
SELECT get_age_group(customer_age) age_group, sum(order_amt) FROM orders GROUP BY age_group;ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。MySQL は GROUP BY に列別名を使えるのは良いですね。
SELECT CASE WHEN customer_age < 20 THEN '10代' WHEN customer_age < 30 THEN '20代' WHEN customer_age < 40 THEN '30代' WHEN customer_age < 50 THEN '40代' WHEN customer_age < 60 THEN '50代' ELSE '60歳以上' END age_group, sum(order_amt) FROM orders GROUP BY age_group;この2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が5.9倍遅いです。
処理 2回目 3回目 4回目 ユーザー定義関数あり 60.21 59.84 60.38 ユーザー定義関数なし 10.2 10.32 10.25 PostgreSQL の場合
年代を算出するためのユーザー定義関数は以下になります。
CREATE OR REPLACE FUNCTION get_age_group(age SMALLINT) RETURNS VARCHAR(10) AS $$ BEGIN RETURN CASE WHEN age < 20 THEN '10代' WHEN age < 30 THEN '20代' WHEN age < 40 THEN '30代' WHEN age < 50 THEN '40代' WHEN age < 60 THEN '50代' ELSE '60歳以上' END; END; $$ LANGUAGE plpgsql;このユーザー定義関数を使った SQL 文は以下です。
SELECT get_age_group(customer_age) age_group, sum(order_amt) FROM orders GROUP BY age_group;ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。PostgreSQL も GROUP BY に列別名を使えます。
SELECT CASE WHEN customer_age < 20 THEN '10代' WHEN customer_age < 30 THEN '20代' WHEN customer_age < 40 THEN '30代' WHEN customer_age < 50 THEN '40代' WHEN customer_age < 60 THEN '50代' ELSE '60歳以上' END age_group, sum(order_amt) FROM orders GROUP BY age_group;この2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が2.7倍遅いです。
処理 2回目 3回目 4回目 ユーザー定義関数あり 7.38 7.32 7.15 ユーザー定義関数なし 2.68 2.68 2.68 回避策
コード・ロジックの再利用ができないは流石に辛いのですが、こういうときはビューを使うのが一般的です。
Oracle を例に示します。
CREATE VIEW v_orders AS SELECT order_id, customer_age, order_amt, CASE WHEN customer_age < 20 THEN '20歳未満' WHEN customer_age < 30 THEN '20代' WHEN customer_age < 40 THEN '30代' WHEN customer_age < 50 THEN '40代' WHEN customer_age < 60 THEN '50代' ELSE '60歳以上' END age_group FROM orders;このビューを使った以下の SQL 文はユーザー定義関数なしとほぼ同じ性能が出ます。
SELECT age_group, sum(order_amt) FROM v_orders GROUP BY age_group;ただし、ビューを利用する方法は、様々なテーブルで同じロジックを適用したい場合にはテーブル毎にビューを作成する必要があるので、冗長性は残ります。
ビューを使う以外には、RDBMS の機能を利用する手段もあります。私が知っている範囲で紹介します。
Oracle の場合
PRAGMA UDF を利用すると性能劣化の程度が多少軽減されます。今回のケースでは2.3倍程度に性能劣化が抑えられます。
CREATE OR REPLACE FUNCTION get_age_group_pg(age NUMBER) RETURN VARCHAR AS PRAGMA UDF; // この要素を追加 BEGIN RETURN CASE WHEN age < 20 THEN '20歳未満' WHEN age < 30 THEN '20代' WHEN age < 40 THEN '30代' WHEN age < 50 THEN '40代' WHEN age < 60 THEN '50代' ELSE '60歳以上' END; END; /【2020/9/29 追記】
また、以下のようにユーザー定義関数を DETERMINISTIC にする、またはファンクション結果キャッシュを使った場合、PRAGMA UDF には及びませんが、ほんの少し改善されます。
- DETERMINISTIC の場合:7.18倍
CREATE OR REPLACE FUNCTION get_age_group_de(age NUMBER) RETURN VARCHAR DETERMINISTIC // 関数が決定的であると明示的に指定 AS (略)
- ファンクション結果キャッシュの場合:7.04倍
CREATE OR REPLACE FUNCTION get_age_group_rs(age NUMBER) RETURN VARCHAR RESULT_CACHE // ファンクション結果キャッシュを有効化 AS (略)(ファンクション結果キャッシュはもっと速いかと思ったら、そうでもなかった…)
まだ正式リリースされていない Oracle20c になると、SQL マクロという機能が追加され、性能劣化なしでロジックの再利用ができるそうです。
SQL Server の場合
SQL Server 2019 からはスカラー UDF のインライン化という最適化機能が実現されています。
Microsoft SQL Server でのスカラー UDF のインライン化 - SQL Server
ある一定の条件を満たすユーザー定義関数呼び出しは SQL 文実行時にインライン化され、その場合は性能劣化なしでユーザー定義関数が利用できます。
ただし、この「一定の条件」の中に「スカラー UDF を呼び出すクエリの GROUP BY 句で、スカラー UDF 呼び出しが参照されていない。」という条件があり、今回の SQL 文ではインライン化されません。以下のように小手先の対応をすることもできますが。
SELECT a.age_group, sum(a.order_amt) FROM ( SELECT dbo.get_age_group(customer_age) age_group, order_amt FROM orders ) a GROUP BY age_groupこのSQL文では、性能劣化は1.7倍まで抑えることができます。
(しかし、GROUP BYでユーザー定義関数が使えないのは痛いなぁ。他にいくつか苦しい条件もあるし)
PostgreSQL の場合
PostgreSQL ではユーザー定義関数の利用において、手続き型言語(今回は PL/pgSQL)だけではなく、他に3種類の利用方法があります。(37.3. ユーザ定義関数 参照)
- 問い合わせ言語関数(SQLで作成された関数)
- 手続型言語関数(PL/pgSQLやPL/Tclなどで作成された関数)
- 内部関数
- C言語関数
このうち、1番目の問い合わせ言語関数を利用することで、今回のケースでは性能劣化を回避することができます。というか、今回の単純さであれば普通はこちらを利用しますね。
CREATE OR REPLACE FUNCTION get_age_group_sql(age SMALLINT) RETURNS VARCHAR(10) AS $$ SELECT CASE WHEN age < 20 THEN '10代' WHEN age < 30 THEN '20代' WHEN age < 40 THEN '30代' WHEN age < 50 THEN '40代' WHEN age < 60 THEN '50代' ELSE '60歳以上' END; $$ LANGUAGE sql;さいごに
本記事では、ユーザー定義関数の利用が大きな性能劣化の原因になることを紹介しました。
ユーザー定義関数の呼び出しオーバーヘッドがかなり大きいのは DB/DWH 技術者の間では常識ですが、だからといって納得している・仕方ないと思っているかというと、全くそんなことはなく、正直怒りしか沸いてきません。もちろん、内部で SQL 文を実行できてしまうなど自由度が非常に高い手続き型言語を SQL 文から呼び出すことの難しさは分からなくもないのですが…
Oracle や SQL Server では RDBMS の機能として解決しようとなりつつあるように見えるので、今後に期待したいです。
あと、新しめの DWH 系 DB(Redshift や Snowflake など)ではどうなんでしょうね。過去のしがらみがない分、初めからこの問題がないと良いのですが。
【2020/9/29 追記】
データは違いますが、Snowflakeで確認してくださった方がいるようです。ご参考まで。
- ユーザー定義関数ありで 3.2 倍悪化
- ストアドプロシージャで動的SQL使用すると劣化は 1.59 倍に抑えられる
Snowflakeでどうなるか気になると書いて頂いていたので、試してみました。テーブルはSNOWFLAKE_SAMPLE_DATA.TPCH_SF10.ORDERSをlimit 1000000したものです。#SnowflakeDB
— Mineaki Motohashi (@mmotohas) September 28, 2020
■前提条件:
・X-Smallというウェアハウス(8vcpu)を使用
・リザルトキャッシュは無効化
・ローカルディスクキャッシュは有効化 https://t.co/EfGozKLm7N