【窗口函数】2.1-第二类窗口函数：排序函数需求实现（1）-CSDN博客

本博客记录博主学习SQL语法中窗口函数的过程和总结，学习视频参考下方链接内容

8.窗口函数之排序函数需求实现（1）

0-数据建表

为练习窗口函数，首先根据视频中的数据，生成一组模拟数据。其具体python代码可以参考下方链接的本系列作品：
【窗口函数】1.1-第一类窗口函数：累积计算窗口函数需求实现（1）
数据预览结果如下：
在这里插入图片描述

1-排序函数需求实现（1）

需求（1）：查询出2020年1月份，购买商品品类数的用户排名
对于这个需求，

最终的目标字段是 “排名”，所以一定会涉及排序窗口函数：row_number()、rank()、dense_rank()
排名的定语是 “2020年1月份，用户的排名，是基于用户所购买商品品类数的”

1). 2020年1月份是对日期的条件筛选，对应代码逻辑为： where(%Y-%m)=‘2020-01’
2). 用户所购买的商品品类数，与"所有城市的GMV"等一类问题的本质较为类似，往往涉及分组聚合

博主将该类问题总结为"每个X的Y的Z"，其中：
X通常为<分组字段>
Y为<聚合字段>
Z为具体的聚合操作，比如求和则为sum()、计数则为count()

对于本例，代入上面的模式得到：每个<用户>的<购买商品品类>的<去重计数>
所以：
分组字段是：用户 / user_id;
聚合字段是：商品品类 / goods_category;
聚合方式是：去重计数 / count(distinct)

所以对应需求（1）可以分为两步求解，分别是：
1. 通过条件筛选+分组聚合，查询出2020年1月份每个用户所购买的商品品类数
2. 将上步查询出的表，作为子查询，应用排序窗口函数row_number()等

Step1：查询出2020年1月份每个用户所购买的商品品类数

基于前面的分析，首先通过where条件筛选出2020年1月内的用户，在对这些用户group by分组，对商品类别count(distinct ) 去重计数得到每个用户购买产品的类别数，查询代码如下：

select
    user_id
    ,count(distinct goods_category) as goods_category_cnt   # 1.3-聚合字段：goods_category/商品类别
                                                            # 1.4-聚合方式：count( distinct ) 去重计数
from over_fun_example_data
where date_format(pay_time,'%Y-%m')='2020-01'               # 1.1-where条件筛选
group by user_id                                            # 1.2-分组字段：user_id/用户
order by goods_category_cnt desc;                           # 1.5-排序字段：按购买商品类数, 降序展示

查询结果如下：
在这里插入图片描述

Step2：在Step1的基础上，应用排序窗口函数

将Step1的查询结果，作为子查询，应用窗口函数。

要给出基于购买商品类别数对用户排名，现在每个用户的购买商品类别数字段已经有了（goods_catefgory_cnt），所以直接应用排序窗口函数：row_number()、rank()与dense_rank()即可，该类的语法为：

 row_number() / rank() / dense_rank()    over( (partition by <分区字段> order by <排序字段> )

其中前面的排序函数rank()等无需传入参数，通过排序字段/order by设置即可；
而由于此前子查询的数据仅来自2020年1月份，所以分区字段partition by无需设置。
但如果存在其他月份的数据与字段，则可以设置partition by为月份字段，即可分区排序不同月份的购买类别数。
最终查询代码如下：

select
    user_id
    ,goods_category_cnt
     
     # 2.2-对子查询依次 使用排序窗口函数：row_number()、rank()、dense_rank()
     # 结果按 商品类别数降序展示
    ,row_number()  over( order by goods_category_cnt desc ) as order_by_RowNumber
    ,rank() over( order by goods_category_cnt desc ) as order_by_Rank
    ,dense_rank() over( order by goods_category_cnt desc ) as order_by_DenseRank
from
    # 2.1-将Step1的结果作为子查询
(
    select
        user_id
        ,count(distinct goods_category) as goods_category_cnt   # 1.3-聚合字段：goods_category/商品类别
                                                                # 1.4-聚合方式：count( distinct ) 去重计数
    from over_fun_example_data
    where date_format(pay_time,'%Y-%m')='2020-01'               # 1.1-where条件筛选
    group by user_id                                            # 1.2-分组字段：user_id/用户
    order by goods_category_cnt desc                            # 1.5-排序字段：按购买商品类数, 降序展示
) as temp_a;

查询结果如下：
在这里插入图片描述
从上图中三类排序窗口函数的返回的结果可以看出

row_number() 会对排序数据，根据行的位置顺序生成唯一次序，
类似与pandas对DataFrame按行生成的位置索引
rank() 会对排序数据中，数值上相同的行 ‘共享次序’，但后面行的次序将基于前面行的实际行数计算：
比如1-7行的用户的排序字段数值均为3，因此rank()将1-7行用户的次序均视为1，但第8行用户的排序字段数值为2，实际次序为：前面所有行的长度+1 = 7+1 = 8
dense_rank() 同样会对排序数据中，数值上相同的行 ‘共享次序’，但后面行的次序将基于前面行的次序计算：
比如1-7行的用户的排序字段数值均为3，因此dense_rank()将1-7行用户的次序均视为1，但第8行用户的排名将顺延上一次序计算，实际次序为：最近行的次序+1 = 1+1 = 2