BinaryDevelop
  1. ホーム
  2. DB2

DB2 row_number() over() 解析関数の使用法

2022-03-02 19:32:41
<スパン row_number() over() パーサーの使用法
row_number() over(partition by col1 order by col2) は、col1 でグループ化し、グループ内で col2 でソートし、ソート後の各グループ内の連番（グループ内で連続するユニークな番号）を表す関数である。 
rownumとの違いは、rownumでソートする場合は結果セットに疑似rowumを加えてからソートするのに対し、この関数はsort句を含めてから行番号を計算する前にソートする点です。 
row_number() は rownum とほぼ同じですが、少し機能が増えています (各グループ内で 1 からソートできます)。 
rank() はジャンプソートで、2位が2回、4位が1回となります（これも各グループ内）。
dense_rank() も連続的にソートされ、やはり2位が2つ、3位が3つとなります。一方、row_number には重複する値はありません。 
1.row_number() over (order by col_1[,col_2 ...]) 
 機能 col_1[,col_2 ...]でソートし、ソート後の結果セットを返す。 
 これはrownumと少し似た使い方をし、各行ごとに異なる値を返します。 
 select rownum,ename,job,    
       row_number() over (order by rownum) row_number    
からのemp;    
    rownum ename job row_number    
---------- ---------- --------- ----------    
         1 スミスクラーク 1    
         2 アレン セールスマン 2    
         3 ウォード セールスマン 3    
         4 ジョーンズ・マネージャー 4    
         5 マーティン セールスマン 5    
         6 ブレイク マネージャー 6    
         7 クラーク マネージャー 7    
         8 スコット・アナリスト 8    
         9 キング プレジデント 9    
        10 ターナー・セールスマン 10    
        11 アダムス事務員 11    
        12 ジェームス・クラーク 12    
        13 フォード・アナリスト 13    
        14 ミラークラーク 14  
 partition by句がない場合、結果集合はorder byで指定された列でソートされる。 
 with row_number_test as()    
     select 22 a,'twenty two' b from dual union all    
     select 1,'one' from dual union all.    
     select 13,'thirteen' from dual union all（13を選択）。   
     select 5,'five' from dual union all.    
     select 4,'four' from dual)    
a,b を選択します。   
       row_number() over (order by b)    
from row_number_test    
をaの順に並べます。 
 予想通り、row_number()はb列でソートされた結果を返します。 
 次に、aでソートすると、次のような結果になります。 
 a b row_number()over(orderbyb)    
-- ---------- --------------------------    
1 1 3    
4 4 2    
5 5 1    
13 13 4    
22 twenty two 5  
2.row_number() over (partition by col_n[,col_m ...]) order by col_1[,col_2 ...]) 
 何をするかというと、まずcol_n[,col_m ...でグループ分けをするのです。 
 次に、各グループで、col_1[,col_2 ...]でソートする（昇順）。 
 最後に、ソート後の結果セットを返します。 
 with row_number_test as(    
     select 22 a,'twenty two' b,'*' c from dual union all    
     select 1,'one','+' from dual union all.    
     select 13,'thirteen','*' from dual union all    
     select 5,'five','+' from dual union all.    
     select 4,'four','+' from dual).    
a,bを選択します。   
       row_number() over (partition by c order by b) row_number    
from row_number_test    
の順に並べます。 
 この例では、まずc列を2つのグループ（'*'グループと'+'グループ）にグループ化します。 
 そして、各グループのb列でソートします（文字列の最初の文字のアスキーコードで）。 
 最後に、a列でソートすると、次のような結果セットになります。 
 A B ROW_NUMBER    
-- ---------- ----------    
1 1 3    
4 4 2    
5 5 1    
13 13 1    
22 twenty two 
 例
フィールドに対して繰り返しのないグループの最初の3つのランダムな項目を抽出する次のような要件がありますか？
 すなわち、id1でグループ化し、重複していないid2の最初のN行をランダムに抽出する。
 例
をtempとして(                                                
select 'dg' id1,13907551201 id2 from dual union all                                                
select 'dg' id1,13907551201 id2 from dual union all                                                
select 'dg' id1,13907551201 id2 from dual union all                                                
select 'dg' id1,13907551204 id2 from dual union all                                                
select 'dg' id1,13907551205 id2 from dual union all                                                
select 'dg' id1,13907551206 id2 from dual union all                                                
select 'dg' id1,13907551207 id2 from dual union all                                                
select 'dg' id1,13907551207 id2 from dual union all                                                
select 'dc' id1,13907551209 id2 from dual union all                                                
select 'dc' id1,13907551210 id2 from dual union all                                                
select 'dc' id1,13907551210 id2 from dual union all                                                
select 'dc' id1,13907551212 id2 from dual                                                 
) 
 実装する。
を、tempを(                                                
select 'dg' id1,13907551201 id2 from dual union all                                                
select 'dg' id1,13907551201 id2 from dual union all                                                
select 'dg' id1,13907551201 id2 from dual union all                                                
select 'dg' id1,13907551204 id2 from dual union all                                                
select 'dg' id1,13907551205 id2 from dual union all                                                
select 'dg' id1,13907551206 id2 from dual union all                                                
select 'dg' id1,13907551207 id2 from dual union all                                                
select 'dg' id1,13907551207 id2 from dual union all                                                
select 'dc' id1,13907551209 id2 from dual union all                                                
select 'dc' id1,13907551210 id2 from dual union all                                                
select 'dc' id1,13907551210 id2 from dual union all                                                
select 'dc' id1,13907551212 id2 from dual                                                 
)                                                
select * from (
select temp.*
,row_number() over(partition by id1 order by dbms_random.random) rid1
から(
select temp.*
--,row_number() over(partition by id1 order by id1) rid1
,row_number() over(partition by id1,id2 order by id1,id2) rn 
テンポラリーより
) テンポ
ここでrn=1)
ここで、rid1<=3
しかし、tempテーブルのデータ量が多いため、もっと良いSQLの実装があるのでしょうか？
select * from (
select temp.*
,row_number() over(partition by id1 order by dbms_random.random) rid1
から(
select distinct id1, id2 from temp)
)
ここで、rid1<=3。

最新

おすすめ

について
/
プライバシー・ポリシー
/
お問い合わせ
© 2022 BinaryDevelop. すべての権利は、それぞれの所有者に帰属します