Oracle
分析函数使用介
绍
分析函数是 oracle816 引入的一个全新的概念 , 为 我 们 分析数据提供了一 种简单 高效的 处 理方式 . 在分析函数出 现 以前 , 我 们 必 须 使用自 联查询 , 子 查询 或者内 联视图 , 甚至 复杂 的存 储过 程 实现 的 语 句 , 现 在只要一条 简单 的 sql 语 句就可以 实现 了 , 而且在 执 行效率方面也有相当大的提高 . 下面我将 针对 分析函数做一些具体的 说 明 .
今天我主要 给 大家介 绍 一下以下几个函数的使用方法
1. 自 动汇总 函数 rollup,cube,
2.rank 函数 , rank,dense_rank,row_number
3.lag,lead 函数
4.sum,avg, 的移 动 增加 , 移 动 平均数
5.ratio_to_report 报 表 处 理函数
6.first,last 取基数的分析函数
基 础 数据
Code: [Copy to clipboard]
06:34:23 SQL> select * from t;
BILL_MONTHAREA_CODENET_TYPELOCAL_FARE
--------------- ---------- ---------- --------------
2004055761G7393344.04
2004055761J5667089.85
2004055762G6315075.96
2004055762J6328716.15
2004055763G8861742.59
2004055763J7788036.32
2004055764G6028670.45
2004055764J6459121.49
2004055765G13156065.77
2004055765J11901671.70
2004065761G7614587.96
2004065761J5704343.05
2004065762G6556992.60
2004065762J6238068.05
2004065763G9130055.46
2004065763J7990460.25
2004065764G6387706.01
2004065764J6907481.66
2004065765G13562968.81
2004065765J12495492.50
2004075761G7987050.65
2004075761J5723215.28
2004075762G6833096.68
2004075762J6391201.44
2004075763G9410815.91
2004075763J8076677.41
2004075764G6456433.23
2004075764J6987660.53
2004075765G14000101.20
2004075765J12301780.20
2004085761G8085170.84
2004085761J6050611.37
2004085762G6854584.22
2004085762J6521884.50
2004085763G9468707.65
2004085763J8460049.43
2004085764G6587559.23
BILL_MONTHAREA_CODENET_TYPELOCAL_FARE
--------------- ---------- ---------- --------------
2004085764J7342135.86
2004085765G14450586.63
2004085765J12680052.38
40 rows selected.
Elapsed: 00:00:00.00
1. 使用 rollup 函数的介 绍
Quote:
下面是直接使用普通 sql 语 句求出各地区的 汇总 数据的例子
06:41:36 SQL> set autot on
06:43:36 SQL> select area_code,sum(local_fare) local_fare
06:43:502from t
06:43:513group by area_code
06:43:574union all
06:44:005select ' 合 计 ' area_code,sum(local_fare) local_fare
06:44:066from t
06:44:087/
AREA_CODELOCAL_FARE
---------- --------------
576154225413.04
576252039619.60
576369186545.02
576453156768.46
5765104548719.19
合 计 333157065.31
6 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=
24884)
10UNION-ALL
21SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871)
32TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=248
71)
41SORT (AGGREGATE)
54TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=170
17)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
6consistent gets
0physical reads
0redo size
561bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
6rows processed
下面是使用分析函数 rollup 得出的 汇总 数据的例子
06:44:09 SQL> select nvl(area_code,' 合 计 ') area_code,sum(local_fare) local_fare
06:45:262from t
06:45:303group by rollup(nvl(area_code,' 合 计 '))
06:45:504/
AREA_CODELOCAL_FARE
---------- --------------
576154225413.04
576252039619.60
576369186545.02
576453156768.46
5765104548719.19
333157065.31
6 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes=
24871)
10SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)
21TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871
)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
4consistent gets
0physical reads
0redo size
557bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
6rows processed
从上面的例子我 们 不 难 看出使用 rollup 函数 , 系 统 的 sql 语 句更加 简单 , 耗用的 资 源更少 , 从 6 个 consistent gets 降到 4 个 consistent gets, 如果基表很大的 话 , 结 果就可想而知了 .
1. 使用 cube 函数的介 绍
Quote:
为 了介 绍 cube 函数我 们 再来看看另外一个使用 rollup 的例子
06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:53:372from t
06:53:383group by rollup(area_code,bill_month)
06:53:494/
AREA_CODEBILL_MONTHLOCAL_FARE
---------- --------------- --------------
576120040513060433.89
576120040613318931.01
576120040713710265.93
576120040814135782.21
576154225413.04
576220040512643792.11
576220040612795060.65
576220040713224298.12
576220040813376468.72
576252039619.60
576320040516649778.91
576320040617120515.71
576320040717487493.32
576320040817928757.08
576369186545.02
576420040512487791.94
576420040613295187.67
576420040713444093.76
576420040813929695.09
576453156768.46
576520040525057737.47
576520040626058461.31
576520040726301881.40
576520040827130639.01
5765104548719.19
333157065.31
26 rows selected.
Elapsed: 00:00:00.00
系 统 只是根据 rollup 的第一个参数 area_code 对结 果集的数据做了 汇总处 理 , 而没有 对 bill_month 做 汇总 分析 处 理 ,cube 函数就是 为 了 这 个而 设计 的 .
下面 , 让 我 们 看看使用 cube 函数的 结 果
06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:58:302from t
06:58:323group by cube(area_code,bill_month)
06:58:424order by area_code,bill_month nulls last
06:58:575/
AREA_CODEBILL_MONTHLOCAL_FARE
---------- --------------- --------------
576120040513060.43
576120040613318.93
576120040713710.27
576120040814135.78
576154225.41
576220040512643.79
576220040612795.06
576220040713224.30
576220040813376.47
576252039.62
576320040516649.78
576320040617120.52
576320040717487.49
576320040817928.76
576369186.54
576420040512487.79
576420040613295.19
576420040713444.09
576420040813929.69
576453156.77
576520040525057.74
576520040626058.46
576520040726301.88
576520040827130.64
5765104548.72
20040579899.53
20040682588.15
20040784168.03
20040886501.34
333157.05
30 rows selected.
Elapsed: 00:00:00.01
可以看到 , 在 cube 函数的 输 出 结 果比使用 rollup 多出了几行 统计 数据 . 这 就是 cube 函数根据 bill_month 做的 汇总统计结 果
1 rollup 和 cube 函数的再深入
Quote:
从上面的 结 果中我 们 很容易 发现 , 每 个 统计 数据所 对应 的行都会出 现 null,
我 们 如何来区分到底是根据那个字段做的 汇总 呢 ,
这时 候 ,oracle 的 grouping 函数就粉墨登 场 了 .
如果当前的 汇总记录 是利用 该 字段得出的 ,grouping 函数就会返回 1, 否 则 返回 0
1select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,
2decode(grouping(bill_month),1,'all month',bill_month) bill_month,
3sum(local_fare) local_fare
4from t
5group by cube(area_code,bill_month)
6* order by area_code,bill_month nulls last
07:07:29 SQL> /
AREA_CODEBILL_MONTHLOCAL_FARE
---------- --------------- --------------
576120040513060.43
576120040613318.93
576120040713710.27
576120040814135.78
5761all month54225.41
576220040512643.79
576220040612795.06
576220040713224.30
576220040813376.47
5762all month52039.62
576320040516649.78
576320040617120.52
576320040717487.49
576320040817928.76
5763all month69186.54
576420040512487.79
576420040613295.19
576420040713444.09
576420040813929.69
5764all month53156.77
5 76520040525057.74
576520040626058.46
576520040726301.88
576520040827130.64
5765all month104548.72
all area20040579899.53
all area20040682588.15
all area20040784168.03
all area20040886501.34
all areaall month333157.05
30 rows selected.
Elapsed: 00:00:00.01
07:07:31 SQL>
可以看到 , 所有的空 值现 在都根据 grouping 函数做出了很好的区分 , 这样 利用 rollup,cube 和 grouping 函数 , 我 们 做数据 统计 的 时 候就可以 轻 松很多了 .
分析函数是 oracle816 引入的一个全新的概念 , 为 我 们 分析数据提供了一 种简单 高效的 处 理方式 . 在分析函数出 现 以前 , 我 们 必 须 使用自 联查询 , 子 查询 或者内 联视图 , 甚至 复杂 的存 储过 程 实现 的 语 句 , 现 在只要一条 简单 的 sql 语 句就可以 实现 了 , 而且在 执 行效率方面也有相当大的提高 . 下面我将 针对 分析函数做一些具体的 说 明 .
今天我主要 给 大家介 绍 一下以下几个函数的使用方法
1. 自 动汇总 函数 rollup,cube,
2.rank 函数 , rank,dense_rank,row_number
3.lag,lead 函数
4.sum,avg, 的移 动 增加 , 移 动 平均数
5.ratio_to_report 报 表 处 理函数
6.first,last 取基数的分析函数
基 础 数据
Code: [Copy to clipboard]
06:34:23 SQL> select * from t;
BILL_MONTHAREA_CODENET_TYPELOCAL_FARE
--------------- ---------- ---------- --------------
2004055761G7393344.04
2004055761J5667089.85
2004055762G6315075.96
2004055762J6328716.15
2004055763G8861742.59
2004055763J7788036.32
2004055764G6028670.45
2004055764J6459121.49
2004055765G13156065.77
2004055765J11901671.70
2004065761G7614587.96
2004065761J5704343.05
2004065762G6556992.60
2004065762J6238068.05
2004065763G9130055.46
2004065763J7990460.25
2004065764G6387706.01
2004065764J6907481.66
2004065765G13562968.81
2004065765J12495492.50
2004075761G7987050.65
2004075761J5723215.28
2004075762G6833096.68
2004075762J6391201.44
2004075763G9410815.91
2004075763J8076677.41
2004075764G6456433.23
2004075764J6987660.53
2004075765G14000101.20
2004075765J12301780.20
2004085761G8085170.84
2004085761J6050611.37
2004085762G6854584.22
2004085762J6521884.50
2004085763G9468707.65
2004085763J8460049.43
2004085764G6587559.23
BILL_MONTHAREA_CODENET_TYPELOCAL_FARE
--------------- ---------- ---------- --------------
2004085764J7342135.86
2004085765G14450586.63
2004085765J12680052.38
40 rows selected.
Elapsed: 00:00:00.00
1. 使用 rollup 函数的介 绍
Quote:
下面是直接使用普通 sql 语 句求出各地区的 汇总 数据的例子
06:41:36 SQL> set autot on
06:43:36 SQL> select area_code,sum(local_fare) local_fare
06:43:502from t
06:43:513group by area_code
06:43:574union all
06:44:005select ' 合 计 ' area_code,sum(local_fare) local_fare
06:44:066from t
06:44:087/
AREA_CODELOCAL_FARE
---------- --------------
576154225413.04
576252039619.60
576369186545.02
576453156768.46
5765104548719.19
合 计 333157065.31
6 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1310 Bytes=
24884)
10UNION-ALL
21SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871)
32TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=248
71)
41SORT (AGGREGATE)
54TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=170
17)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
6consistent gets
0physical reads
0redo size
561bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
6rows processed
下面是使用分析函数 rollup 得出的 汇总 数据的例子
06:44:09 SQL> select nvl(area_code,' 合 计 ') area_code,sum(local_fare) local_fare
06:45:262from t
06:45:303group by rollup(nvl(area_code,' 合 计 '))
06:45:504/
AREA_CODELOCAL_FARE
---------- --------------
576154225413.04
576252039619.60
576369186545.02
576453156768.46
5765104548719.19
333157065.31
6 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1309 Bytes=
24871)
10SORT (GROUP BY ROLLUP) (Cost=5 Card=1309 Bytes=24871)
21TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871
)
Statistics
----------------------------------------------------------
0recursive calls
0db block gets
4consistent gets
0physical reads
0redo size
557bytes sent via SQL*Net to client
503bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
6rows processed
从上面的例子我 们 不 难 看出使用 rollup 函数 , 系 统 的 sql 语 句更加 简单 , 耗用的 资 源更少 , 从 6 个 consistent gets 降到 4 个 consistent gets, 如果基表很大的 话 , 结 果就可想而知了 .
1. 使用 cube 函数的介 绍
Quote:
为 了介 绍 cube 函数我 们 再来看看另外一个使用 rollup 的例子
06:53:00 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:53:372from t
06:53:383group by rollup(area_code,bill_month)
06:53:494/
AREA_CODEBILL_MONTHLOCAL_FARE
---------- --------------- --------------
576120040513060433.89
576120040613318931.01
576120040713710265.93
576120040814135782.21
576154225413.04
576220040512643792.11
576220040612795060.65
576220040713224298.12
576220040813376468.72
576252039619.60
576320040516649778.91
576320040617120515.71
576320040717487493.32
576320040817928757.08
576369186545.02
576420040512487791.94
576420040613295187.67
576420040713444093.76
576420040813929695.09
576453156768.46
576520040525057737.47
576520040626058461.31
576520040726301881.40
576520040827130639.01
5765104548719.19
333157065.31
26 rows selected.
Elapsed: 00:00:00.00
系 统 只是根据 rollup 的第一个参数 area_code 对结 果集的数据做了 汇总处 理 , 而没有 对 bill_month 做 汇总 分析 处 理 ,cube 函数就是 为 了 这 个而 设计 的 .
下面 , 让 我 们 看看使用 cube 函数的 结 果
06:58:02 SQL> select area_code,bill_month,sum(local_fare) local_fare
06:58:302from t
06:58:323group by cube(area_code,bill_month)
06:58:424order by area_code,bill_month nulls last
06:58:575/
AREA_CODEBILL_MONTHLOCAL_FARE
---------- --------------- --------------
576120040513060.43
576120040613318.93
576120040713710.27
576120040814135.78
576154225.41
576220040512643.79
576220040612795.06
576220040713224.30
576220040813376.47
576252039.62
576320040516649.78
576320040617120.52
576320040717487.49
576320040817928.76
576369186.54
576420040512487.79
576420040613295.19
576420040713444.09
576420040813929.69
576453156.77
576520040525057.74
576520040626058.46
576520040726301.88
576520040827130.64
5765104548.72
20040579899.53
20040682588.15
20040784168.03
20040886501.34
333157.05
30 rows selected.
Elapsed: 00:00:00.01
可以看到 , 在 cube 函数的 输 出 结 果比使用 rollup 多出了几行 统计 数据 . 这 就是 cube 函数根据 bill_month 做的 汇总统计结 果
1 rollup 和 cube 函数的再深入
Quote:
从上面的 结 果中我 们 很容易 发现 , 每 个 统计 数据所 对应 的行都会出 现 null,
我 们 如何来区分到底是根据那个字段做的 汇总 呢 ,
这时 候 ,oracle 的 grouping 函数就粉墨登 场 了 .
如果当前的 汇总记录 是利用 该 字段得出的 ,grouping 函数就会返回 1, 否 则 返回 0
1select decode(grouping(area_code),1,'all area',to_char(area_code)) area_code,
2decode(grouping(bill_month),1,'all month',bill_month) bill_month,
3sum(local_fare) local_fare
4from t
5group by cube(area_code,bill_month)
6* order by area_code,bill_month nulls last
07:07:29 SQL> /
AREA_CODEBILL_MONTHLOCAL_FARE
---------- --------------- --------------
576120040513060.43
576120040613318.93
576120040713710.27
576120040814135.78
5761all month54225.41
576220040512643.79
576220040612795.06
576220040713224.30
576220040813376.47
5762all month52039.62
576320040516649.78
576320040617120.52
576320040717487.49
576320040817928.76
5763all month69186.54
576420040512487.79
576420040613295.19
576420040713444.09
576420040813929.69
5764all month53156.77
5 76520040525057.74
576520040626058.46
576520040726301.88
576520040827130.64
5765all month104548.72
all area20040579899.53
all area20040682588.15
all area20040784168.03
all area20040886501.34
all areaall month333157.05
30 rows selected.
Elapsed: 00:00:00.01
07:07:31 SQL>
可以看到 , 所有的空 值现 在都根据 grouping 函数做出了很好的区分 , 这样 利用 rollup,cube 和 grouping 函数 , 我 们 做数据 统计 的 时 候就可以 轻 松很多了 .
2. rank
函数的介
绍
介 绍 完 rollup 和 cube 函数的使用 , 下面我 们 来看看 rank 系列函数的使用方法 .
问题 2. 我想 查 出 这 几个月份中各个地区的 总话费 的排名 .
Quote:
为 了将 rank,dense_rank,row_number 函数的差 别显 示出来 , 我 们对 已有的 基 础 数据做一些修改 , 将 5763 的数据改成与 5761 的数据相同 .
1update t t1 set local_fare = (
2select local_fare from t t2
3where t1.bill_month = t2.bill_month
4and t1.net_type = t2.net_type
5and t2.area_code = '5761'
6* ) where area_code = '5763'
07:19:18 SQL> /
8 rows updated.
Elapsed: 00:00:00.01
我 们 先使用 rank 函数来 计 算各个地区的 话费 排名 .
07:34:19 SQL> select area_code,sum(local_fare) local_fare,
07:35:252rank() over (order by sum(local_fare) desc) fare_rank
07:35:443from t
07:35:454group by area_codee
07:35:505
07:35:52 SQL> select area_code,sum(local_fare) local_fare,
07:36:022rank() over (order by sum(local_fare) desc) fare_rank
07:36:203from t
07:36:214group by area_code
07:36:255/
AREA_CODELOCAL_FAREFARE_RANK
---------- -------------- ----------
5765104548.721
576154225.412
576354225.412
576453156.774
576252039.625
Elapsed: 00:00:00.01
我 们 可以看到 红 色 标 注的 地方出 现 了 , 跳位 , 排名 3 没有出 现
下面我 们 再看看 dense_rank 查询 的 结 果 .
07:36:26 SQL> select area_code,sum(local_fare) local_fare,
07:39:162dense_rank() over (order by sum(local_fare) desc ) fare_rank
07:39:393from t
07:39:424group by area_code
07:39:465/
AREA_CODELOCAL_FAREFARE_RANK
---------- -------------- ----------
5765104548.721
576154225.412
576354225.412
576453156.773 这 是 这 里出 现 了第三名
576252039.624
Elapsed: 00:00:00.00
在 这 个例子中 , 出 现 了一个第三名 , 这 就是 rank 和 dense_rank 的差 别 ,
rank 如果出 现 两个相同的数据 , 那 么 后面的数据就会直接跳 过这 个排名 , 而 dense_rank 则 不会 ,
差 别 更大的是 ,row_number 哪怕是两个数据完全相同 , 排名也会不一 样 , 这 个特性在我 们 想找出 对应 没个条件的唯一 记录 的 时 候又很大用 处
1select area_code,sum(local_fare) local_fare,
2row_number() over (order by sum(local_fare) desc ) fare_rank
3from t
4* group by area_code
07:44:50 SQL> /
AREA_CODELOCAL_FAREFARE_RANK
---------- -------------- ----------
5765104548.721
576154225.412
576354225.413
576453156.774
576252039.625
在 row_nubmer 函数中 , 我 们发现 , 哪怕 sum(local_fare) 完全相同 , 我 们还 是得到了不一 样 排名 , 我 们 可以利用 这 个特性剔除数据 库 中的重 复记录 .
这 个帖子中的几个例子是 为 了 说 明 这 三个函数的基本用法的 . 下个帖子我 们 将 详细 介 绍 他 们 的一些用法 .
2. rank 函数的介 绍
a. 取出数据 库 中最后入网的 n 个用 户
select user_id,tele_num,user_name,user_status,create_date
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;
b. 根据 object_name 删 除数据 库 中的重 复记录
create table t as select obj#,name from sys.obj$;
再 insert into t1 select * from t1 数次 .
delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);
c. 取出各地区的 话费 收入在各个月份排名 .
SQL> select bill_month,area_code,sum(local_fare) local_fare,
2rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
3from t
4group by bill_month,area_code
5/
BILL_MONTHAREA_CODELOCAL_FAREAREA_RANK
--------------- --------------- -------------- ----------
200405576525057.741
200405576113060.432
200405576313060.432
200405576212643.794
200405576412487.795
200406576526058.461
200406576113318.932
200406576313318.932
200406576413295.194
200406576212795.065
200407576526301.881
200407576113710.272
200407576313710.272
200407576413444.094
200407576213224.305
200408576527130.641
200408576114135.782
200408576314135.782
200408576413929.694
200408576213376.475
20 rows selected.
SQL>
3. lag 和 lead 函数介 绍
取出 每 个月的上个月和下个月的 话费总额
1select area_code,bill_month, local_fare cur_local_fare,
2lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
3lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
4lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
5lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
6from (
7select area_code,bill_month,sum(local_fare) local_fare
8from t
9group by area_code,bill_month
10* )
SQL> /
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
576120040513060.4330013318.9313710.265
576120040613318.93013060.43313710.26514135.781
576120040713710.26513060.43313318.9314135.7810
576120040814135.78113318.9313710.26500
576220040512643.7910012795.0613224.297
576220040612795.06012643.79113224.29713376.468
576220040713224.29712643.79112795.0613376.4680
576220040813376.46812795.0613224.29700
576320040513060.4330013318.9313710.265
576320040613318.93013060.43313710.26514135.781
576320040713710.26513060.43313318.9314135.7810
576320040814135.78113318.9313710.26500
576420040512487.7910013295.18713444.093
576420040613295.187012487.79113444.09313929.694
576420040713444.09312487.79113295.18713929.6940
576420040813929.69413295.18713444.09300
576520040525057.7360026058.4626301.881
576520040626058.46025057.73626301.88127130.638
576520040726301.88125057.73626058.4627130.6380
576520040827130.63826058.4626301.88100
20 rows selected.
利用 lag 和 lead 函数 , 我 们 可以在同一行中 显 示前 n 行的数据 , 也可以 显 示后 n 行的数据 .
4. sum,avg,max,min 移 动计 算数据介 绍
计 算出各个 连续 3 个月的通 话费 用的平均数
1select area_code,bill_month, local_fare,
2sum(local_fare)
3over (partition by area_code
4order by to_number(bill_month)
5range between 1 preceding and 1 following ) "3month_sum",
6avg(local_fare)
7over (partition by area_code
8order by to_number(bill_month)
9range between 1 preceding and 1 following ) "3month_avg",
10max(local_fare)
11over (partition by area_code
12order by to_number(bill_month)
13range between 1 preceding and 1 following ) "3month_max",
14min(local_fare)
15over (partition by area_code
16order by to_number(bill_month)
17range between 1 preceding and 1 following ) "3month_min"
18from (
19select area_code,bill_month,sum(local_fare) local_fare
20from t
21group by area_code,bill_month
22* )
SQL> /
AREA_CODE BILL_MONTHLOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min
--------- ---------- ---------------- ---------- ---------- ---------- ----------
576120040513060.43326379.363 13189.681513318.9313060.433
576120040613318.93040089.628 13363.209313710.26513060.433
576120040713710.26541164.976 13721.658714135.78113318.93
40089.628 = 13060.433 + 13318.930 + 13710.265
13363.2093 = (13060.433 + 13318.930 + 13710.265) / 3
13710.265 = max(13060.433 + 13318.930 + 13710.265)
13060.433 = min(13060.433 + 13318.930 + 13710.265)
576120040814135.78127846.04613923.02314135.78113710.265
576220040512643.79125438.851 12719.425512795.0612643.791
576220040612795.06038663.14812887.71613224.29712643.791
576220040713224.29739395.825 13131.941713376.46812795.06
576220040813376.46826600.765 13300.382513376.46813224.297
576320040513060.43326379.363 13189.681513318.9313060.433
576320040613318.93040089.628 13363.209313710.26513060.433
576320040713710.26541164.976 13721.658714135.78113318.93
576320040814135.78127846.04613923.02314135.78113710.265
576420040512487.79125782.97812891.48913295.18712487.791
576420040613295.18739227.071 13075.690313444.09312487.791
576420040713444.09340668.974 13556.324713929.69413295.187
576420040813929.69427373.787 13686.893513929.69413444.093
576520040525057.73651116.19625558.09826058.4625057.736
576520040626058.46077418.077 25806.025726301.88125057.736
576520040726301.88179490.97926496.99327130.63826058.46
576520040827130.63853432.519 26716.259527130.63826301.881
20 rows selected.
5. ratio_to_report 函数的介 绍
Quote:
1select bill_month,area_code,sum(local_fare) local_fare,
2ratio_to_report(sum(local_fare)) over
3( partition by bill_month ) area_pct
4from t
5* group by bill_month,area_code
SQL> break on bill_month skip 1
SQL> compute sum of local_fare on bill_month
SQL> compute sum of area_pct on bill_month
SQL> /
BILL_MONTH AREA_CODELOCAL_FAREAREA_PCT
---------- --------- ---------------- ----------
200405576113060.433 .171149279
576212643.791 .165689431
576313060.433 .171149279
576412487.791 .163645143
576525057.736 .328366866
**********---------------- ----------
sum76310.1841
200406576113318.930 .169050772
576212795.060 .162401542
576313318.930 .169050772
576413295.187 .168749414
576526058.460 .330747499
**********---------------- ----------
sum78786.5671
200407576113710.265 .170545197
576213224.297 .164500127
576313710.265 .170545197
576413444.093 .167234221
576526301.881 .327175257
**********---------------- ----------
sum80390.8011
200408576114135.781 .170911147
576213376.468 .161730539
576314135.781 .170911147
576413929.694 .168419416
576527130.638 .328027751
**********---------------- ----------
sum82708.3621
20 rows selected.
6 first,last 函数使用介 绍
Quote:
取出 每 月通 话费 最高和最低的两个用 户 .
1select bill_month,area_code,sum(local_fare) local_fare,
2first_value(area_code)
3over (order by sum(local_fare) desc
4rows unbounded preceding) firstval,
5first_value(area_code)
6over (order by sum(local_fare) asc
7rows unbounded preceding) lastval
8from t
9group by bill_month,area_code
10* order by bill_month
SQL> /
BILL_MONTH AREA_CODELOCAL_FARE FIRSTVALLASTVAL
---------- --------- ---------------- --------------- ---------------
200405576412487.791 57655764
200405576212643.791 57655764
200405576113060.433 57655764
200405576525057.736 57655764
200405576313060.433 57655764
200406576212795.060 57655764
200406576313318.930 57655764
200406576413295.187 57655764
200406576526058.460 57655764
200406576113318.930 57655764
200407576213224.297 57655764
200407576526301.881 57655764
200407576113710.265 57655764
200407576313710.265 57655764
200407576413444.093 57655764
200408576213376.468 57655764
200408576413929.694 57655764
200408576114135.781 57655764
200408576527130.638 57655764
200408576314135.781 57655764
20 rows selected.
介 绍 完 rollup 和 cube 函数的使用 , 下面我 们 来看看 rank 系列函数的使用方法 .
问题 2. 我想 查 出 这 几个月份中各个地区的 总话费 的排名 .
Quote:
为 了将 rank,dense_rank,row_number 函数的差 别显 示出来 , 我 们对 已有的 基 础 数据做一些修改 , 将 5763 的数据改成与 5761 的数据相同 .
1update t t1 set local_fare = (
2select local_fare from t t2
3where t1.bill_month = t2.bill_month
4and t1.net_type = t2.net_type
5and t2.area_code = '5761'
6* ) where area_code = '5763'
07:19:18 SQL> /
8 rows updated.
Elapsed: 00:00:00.01
我 们 先使用 rank 函数来 计 算各个地区的 话费 排名 .
07:34:19 SQL> select area_code,sum(local_fare) local_fare,
07:35:252rank() over (order by sum(local_fare) desc) fare_rank
07:35:443from t
07:35:454group by area_codee
07:35:505
07:35:52 SQL> select area_code,sum(local_fare) local_fare,
07:36:022rank() over (order by sum(local_fare) desc) fare_rank
07:36:203from t
07:36:214group by area_code
07:36:255/
AREA_CODELOCAL_FAREFARE_RANK
---------- -------------- ----------
5765104548.721
576154225.412
576354225.412
576453156.774
576252039.625
Elapsed: 00:00:00.01
我 们 可以看到 红 色 标 注的 地方出 现 了 , 跳位 , 排名 3 没有出 现
下面我 们 再看看 dense_rank 查询 的 结 果 .
07:36:26 SQL> select area_code,sum(local_fare) local_fare,
07:39:162dense_rank() over (order by sum(local_fare) desc ) fare_rank
07:39:393from t
07:39:424group by area_code
07:39:465/
AREA_CODELOCAL_FAREFARE_RANK
---------- -------------- ----------
5765104548.721
576154225.412
576354225.412
576453156.773 这 是 这 里出 现 了第三名
576252039.624
Elapsed: 00:00:00.00
在 这 个例子中 , 出 现 了一个第三名 , 这 就是 rank 和 dense_rank 的差 别 ,
rank 如果出 现 两个相同的数据 , 那 么 后面的数据就会直接跳 过这 个排名 , 而 dense_rank 则 不会 ,
差 别 更大的是 ,row_number 哪怕是两个数据完全相同 , 排名也会不一 样 , 这 个特性在我 们 想找出 对应 没个条件的唯一 记录 的 时 候又很大用 处
1select area_code,sum(local_fare) local_fare,
2row_number() over (order by sum(local_fare) desc ) fare_rank
3from t
4* group by area_code
07:44:50 SQL> /
AREA_CODELOCAL_FAREFARE_RANK
---------- -------------- ----------
5765104548.721
576154225.412
576354225.413
576453156.774
576252039.625
在 row_nubmer 函数中 , 我 们发现 , 哪怕 sum(local_fare) 完全相同 , 我 们还 是得到了不一 样 排名 , 我 们 可以利用 这 个特性剔除数据 库 中的重 复记录 .
这 个帖子中的几个例子是 为 了 说 明 这 三个函数的基本用法的 . 下个帖子我 们 将 详细 介 绍 他 们 的一些用法 .
2. rank 函数的介 绍
a. 取出数据 库 中最后入网的 n 个用 户
select user_id,tele_num,user_name,user_status,create_date
from (
select user_id,tele_num,user_name,user_status,create_date,
rank() over (order by create_date desc) add_rank
from user_info
)
where add_rank <= :n;
b. 根据 object_name 删 除数据 库 中的重 复记录
create table t as select obj#,name from sys.obj$;
再 insert into t1 select * from t1 数次 .
delete from t1 where rowid in (
select row_id from (
select rowid row_id,row_number() over (partition by obj# order by rowid ) rn
) where rn <> 1
);
c. 取出各地区的 话费 收入在各个月份排名 .
SQL> select bill_month,area_code,sum(local_fare) local_fare,
2rank() over (partition by bill_month order by sum(local_fare) desc) area_rank
3from t
4group by bill_month,area_code
5/
BILL_MONTHAREA_CODELOCAL_FAREAREA_RANK
--------------- --------------- -------------- ----------
200405576525057.741
200405576113060.432
200405576313060.432
200405576212643.794
200405576412487.795
200406576526058.461
200406576113318.932
200406576313318.932
200406576413295.194
200406576212795.065
200407576526301.881
200407576113710.272
200407576313710.272
200407576413444.094
200407576213224.305
200408576527130.641
200408576114135.782
200408576314135.782
200408576413929.694
200408576213376.475
20 rows selected.
SQL>
3. lag 和 lead 函数介 绍
取出 每 个月的上个月和下个月的 话费总额
1select area_code,bill_month, local_fare cur_local_fare,
2lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
3lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
4lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
5lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
6from (
7select area_code,bill_month,sum(local_fare) local_fare
8from t
9group by area_code,bill_month
10* )
SQL> /
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
576120040513060.4330013318.9313710.265
576120040613318.93013060.43313710.26514135.781
576120040713710.26513060.43313318.9314135.7810
576120040814135.78113318.9313710.26500
576220040512643.7910012795.0613224.297
576220040612795.06012643.79113224.29713376.468
576220040713224.29712643.79112795.0613376.4680
576220040813376.46812795.0613224.29700
576320040513060.4330013318.9313710.265
576320040613318.93013060.43313710.26514135.781
576320040713710.26513060.43313318.9314135.7810
576320040814135.78113318.9313710.26500
576420040512487.7910013295.18713444.093
576420040613295.187012487.79113444.09313929.694
576420040713444.09312487.79113295.18713929.6940
576420040813929.69413295.18713444.09300
576520040525057.7360026058.4626301.881
576520040626058.46025057.73626301.88127130.638
576520040726301.88125057.73626058.4627130.6380
576520040827130.63826058.4626301.88100
20 rows selected.
利用 lag 和 lead 函数 , 我 们 可以在同一行中 显 示前 n 行的数据 , 也可以 显 示后 n 行的数据 .
4. sum,avg,max,min 移 动计 算数据介 绍
计 算出各个 连续 3 个月的通 话费 用的平均数
1select area_code,bill_month, local_fare,
2sum(local_fare)
3over (partition by area_code
4order by to_number(bill_month)
5range between 1 preceding and 1 following ) "3month_sum",
6avg(local_fare)
7over (partition by area_code
8order by to_number(bill_month)
9range between 1 preceding and 1 following ) "3month_avg",
10max(local_fare)
11over (partition by area_code
12order by to_number(bill_month)
13range between 1 preceding and 1 following ) "3month_max",
14min(local_fare)
15over (partition by area_code
16order by to_number(bill_month)
17range between 1 preceding and 1 following ) "3month_min"
18from (
19select area_code,bill_month,sum(local_fare) local_fare
20from t
21group by area_code,bill_month
22* )
SQL> /
AREA_CODE BILL_MONTHLOCAL_FARE 3month_sum 3month_avg 3month_max 3month_min
--------- ---------- ---------------- ---------- ---------- ---------- ----------
576120040513060.43326379.363 13189.681513318.9313060.433
576120040613318.93040089.628 13363.209313710.26513060.433
576120040713710.26541164.976 13721.658714135.78113318.93
40089.628 = 13060.433 + 13318.930 + 13710.265
13363.2093 = (13060.433 + 13318.930 + 13710.265) / 3
13710.265 = max(13060.433 + 13318.930 + 13710.265)
13060.433 = min(13060.433 + 13318.930 + 13710.265)
576120040814135.78127846.04613923.02314135.78113710.265
576220040512643.79125438.851 12719.425512795.0612643.791
576220040612795.06038663.14812887.71613224.29712643.791
576220040713224.29739395.825 13131.941713376.46812795.06
576220040813376.46826600.765 13300.382513376.46813224.297
576320040513060.43326379.363 13189.681513318.9313060.433
576320040613318.93040089.628 13363.209313710.26513060.433
576320040713710.26541164.976 13721.658714135.78113318.93
576320040814135.78127846.04613923.02314135.78113710.265
576420040512487.79125782.97812891.48913295.18712487.791
576420040613295.18739227.071 13075.690313444.09312487.791
576420040713444.09340668.974 13556.324713929.69413295.187
576420040813929.69427373.787 13686.893513929.69413444.093
576520040525057.73651116.19625558.09826058.4625057.736
576520040626058.46077418.077 25806.025726301.88125057.736
576520040726301.88179490.97926496.99327130.63826058.46
576520040827130.63853432.519 26716.259527130.63826301.881
20 rows selected.
5. ratio_to_report 函数的介 绍
Quote:
1select bill_month,area_code,sum(local_fare) local_fare,
2ratio_to_report(sum(local_fare)) over
3( partition by bill_month ) area_pct
4from t
5* group by bill_month,area_code
SQL> break on bill_month skip 1
SQL> compute sum of local_fare on bill_month
SQL> compute sum of area_pct on bill_month
SQL> /
BILL_MONTH AREA_CODELOCAL_FAREAREA_PCT
---------- --------- ---------------- ----------
200405576113060.433 .171149279
576212643.791 .165689431
576313060.433 .171149279
576412487.791 .163645143
576525057.736 .328366866
**********---------------- ----------
sum76310.1841
200406576113318.930 .169050772
576212795.060 .162401542
576313318.930 .169050772
576413295.187 .168749414
576526058.460 .330747499
**********---------------- ----------
sum78786.5671
200407576113710.265 .170545197
576213224.297 .164500127
576313710.265 .170545197
576413444.093 .167234221
576526301.881 .327175257
**********---------------- ----------
sum80390.8011
200408576114135.781 .170911147
576213376.468 .161730539
576314135.781 .170911147
576413929.694 .168419416
576527130.638 .328027751
**********---------------- ----------
sum82708.3621
20 rows selected.
6 first,last 函数使用介 绍
Quote:
取出 每 月通 话费 最高和最低的两个用 户 .
1select bill_month,area_code,sum(local_fare) local_fare,
2first_value(area_code)
3over (order by sum(local_fare) desc
4rows unbounded preceding) firstval,
5first_value(area_code)
6over (order by sum(local_fare) asc
7rows unbounded preceding) lastval
8from t
9group by bill_month,area_code
10* order by bill_month
SQL> /
BILL_MONTH AREA_CODELOCAL_FARE FIRSTVALLASTVAL
---------- --------- ---------------- --------------- ---------------
200405576412487.791 57655764
200405576212643.791 57655764
200405576113060.433 57655764
200405576525057.736 57655764
200405576313060.433 57655764
200406576212795.060 57655764
200406576313318.930 57655764
200406576413295.187 57655764
200406576526058.460 57655764
200406576113318.930 57655764
200407576213224.297 57655764
200407576526301.881 57655764
200407576113710.265 57655764
200407576313710.265 57655764
200407576413444.093 57655764
200408576213376.468 57655764
200408576413929.694 57655764
200408576114135.781 57655764
200408576527130.638 57655764
200408576314135.781 57655764
20 rows selected.
扫一扫
365
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
