SQLのoverについてまとめています。
SQL overはWindow関数（分析関数）
overはSUMやMAXなどの集合関数と組み合わせると、適用範囲を制限することができます。overを使って適用範囲を制限した集合関数を分析関数（またはウインドウ関数）とかOLAP関数と呼びます。
以下は、employees（社員）テーブル、dept_emp（社員・部署紐付けテーブル）、departments（部署）テーブルをjoinし、社員番号順にorder byでソートした結果に、overを使って部署の所属者数を分析関数で取得する例です。
SELECT
employees.first_name,
employees.last_name,
departments.dept_name,
count(*) over (partition by departments.dept_no) as total_member
FROM
`employees`
left join dept_emp on employees.emp_no = dept_emp.emp_no
left join departments on dept_emp.dept_no = departments.dept_no
order by employees.emp_no
limit 20;
+------------+-------------+--------------------+--------------+
| first_name | last_name | dept_name | total_member |
+------------+-------------+--------------------+--------------+
| Georgi | Facello | Development | 85707 |
| Bezalel | Simmel | Sales | 52245 |
| Parto | Bamford | Production | 73485 |
| Chirstian | Koblick | Production | 73485 |
| Kyoichi | Maliniak | Human Resources | 17786 |
| Anneke | Preusig | Development | 85707 |
| Tzvetan | Zielinski | Research | 21126 |
| Saniya | Kalloufi | Development | 85707 |
| Sumant | Peac | Quality Management | 20117 |
| Duangkaew | Piveteau | Quality Management | 20117 |
| Duangkaew | Piveteau | Production | 73485 |
| Mary | Sluis | Customer Service | 23580 |
| Patricio | Bridgland | Development | 85707 |
| Eberhardt | Terkki | Human Resources | 17786 |
| Berni | Genin | Development | 85707 |
| Guoxiang | Nooteboom | Research | 21126 |
| Kazuhito | Cappelletti | Sales | 52245 |
| Cristinel | Bouloucos | Marketing | 20211 |
| Kazuhide | Peha | Development | 85707 |
| Kazuhide | Peha | Production | 73485 |
+------------+-------------+--------------------+--------------+
20 rows in set (3.02 sec)
【関連記事】
▶SQL Joinサンプル集 Joinで遅いSQLの原因を調べる方法
特にビッグデータを扱う場合、「データ全体のカウント数」「データ全体の最大数」などを算出しようとするとクエリの返答が返ってこなくなるほど時間がかかるんですね。そのため、範囲を区切って集計をおこなう関数が必要になってきたというわけです。
Window関数は遅い ビッグデータ環境以外ではgroup byによる集合関数を使ったほうが速い
Window関数は、巨大なデータを扱う際に集計範囲を区切って集計をおこなう苦肉の策のようなものです。一般的な集計クエリでは、group byを使うほうが早くなります。
以下は、上記SQLをexplainで実行計画を確認した結果です。サブクエリなどを使わずに実行できるぶん、実行計画もシンプルです。
explain SELECT
employees.first_name,
employees.last_name,
departments.dept_name,
count(*) over (partition by departments.dept_no) as total_member
FROM
`employees`
left join dept_emp on employees.emp_no = dept_emp.emp_no
left join departments on dept_emp.dept_no = departments.dept_no
order by employees.emp_no;
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filte
red | Extra |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+---------------------------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299246 | 100
.00 | Using temporary; Using filesort |
| 1 | SIMPLE | dept_emp | NULL | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | 100
.00 | Using index |
| 1 | SIMPLE | departments | NULL | eq_ref | PRIMARY | PRIMARY | 16 | employees.dept_emp.dept_no | 1 | 100
.00 | NULL |
+----+-------------+-------------+------------+--------+---------------+---------+---------+----------------------------+--------+------
----+---------------------------------+
際限なくデータが増えていく上、集計処理に高速さが求められる分析業務の場合、Window関数を使ってレスポンスの高速化ができるかどうか検討してみると良いでしょう。
まとめ
ポテパンダの一言メモ
- overは分析関数（window関数）を扱うSQLコード
- 分析関数を使うと集計範囲を区切ることができ、巨大データの集計に対するレスポンスを改善することが可能
- 一般的なデータサイズでは、サブクエリやgroup byによる集計関数を使った方が速いケースもある
