標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination Tweet

いよいよ始まりました。Advent Calendar 2020　
今年も勢いで全部俺ですw

JPOUG Advent Calendar 2020もよろしくお願いします！
（最後まで書けるのか、書き抜ける喜びw　どこかのCMっぽくなってしまいましたが、第一日めの窓を開けましょうw）

今年の全部俺はレントゲンではなく、標準はあれど、非常に癖の多いSQLを25回に渡り、眠い目を擦りながら書きづつけて行きたい（すでに希望になってるw）と思っております。はい。

先日、AthenaでLIMIT句使おうとして、エラーに遭遇したところからw

Oracle/MySQL/PostgreSQL/Redshiftとか LIMIT OFFSET的ところから違うわけですが（同じ部分もあります）、何も考えずに、Athenaに投げて、撃沈w
Prestoの311以降だとLIMIT OFFSETでPagination可能なわけですが、Amazon Athenaは今の所(2020/12/1現在）Presto 0.172なのをすっかり忘れてたわけです。はい。さーせん。

あ〜、SQLってバージョンでもそうですが、エンジン違えば、気が狂うw程度に違う時があって、き〜〜〜って。なることしばしば。
で、イラっとしたSQLの違いを Oracleの構文と比較しながら自分の備忘録として書いて残しておこうと思った次第です。

（なーんだ、自分の為か、と思わないでくださいね。明日はわが身かもしれませんよw）

以下、どの構文がどのエンジンで通るのかをざっとまとめたもの。

1.ROWNUMとWHERE句によるPagination
Oracle

SELECT
  id
FROM (
  SELECT
    ROWNUM as ln
    ,id
  FROM
    hoge
  ORDER BY id
)
WHERE
  ln BETWEEN 1 AND 100;

2.ROW_NUMBER()ウィンドウ関数とWHERE句によるPagination
意外に使えるw　可読性悪いけどねー

Oracle / PostgreSQL / Redshift / Athena

SELECT
  id
FROM (
  SELECT
    ROW_NUMBER() OVER(
      ORDER BY id
    ) AS ln
    ,id
  FROM
    hoge
)
WHERE
  ln BETWEEN 1 AND 100;

3.OFFSET句とFETCH FIRST n ROWS ONLYのPagination
Oracle / PostgreSQL

SELECT
  id
FROM
  hoge
ORDER BY
  id
OFFSET 0 ROW
FETCH FIRST 100 ROWS ONLY;

4.LIMIT句とOFFSET句のPagination
PostgreSQL / MySQL / Redshift

SELECT
  id
FROM
  hoge
ORDER BY
  id
LIMIT 100 OFFSET 0;

ちなみに、Oracleでは、2.のROW_NUMBER()と3.のOFFSET + FETCHの構文の実行計画は同じなので、可読性が高い2.と3.いずれかと言われれば3.がおすすめ。

----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    10 |   260 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                  |         |    10 |   260 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|         |    10 |    50 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | HOGE_PK |  1000K|  4882K|     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("LN">=1 AND "LN"<=100)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=100)

----------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |    10 |   390 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                  |         |    10 |   390 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY|         |    10 |    50 |     3   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | HOGE_PK |  1000K|  4882K|     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=100 AND
             "from$_subquery$_002"."rowlimit_$$_rownumber">0)
  2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")<=100)

---

参考
Oracle
https://docs.oracle.com/cd/E82638_01/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726
https://docs.oracle.com/cd/F19136_01/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6

MySQL
https://dev.mysql.com/doc/refman/8.0/en/select.html

PostgreSQL
https://www.postgresql.jp/document/11/html/queries-limit.html

Reshift
https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_ORDER_BY_clause.html

Amazon Athena
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/select.html

---

実行計画は、SQL文のレントゲン写真だ！　Oracle Database編 (全部俺） Advent Calendar 2019

寝ぼけながら書いているので、誤り等ございましたらご指摘願います。
仕事忙しいのに全部俺始めてしまい、かなり不安なスタートw（この時間だし）