Oracleでページングを実現! OFFSET句の使い方

 
一覧画面の表示などでページャーを作成する場合、
DBからデータを取得するSQLで取得件数を制御する方法があります。
 
oracle DatabeseではこれまでROWNUMやROW_NUMBERで実現可能でしたが、
ROWNUMの採番順序とソートの順序などを考慮するなど直感的にできませんでした。
 
しかしoracle12cからはOFFSET句に対応しており、簡単に取得する行数を指定できるようになりました。
 
そこで今回はOFFSET句についてご説明したいと思います。
 

構文

SELECT … [OFFSET <スキップ行数> ROWS]  
FETCH {FIRST|NEXT} [<取得行数>|<パーセント> PERCENT]  
ROWS {ONLY|WITH TIES} ;
 
 

基本的な使い方

以下のようなID、NAME、RANKを持つSAMPLEテーブルを用意します。

SQL> SELECT * FROM SAMPLE ORDER BY RANK;
        ID NAME                 RANK
---------- -------------------- --------------------
         3 Aさん                1位
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位
         4 Dさん                4位
         5 Fさん                6位
         7 Gさん                7位

 
 
ここで例えばRANKの昇順で2行目から3件を取得したい場合
スキップ行数に1を、取得行数に3を設定し、以下のように記述します。
SQL> SELECT * 
FROM SAMPLE 
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 3 ROWS ONLY;
        ID NAME                 RANK
---------- -------------------- --------------------
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位

1行目がスキップされ、2行目から3件が取得されます。
 
 

WITH TIESの使い方

基本的な使い方ではOFFSET句の最後にROWS ONLYを指定していますが
ここでWITH TIESを指定すると、最後の行のORDER BYキーと同じ値の行が全て出力されます。

SQL> SELECT * 
FROM SAMPLE 
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 3 ROWS WITH TIES;
        ID NAME                 RANK
---------- -------------------- --------------------
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位
         4 Dさん                4位

例ではEさんとDさんのRANKが4位で同じ値なので3件指定ですが4件出力されています。
ランキング画面で同率を考慮する場合などはWITH TIESが役立ちそうですね。
 
 

PERCENTの使い方

基本的な使い方では取得する行数を直接指定して取得していますが、
取得する行数を%(割合)でも指定することができます。
ただしこの割合とはスキップする前の行数、
つまりOFFSET句の前までのSQLに対する割合ですので注意してください。

SQL> SELECT * 
FROM SAMPLE 
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 50 PERCENT ROWS ONLY;
        ID NAME                 RANK
---------- -------------------- --------------------
         2 Bさん                2位
         6 Cさん                3位
         1 Eさん                4位
         4 Dさん                4位

例の場合は全7件の50%は3.5件(4件となる)なので
1行スキップされ、2行目から4件取得されます。
 
 
“OFFSET句の前までのSQL”なので、WHERE句で指定した条件は割合に反映されます。
SQL> SELECT * 
FROM SAMPLE 
WHERE ID > 3
ORDER BY RANK 
OFFSET 1 
ROWS FETCH FIRST 50 PERCENT ROWS ONLY;
        ID NAME                 RANK
---------- -------------------- --------------------
         4 Dさん                4位
         5 Fさん                6位

例の場合はWHERE句に該当する全4件の50%は2件なので
1行スキップされ、2行目から2件取得されます。
 
 

まとめ

新しい書き方で書くよりも慣れ親しんだ書き方の方が良いという方もいるかと思います。
しかし、OFFSET句を使用することで副問い合わせが不要になるなど可読性の向上が可能です。
保守等でSQLを解析する時なども考慮して、しっかり抑えておきましょう。

記事をシェア
MOST VIEWED ARTICLES