「最初の n 行」は DB2 では FETCH FIRST n ROWS ONLY で取得できる

前回、ROWNUM 擬似列は標準 SQL の ROW_NUMBER で書き換えられる、ということをご紹介しました、

ROW_NUMBER の場合、副照会や WITH 句などを使ってあらかじめ行を順序づけしておく必要があるわけですが、もうちょっと手軽に「既存の SQL のままで、ただ最初の部分を n 行だけほしい」なんてときもあるでしょう。

たとえば、ROWNUM 擬似列の典型的な使い方である ↓ のような例ですね。

SELECT * FROM employees WHERE ROWNUM < 10;

↑ の

SELECT * FROM employees

のところがどんな SQL だったとしても、とりあえず最後に

ROWNUM < 10

と条件をつけると、最初の 10件が得られる、といったことがしたい場合、ありますよね。

Oracle の場合は、前回紹介したように、そんな単純にはいかないのですが、DB2 ファミリーではこれがカンタンにできるようになっています。

↓ のように、最後に FETCH FIRST n ROWS ONLY とつければいいんですね。

SELECT * 
  FROM Sample.Employee 
 FETCH FIRST 9 ROWS ONLY  

英語そのままの、「最初の n 行のみを取ってくる」わけです。

くりかえしになりますが、Oracle で ROWNUM 擬似列を使う場合には ↓ のような考慮点があります。

「結果は、行がアクセスされる方法によって異なります。

たとえば、ORDER BY句の指定によってOracleが索引を使用してデータにアクセスする場合、索引なしの場合とは異なる順序で行が取り出されることがあります。

このため、次の文では、前述の例と同じ行が戻されるとはかぎりません。」

DB2 の場合にはそんなことを気にすることはありません。ごく自然に ORDER BY をつけてしまってかまいません。

SELECT * FROM Sample.Employee 
 ORDER BY lastname 
FETCH FIRST 9 ROWS ONLY                         

DB2 for IBM i 7.1 での実行例です。

残念ながら、この FETCH FIRST n ROWS ONLY は標準SQL ではありません。DB2 ファミリー固有の構文です。（と思っていたのですが、実はそうではありませんでした → 「FETCH FIRST n ROWS ONLY が Oracle 12c で使えるようになっていますね!!」 2013/7/5 追記）

こういうちょっとした”小技”的なものは各データベースで固有の構文として実現されていることが多いですね。

時々、「Oracle の ROWNUM ではカンタンに行数制限ができるのに DB2 ではやり方がわからない」という質問をもらうことがあるのですが、けっこうこういうシンプルな解決策があったりします。（クドいようですが、、前回紹介したように Oracle でも「カンタン」ではありません…）

もちろん、標準SQLだったら ROW_NUMBER が使えますね。機能は ROW_NUMBER をはじめとする「OLAP 関数」の方がはるかに豊富です。

昔は各個別の製品で”便利な機能”を競っていて、そうしたものを使って「手軽に」「短く（コードの行数だったり期間だったり）」プログラムを作ることが優先される傾向がありました。

しかし、そうしたことで各製品間でポータビリティが下がってしまい、また、時間が経って、知っている人がいなくなったり、その製品でその機能がなくなったり、でいわば「世代間」の「ポータビリティ」が下がってしまい、長い目で見た場合は弊害の方が多いのでは？という意見も現在は多くなってきました。

思えば、昔は PC の OS が各メーカーで固有だったり、ネットワークで接続するにもプロトコルが各メーカーで固有だったり、というのが、今はもう標準化されてそんなことを気にする必要はなくなりましたよね。

同じことがプログラミングの世界でも起きている、と考えていいでしょう。「標準」に準拠して、”うまい””短い”やり方ではなく、ていねいに、誤解のないように書く、ということが優先されるようになってきています。

そうした観点では、やはり原則としては ROWNUM/FETCH FIRST n ROWS ONLY ではなく、ROW_NUMBER をはじめとする OLAP 関数を使ったほうがいいと思います。

機能が豊富なのはもとより、理解できる人も多くいるわけで、それだけプログラムの寿命も延びるわけです。

ただ、杓子定規に原則をあてはめるのではなく、状況を判断しながら ROWNUM や FETCH FIRST n ROWS ONLY なども使えるときは使えばいいのではないでしょうか。

Oracle の ROWNUM は標準SQL の ROW_NUMBER で

ここのところ Oracle 関係の話題が続いたので、今回もちょっとした関連のおはなしをしましょう。

Oracle には ROWNUM という擬似列があります。

ROWNUM 擬似列がどんなものかは、↓ のような典型的な使い方の例を見ればわかりますよね。

SELECT * FROM employees WHERE ROWNUM < 10;

戻してくる行数を制限するような場合によく使われています。

マニュアルにも

「同じ問合せでROWNUMにORDER BY句が続く場合、ORDER BY句によって行が再び順序付けられます」

とあるように、ROWID のように行によって固定されたものではありません。

実際の要件としてありそうなのは、↑の例のような「何でもいいけど十件以下」という選択よりは「～順で上位（下位）十件以下」というものがよくありそうですよね。

そこで、↓ のような SQL が思いつくかもしれませんね。実は、これがそんなカンタンにはいかないんです。

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name

↑ の ORDER BY をつけた例について、マニュアルに ↓ のように載っています。

「結果は、行がアクセスされる方法によって異なります。

たとえば、ORDER BY句の指定によってOracleが索引を使用してデータにアクセスする場合、索引なしの場合とは異なる順序で行が取り出されることがあります。

このため、次の文では、前述の例と同じ行が戻されるとはかぎりません。」

↑ の SQL を書いた人の想定はおそらく「ORDER BY で並べられた選択結果から最初の十件」だと思うのですが、そうならないケースがあるということなんですね。

（「OracleではLimit句が使えない」などを見てみると参考になると思います）

どうすればいいか、というと、↓ のように書き換える必要があるとマニュアルには書いてあります。

SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;

こういうふうにしたとしても、依然 Oracle の ROWNUM には↓ のような考慮事項（マニュアルにのっています）もあり、やはり使うのにはいろいろ注意と工夫が必要になります…

比較条件「ROWNUM値＞正の整数」は、常に偽となるため注意してください。たとえば、次の問合せでは行は戻されません。

SELECT * FROM employees WHERE ROWNUM > 1;

実際、ROWNUM 擬似列についてマニュアルの一番最初に↓ のような「注意」があります。

注意: ROW_NUMBER組込みSQLファンクションは、問合せの結果の順序付けを強力にサポートします。

詳細は、「ROW_NUMBER」を参照してください。

ROWNUM の解説の最初にこんな記述がある、というのは「これからは ROWNUM のかわりに ROW_NUMBER を使ってください」と言っているようにも取れますね。

あまり積極的に使ってほしい位置づけではなさそうです。

実際、↑ にあげたような例はそのまま ROW_MUMBER を使って書き換えることができます。

SELECT *                                                   
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY lastname) AS RN,
               Emp.*                                       
          FROM Sample/Employee AS Emp) AS E                
 WHERE E.RN < 11 ;

DB2 for IBM i 7.1 での実行結果です。

BETWEEN で指定しても同じことですね。

SELECT *                                                      
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY lastname) AS RN,
               Emp.*                                     
          FROM Sample.Employee AS Emp) AS E                          
 WHERE E.RN BETWEEN 1 AND 10 ;                                

DB2 for IBM i 7.1 での実行結果です。

BETWEEN で指定をすると、途中の行も範囲として自由に指定できるようになります。つまり、よく MySQL や PostgreSQL などで使われている LIMIT/OFFSET と同じことができるわけです。

よく「Oracle でも LIMIT/OFFSET を使いたい」という意図の検索がこのサイトにも届くのですが、それが可能になるということなんです。

（ちなみに、以前「標準SQLでLIMIT/OFFSETと同じ結果を得るには?」でもご紹介しました）

ROW_NUMBER を使った場合は↑のような「比較条件「ROWNUM値＞正の整数」は、常に偽となる」といった注意は必要ありません。

SELECT *                                                      
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY lastname) AS RN,
               Emp.*                                     
          FROM Sample.Employee AS Emp) AS E                          
 WHERE E.RN > 1 ;  

DB2 for IBM i 7.1 での実行結果です。

ROWNUM を使うよりは ROW_NUMBER を使った方が、クセがなくわかりやすいと思いますし、いろんな DB で使える書き方になります。

ROW_NUMBER をそのまま素直な使えば、「最初の n 件」から「途中の n 件」まで自由にデータを取得することができるようになります。

Oracle でも「標準SQL」を使っていった方が便利なことが多くなってきているんですね。

分析関数を「素直な SQL」で書き換える（4-4）（PARTITION BY のある MIN）

「分析関数の衝撃（完結編）」から最後の例です。

4. 全称文を述語で表現する

今回の例になる SQL です。

--分析関数で書き換えたSQL
select team_id,member
from (select team_id,member,
      min(case when status = '待機' then 1 else 0 end)
      over(partition by team_id) as willOut
        from Teams) dummy
where willOut = 1;

分析関数が使用されているのは FROM 句の中だけなので、まずその SQL を取り出して見てみましょう。

select team_id,member,
      min(case when status = '待機' then 1 else 0 end)
      over(partition by team_id) as willOut
        from Teams

「ウィンドウ関数をより「素直」に書き換えて理解しよう（2）（OVER 句の PARTITION BY）」で見た PARTITION BY のある集約関数のケースなので、同様に書き換え可能です。

「分析関数を「素直な SQL」で書き換える（4-3）（PARTITION BY のある COUNT/COUNT(DISTINCT)/decode/all）」でも出てきましたね。

SELECT team_id, 
       member,  
      (SELECT MIN(CASE WHEN status = '待機' THEN 1 ELSE 0 END)  
          FROM Teams B
         WHERE A.team_id = B.team_id) AS willOut
  FROM Teams A ;

DB2 for IBM i 7.1 での実行例です。

これをそのままもとの SQL の FROM 句の中と入れ替えてやれば書き換え完成です。

SELECT team_id, 
       member 
  FROM (SELECT team_id, 
               member,  
               (SELECT MIN(CASE WHEN status = '待機' THEN 1 ELSE 0 END)  
                  FROM Teams B
                 WHERE A.team_id = B.team_id) AS willOut
          FROM Teams A) AS dummy 
 WHERE willOut = 1 ;

DB2 for IBM i 7.1 での実行例です。

分析関数を使っている部分は WHERE 句で使われているだけなので、移設するとちょっとすっきりした SQL になります。

SELECT team_id, 
       member 
  FROM Teams A 
 WHERE (SELECT MIN(CASE WHEN status = '待機' THEN 1 ELSE 0 END) 
          FROM Teams B
         WHERE A.team_id = B.team_id) = 1;

DB2 for IBM i 7.1 での実行例です。

今回の例をためしてみるためのデータは ↓ の SQL で作成することができます。ぜひやってみてください。

create table Teams(
member  char(24) not null primary key,
team_id integer,
status  char(18));

insert into Teams values('ジョー',   1, '待機');
insert into Teams values('ケン',     1, '出動中');
insert into Teams values('ミック',   1, '待機');
insert into Teams values('カレン',   2, '出動中');
insert into Teams values('キース',   2, '休暇');
insert into Teams values('ジャン',   3, '待機');
insert into Teams values('ハート',   3, '待機');
insert into Teams values('ディック', 3, '待機');
insert into Teams values('ベス',     4, '待機');
insert into Teams values('アレン',   5, '出動中');
insert into Teams values('ロバート', 5, '休暇');
insert into Teams values('ケーガン', 5, '待機');
commit;

分析関数を「素直な SQL」で書き換える（4-3）（PARTITION BY のある COUNT/COUNT(DISTINCT)/decode/all）

「分析関数の衝撃（完結編）」から三つ目の例です。

3. 等しい部分集合を見つける

今回の例は大作ですね。

--分析関数で書き換えたSQL
select distinct s1,s2
from (select a.sup as s1,b.sup as s2,
      count(distinct a.part) over(partition by a.sup,b.sup) as SupCount1,
      count(distinct b.part) over(partition by a.sup,b.sup) as SupCount2,
      count(decode(a.part,b.part,1))
      over(partition by a.sup,b.sup) as ExistSum
        from SupParts a,SupParts b
       where a.sup < b.sup)
where ExistSum = all(SupCount1,SupCount2);

プログラムの場合、いくら大作でも必ず部分の積み重ねからできています。

まずは特徴的な部分を取り出して、見てみましょう。

CROSS JOIN に対する COUNT OVER(PARTITION BY)

-今までどおりの書き方

COUNT の中身は distinct a.part/distinct b.part/decode(a.part,b.part,1) と変わっていますが、OVER 句の中は常に partition by a.sup,b.sup となっていますね。

とりあえず COUNT の中身は考えず、 COUNT(*) として考えてみましょう。

       select a.sup, b.sup, 
           count(*)
      over(partition by a.sup, b.sup) as ExistSum
        from SupParts a, SupParts b;

FROM 句の、結合条件指定のないテーブルの列挙は「直積」または「デカルト積」といい、すべての結合の組み合わせを出力する指定です。

標準 SQL では CROSS JOIN と記述されます。

PARTITION BY は「ウィンドウ関数をより「素直」に書き換えて理解しよう（2）（OVER 句の PARTITION BY）」で見たように、集約関数の WHERE 句への指定に相当します。複数カラムの指定がある場合は、それぞれを WHERE 句で指定する必要があります。

以上の二点を考慮すると、こんなふうに書き換えられますね。

SELECT A.sup,
       B.sup,  
       (SELECT COUNT(*) AS ExistSum
          FROM (supparts A1 CROSS JOIN supparts B1) 
         WHERE A.sup = A1.sup AND B.sup = B1.sup) AS ExistSum
  FROM (supparts A CROSS JOIN supparts B)
 ORDER BY A.sup, B.sup ;

DB2 for IBM i 7.1 での実行例です。

-GROUP BY による別の書き方

ちなみに、別途 GROUP BY で COUNT して JOIN しても同じ結果が得られます。

SELECT A.sup,
       B.sup,  
       ExistSum
  FROM (supparts A CROSS JOIN supparts B) 
       JOIN 
       (SELECT A.sup AS A_sup, 
               B.sup AS B_sup, 
               COUNT(*) AS ExistSum
          FROM supparts A CROSS JOIN supparts B 
         GROUP BY A.sup, B.sup) C 
       ON A.sup = C.A_sup AND B.sup = C.B_sup
 ORDER BY A.sup, B.sup;

DB2 for IBM i 7.1 での実行例です。

ふたつの書き換え例を比較してみると、見るからに JOIN の例の方が長いですよね。

今回は最初の書き換え例の方を採用して先に進みたいと思います。

DECODE は CASE 式に

次に、COUNT の中の decode を見てみましょう。

      select a.sup, b.sup,
           count(decode(a.part,b.part,1)) 
      over(partition by a.sup,b.sup) as ExistSum
        from SupParts a,SupParts b;

decode はそのまま CASE 式に書き換えることができます。COUNT の中に入れて書き換えてみましょう。

SELECT A.sup,
       B.sup,  
       (SELECT COUNT(CASE A1.part WHEN B1.part THEN 1 END) AS ExistSum
          FROM (supparts A1 CROSS JOIN supparts B1) 
         WHERE A.sup = A1.sup AND B.sup = B1.sup) AS ExistSum
  FROM (supparts A CROSS JOIN supparts B)
 ORDER BY A.sup, B.sup ;

DB2 for IBM i 7.1 での実行例です。

WHERE 句を追加

これにさらに WHERE 句が追加されています。

       select 
           count(case a.part when b.part then 1 end)
      over(partition by a.sup,b.sup) as ExistSum
        from SupParts a,SupParts b
       where a.sup < b.sup;

書き換え例にも同様に WHERE 句を追加します。

SELECT A.sup,
       B.sup,  
       (SELECT COUNT(CASE A1.part WHEN B1.part THEN 1 END) AS ExistSum
          FROM (supparts A1 CROSS JOIN supparts B1) 
         WHERE A.sup = A1.sup AND B.sup = B1.sup) AS ExistSum
  FROM (supparts A CROSS JOIN supparts B) 
 WHERE A.sup < B.sup 
 ORDER BY A.sup, B.sup ;

DB2 for IBM i 7.1 での実行例です。

COUNT(DISTINCT)

DB2 for IBM i の場合、COUNT(DISTINCT) はそのまま使えるので、↑ の書き換え例を核にしてもとの SQL の FROM 句の中の SQL を書き換えてみましょう。

SELECT A.sup AS S1,
       B.sup AS S2,  
       (SELECT COUNT(DISTINCT A1.part) 
          FROM (supparts A1 CROSS JOIN supparts B1) 
         WHERE A.sup = A1.sup AND B.sup = B1.sup) AS SupCount1,
       (SELECT COUNT(DISTINCT B1.part) 
          FROM (supparts A1 CROSS JOIN supparts B1) 
         WHERE A.sup = A1.sup AND B.sup = B1.sup) AS SupCount2,
       (SELECT COUNT(CASE A1.part WHEN B1.part THEN 1 END) 
          FROM (supparts A1 CROSS JOIN supparts B1) 
         WHERE A.sup = A1.sup AND B.sup = B1.sup) AS ExistSum
  FROM (supparts A CROSS JOIN supparts B) 
 WHERE A.sup < B.sup 
 ORDER BY A.sup, B.sup ;

DB2 for IBM i 7.1 での実行例です。

完成形

これをもとの SQL の FROM 句の中に入れると、書き換えは完成です。

SELECT DISTINCT S1,
       S2 
  FROM (SELECT A.sup AS S1,
               B.sup AS S2,  
               (SELECT COUNT(DISTINCT A1.part) 
                  FROM (supparts A1 CROSS JOIN supparts B1) 
                 WHERE A.sup = A1.sup AND B.sup = B1.sup) AS SupCount1,
               (SELECT COUNT(DISTINCT B1.part) 
                  FROM (supparts A1 CROSS JOIN supparts B1) 
                 WHERE A.sup = A1.sup AND B.sup = B1.sup) AS SupCount2,
               (SELECT COUNT(CASE A1.part WHEN B1.part THEN 1 END) 
                  FROM (supparts A1 CROSS JOIN supparts B1) 
                 WHERE A.sup = A1.sup AND B.sup = B1.sup) AS ExistSum
          FROM (supparts A CROSS JOIN supparts B) 
         WHERE A.sup < B.sup) AS X 
 WHERE ExistSum = SupCount1
   AND ExistSum = SupCount2;

DB2 for IBM i 7.1 での実行例です。

ALL の書き換え

もとの記事には GROUP BY を使った書き換え例も載っています。

--group byを使ったSQL
select a.sup as s1,b.sup as s2
  from SupParts a,SupParts b
 where a.sup < b.sup
group by a.sup,b.sup
having count(decode(a.part,b.part,1))
     = all(count(distinct a.part),count(distinct b.part));

Oracle 独自の演算子である all が使われているのですが、標準 SQL では ↓ のように書き換えられます。

SELECT A.sup AS S1,
       B.sup AS S2
  FROM SupParts A CROSS JOIN SupParts B 
 WHERE A.sup < B.sup
 GROUP BY A.sup, B.sup
HAVING COUNT(CASE A.part WHEN B.part THEN 1 END) = COUNT(DISTINCT A.part)
   AND COUNT(CASE A.part WHEN B.part THEN 1 END) = COUNT(DISTINCT B.part);

DB2 for IBM i 7.1 での実行例です。

今回の例を実際に実行してみるためのデータは ↓ の SQL で作成できます。ぜひトライしてみてください！

create table SupParts(
sup  char(1) not null,
part char(24) not null,
primary key(sup,part));

insert into SupParts values('A','ボルト');
insert into SupParts values('A','ナット');
insert into SupParts values('A','パイプ');
insert into SupParts values('B','ボルト');
insert into SupParts values('B','パイプ');
insert into SupParts values('C','ボルト');
insert into SupParts values('C','ナット');
insert into SupParts values('C','パイプ');
insert into SupParts values('D','ボルト');
insert into SupParts values('D','パイプ');
insert into SupParts values('E','ヒューズ');
insert into SupParts values('E','ナット');
insert into SupParts values('E','パイプ');
insert into SupParts values('F','ヒューズ');
commit;