SQLの分析関数についてまとめています。
MySQLのサンプルデータベースEmployeesを使用しています。
SQLの分析関数は、ビッグデータ用の適用範囲限定の関数 overで指定する
分析関数は、行のグループに対する結果を返します。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 10;
+------------+-----------+--------------------+--------------+
| 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 |
+------------+-----------+--------------------+--------------+
10 rows in set (3.95 sec)
【関連記事】
▶SQL overは、分析関数 ビッグデータの集計区間を制限しレスポンスを上げる
莫大な件数のビッグデータに対して集計をおこなう際、まともに全件処理するとクエリの返答が一向に返ってこないんですね。そこで、集計区間を制限することでレスポンスをあげるために使われます。
上記は、各社員が所属する部署の部員数（total_member）を算出するサンプルです。
分析関数では、count(*)=総数を数える以外に、総計、小計、累計、移動平均、範囲内のアイテム数、範囲の最後の値、ランクの計算などの算出が可能です。
以下は、各社員が所属する部署の中で、もっとも社員番号が大きい社員を新人（sinjin）として算出するサンプルコードです。
SELECT
employees.emp_no,
employees.first_name,
employees.last_name,
departments.dept_name,
max(employees.emp_no) over (partition by departments.dept_no) as sinjin
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 10;
+--------+------------+-----------+--------------------+--------+
| emp_no | first_name | last_name | dept_name | sinjin |
+--------+------------+-----------+--------------------+--------+
| 10001 | Georgi | Facello | Development | 499997 |
| 10002 | Bezalel | Simmel | Sales | 499988 |
| 10003 | Parto | Bamford | Production | 499999 |
| 10004 | Chirstian | Koblick | Production | 499999 |
| 10005 | Kyoichi | Maliniak | Human Resources | 499992 |
| 10006 | Anneke | Preusig | Development | 499997 |
| 10007 | Tzvetan | Zielinski | Research | 499985 |
| 10008 | Saniya | Kalloufi | Development | 499997 |
| 10009 | Sumant | Peac | Quality Management | 499964 |
| 10010 | Duangkaew | Piveteau | Quality Management | 499964 |
+--------+------------+-----------+--------------------+--------+
10 rows in set (3.50 sec)
まとめ
ポテパンダの一言メモ
- 分析関数はウインドウ関数とも呼ばれ、集計区間を制限して処理をおこなう
- 計算区間の定義はover句で指定する
- 分析関数は、アイテムの総数、総計、累計、移動平均、範囲の最初・最後の値、ランクなどを算出可能
