20200928のMySQLに関する記事は7件です。

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

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)

参考資料

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

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.csv
1,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.html

https://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.php

PDOはデフォルトではエラー出力をしないです
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

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

データベースのカラムなどを修正したい場合

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オプションも積極的に使って、作業性をあげていきましょう。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

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楽しいです。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【ウィンドウ関数】各グループごとの最大値行だけを取得する【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.sql
CREATE 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.sql
select
    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

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

データベース用語集

この度、研修も終わりイルカさんチームに配属されたので、勉強がてらデータベースの用語を簡単にまとめてみようと思います。
(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

複数の並列して動くトランザクションそれぞれの結果が、逐次実行した場合と同じ結果になる。最も強い分離レベルであり、最も安全にデータを操作できるが、相対的に性能は低い。

レプリケーション

あるデータベースの複製を、別のコンピュータ上に展開しデータの内容を同期させる機能。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

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:注文ID
  • customer_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 倍に抑えられる

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む