分析函数和聚合函数区别_getnumber函数的用法

2024年 9月 17日下午12:16 • 激活谷笔记

碎语

学习的正态曲线：入门容易，精通难
积累的正态曲线：先越读越多，后越读越少

星中有数

在工作中，我们会遇到这样的场景，为了激励不同地区销售，我们每年都要奖励销售额前三的地区，这时候老板问你：

1. 2014年销售额前三的地区，你该怎么取？

2. 2014年到2017年每年销售额前三地区，你该怎么取？

比如我们像字节跳动一样给力，老板说你把2016年到2017年每个月销售额前2的地区都找出来，每个人多奖励半年的工资，你该怎么去取？（多想有这么好的老板）

回到现实我们想想怎么做？

SQL

数据参考：数据分析师经常遇到的SQL场景解析

1.2014年销售额前三的地区

基本取法-SQL-1

select Region, sum(sales) as sum_sales from chaoshi.order where year(date(order_date) ) = '2014' group by Region order by sum_sales desc limit 3

结果

开窗函数-SQL-1-1

select region, sum_sales, num from (select Region, sum(sales) as sum_sales, row_number() over(order by sum(sales) desc) as num from chaoshi.order where year(date(order_date) ) = '2014' group by Region )a where num <=3

结果

2. 2014年到2017年每年销售额前三的地区

SQL-2

对于单一一年销售额前三，我们可以使用order by和limit来取到，但是我要取每一年销售额前三，就不是那么容易，此时就体现了开窗函数的强大之处

select sal_year, region, sum_sales, num from (select year(date(order_date) ) as sal_year, Region, sum(sales) as sum_sales, row_number() over(partition by year(date(order_date) ) order by sum(sales) desc) as num from chaoshi.order where year(date(order_date) ) between 2014 and 2017 group by year(date(order_date) ), Region )a where num <=3

结果

3.2016年到2017年每个月销售额前2地区

SQL-3

select sal_year, sal_month, region, sum_sales, num from (select year(date(order_date) ) as sal_year, month(date(order_date)) as sal_month, Region, sum(sales) as sum_sales, row_number() over(partition by year(date(order_date) ) , month(date(order_date)) order by sum(sales) desc) as num from chaoshi.order where year(date(order_date) ) between 2016 and 2017 group by year(date(order_date) ), month(date(order_date)), Region )a where num <=2

部分预览结果

星星详析

原理

1. 2014年销售额前三的地区

当我们只取一年的销售额前三销售地区时，我们很容易想到以地区进行分组（group by），对销售额进行求和（sum(sales)），然后对于销售额排序(order by )，之后，取前三行(limit 3 )就可以了,但是要对每年都取前三就不能这么取了。所以需要使用开窗函数。

2. 2014年到2017年每年销售额前三的地区

我们看SQL-2，直接就是使用了开窗函数和row_number()一起。我们对于年和地区分组（group by）之后，就直接对于年进行重新分组（partition by year(date(order_date)），分组完后，就以聚合sum(sales)进行排序，此时，相当于在每年中进行排序，未来我们直接取排序小于等于3就可以了。如果不好理解，我建议初学者都嵌套一层：

select sal_year, region, sum_sales, num from(##开窗 select sal_year, region, sum_sales, row_number() over(partition by sal_year order by sum_sales desc) as num #对年进行分组，以sum_sales降序排列 from (select ## 只为聚合 year(date(order_date) ) as sal_year, Region, sum(sales) as sum_sales from chaoshi.order where year(date(order_date) ) between 2014 and 2017 group by year(date(order_date) ), Region )a )b where num <=3;

结果

我们在这里多加了一层嵌套，主要是为了区分group by 和partition by的区别，希望可以体会

3. 2016年到2017年每个月销售额前2地区

对于SQL3,在这里我们主要想取到每个月销售额前2的地区，这个时候，我们在开窗分组时候（partition by）需要对于年和月分组，然后还是以销售额进行降序，取排序之后小于等于2，即可以取到每年每月前2的销售额和地区了

注意

初学者一定最好多嵌套一层，不要同时使用group by和partition by,不然很难理解开窗的精髓。

结束语

如果我不能让您看懂，那是我的问题，如果有疑问可以关注我，然后私聊我，我会尽最大的努力帮助你,如果觉得对你有帮助，请帮忙点赞/关注/分享/收藏，谢谢！

分析函数和聚合函数区别_getnumber函数的用法

碎语

星中有数

SQL

星星详析

结束语

相关推荐