2018年 05月

ORACLE の LIMIT OFFSET

業務でデータベースは PostgreSQL を使うことが多いですが ORACLE も使います。 その ORACLE が 12c になりました。 そう、12c になるということは LIMIT や OFFSET が使えるということです。 そんな嬉しさから書きます。

まずはデータの準備。

テーブルを作りたくないので 次の SQL でゴマかします。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4
         5         80          4
         7         75          6
         2         70          7

このようにテストの点数と ランキングが入っています。

まずは LIMIT ですが ORACLE (というかSQL:2008)では LIMIT ではなく 次のような書き方をします。

FETCH FIRST 100 ROWS ONLY

ちなみに この書き方は PostgreSQL でもサポートされています。 (完全に同じではありません)

まずは 4 行だけ取得。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 4 ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4

4 行取得できました。

取得元のデータには RANK が 4 のデータが 2 レコードあります。 4位タイということですね。 4位までをを取得しようとして 4位が 1人しか取得できないのは困りますよね。

そんなときは 次のように書きます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK
FETCH FIRST 4 ROWS WITH TIES
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4
         5         80          4

これで 4 位までを取得することができました。

ここで注意しなくてはならないのは ORDER BY から ID 列を外していることです。

WITH TIES の仕様は “ソート順の最後の項目が同じであれば取得する”ということなので ID が最後ではダメなのです。 もし更に“同順位は ID でソートしたい”ということであれば もう 1 段階ラッピングする必要があります。

行の他に百分率で指定することもできます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
FETCH FIRST 50 PERCENT ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         3         98          1
         6         95          2
         1         85          3
         4         80          4

50% を指定したので 7行中の 4行が取得できました。

次は OFFSET です。
次のような書き方をします。

OFFSET 10 ROWS

OFFSET 4 で 5行目から取得してみます。

SELECT *
FROM     (SELECT 1 AS ID, 85 AS SCORE, 3 AS RANK FROM DUAL
UNION ALL SELECT 2 AS ID, 70 AS SCORE, 7 AS RANK FROM DUAL
UNION ALL SELECT 3 AS ID, 98 AS SCORE, 1 AS RANK FROM DUAL
UNION ALL SELECT 4 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 5 AS ID, 80 AS SCORE, 4 AS RANK FROM DUAL
UNION ALL SELECT 6 AS ID, 95 AS SCORE, 2 AS RANK FROM DUAL
UNION ALL SELECT 7 AS ID, 75 AS SCORE, 6 AS RANK FROM DUAL)
ORDER BY RANK, ID
OFFSET 4 ROWS
FETCH FIRST 4 ROWS ONLY
SQL> /
        ID      SCORE       RANK
---------- ---------- ----------
         5         80          4
         7         75          6
         2         70          7

5 行目から 3行取得できました。

これでページ送りなんかの機能も簡単に作れますね。

Google サイト内検索

Amazonアソシエイト