使用SQL进行数据分析——窗口函数示例分享

   窗口函数在日常的处理分析中必不可少，相关详细介绍可查看之前的文章，大家可在公众号（Excel办公小技巧）首页点击右上角搜索按钮进行搜索。今天主要是有节奏的从使用示例中巩固。

【1、从排序开始入门】

    学习窗口函数，怎么能错过排序用法使用。从三种排序函数的功能使用中，还可以掌握窗口函数结构，比如分组、排序参数的使用。

    比如，按订单时间降序排序用户订单数据：

1.1 row_number

    普通排序，重复的编号+1，编号最大值与实际数据行数相同。

select *,row_number() over(partition by userid order by orderdate desc) rnfrom table

1.2 rank

    重复的编号不变，后面的按前面行数+1往后排。​​​​​​​

select *,rank() over(partition by userid order by orderdate desc) rnfrom table

1.3 dense_rank

   重复行编号不变，后面的编号+1，行的数量≥排序编号​​​​​​​

select *,dense_rank() over(partition by userid order by orderdate desc) rnfrom table

【2、使用聚合函数】

    数据统计分析，聚合函数当然必不可少，如何将聚合函数与窗口函数完美结合呢？

    比如，获得用户的销售总额、最大销售、订单数量等等：

2.1 sum函数+窗口函数​​​​​​​

select *,sum(money) over(partition by userid ) rnfrom table

2.2 max函数+窗口函数​​​​​​​

select *,max(money) over(partition by userid ) rnfrom table

2.3 count函数+窗口函数​​​​​​​

select *,count(orderid) over(partition by userid ) rnfrom table

【3、进一步应用】

    以上，可以看到，窗口函数不会改变原有数据的行数，还能帮助我们添加想要的列，而有了新增列，你想做什么，是不是很容易了呢？

3.1 窗口函数+聚合函数

    取销售额前三名的用户订单明细：​​​​​​​

select *from(select * ,DENSE_RANK()over( order by rn desc) rn1   from(select *,sum(money) over(partition by userid ) rn    from hdp_yunchuang_defaultdb.guofeng06_gogo))where rn1<4    

    同样的方法，也可以取到订单数、最大销售额、销售数量、平均销售额等排序后的用户订单明细。

3.2 连续登陆判别

    如何判别客户是否连续下单（天维度）​​​​​​​

select *,lead(orderdate,1,'补足') over(partition by userid order by orderdate asc ) rnfrom table

    当‘orderdate’列和“求和”列的差值=1，说明用户有连续下单行为出现。

    如果是判别某天下单用户在前一天是否有下单，则可以使用lag函数。

3.3 指定连续天数判别

    比如取半个月内，连续15天登陆，14天，...，2天等​​​​​​​

 select *,lead(orderdate,3) over(partition by userid order by orderdate asc ) lianxu4,     lead(orderdate,2) over(partition by userid order by orderdate asc ) lianxu3,     lead(orderdate,1) over(partition by userid order by orderdate asc ) lianxu2,lead(orderdate,0) over(partition by userid order by orderdate asc ) lianxu1 from table

    不为空即为符合要求结果，后面简单处理就可以了（以上，记得先去重，本例未去重，实际计算的不对，再在后续进行天数比较）。