使用SQL窗口函数进行商务数据分析

本文会从一个商务分析案例入手，说明SQL窗口函数的使用方式。通过本文的5个需求分析，可以看出SQL窗口函数的功能十分强大，不仅能够使我们编写的SQL逻辑更加清晰，而且在某种程度上可以简化需求开发。

数据准备

本文主要分析只涉及一张订单表orders，操作过程在Hive中完成，具体数据如下：

需求1：收入增长

在业务方面，第m1个月的收入增长计算如下：100 *（m1-m0）/ m0

其中，m1是给定月份的收入，m0是上个月的收入。因此，从技术上讲，我们需要找到每个月的收入，然后以某种方式将每个月的收入与上一个收入相关联，以便进行上述计算。计算当时如下：

结果输出

我们还可以按照按城市分组进行统计，查看某个城市某个月份的收入增长情况

结果输出

需求2：累计求和

累计汇总，即当前元素和所有先前元素的总和，如下面的SQL：

结果输出

我们还可以使用下面的组合方式进行分析，SQL如下：

结果输出：

需求3：处理重复数据

从上面的数据可以看出，存在两条重复的数据**(5,"D","上海","2020-02-05 00:00:00.000000",250),(5,"D","上海","2020-02-05 12:00:00.000000",300),**显然需要对其进行清洗去重，保留最新的一条数据，SQL如下：

我们先进行分组排名，然后保留最新的那条数据即可：

结果输出：

经过上面的清洗过程，对数据进行了去重。重新计算上面的需求1，正确SQL脚本为：

结果输出：

将清洗后的数据创建成视图，方便以后使用

需求4：分组取TopN

分组取topN是最长见的SQL窗口函数使用场景，下面的SQL是计算每个月份的top2订单金额，如下：

需求5：重复购买行为

下面的SQL计算重复购买率：重复购买的人数/总人数*100%以及第一笔订单金额与第二笔订单金额之间的典型差额:avg(第二笔订单金额/第一笔订单金额)

结果输出：

WITH结果输出：

最终结果输出：

总结

本文主要分享了SQL窗口函数的基本使用方式以及使用场景，并结合了具体的分析案例。通过本文的分析案例，可以加深对SQL窗口函数的理解。