红联Linux门户
Linux帮助

初学者如何学习分析函数

发布时间:2006-11-05 00:48:45来源:红联作者:flycocoon
经常发现一些网友问分析函数的问题,虽然分析函数比较多,有一些分析函数也确实比较难以理解,会给初学者有很大的心理压力,其实对于大部分的分析函数,一般都不会有机会用到,所以掌握常用的几个分析函数就可以啦。

常用的分析函数如下所列:

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
文章评论

共有 2615 条评论

  1. 125.120.137.* 于 2007-05-31 07:03:03发表:

    wow power leveling,Lineage 2 wow power leveling.
    wow power leveling lvl service
    and cheap wow power leveling,wow power leveling
    sale for youWe offerwow power leveling

  2. 60.177.54.* 于 2007-05-29 07:59:11发表:

    性功能障碍
    东莞液压设备
    昆明高尔夫
    自动门
    加湿机