ページネーションは、大きなデータを小さな個別のページに分割するための処理で、この処理はページングとも呼ばれています。 パジネーションはウェブアプリケーションでよく使われ、Googleでも見ることができます。
さて、次のパートでは、SQL Serverでページ分割を実現する方法について説明します。
サンプル データの準備
ページ付けについて詳しく説明する前に、サンプル テーブルを作成し、そこにいくつかの合成データを入力することにします。 次のクエリでは、果物の名前と販売価格を格納する SampleFruits テーブルを作成します。 次のパートでは、このテーブルを使用します。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
CREATE TABLE SampleFruits (
Id INT PRIMARY KEY IDENTITY(1,1) ,
FruitName VARCHAR(50) ,
Price INT
)
GO
INSERT INTO SampleFruits VALUES(‘Apple’.Fruit) , INSERT INSERT
FruitName VALUES(‘Apple’,20)
INSERT INTO SampleFruits VALUES(‘Apricot’,12)
INSERT INTO SampleFruits VALUES(‘Banana'(バナナ),8)
INSERT INTO SampleFruits VALUES(‘Cherry’,11)
INSERT INTO SampleFruits VALUES(‘Strawberry’,26)
INSERT INTO SampleFruits VALUES(‘Lemon’,4)
INSERT INTO SampleFruits VALUES(‘Kiwi’,14)
INSERT INTO SampleFruits VALUES(‘Coconut’,34)
INSERT INTO SampleFruits VALUES(‘Orange’,24)
INSERT INTO SampleFruits VALUES(‘Raspberry’,13)
INSERT INTO SampleFruits VALUES(‘マンゴー’,9)
INSERT INTO SampleFruits VALUES(‘マンダリン’,19)
INSERT INTO SampleFruits VALUES(‘パインアップル’,22)
GO
SELECT * FROM SampleFruits
|
SQL Server のページネーションとは何ですか？
SQL Server では、ページ分割の目的は、クエリの助けを借りて、結果セットを個別のページに分割することです。 SELECT ステートメントの ORDER BY 句で OFFSET および FETCH 引数を使用すると、SQL Server のページネーション ソリューションになります。
OFFSET 引数は、クエリの結果セットからスキップされる行の数を指定します。 次の例では、クエリは SampleFruits テーブルの最初の 3 行をスキップし、その後、残りのすべての行を返します。
|
1
2
3
4
|
SELECT FruitName.SELECT Fruit Name.Fruit Name, 価格
FROM SampleFruits
ORDER BY 価格
OFFSET 3 ROWS
|
OFFSET 値として0を設定すると結果セットから1行スキップされることはないです。 次のクエリは、この使用法の例です。
|
1
2
3
|
SELECT FruitName.FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 0 ROWS
|
逆に結果セットの合計行数より大きなOFFSET値を設定してしまうと結果上には行は何も表示されないことになります． 次のクエリを考えてみると、SampleFruitsテーブルの総行数は13行で、OFFSET値を20に設定したので、クエリは結果を表示しません。
|
1
2
3
|
SELECT FruitName.FruitName.FruitName.FruitName.FruitName,Price FROM SampleFruits
ORDER BY Price
OFFSET 20 ROWS
|
FETCH引数は結果に何行表示するか指定しますのでOFFSET引数と一緒に使用しないといけません。 次の例では、サンプル・テーブルに対して、最初の5行をスキップして、結果セットを6行に制限しています。
|
1
2
3
4 SELECT FruitName.FruitName, 価格
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 6 ROWS ONLY
|
Tip: TOP CLAUSE は、SELECT ステートメントから返される行の数を制限します。 ORDER BYなしでTOP句を使用するとき、それは任意の結果を返すことができます。 次の例では、クエリの各実行でランダムに3行を返します。
|
1
2
|
SELECT TOP 7 FruitName.FruitName.FruitName, 価格
FROM SampleFruits
|
これまで学んだように、OFFSET-FETCH引数はSELECT文でORDER BY句を必要とします。 OFFSET-FETCH引数を使ったTOP句のような未定義の順序を実装したい場合、以下のようなクエリを使用することができます。
|
1
2
3
|
SELECT FruitName ,価格 FROM SampleFruits
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 7 ROWS ONLY
|
Pagination query in SQL Server
“Pagination” とは何かという答えを理解した後、”Pagination query in SQL Server” を選択します。「という質問の答えを理解した後、SQL Server でページネーション クエリをどのように書くことができるかを学びます。 まずは、以下のクエリを実行し、クエリに取り組みます。
|
1
2
3
4
5
6
7
8
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=4
SELECT FruitName.RowsOfPage
SET @PageNumber.RowsOfPage
DECLARE @PageNumber> AS INT
SET @PageNumber=2
ORDER BY Price
OFFSET (@PageNumber-)1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
このように、このようになります。 上記のクエリでは2つの変数を宣言していますが、これらの変数とは
- @PageNumber – 表示されるページの番号を指定します
- @RowsOfPage – ページに表示される行の数を指定します
- @PageNumber – 表示されるページの番号を指定します
- @PageNumber – 表示されるページの番号を指定します。 その結果、SELECT文は4行を含む2ページ目を表示します
- 著者
- 最近の投稿
- Query Optimization Myths – March 23, 2021年3月17日
- SQL Serverにおけるパラメータ・スニッフィングの症状 – 2021年3月4日
- クエリチューニングに自動プラン補正を使用 – 2021年3月4日
@RowsOfPage – ページに表示される行の数を指定します
Dynamic Sorting with Pagination
アプリケーションでは、異なる列に従ってデータを昇順または降順で並べ替え、ページ付けすることが必要になる場合があります。 この種の要件を克服するために、CASE 条件で ORDER BY 節を使用し、変数でソートできるクエリを取得することができます。 次のクエリは、この使用法の一例です。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @SortingCol AS VARCHAR(100) =’FruitName’
DECLARE @SortType AS VARCHAR(100) = ‘DESC’
SET @PageNumber=1
SET @RowsOfPage=4
SELECT FruitName,価格 FROM SampleFruits
ORDER BY
CASE WHEN @SortingCol = ‘Price’ AND @SortType =’ASC’ THEN Price END ,
CASE WHEN @SortingCol = ‘Price’ AND @SortType =’DESC’ THEN Price END DESC,
CASE WHEN @SortingCol = ‘FruitName’ AND @SortType =’ASC’ THEN FruitName END ,
CASE WHEN @SortingCol = ‘FruitName’ AND @SortType =’DESC’ THEN FruitName END DESC
OFFSET (@PageNumber-, ‘FruitName’)1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
|
また、以下のようなこともあります。 上記のクエリでは、変数でソート列とソート方向を変更することができます。
ループでのページ分割
この例では、1つのクエリですべての離散ページの結果を返すクエリテクニックを学びます。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @MaxTablePage AS FLOAT
SET @PageNumber=1
SET @RowsOfPage=4
SELECT @MaxTablePage = COUNT(*) FROM SampleFruits
SET @MaxTablePage = CEILING(@MaxTablePage/@RowsOfPage)
WHILE @MaxTablePage >= @PageNumber
BEGIN
SELECT FruitName.FruitNumber
SET @MaxTablePage = CEILING(@MaxTablePage/@PagesOfPage) ORDER BY Price
OFFSET (@PageNumber-)1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
SET @PageNumber = @PageNumber + 1
END
|
このクエリーについてです。 という非常にシンプルな数式を作成しました。 まず、SampleFruitテーブルの総行数を@MaxTablePage変数に代入し、それを1ページに何行表示させるかに分けています。 つまり、表示されるページ数を計算したわけです。 ただし、計算値は小数になることがあるので、その場合はCEILING関数を使って、計算値より大きい最小の整数値に切り上げています。 第二段階として、WHILE-LOOPを実装し、@PageNumber変数を最後のページまで反復しています。
まとめ
この記事では、特にSQL Serverについて「ページネーションとは何か」という問いに対する答えを探ろうとしました。 OFFSET-FETCH引数は、SELECT文のORDER BY句で使用する際に、何行スキップして、何行結果セットに表示させるかを実装するのに便利です。 そして最後に、これらの引数を使ってSQL Serverでページネーションを実現する方法を学びました。
彼のキャリアのほとんどは、SQL Server データベース管理と開発に焦点を当ててきました。 現在の興味は、データベース管理とビジネス・インテリジェンスです。 LinkedInで彼を見つけることができます。
Esat Erkecの投稿をすべて表示