数据仓库ＳＱＬ分析之二

(5)rank() over开窗函数
按照月份、地区，求打工收入排序

Sql代码  

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   

       rank() over (partition by earnmonth,area order by personincome desc) 排名  

from earnings;  

 查看结果：

(6)dense_rank() over开窗函数
按照月份、地区，求打工收入排序2

Sql代码  

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   

       dense_rank() over (partition by earnmonth,area order by personincome desc) 排名  

from earnings;  

 结果如下：

(7)row_number() over开窗函数
按照月份、地区，求打工收入排序3

Sql代码  

select earnmonth 月份,area 地区,sname 打工者, personincome 收入,   

       row_number() over (partition by earnmonth,area order by personincome desc) 排名  

from earnings;  

 结果如下：

通过(5)(6)(7)发现rank,dense_rank,row_number的区别：
结果集中如果出现两个相同的数据，那么rank会进行跳跃式的排名，
比如两个第二，那么没有第三接下来就是第四；
但是dense_rank不会跳跃式的排名，两个第二接下来还是第三；
row_number最牛，即使两个数据相同，排名也不一样。

 

(8)sum累计求和
根据月份求出各个打工者收入总和，按照收入由少到多排序

Sql代码  

select earnmonth 月份,area 地区,sname 打工者,   

       sum(personincome) over (partition by earnmonth,area order by personincome) 总收入  

from earnings;  

 查看结果如下：

(9)max，min，avg和sum函数综合运用
按照月份和地区求打工收入最高值，最低值，平均值和总额

Sql代码  

select distinct earnmonth 月份, area 地区,  

       max(personincome) over(partition by earnmonth,area) 最高值,  

       min(personincome) over(partition by earnmonth,area) 最低值,  

       avg(personincome) over(partition by earnmonth,area) 平均值,  

       sum(personincome) over(partition by earnmonth,area) 总额  

from earnings;  

 结果如下：

(10)lag和lead函数
求出每个打工者上个月和下个月有没有赚钱(personincome大于零即为赚钱)

Sql代码

select earnmonth 本月,sname 打工者,  

       lag(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 上月,  

       lead(decode(nvl(personincome,0),0,'没赚','赚了'),1,0) over(partition by sname order by earnmonth) 下月  

from earnings; 

11,窗口函数

—累计求和

select earnmonth,

       sum(personincome),

       sum(sum(t.personincome)) over(order by t.earnmonth rows between unbounded preceding and current row)

  from earnings t

 group by earnmonth

--order by earnmonth,area nulls last;

 

select earnmonth,

       area,

       sum(personincome),

       sum(sum(t.personincome)) over(order by t.earnmonth, t.area rows between unbounded preceding and current row)

  from earnings t

 group by earnmonth, area

--order by earnmonth,area nulls last;

--滚动求和：求前一行，当前行，后一行的和

select earnmonth,

       area,

       sum(personincome),

       sum(sum(t.personincome)) over(order by t.earnmonth, t.area rows between 1 preceding and 1 following)

  from earnings t

 group by earnmonth, area

--order by earnmonth,area nulls last;

 

--累计求平均值

select earnmonth,

       sum(personincome),

       avg(sum(t.personincome)) over(order by t.earnmonth rows between unbounded preceding and current row)

  from earnings t

 group by earnmonth

--order by earnmonth,area nulls last;

 

select earnmonth,

       area,

       sum(personincome),

       avg(sum(t.personincome)) over(order by t.earnmonth, t.area rows between unbounded preceding and current row)

  from earnings t

 group by earnmonth, area

--order by earnmonth,area nulls last;

 

--移动平均值

select earnmonth,

       area,

       sum(personincome),

       avg(sum(t.personincome)) over(order by t.earnmonth, t.area rows between 1 preceding and 1 following)

  from earnings t

 group by earnmonth, area

--order by earnmonth,area nulls last;

 

---返回窗口的第一行

select earnmonth,

       area,

       sum(personincome),

       first_value(sum(t.personincome)) over(order by t.earnmonth, t.area rows between 1 preceding and 1 following)

  from earnings t

 group by earnmonth, area

--order by earnmonth,area nulls last;

--返回窗口的最后一行

select earnmonth,

       area,

       sum(personincome),

       LAST_VALUE(sum(t.personincome)) over(order by t.earnmonth, t.area rows between 1 preceding and 1 following)

  from earnings t

 group by earnmonth, area

--order by earnmonth,area nulls last;

 

--获取当前窗口的前x行的值

select earnmonth,

       area,

       sum(personincome),

       lag(sum(t.personincome),2) over(order by t.earnmonth,t.area)

  from earnings t

 group by earnmonth, area

--order by earnmonth,area nulls last;

 

--获取当前窗口的后x行的值

select earnmonth,

       area,

       sum(personincome),

       lead(sum(t.personincome),2) over(order by t.earnmonth,t.area)

  from earnings t

 group by earnmonth, area

 --order by earnmonth,area nulls last;

 

说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦，惭愧，我用子查询有的还查不出来呢。

 

语法如下：

lag(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
lead(value_expression [,offset] [,default]) over ([query_partition_clase] order_by_clause)；
其中：
value_expression：可以是一个字段或一个内建函数。
offset是正整数，默认为1，指往前或往后几点记录．因组内第一个条记录没有之前的行，最后一行没有之后的行，
default就是用于处理这样的信息，默认为空。

 

再讲讲所谓的开窗函数，依本人遇见，开窗函数就是 over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，但是我根据什么来呢？over提供一个窗口，可以根据什么什么分组，就 用partition by，然后在组内根据什么什么进行内部排序，就用 order by

 

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

转载于:http://blog.itpub.net/30109892/viewspace-1972983/