PostgreSQL ROW_NUMBER() OVER()

構文:ROW_NUMBER()OVER([PRITION BY col 1]ORDER BY col 2[DESC])解釈:ROW_NUMBER()は、返されるレコードの行番号を定義し、PARTITION BY col 1はcol 1パケット、ORDER BY col 2[DESC]はcol 2によるソートである.例:

postgres=# create table student(id serial,name character varying,course character varying,score integer);
CREATE TABLE
postgres=# 
postgres=# \d student
                              Table "public.student"
 Column |       Type        |                      Modifiers                       
--------+-------------------+----------------------------------------------
 id     | integer           | not null default nextval('student_id_seq'::regclass)
 name   | character varying | 
 course | character varying | 
 score  | integer           | 

 insert into student (name,course,score) values('   ','  ',89);

 insert into student (name,course,score) values('   ','  ',99);

 insert into student (name,course,score) values('   ','  ',67);

 insert into student (name,course,score) values('   ','  ',77);

 insert into student (name,course,score) values('   ','  ',87);

 insert into student (name,course,score) values('   ','  ',91);

 insert into student (name,course,score) values('   ','  ',81);

 insert into student (name,course,score) values('   ','  ',88);

 insert into student (name,course,score) values('   ','  ',68);

 insert into student (name,course,score) values('   ','  ',83);

 insert into student (name,course,score) values('  ','  ',85);

 insert into student (name,course,score) values('  ','  ',65);

 insert into student (name,course,score) values('  ','  ',95);

 insert into student (name,course,score) values('  ','  ',90);

 insert into student (name,course,score) values('  ','  ',78);

1.スコアでソート

postgres=# select *,row_number() over(order by score desc)rn from student;
 id |  name  | course | score | rn 
----+--------+--------+-------+----
  2 |     |      |    99 |  1
 13 |      |      |    95 |  2
  6 |     |      |    91 |  3
 14 |      |      |    90 |  4
  1 |     |      |    89 |  5
  8 |     |      |    88 |  6
  5 |     |      |    87 |  7
 11 |      |      |    85 |  8
 10 |     |      |    83 |  9
  7 |     |      |    81 | 10
 15 |      |      |    78 | 11
  4 |     |      |    77 | 12
  9 |     |      |    68 | 13
  3 |     |      |    67 | 14
 12 |      |      |    65 | 15
(15 rows)

rnは私たちに与えられたソートです.2.科目別にグループ化し、点数別にソートする

postgres=# select *,row_number() over(partition by course order by score desc)rn from student;
 id |  name  | course | score | rn 
----+--------+--------+-------+----
  5 |     |      |    87 |  1
 10 |     |      |    83 |  2
 15 |      |      |    78 |  3
 13 |      |      |    95 |  1
  8 |     |      |    88 |  2
  3 |     |      |    67 |  3
  2 |     |      |    99 |  1
  7 |     |      |    81 |  2
 12 |      |      |    65 |  3
 14 |      |      |    90 |  1
  4 |     |      |    77 |  2
  9 |     |      |    68 |  3
  6 |     |      |    91 |  1
  1 |     |      |    89 |  2
 11 |      |      |    85 |  3
(15 rows)

3.各科目の最高点を取得する

postgres=# select * from(select *,row_number() over(partition by course order by score desc)rn from student)t where rn=1;
 id |  name  | course | score | rn 
----+--------+--------+-------+----
  5 |     |      |    87 |  1
 13 |      |      |    95 |  1
  2 |     |      |    99 |  1
 14 |      |      |    90 |  1
  6 |     |      |    91 |  1
(5 rows)

4.科目ごとの最低点も同じ

postgres=# select * from(select *,row_number() over(partition by course order by score)rn from student)t where rn=1;
 id |  name  | course | score | rn 
----+--------+--------+-------+----
 15 |      |      |    78 |  1
  3 |     |      |    67 |  1
 12 |      |      |    65 |  1
  9 |     |      |    68 |  1
 11 |      |      |    85 |  1
(5 rows)

科目によって並べ替えられているときに、低い順から高い順に並べばいいのです.