新しい特性解読|MySQL 8.0ウィンドウ関数の詳細
8527 ワード
作者:楊涛
背景
従来、MySQLは、MAX、AVGなどの集約関数に対する要約クラス機能のみであり、SQLレイヤから集約クラスの各グループに対して処理を展開する機能はなかった.しかしMySQLはUDFインタフェースをオープンして、Cで自分でUDFを書くことができて、これは機能行の難易度を増加しました.
このような各グループに対する展開処理の機能をウィンドウ関数と呼び,分析関数と呼ぶデータベースもある.
MySQL 8.0の前に、このような結果を得るには、以下の方法で実現しなければなりません.
1.session変数
2. group_concat関数の組合せ
3.自分でstore routinesを書く
次に、古典的な学生/カリキュラム/成績を用いてウィンドウ関数のプレゼンテーションを行います.
の準備を
学生表
カリキュラム
成績表
テストデータ
MySQL 8.0より前
例えば、成績上位3位の学生の順位を求めて、session変数とgroupを挙げます.concat関数でそれぞれ実現する例:
session変数方式
各グループには、初期値シーケンス番号と初期グループフィールドが割り当てられます.
group_concat関数方式
findinset内蔵関数を使用して、シーケンス番号として下付き文字を返します.
MySQL 8.0ウィンドウ関数
MySQL 8.0では、オリジナルのウィンドウ関数のサポートが提供されます.構文はほとんどのデータベースと同じです.たとえば、前の例です.
row_でnumber()over()は直接ランキングを検索します.
では、カリキュラムMySQLとDBLEで不合格になった最下位の2人の学生リストを見つけましょう.
ここまではrow_をプレゼンテーションしただけですnumber()over()関数の使い方は、他の関数に興味のある方が自分で体験できるので、方法はそれほど悪くありません.
背景
従来、MySQLは、MAX、AVGなどの集約関数に対する要約クラス機能のみであり、SQLレイヤから集約クラスの各グループに対して処理を展開する機能はなかった.しかしMySQLはUDFインタフェースをオープンして、Cで自分でUDFを書くことができて、これは機能行の難易度を増加しました.
このような各グループに対する展開処理の機能をウィンドウ関数と呼び,分析関数と呼ぶデータベースもある.
MySQL 8.0の前に、このような結果を得るには、以下の方法で実現しなければなりません.
1.session変数
2. group_concat関数の組合せ
3.自分でstore routinesを書く
次に、古典的な学生/カリキュラム/成績を用いてウィンドウ関数のプレゼンテーションを行います.
の準備を
学生表
mysql> show create table student \G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE student (
sid int(10) unsigned NOT NULL,
sname varchar(64) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)カリキュラム
mysql> show create table course\G
*************************** 1. row ***************************
Table: course
Create Table: CREATE TABLE `course` (
`cid` int(10) unsigned NOT NULL,
`cname` varchar(64) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)成績表
mysql> show create table score\G
*************************** 1. row ***************************
Table: score
Create Table: CREATE TABLE `score` (
`sid` int(10) unsigned NOT NULL,
`cid` int(10) unsigned NOT NULL,
`score` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)テストデータ
mysql> select * from student;
+-----------+--------------+
| sid | sname |
+-----------+--------------+
| 201910001 | |
| 201910002 | |
| 201910003 | |
| 201910004 | |
| 201910005 | |
| 201910006 | |
| 201910007 | |
| 201910008 | |
| 201910009 | |
| 201910010 | |
+-----------+--------------+
10 rows in set (0.00 sec)
mysql> select * from score;;
+-----------+----------+-------+
| sid | cid | score |
+-----------+----------+-------+
| 201910001 | 20192001 | 50 |
| 201910001 | 20192002 | 88 |
| 201910001 | 20192003 | 54 |
| 201910001 | 20192004 | 43 |
| 201910001 | 20192005 | 89 |
| 201910002 | 20192001 | 79 |
| 201910002 | 20192002 | 97 |
| 201910002 | 20192003 | 82 |
| 201910002 | 20192004 | 85 |
| 201910002 | 20192005 | 80 |
| 201910003 | 20192001 | 48 |
| 201910003 | 20192002 | 98 |
| 201910003 | 20192003 | 47 |
| 201910003 | 20192004 | 41 |
| 201910003 | 20192005 | 34 |
| 201910004 | 20192001 | 81 |
| 201910004 | 20192002 | 69 |
| 201910004 | 20192003 | 67 |
| 201910004 | 20192004 | 99 |
| 201910004 | 20192005 | 61 |
| 201910005 | 20192001 | 40 |
| 201910005 | 20192002 | 52 |
| 201910005 | 20192003 | 39 |
| 201910005 | 20192004 | 74 |
| 201910005 | 20192005 | 86 |
| 201910006 | 20192001 | 42 |
| 201910006 | 20192002 | 52 |
| 201910006 | 20192003 | 36 |
| 201910006 | 20192004 | 58 |
| 201910006 | 20192005 | 84 |
| 201910007 | 20192001 | 79 |
| 201910007 | 20192002 | 43 |
| 201910007 | 20192003 | 79 |
| 201910007 | 20192004 | 98 |
| 201910007 | 20192005 | 88 |
| 201910008 | 20192001 | 45 |
| 201910008 | 20192002 | 65 |
| 201910008 | 20192003 | 90 |
| 201910008 | 20192004 | 89 |
| 201910008 | 20192005 | 74 |
| 201910009 | 20192001 | 73 |
| 201910009 | 20192002 | 42 |
| 201910009 | 20192003 | 95 |
| 201910009 | 20192004 | 46 |
| 201910009 | 20192005 | 45 |
| 201910010 | 20192001 | 58 |
| 201910010 | 20192002 | 52 |
| 201910010 | 20192003 | 55 |
| 201910010 | 20192004 | 87 |
| 201910010 | 20192005 | 36 |
+-----------+----------+-------+
50 rows in set (0.00 sec)
mysql> select * from course;
+----------+------------+
| cid | cname |
+----------+------------+
| 20192001 | mysql |
| 20192002 | oracle |
| 20192003 | postgresql |
| 20192004 | mongodb |
| 20192005 | dble |
+----------+------------+
5 rows in set (0.00 sec)MySQL 8.0より前
例えば、成績上位3位の学生の順位を求めて、session変数とgroupを挙げます.concat関数でそれぞれ実現する例:
session変数方式
各グループには、初期値シーケンス番号と初期グループフィールドが割り当てられます.
SELECT
b.cname,
a.sname,
c.score, c.ranking_score
FROM
student a,
course b,
(
SELECT
c.*,
IF(
@cid = c.cid,
@rn := @rn + 1,
@rn := 1
) AS ranking_score,
@cid := c.cid AS tmpcid
FROM
(
SELECT
*
FROM
score
ORDER BY cid,
score DESC
) c,
(
SELECT
@rn := 0 rn,
@cid := ''
) initialize_table
) c
WHERE a.sid = c.sid
AND b.cid = c.cid
AND c.ranking_score <= 3
ORDER BY b.cname,c.ranking_score;
+------------+-----------+-------+---------------+
| cname | sname | score | ranking_score |
+------------+-----------+-------+---------------+
| dble | | 89 | 1 |
| dble | | 88 | 2 |
| dble | | 86 | 3 |
| mongodb | | 99 | 1 |
| mongodb | | 98 | 2 |
| mongodb | | 89 | 3 |
| mysql | | 100 | 1 |
| mysql | | 81 | 2 |
| mysql | | 79 | 3 |
| oracle | | 98 | 1 |
| oracle | | 97 | 2 |
| oracle | | 88 | 3 |
| postgresql | | 95 | 1 |
| postgresql | | 90 | 2 |
| postgresql | | 82 | 3 |
+------------+-----------+-------+---------------+
15 rows in set, 5 warnings (0.01 sec)group_concat関数方式
findinset内蔵関数を使用して、シーケンス番号として下付き文字を返します.
SELECT
*
FROM
(
SELECT
b.cname,
a.sname,
c.score,
FIND_IN_SET(c.score, d.gp) score_ranking
FROM
student a,
course b,
score c,
(
SELECT
cid,
GROUP_CONCAT(
score
ORDER BY score DESC SEPARATOR ','
) gp
FROM
score
GROUP BY cid
ORDER BY score DESC
) d
WHERE a.sid = c.sid
AND b.cid = c.cid
AND c.cid = d.cid
ORDER BY d.cid,
score_ranking
) ytt
WHERE score_ranking <= 3；
+------------+-----------+-------+---------------+
| cname | sname | score | score_ranking |
+------------+-----------+-------+---------------+
| dble | | 89 | 1 |
| dble | | 88 | 2 |
| dble | | 86 | 3 |
| mongodb | | 99 | 1 |
| mongodb | | 98 | 2 |
| mongodb | | 89 | 3 |
| mysql | | 100 | 1 |
| mysql | | 81 | 2 |
| mysql | | 79 | 3 |
| oracle | | 98 | 1 |
| oracle | | 97 | 2 |
| oracle | | 88 | 3 |
| postgresql | | 95 | 1 |
| postgresql | | 90 | 2 |
| postgresql | | 82 | 3 |
+------------+-----------+-------+---------------+
15 rows in set (0.00 sec)MySQL 8.0ウィンドウ関数
MySQL 8.0では、オリジナルのウィンドウ関数のサポートが提供されます.構文はほとんどのデータベースと同じです.たとえば、前の例です.
row_でnumber()over()は直接ランキングを検索します.
mysql>
SELECT
*
FROM
(
SELECT
b.cname,
a.sname,
c.score,
row_number() over (
PARTITION BY b.cname
ORDER BY c.score DESC
) score_rank
FROM
student AS a,
course AS b,
score AS c
WHERE a.sid = c.sid
AND b.cid = c.cid
) ytt
WHERE score_rank <= 3;
+------------+-----------+-------+------------+
| cname | sname | score | score_rank |
+------------+-----------+-------+------------+
| dble | | 89 | 1 |
| dble | | 88 | 2 |
| dble | | 86 | 3 |
| mongodb | | 99 | 1 |
| mongodb | | 98 | 2 |
| mongodb | | 89 | 3 |
| mysql | | 100 | 1 |
| mysql | | 81 | 2 |
| mysql | | 79 | 3 |
| oracle | | 98 | 1 |
| oracle | | 97 | 2 |
| oracle | | 88 | 3 |
| postgresql | | 95 | 1 |
| postgresql | | 90 | 2 |
| postgresql | | 82 | 3 |
+------------+-----------+-------+------------+
15 rows in set (0.00 sec)では、カリキュラムMySQLとDBLEで不合格になった最下位の2人の学生リストを見つけましょう.
mysql>
SELECT
*
FROM
(
SELECT
b.cname,
a.sname,
c.score,
row_number () over (
PARTITION BY b.cid
ORDER BY c.score ASC
) score_ranking
FROM
student AS a,
course AS b,
score AS c
WHERE a.sid = c.sid
AND b.cid = c.cid
AND b.cid IN (20192005, 20192001)
AND c.score < 60
) ytt
WHERE score_ranking < 3;
+-------+--------------+-------+---------------+
| cname | sname | score | score_ranking |
+-------+--------------+-------+---------------+
| mysql | | 40 | 1 |
| mysql | | 42 | 2 |
| dble | | 34 | 1 |
| dble | | 36 | 2 |
+-------+--------------+-------+---------------+
4 rows in set (0.00 sec)ここまではrow_をプレゼンテーションしただけですnumber()over()関数の使い方は、他の関数に興味のある方が自分で体験できるので、方法はそれほど悪くありません.