[Oracle] グループ毎に１行だけ取り出す

テーマ：

個人的なメモメモ。


（SQLが表示されないなどがあったので、適当なとこで折り返しをつけてます）

以下のような表があるとします。



| id | name | stid | ndid |
|  1 | aaa  | 100  | 10   |
|  2 | bbb  | 101  | 20   |
|  3 | ccc  | 102  | 20   |
|  4 | ddd  | 200  | 30   |
|  5 | eee  | 199  | 30   |
|  6 | fff  | 198  | 30   |


この時、"ndid"単位でグループ化して、グループの中の"stid"が最も小さいものを選びたい。

こんな結果が欲しい訳です。


| id | name | stid | ndid |
|  1 | aaa  | 100  | 10   |
|  2 | bbb  | 101  | 20   |
|  6 | fff  | 198  | 30   |



ndid=10のうち最もstidが小さいid=1

ndid=20のうち最もstidが小さいid=2

ndid=30のうち最もstidが小さいid=6

が選ばれています。


この選び方をするのに「partition by」を用いました。


まずはこちらのSQLを実行してみます。表名は"test"で作ってあります。



select
  id,name,ndid,stid,
  row_number()
    over( partition by ndid order by stid ) row_num
from test


結果は以下のようになります。



| id | name | stid | ndid | row_num |
|  1 | aaa  | 100  | 10   | 1       |
|  2 | bbb  | 101  | 20   | 1       |
|  3 | ccc  | 102  | 20   | 2       |
|  6 | fff  | 198  | 30   | 1       |
|  5 | eee  | 199  | 30   | 2       |
|  4 | ddd  | 200  | 30   | 3       |


簡単に言うと、「partition by」で小さなグループに分けられて処理されます。

分けた小さなグループ単位で行数を割り振っています。


ここまで抜ければ、後は条件を１つ足すだけです。



select *
from
(
 select
  id,name,ndid,stid,
  row_number()
    over( partition by ndid order by stid ) row_num
 from test
)
where row_num = 1


結果がこちら。



| id | name | stid | ndid | row_num |
|  1 | aaa  | 100  | 10   | 1       |
|  2 | bbb  | 101  | 20   | 1       |
|  6 | fff  | 198  | 30   | 1       |


抜き出したいものを抜き出すことができました。




（番外編）

ndid単位で抜き出したけど、それぞれ何行がひとまとまりになったのだろうか？

という場合はこちら。



select *
from
(
 select
  id,name,ndid,stid,
  row_number()
    over( partition by ndid order by stid ) row_num,
  count(ndid) over ( partition by ndid ) cn
 from test
)
where row_num = 1


結果がこちら。



| id | name | stid | ndid | row_num | cn |
|  1 | aaa  | 100  | 10   | 1       | 1  |
|  2 | bbb  | 101  | 20   | 1       | 2  |
|  6 | fff  | 198  | 30   | 1       | 3  |


cnを見れば、いくつがまとまったのかが分かります。