SQL:類別して採番(partition by)

Oracle の SQL で類別したレコード内で採番する方法。

▼問題

たとえば、次のようなよくある従業員テーブルを考える。
ここで、従業員を男、女という性別で分け、同じ性別の従業員内で番号を振るにはどうすればよいか?

SQL> CREATE TABLE emp(
  id     number(4) NOT NULL,
  name   varchar2(10),
  gender varchar2(10)
);
表が作成されました。

DESC emp
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(4)
 NAME                                               VARCHAR2(10)
 GENDER                                             VARCHAR2(10)

▼男女それぞれのダミーデータを挿入。

SQL> INSERT INTO emp VALUES(1, 'john',  'male');
1行が作成されました。

SQL> INSERT INTO emp VALUES(2, 'mike',  'male');
1行が作成されました。

SQL> INSERT INTO emp VALUES(3, 'jane',  'female');
1行が作成されました。

SQL> INSERT INTO emp VALUES(5, 'kay',   'female');
1行が作成されました。

SQL> INSERT INTO emp VALUES(4, 'chris', 'male');
1行が作成されました。

▼挿入したデータを確認

SELECT id,
       name,
       gender
  FROM emp;
        ID NAME       GENDER
---------- ---------- ----------
         1 john       male
         2 mike       male
         3 jane       female
         4 chris      male
         5 kay        female

Q. IDとは別に、性別が同じ従業員同士で番号を振りたい。
A. partition by を利用する。

SELECT id,
       name,
       gender,
       row_number() over (partition by gender order by name) as no
  FROM emp;

        ID NAME       GENDER             NO
---------- ---------- ---------- ----------
         3 jane       female              1
         5 kay        female              2
         4 chris      male                1
         1 john       male                2
         2 mike       male                3

▼確認

「NO」列を確認すると Name でソートしたうえで性別ごとに採番されている。
PostgreSQL でも 8.4 から window function まわりが強化された模様。

Advertisements
Tagged with: , , , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: