ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]oracle 常用分析函数

[转]oracle 常用分析函数

原创 Linux操作系统 作者:31597359 时间:2019-06-07 20:48:07 0 删除 编辑

常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)


常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

示例:
14:33:29 SQL> select type,qty from test;
TYPE QTY
---------- ----------
1 3
1 6
2 5
2 9
2 7

14:33:36 SQL> select type,qty,to_char(row_number() over(partition by type order by qty))||'/'||to_char(count(*) over(partition by type)) as cnt2 from test;
TYPE QTY CNT2
---------- ---------- ------------
1 3 1/2
1 6 2/2
2 5 1/3
2 7 2/3
2 9 3/3

SQL> select * from test;

ID MC
---------- --------------------------------------------------
1 11111
2 22222
3 33333
4 44444

SQL>
SQL> select t.id,mc,to_char(b.rn)||'/'||t.id
2 from test t,
3 (select rownum rn from (select max(to_number(id)) mid from test) connect by rownum <=mid ) b
4 where b.rn<=to_number(t.id)
5 order by id,3
6 /

ID MC TO_CHAR(B.RN)||'/'||T.ID
---------- -------------------------------------------------- ---------------------------------------------------
1 11111 1/1
2 22222 1/2
2 22222 2/2
3 33333 1/3
3 33333 2/3
3 33333 3/3
4 44444 1/4
4 44444 2/4
4 44444 3/4
4 44444 4/4

10 rows selected

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8000572/viewspace-62185/,如需转载,请注明出处,否则将追究法律责任。

下一篇: instr和decode
请登录后发表评论 登录
全部评论

注册时间:2006-10-07

  • 博文量
    120
  • 访问量
    76974