SQL PARTITION BYのサンプル(単位毎に表示)

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

目次

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

PARTITION BYとは

  • partition byは、グループ単位で表示します。
  • group byはグループでまとめて1行にしますが、partition byは1行にしません。
  • partition(パーティション)は、仕切りという意味です。

partition byの使用例

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

syouhinテーブル
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 syouhin
order by category;

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

結果

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

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

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

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

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

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

syouhinテーブル
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 syouhin
order by category;

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

結果

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

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

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

rank()とrow_number()の違い

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

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

syouhinテーブル
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 syouhin
order by category;

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

結果

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

syouhinテーブル
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関数で指定した前の行の値を取得するサンプルです。

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

syouhinテーブル
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 syouhin
order by category;

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

結果

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

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

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

関連の記事

SQL 複数の行をまとめる(集約関数/group by/having)

△上に戻る