分析函数over

来源：互联网发布：单页面seo 编辑：程序博客网时间：2024/04/29 15:58

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。

1：统计某商店的营业额。
     date       sale
     1           20
     2           15
     3           14
     4           18
     5           30
    规则：按天统计：每天都统计前面几天的总额

c:/>select id,sale ,sum(sale) over (partition by id,sale order by sale) from t1;

        ID       SALE SUM(SALE)OVER(PARTITIONBYID,SALEORDERBYSALE)
---------- ---------- --------------------------------------------
         1         20                                           20
         2         15                                           15
         3         14                                           14
         4         18                                           18
         5         30                                           30

c:/>select id,sale ,sum(sale) over (order by sale) from t1;

        ID       SALE SUM(SALE)OVER(ORDERBYSALE)
---------- ---------- --------------------------
         3         14                         14
         2         15                         29
         4         18                         47
         1         20                         67
         5         30                         97


2:统计各班成绩第一名的同学信息
    NAME   CLASS S
    ----- ----- ----------------------
    fda    1      80
    ffd    1      78
    dss    1      95
    cfe    2      74
    gds    2      92
    gf     3      99
    ddd    3      99
    adf    3      45
    asdf   3      55
    3dd    3      78



c:/>select name,class ,score,max(score) over(partition by class order by score d
esc) from t2;

NAME CLASS SCORE MAX(SCORE)OVER(PARTITIONBYCLASSORDERBYSCOREDESC)
-------------------- ---------- ---------- ---------------

dss                           1         95     95


fda                           1         80     95


ffd                           1         78     95


gds                           2         92     92


cfe                           2         74     92


gf                            3         99     99


ddd                           3         99     99


3dd                           3         78     99


asdf                          3         55     99


adf                           3         45     99

已选择10行。

select * from
    (
    select name,class,s,rank()over(partition by class order by s desc) mm from t2
    )
    where mm=1

NAME                      CLASS      SCORE         MM
-------------------- ---------- ---------- ----------
dss                           1         95          1
gds                           2         92          1
ddd                           3         99          1
gf                            3         99          1

注意：
    1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果
    2.rank()和dense_rank()的区别是：
    --rank()是跳跃排序，有两个第二名时接下来就是第四名
    --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

Rank() over()的用法
创建一个test表，并插入6条数据。
CREATE TABLE test
(
a INT,
b INT,
c CHAR
)
INSERT INTO test VALUES(1,3,'E');
INSERT INTO test VALUES(2,4,'A');
INSERT INTO test VALUES(3,2,'D');
INSERT INTO test VALUES(3,5,'B');
INSERT INTO test VALUES(4,2,'C');
INSERT INTO test VALUES(2,4,'B');

SELECT * from test

a           b           c
----------- ----------- ----
1           3           E
2           4           A
3           2           D
3           5           B
4           2           C
2           4           B

(6 行受影响)

1、整个结果集是一个分组，以a进行排名

SELECT a,b,c,rank () OVER (ORDER BY a) rank FROM test

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    2
2           4           B    2
3           2           D    4
3           5           B    4
4           2           C    6

(6 行受影响)

2、整个结果集是一个分组，以b进行排名

SELECT a,b,c,rank () OVER (ORDER BY b) rank FROM test

a           b           c    rank
----------- ----------- ---- --------------------
3           2           D    1
4           2           C    1
1           3           E    3
2           4           A    4
2           4           B    4
3           5           B    6

(6 行受影响)

3、以a,b进行分组，在每个组内以b进行排名。分了5个组，第2行跟第3行是一个组，其他的每行是一个组。
在第2行与第3行的组内以b排名，并列为1

SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY b) rank FROM test

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    1
2           4           B    1
3           2           D    1
3           5           B    1
4           2           C    1

(6 行受影响)

4、以a,b进行分组，在每个组内以c进行排名。分了5个组，第2行跟第3行是一个组，其他的每行是一个组。
在第2行与第3行的组内以c排名，由于c列一个是A，一个是B，所以Rank分别为1、2。

SELECT a,b,c,rank () OVER (PARTITION BY a,b ORDER BY c) rank FROM test

a           b           c    rank
----------- ----------- ---- --------------------
1           3           E    1
2           4           A    1
2           4           B    2
3           2           D    1
3           5           B    1
4           2           C    1

(6 行受影响)

总结：1、partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组。

first_value,last_value用法
原始数据：

SQL>select * from a;

NO NAME SCOTT

-- ---------- ----------

1 LIFEN 86

2 HONGGE 76

3 AIKAI 90

4 LIFEN 73

5 KUJUHD 69

6 LIFEN 93

7 KOU 80

8 HONGGE 83

......................

select T.*, first_value(scoot) over (partition by name order by scott) F,last_value(scoot) over (partition by name order by scott) L from a T order by no ;

NO NAME SCOTT F L

-- ---------- ---------- ----- -----

1 LIFEN 86 73 86

2 HONGGE 76 76 76

3 AIKAI 90 90 90

4 LIFEN 73 73 73

5 KUJUHD 69 69 69

6 LIFEN 93 73 93

7 KOU 80 80 80

8 HONGGE 83 76 83

可以看出能得到姓名相同中的最小分数却不能得到最大分数也就是last_value這函數里存在order和partition會得不到預期的結果﹐

可以將order by 放在最后面

找出最高分数（如果有重复姓名）和最低分数的NO

SQL>SELECT T.*,

2 first_value(scott) OVER (PARTITION BY NAME) lowest_scott,

3 last_value(scott) OVER (PARTITION BY NAME) highest_scott

4 FROM a T

5 ORDER BY scott;

        NO NAME                      SCOTT          F          L
---------- -------------------- ---------- ---------- ----------
         5 kujuhd                       69         69         69
         4 lifen                           73         93         73
         2 hongge                      76         76         83
         7 kou                           80         80         80
         8 hongge                      83         76         83
         1 lifen                          86         93         73
         3 aikai                          90         90         90
         6 lifen                           93         93         73

已选择8行。

第二种方法就是

select T.*, first_value(scoot) over (partition by name order by scott) F,

last_value(scoot) over (partition by name order by scott rows between unbounded preceding and unbounded following) L from a T order by no ;

ps:分析函数分析的时候是在一个分析窗口中进行的，默认的窗口是当前组的第一行到当前行，如果让窗口是整个组，需要指定窗口子句

删除姓名重复的分数最高的保留下来

SQL>delete from a where no not in (select first_value(no) over (partition by name order by scott desc ) no from a );

select deptno,ename,hiredate,count(1) over (partition by deptno order by hiredate nulls
first range 100 preceding) cnt_range,count(1) over(partition by deptno order by hiredate
nulls first rows 2 preceding) cnt_rows from emp order by deptno,hiredate