この記事では、SQL PARTITIONBY句と特に、selectステートメントのGROUPBYとの違い。また、SQL PARTITIONBYのさまざまな使用例についても説明します。
SQL PARTITION BYを使用して、結果セットをパーティションに分割し、パーティション化されたデータの各サブセットに対して計算を実行します。
サンプルの準備データ
サンプルデータベースSQLShackDemoにOrdersテーブルを作成し、レコードを挿入してさらにクエリを記述しましょう。
ApexSQL Generateを使用して、この記事にサンプルデータを挿入します。 Ordersテーブルを右クリックして、テストデータを生成します。
ApexSQLGenerateを起動します。 Ordersテーブルにデータを挿入するスクリプトを生成しました。このスクリプトを実行して、Ordersテーブルに100レコードを挿入します。
insertステートメントを実行すると、次の画像のOrdersテーブルにデータが表示されます。
SQL GROUP BY句を使用して、指定した列で結果をグループ化し、Avg（）、Min（）、Max（）などの集計関数を使用して必要な値を計算します。
グループ化関数の構文
|
1
2
3
4
|
SELECT式、集計関数（）
FROMテーブル
WHERE条件
GROUPBY式
|
Ordersテーブルで次の値を検索するとします
- 都市の最小注文値
- 都市の最大注文値
- 都市の平均注文値
GROUP BY句を使用して次のクエリを実行し、これらの値を計算します。
|
1
2
3
4
5
6
|
SELECT Customercity、
AVG（Orderamount）AS AvgOrderAmount、
MIN（OrderAmount）AS MinOrderAmount、
SUM（ Orderamount）TotalOrderAmount
FROM。
GROUP BY Customercity;
|
次のスクリーンショットでは、平均、最小を確認できます。 CustomerCityによってグループ化された最大値。
ここで、CustomerName列とOrderAmount列も出力に追加します。 selectステートメントにこれらの列を追加して次のコードを実行しましょう。
|
1
2
3
4
5
6
|
SELECT Customercity、CustomerName、OrderAmount、
AVG（Orderamount）AS AvgOrderAmount、
MIN（OrderAmount）AS MinOrderAmount、
SUM（Orderamount）TotalOrderAmount
FROM。
GROUP BY Customercity;
|
このクエリを実行すると、エラーメッセージが表示されます。 SQL GROUP BY句では、selectステートメントの列がGroup by句でも使用されている場合は、その列を使用できます。 GROUPBY句の一部ではないselect句の列は許可されません。
SQLを使用できますこの問題を解決するためのPARTITIONBY句。次のセクションでさらに詳しく見ていきましょう。
SQL PARTITION BY
SQL PARTITION BY句とOVER句を使用して、集計を実行する必要のある列を指定できます。 。前の例では、CustomerCity列でGroup Byを使用し、平均値、最小値、最大値を計算しました。
次のクエリを使用して、SQL PARTITIONBY句を使用してこのシナリオを再実行します。
|
1
2
3
4
5
|
SELECT Customercity、
AVG（Orderamount）OVER（PARTITION BY Customercity）AS AvgOrderAmount、
MIN（OrderAmount）OVER（PARTITION BY Customercity）AS MinOrderAmount、
SUM（Orderamount）OVER（PARTITION BY Customercity）TotalOrderAmount
FROM。;
|
出力では、次のような集計値が得られます。 GROUPBy句。 SQL PARTITIONBY句とGROUPBY句の出力の違いに気付くかもしれません。
|
グループ化 |
SQL PARTITION BY |
|
GroupBy句を使用して取得するレコードの数は限られています |
PARTITIONBY句を使用してテーブル内のすべてのレコードを取得します。 |
|
結果セットのグループごとの行。たとえば、GROUPBY句でCustomerCityの各グループの結果を取得します。 |
指定されたテーブルの各レコードを含む集計列を提供します。 Ordersテーブルには15個のレコードがあります。 SQL PARTITION BYのクエリ出力では、最小値、最大値、平均値とともに15行も取得します。 |
前の例では、GROUPBY句の一部ではない列を追加しようとするとエラーメッセージが表示されます。
SQL PARTITIONBY句を使用してselectステートメントに必要な列を追加できます。 。 CustomerName列とOrderAmount列を追加して、次のクエリを実行しましょう。
|
1
2
3
4
5
6
7
|
SELECT Customercity、
CustomerName、
OrderAmount、
AVG（Orderamount）OVER（PARTITION BY Customercity）AS AvgOrderAmount 、
MIN（OrderAmount）OVER（PARTITION BY Customercity）AS MinOrderAmount、
SUM（Orderamount）OVER（PARTITION BY Customercity）TotalOrderAmount
FROM。;
|
CustomerName列とOrderAmount列を次の出力とともに取得します。集約された関数。 Ordersテーブルで利用可能なすべての行も取得します。
次のスクリーンショットでは、CustomerCityChicagoで実行できます。集計（平均、最小、最大）し、それぞれの列に値を示します。
同様に、次のような他の集計関数を使用できます。 SQL PARTITIONBY句を使用して特定の都市の注文の総数を確認するためのカウントとして。
|
1
2
3
4
5
6
7
8
|
SELECT Customercity、
CustomerName、
OrderAmount、
COUNT（OrderID）OVER（PARTITION BY Customercity）AS CountOfOrders、
AVG（Orderamount）OVER（PARTITION BY Customercity）AS AvgOrderAmount、
MIN（OrderAmount）OVER（PARTITIO N BY Customercity）AS MinOrderAmount、
SUM（Orderamount）OVER（PARTITION BY Customercity）TotalOrderAmount
FROM。;
|
特定の都市の注文数を確認できます。たとえば、オースティン市から2つの注文があります。 CountofOrders列に値2が表示されます。
ROW_NUMBER（）を使用したPARTITIONBY句
SQL PARTITION BY句をROW_NUMBER（）関数とともに使用して、各行の行番号を指定します。 SQL PARTITIONBY句でROW_NUMBERを使用するために、次のパラメータを定義します。
- PARTITION BY列–この例では、CustomerCity列でデータを分割します
- Order By ：ORDER BY列では、行番号を定義する列または条件を定義します。この例では、OrderAmount列のデータを並べ替えます
|
1
2
3
4
5
6
7
8
9
10
|
SELECT Customercity、
CustomerName、
ROW_NUMBER（）OVER（PARTITION BY Customercity
ORDER BY OrderAmount DESC）AS “Row Number”、
OrderAmount、
COUNT （OrderID）OVER（PARTITION BY Customercity）AS CountOfOrders、
AVG（Orderamount）OVER（PARTITION BY Customercity）AS AvgOrderAmount、
MIN（OrderAmount）OVER（PARTITION BY Customercity）AS MinOrderAmount、
SUM（Orderamount）OVER（PARTITION BY Customercity）TotalOrderAmount
FROM。;
|
次のスクリーンショットでは、CustomerCityChicagoが表示されています。 、最大金額7577.90の注文の行番号1があります。行番号にOrderAmountの降順を提供します。
累積合計値を含むPARTITIONBY句
パーティション内の注文の累積合計を取得したい。累積合計は、パーティション内の現在の行と次の行である必要があります。
たとえば、シカゴ市では、 4つの注文があります。
|
CustomerCity |
CustomerName |
ランク |
OrderAmount |
累積合計行数 |
累積合計 |
|
シカゴ |
マーヴィン |
ランク1 + 2 |
|||
|
シカゴ |
ローレンス |
ランク2+ 3 |
|||
|
シカゴ |
Alex |
ランク3+ 4 |
|||
|
シカゴ |
ジェローム |
ランク4 |
次のクエリでは、ROWS句を指定して設定します現在の行（CURRENT ROWを使用）と次の行（1 FOLLOWINGを使用）を選択します。さらに、CustomerCityのパーティションでsum（Orderamount）を使用して（OVER（PARTITION BY Customercity ORDER BY OrderAmount DESC）を使用して）これらの行の合計を計算します。
|
1
2
3
4
5
6
7
|
SELECT Customercity、
CustomerName、
OrderAmount、
ROW_NUMBER（）OVER（PARTITION BY Customercity
ORDER BY OrderAmount DESC）AS “Row Number”、
CONVERT（VARCHAR（20）、SUM（orderamount）OVER （Customition by Customercity
ORDER BY OrderAmount DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING）、1）AS CumulativeTotal、
|
同様に、次を使用して累積平均を計算できます。次のクエリSQL PARTITIONBY句を使用します。
|
1
2
3
4
5
6
7
|
SELECT Customercity、
CustomerName、
OrderAmount、
ROW_NUMBER（）OVER（PARTITION BY Customercity
ORDER BY OrderAmount DESC）AS “Row Number”、
CONVERT（VARCHAR（20）、AVG（orderamount）OVER（PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING） 、1）AS CumulativeAVG
|
PARTITIONBY句を使用したROWSUNBOUNDED PRECEDING
SQL PARTITIONBY句を使用したROWSUNBOUNDED PRECEDINGを使用して、パーティション内の行を選択できます。現在の行と最大値rの前現在の行の後にあります。
次の表では、行1を確認できます。このパーティションには、値の高い行はありません。したがって、累積平均値は行1のOrderAmountと同じです。
行2の場合、現在の行の値（7199.61）と最大値の行1（7577.9）を検索します。これら2つの金額の平均を計算します。
行3の場合、現在の値（6847.66）と、この値である7199.61および7577.90よりも高い金額の値を探します。これらの平均を計算して返します。
|
CustomerCity |
CustomerName |
ランク |
OrderAmount |
累積平均行数 |
累積平均 |
|
シカゴ |
マーヴィン |
ランク1 |
|||
|
シカゴ |
ローレンス |
ランク1+ 2 |
|||
|
シカゴ |
アレックス |
ランク1+ 2 + 3 |
|||
|
シカゴ |
ジェローム |
ランク1+ 2 + 3 + 4 |
次のクエリを実行して、サンプルデータでこの結果を取得します。
|
1
2
3
4
5
6
7
8
|
SELECT Customercity、
CustomerName、
OrderAmount、
ROW_NUMBER（）OVER（PARTITION BY Customercity
ORDER BY OrderAmount DESC）AS “行番号”、
CONVERT（VARCHAR（20）、AVG（orderamount）OVER（PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING）、1） AS CumulativeAvg
FROM。;
|
結論
この記事では、SQL PARTIIONBY句とそのGROUPBY句との比較について説明しました。また、いくつかの例でその使用法を学びました。この記事がお役に立てば幸いです。下のコメントでお気軽に質問してください。
- 作成者
- 最近の投稿
彼は、SQL Server Always On可用性グループに関する50部構成のシリーズで、単一のトピックに関する記事の最大の無料オンラインコレクションの1つを作成しています。 SQL Serverコミュニティへの貢献に基づいて、彼はSQLShackで2020年と2021年に継続的に名誉ある「今年のベストオーサー」を含むさまざまな賞を受賞しています。
Rajは常に新しい課題に関心を持っているため、コンサルティングが必要な場合は彼の著作で取り上げられている主題についてのヘルプは、rajendra.gupta16 @ gmail.comに連絡できます。
RajendraGuptaによるすべての投稿を表示
- SQLServerのセッションタイムアウトAlwaysOn Availabilityグループ-2021年2月8日
- AWS RDS SQLServerのマイナーバージョンとメジャーバージョンのアップグレードの実行-2021年1月29日
- AWS RDSPostgreSQLインスタンスのデプロイ-2021年1月27日