指定した列の値でグループ化して集計する - PARTITION BY の利用 - SQL

指定した列の値でグループ化して集計するSQLを紹介します。

概要

集計関数で値を求める際に、指定した列の値でグループ化して、各グループごとに集計関数を実行したい場合があります。
SQLの PARTITION BY (OVER PARTITION BY)を利用すると、指定した列ごとにグループ化して集計関数を実行できます。

書式

以下の書式を利用します。

記述例

select *, rank() over (partition by point) as ranking from ItemTable

例1:rank() による順位付与

以下のテーブルを準備します。

Products テーブル

id	name	price	category
1	Penguin	500	Bird
2	Bear	1050	Mammal
3	Duck	150	Bird
4	Camel	920	Mammal
5	Owl	185	Bird
6	Whale	880	Mammal

次のSQLを実行します。

SELECT *, RANK() OVER (PARTITION BY category ORDER BY price) AS ranking FROM Products

解説

RANK() OVER (PARTITION BY category ORDER BY price) AS ranking 部分が PARTITION BY によるSQL文です。 category の値ごとにグループ化し、それぞれのグループでORDER BY priceを実行し、priceの値でソートして、RANK関数で順位の値を設定します。
RANK関数の値は AS ranking より、ranking 列とします。

実行結果

SQL文を実行すると以下の結果が返ります。
category列の値ごとにpriceの値で順位の値がranking列に設定された結果が得られます。

id	name	price	category	ranking
3	Duck	150	Bird	1
5	Owl	185	Bird	2
1	Penguin	500	Bird	3
6	Whale	880	Mammal	1
4	Camel	920	Mammal	2
2	Bear	1050	Mammal	3

例2:max() による最高値

先のProductsテーブルを用意し以下のSQL文を実行します。

SELECT *, MAX(price) OVER (PARTITION BY category) AS maxprice FROM Products

解説

MAX(price) OVER (PARTITION BY category) AS maxprice 部分が PARTITION BY によるSQL文です。 categoryごとにグループ化し、そのグループ内でのpriceの最大値を列に設定します。 列名は、AS maxprice により、maxprice列となります。

実行結果

SQL文を実行すると以下の結果が返ります。
category列ごとに最大値がmaxpriceに設定されます。 category が Bird のレコードは、"id=1" の "Penguin" のprice の値が一番大きい500ですので、maxpriceには500が設定されます。 一方、categoryが Mammal のレコードは、"id=2" の　"Bear" のprice の値が一番大きい1050ですので、maxpriceには1050が設定されます。

id	name	price	category	maxprice
1	Penguin	500	Bird	500
3	Duck	150	Bird	500
5	Owl	185	Bird	500
6	Whale	880	Mammal	1050
4	Camel	920	Mammal	1050
2	Bear	1050	Mammal	1050

例3:sum() による合計

先のProductsテーブルを用意し以下のSQL文を実行します。

SELECT *, SUM(price) OVER (PARTITION BY category) AS total FROM Products

解説

SUM(price) OVER (PARTITION BY category) 部分が PARTITION BY によるSQL文です。 categoryごとにグループ化し、そのグループ内でのpriceの合計値を列に設定します。 列名は、AS total により、total列の名称になります。

実行結果

SQL文を実行すると以下の結果が返ります。
categoryの値ごとに、priceの合計値がtotal列に設定されます。 categoryがBirdのレコードは、Penguinの500、Duckの150、Owlの185を足した835が設定されます。 categoryがMammalのレコードは、Whaleの880、Camelの920、Bearの1050を足した2850が設定されます。

id	name	price	category	total
1	Penguin	500	Bird	835
3	Duck	150	Bird	835
5	Owl	185	Bird	835
6	Whale	880	Mammal	2850
4	Camel	920	Mammal	2850
2	Bear	1050	Mammal	2850

例4

PARTITION BY 句を利用して、GROUP BYクエリーが返す列の値に最大または最新のレコードの値を返す処理を実現できます。
詳細はこちらの記事を参照してください。