PSI Labs RSS feed

[Oracle]ソートした後にレコードに連番を付与

こんにちは。shintaniです。

SQLで連番を付与する場合はROWNUMを使う方法が一般的ですが、Oracleでは
「(並び順がバラバラの状態で)select句でrownumが1から付番されてからorder byが実行される」ため、
下記のようにソートが必要な場合には番号の並びが狂う場合があります。

select
  ROWNUM
 ,SHAIN_NO
 ,NYUSHA_NENGETSU
from M_SHAIN
order by NYUSHA_NENGETSU desc ,SHAIN_NO

これを是正する場合には、下記のように「order by で並べ替えてから、それを副問い合わせで検索する」という方法があります。

select
 ROWNUM
,SHAIN_NO
,NYUSHA_NENGETSU
,SHIMEI
 from(
  select
     SHAIN_NO
    ,NYUSHA_NENGETSU
    ,MYOJI || MEI as SHIMEI
  from M_SHAIN
   order by  NYUSHA_NENGETSU desc ,SHAIN_NO)

ただこの方法も下記のような問題があります。
・行番号のためだけに全体を副問い合わせにする必要がある
・一度全体のソートが必要のためパフォーマンスが劣化する危険がある

そこで、下記のように分析関数を使用する方法がお勧めです。

SELECT
   ROW_NUMBER() OVER (ORDER BY NYUSHA_NENGETSU DESC, SHAIN_NO)
  ,SHAIN_NO
  ,NYUSHA_NENGETSU
  ,MYOJI || MEI as SHIMEIFROM M_SHAIN
order by
 NYUSHA_NENGETSU desc,SHAIN_NO

ROW_NUMBER()は連番を設定していく関数です。
partition by 句を設定するとその列の値のグループで連番を設定しますが、設定しなければ結果セット全体に対して一意の連番が設定されます。
ROW_NUMBER() の中の order by で連番の順番が設定されます。
これをSQLの最後の order by 句と同じものを設定すれば、正しい順番で連番が設定されます。
(ソート順を修正する場合には、ROW_NUMBER()の箇所と最後の箇所の2箇所の修正が必要になります)