碎语

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

星中有数

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

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,不然很难理解开窗的精髓。

结束语

如果我不能让您看懂，那是我的问题，如果有疑问可以关注我，然后私聊我，我会尽最大的努力帮助你