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
  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 6 months ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 6 months ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 6 months ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 1 year ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 1 year ago
%d bloggers like this: