程序博客网 > 淘宝天猫杜蕾斯可信吗

oracle中sql分析函数

来源：互联网 发布：淘宝天猫杜蕾斯可信吗 编辑：程序博客网 时间：2024/05/22 11:38

分析函数能够将复杂的需求使用简单的sql实现，而且执行效率较高。

测试环境：

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

create table test(name varchar2(20),val number);
insert into test values('a',1);
insert into test values('b',2);
insert into test values('c',3);
insert into test values('d',5);
insert into test values('e',7);
insert into test values('f',9);
insert into test values('g',12);
insert into test values('h',15);

SQL> select * from test;

NAME                        VAL
-------------------- ----------
a                             1
b                             2
c                             3
d                             5
e                             7
f                             9
g                            12
h                            15

1、连需求和

SQL> select name,val,sum(val) over(order by name desc) "连需求和" from test;

NAME                        VAL   连需求和
-------------------- ---------- ----------
h                            15         15
g                            12         27
f                             9         36
e                             7         43
d                             5         48
c                             3         51
b                             2         53
a                             1         54

已选择8行。

2、依据差值范围求和

SQL> select name,val,sum(val)over(order by val range between 2 preceding and 1 f
ollowing) "范围求和" from test;

NAME                        VAL   范围求和
-------------------- ---------- ----------
a                             1          3
b                             2          6
c                             3          6
d                             5          8
e                             7         12
f                             9         16
g                            12         12
h                            15         15

已选择8行。

SQL> select name,val,sum(val)over(order by val desc range between 2 preceding an
d 1 following ) "范围求和" from test;

NAME                        VAL   范围求和
-------------------- ---------- ----------
h                            15         15
g                            12         12
f                             9          9
e                             7         16
d                             5         12
c                             3         10
b                             2          6
a                             1          6

已选择8行。

3、不连续求和

SQL> select name,val,sum(val)over() "不连续求和" from test;

NAME                        VAL 不连续求和
-------------------- ---------- ----------
a                             1         54
b                             2         54
c                             3         54
d                             5         54
e                             7         54
f                             9         54
g                            12         54
h                            15         54

已选择8行。

求比例值：

SQL> select name,val,sum(val)over(order by val) "连续求和"
  2  ,sum(val)over() "总和",100*round(val/sum(val)over(),4) "所占比例" from test
;

NAME                        VAL   连续求和       总和   所占比例
-------------------- ---------- ---------- ---------- ----------
a                             1          1         54       1.85
b                             2          3         54        3.7
c                             3          6         54       5.56
d                             5         11         54       9.26
e                             7         18         54      12.96
f                             9         27         54      16.67
g                            12         39         54      22.22
h                            15         54         54      27.78

已选择8行。

4、使用子分区查询

数据环境：

update test set name='a' where val<3;
update test set name='b' where val>=3 and val<10;
update test set name='c' where val>10;

SQL> select * from test;

NAME                        VAL
-------------------- ----------
a                             1
a                             2
b                             3
b                             5
b                             7
b                             9
c                            12
c                            15

已选择8行。

--按name连续求和

SQL> select name,val,sum(val)over(partition by name order by val desc) "按name连
续求和" from test;

NAME                        VAL 按name连续求和
-------------------- ---------- --------------
a                             2              2
a                             1              3
b                             9              9
b                             7             16
b                             5             21
b                             3             24
c                            15             15
c                            12             27

已选择8行。

--按name求和

SQL> select name,val,sum(val)over(partition by name ) "按name求和" from test;

NAME                        VAL 按name求和
-------------------- ---------- ----------
a                             1          3
a                             2          3
b                             3         24
b                             5         24
b                             9         24
b                             7         24
c                            12         27
c                            15         27

已选择8行。

5、使用rank()进行排序操作，rank()如果前面有并列则下一名会空出并列的此时，dense_rank()则会按顺序向下排名。

SQL> select name,val,dense_rank() over(partition by name order by val nulls las
) "在name列内部排序" ,
  2   dense_rank()over(order by val nulls last) "整体排序" from test;

NAME                        VAL 在name列内部排序   整体排序
-------------------- ---------- ---------------- ----------
a                             1                1          1
a                             2                2          2
b                             3                1          3
b                             5                2          4
b                             5                2          4
b                             9                3          5
c                            12                1          6
c                            15                2          7

已选择8行。

6、求top n，使用row_number(),它与rank的不同是不会出现并列情况，会依据行的先后顺序给出top n

SQL>  select name,val,row_number()over(partition by name order by val nulls last
) row_num from test;

NAME                        VAL    ROW_NUM
-------------------- ---------- ----------
a                             1          1
a                             2          2
b                             3          1
b                             5          2
b                             5          3
b                             9          4
c                            12          1
c                            15          2

已选择8行。

7、窗口之行窗口（使用rows关键字）

--依据name分组，向前取两条（共三条）记录求平均值

SQL> select name ,val,avg(val)over(partition by name order by val rows 2 precedi
ng) x from test
  2  ;

NAME                        VAL          X
-------------------- ---------- ----------
a                             1          1
a                             2        1.5
b                             3          3
b                             5          4
b                             5 4.33333333
b                             9 6.33333333
c                            12         12
c                            15       13.5

已选择8行。

SQL>   select name,val,sum(val)over( order by val rows between 1 preceding and 1
 following ) x from test
  2  ;

NAME                        VAL          X
-------------------- ---------- ----------
a                             1          3
a                             2          6
b                             3         10
b                             5         15
a                             7         19
b                             7         23
b                             9         28
c                            12         36
c                            15         27

已选择9行。

8、窗口之范围窗口（使用range关键字）

SQL> select name,val,sum(val)over(order by val range between 2 preceding and 2 f
ollowing) x from test;

NAME                        VAL          X
-------------------- ---------- ----------
a                             1          6
a                             2          6
b                             3         11
b                             5         22
a                             7         28
b                             7         28
b                             9         23
c                            12         12
c                            15         15

已选择9行。

SQL> select name,val,sum(val)over(order by val range  2 preceding ) x from test;


NAME                        VAL          X
-------------------- ---------- ----------
a                             1          1
a                             2          3
b                             3          6
b                             5          8
a                             7         19
b                             7         19
b                             9         23
c                            12         12
c                            15         15

已选择9行。

 

 

 

 

淘宝天猫杜蕾斯可信吗 淘宝天猫杜蕾斯可信吗
原创粉丝点击
热门IT博客
linux安装内核源码linux安装内核源码
设备通讯端口 英文设备通讯端口 英文
javascript pdf
淘宝宝贝修改价格影响
男士双肩包品牌 知乎
通联数据和通联支付
淘宝好评返现怎么举报
淘宝卖家数据采集软件
网络奇兵
亿网科技域名可靠吗
网络管理兼职
软件授权码
python 散点图矩阵
php array 添加数据
手机淘宝开网店教程
cf网络出现异常
淘宝论坛在哪里发帖
mac全屏游戏返回桌面
java线程sleep
二胡配件图片大全淘宝
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 瑞丽机场 大理到瑞丽 瑞丽吧 瑞丽时尚网 瑞丽论坛 瑞丽网站 瑞丽服饰 瑞丽招聘 时尚瑞丽 瑞丽在哪 瑞丽女装 云南瑞丽市 德宏瑞丽 瑞丽整形 瑞丽诗 昆明 瑞丽 瑞丽美食 瑞丽护肤品 瑞丽化妆品 瑞丽模特 瑞丽杂志社 瑞丽是哪里 瑞丽风格 瑞丽到大理 张瑞丽 瑞丽在哪儿 瑞丽学校 瑞丽在那里 瑞丽特色 中国瑞丽 浙江瑞丽 瑞丽美 冯瑞丽 刘瑞丽 大理至瑞丽 昆明-瑞丽 成都瑞丽 瑞丽多少钱 瑞丽斯植发 北京 瑞丽 瑞丽包包

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里