SQL PARTITION BYのサンプル(group byとの違い)

SQLのPARTITION BYで、単位毎に表示するサンプルです。

単位毎に表示	PARTITION BYとは
	PARTITION BYで累計を表示する(sum)
	rank()とrow_number()の違い
	lag関数で指定した前の行の値を取得する

PARTITION BYとは

上記図は、partition byとgroup byのイメージ図です。
partition byは、グループ単位で行を表示します。
group byはグループでまとめた行を表示します。
partition(パーティション)は、仕切りという意味です。

partition byの使用例

次のテーブルがあるとします。

salesテーブル
id	name	category	price
1	レタス	野菜	100
2	みかん	フルーツ	200
3	にんじん	野菜	200
4	りんご	フルーツ	150
5	とまと	野菜	250

SQL

上記テーブルに対してpartition byを使用して単位にまとめて表示します。

select name, category ,price,
       rank() over(partition by category order by price desc) 順位
from sales
order by category;

2行目は、partition byでcategory単位に表示し、order byとdescでpriceの高いものから表示します。

結果

結果は、以下のとおりです。

name	category	price	順位
みかん	フルーツ	200	1
りんご	フルーツ	150	2
とまと	野菜	250	1
にんじん	野菜	200	2
レタス	野菜	100	3

category単位でpriceの降順で表示されました。

group by

以下のようにcategoryをgroup byした場合、

select category 
from sales
group by category

結果は、以下になります。

category
フルーツ
野菜

PARTITION BYで累計を表示する(sum)

PARTITION BYで累計を表示するサンプルです。

次のテーブルがあるとします。

salesテーブル
id	name	category	price
1	レタス	野菜	100
2	みかん	フルーツ	200
3	にんじん	野菜	200
4	りんご	フルーツ	150
5	とまと	野菜	250

SQL

上記テーブルに対してpartition byを使用して単位にまとめて表示します。

select name, category ,price,
       sum(price) over(partition by category order by price ) 累計
from sales
order by category;

2行目は、partition byでcategory単位に表示し、sum(price)でpriceを合計しています。

結果

結果は、以下のとおりです。

salesテーブル
name	category	price	累計
りんご	フルーツ	150	150
みかん	フルーツ	200	350
レタス	野菜	100	100
にんじん	野菜	200	300
とまと	野菜	250	550

category単位で表示され、累計の列では、priceの累計が表示されます。

rank()とrow_number()の違い

rank()とrow_number()の違いです。

次のテーブルがあるとします。野菜のにんじんととまとのpriceが同じになっています。

salesテーブル
id	name	category	price
1	レタス	野菜	100
2	みかん	フルーツ	200
3	にんじん	野菜	200
4	りんご	フルーツ	150
5	とまと	野菜	200

SQL

上記テーブルに対してpartition byを使用して単位にまとめて表示します。

select name, category ,price,
     rank()       over(partition by category order by price desc) rank,
     row_number() over(partition by category order by price desc) row1
from sales
order by category;

2行目は、rank()で3行目は、row_number()を使用しています。

結果

結果は、以下のとおりです。

salesテーブル
name	category	price	rank	row1
みかん	フルーツ	200	1	1
りんご	フルーツ	150	2	2
にんじん	野菜	200	1	1
とまと	野菜	200	1	2
レタス	野菜	100	3	3

rank()は、値が同じ時は、同じ順位がつきます。その次の順位は重複した分とびます。
row_number()は、同じ順位はつきません。値が同じ場合を想定してorder byで優先する項目を指定して対応します。

lag関数で指定した前の行の値を取得する

lag(項目,表示したい前の行)

lag関数で指定した前の行の値を取得するサンプルです。

次のテーブルがあるとします。

salesテーブル
id	name	category	price
1	レタス	野菜	100
2	みかん	フルーツ	200
3	にんじん	野菜	200
4	りんご	フルーツ	150
5	とまと	野菜	250

SQL

上記テーブルに対してpartition byとlagを使用します。

select name, category ,price,
     lag(name,1)  over(partition by category order by price desc) rank1,
     lag(name,2)  over(partition by category order by price desc) rank2
from sales
order by category;

2行目は、lag関数の引数に項目のnameと1を、3行目は、項目のnameと1を指定しています。

結果

結果は、以下のとおりです。

salesテーブル
name	category	price	lag1	lag2
みかん	フルーツ	200
りんご	フルーツ	150	みかん
にんじん	野菜	200
とまと	野菜	200	にんじん
レタス	野菜	100	とまと	にんじん

lag関数は、指定した項目の指定した前の行の値を取得します。