Oracle 中分析函式用法之--rank(),dense_rank(),partition,over()

rank()對錶中資料進行分級排序

  比如有張學生表：Student
SQL> conn scott/tiger
Connected.
SQL> desc student;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(10)
 SUBJECT                                            VARCHAR2(20)
 GRADE                                              NUMBER

SQL>  SELECT * FROM student ORDER BY subject;

NAME       SUBJECT                   GRADE
---------- --------------------             ----------
zhangsan   chinses                        90
lisi                chinses                        94
wang           chinses                        85
zhangsan   english                          75
lisi                english                          89
zhangsan   maths                            80
lisi                maths                            72
lisi                maths                            98

8 rows selected.

如果要想得到表中chinses 、english和maths各前兩名的成績，
那麼可以通過rank()函式實現

--首先通過subject分類，然後通過grade列降序
SQL> SELECT rank() over(partition by subject order by grade desc) rk,s.* FROM student s;

        RK NAME       SUBJECT                   GRADE
---------- ---------- --------------------            ----------
         1 lisi                chinses                      94
         2 zhangsan   chinses                      90
         3 wang           chinses                      85
         1 lisi                english                       89
         2 zhangsan   english                       75
         1 lisi                maths                         98
         2 zhangsan   maths                         80
         3 lisi                maths                         72

8 rows selected.

然後再加過濾條件rk<=2
SQL> SELECT * FROM
  2  (SELECT rank() over(partition by subject order by grade desc) rk,s.* FROM student s)
  3  tmptb
  4  WHERE tmptb.rk<=2;

        RK NAME       SUBJECT                   GRADE
---------- ---------- --------------------            ----------
         1 lisi                chinses                      94
         2 zhangsan   chinses                      90
         1 lisi                english                       89
         2 zhangsan   english                       75
         1 lisi                maths                         98
         2 zhangsan   maths                         80

6 rows selected.

dense_rank()的用法，取科目為chinses的前兩名成績
"afiedt.buf" 9L, 138C written

  1  SELECT *
  2  FROM (
  3  SELECT name,subject,grade,
  4  dense_rank() over(ORDER BY grade DESC) rn
  5  FROM student
  6  WHERE subject='chinses'
  7  )
  8* WHERE rn<=2
SQL> /

NAME          SUBJECT       GRADE         RN
----------   -------------------- ----------     ----------
lisi                chinses            94               1
zhangsan   chinses            90               2

row_number()的使用
SQL> SELECT row_number() over(order by grade desc) rk,s.* FROM student s;

        RK          NAME          SUBJECT                   GRADE
----------         ----------    --------------------              ----------
         1             lisi                maths                           98
         2             lisi               chinses                         94
         3            zhangsan   chinses                         90
         4            lisi                english                          89
         5            wang           chinses                         85
         6            zhangsan   maths                            80
         7            zhangsan   english                          75
         8            lisi                maths                            72

8 rows selected.

取所有人的前3名
SQL> SELECT *    
  2  FROM (
  3  SELECT row_number() over(order by grade desc) rk,s.* FROM student s
  4  )
  5  WHERE  rk<=3;

          RK                    NAME                   SUBJECT                   GRADE
      ----------               ----------            --------------------                ----------
         1                          lisi                         maths                         98
         2                          lisi                         chinses                      94
         3                         zhangsan             chinses                      90

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16400082/viewspace-693044/，如需轉載，請註明出處，否則將追究法律責任。