受講料が最大70%OFF 受講料が最大70%OFF

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句で指定する
  • 分析関数は、アイテムの総数、総計、累計、移動平均、範囲の最初・最後の値、ランクなどを算出可能

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。