关于SQL的开窗函数，你踩过哪些坑？-CSDN博客

1.简单的 count() over() 竟然有大大的坑？

问题背景

关于count() over()的坑，群里也有同学反馈过，今天刚好收录一下，分析一下。

先看这样一张表（event）：

code

我们的预期结果是这样的：

但实际上，上述code的结果是这样的：

是的，即使是换成更好理解的count() over()，结果也一样：

是的，他们并不准确！

原因

其实，这里我们order by进行排序的行，可以理解为是一种“rank()行（或者说是range行）”。

两条C1因为time字段的取值都一样，使用rank()计算的结果都是1，在逻辑上会看成同一行，所以后续无论是使用sum()还是count()，其最终的结论都是一样的。

解决思路（1）

可以在排序的字段后面增加二级排序字段，并确保其是唯一的，不会重复，例如下面：

解决思路（2）

使用rows between and语句限制范围，例如：

此时，order by进行排序的行，就是基础行了，而非之前的“rank()行”。

2.揭秘开窗函数真正的执行原理

问题背景

我们依然是使用上面的表格为例：

现在有一个需求，先按照user_id+time+id进行分组，然后按照user_id为窗口，按照时间进行排序，分别计算row_number()和dense_rank()。

最后，对所有id不为null的行计算一个整体的row_number()，并且仍然按照time进行排序，预期结果是这样的：

大家看下面一段code：

上述code的结果如下：

这好像和我们的预期结果不一样！其实，这里就涉及到了开窗函数的实现原理。

后面，先对开窗函数的实现原理进行解释，然后再来说本次需求该如何实现。

开窗函数的实现原理与案例解析

开窗函数的结构就不多说了，由窗口函数部分 + 窗口定义部分组成。

窗口函数部分可以是：聚合函数、排序函数、分析窗口函数等等。

而窗口定义部分就是over后面的部分，里面需要我们可以选择指定对应的：partition by、order by、rows | range between

而对于窗口函数的实现原理，主要借助PTF实现(Partitioned Table Function)，PTF的输入可以是：表、子查询、或者另一个PFT的输出；而PTF的输出也是一张表。

以下面SQL为例，看一下对应的Spark执行计划，进而得到数据的流转方向：

对应的执行计划（Hive的执行计划太长了...我们看一下Spark的执行计划，套路是一样的哈）：

解析：从上面执行计划可以看出，共出现了两个PTF函数

第一个是：row_number() windowspecdefinition(time#43 ASC NULLS FIRST,...

第二个是：row_number() windowspecdefinition(user_id#42, time#43 ASC NULLS FIRST, specifiedwindowframe(RowFrame, unboundedpreceding())) AS rk1#39, dense_rank(time#43) windowspecdefinition(user_id#42, time#43 ASC NULLS FIRST...

显然，第一个PTF的输入是group by之后的结果，可以看成是下面code的结果作为第一个PTF的输入：

with event as
(
    select 'a' as user_id, '2023-03-03 01:00:00.0' as time, null as id
    union all
    select 'b' as user_id, '2023-03-03 01:01:00.0' as time, 'C1' as id
    union all
    select 'a' as user_id, '2023-03-03 01:01:00.0' as time, 'C1' as id
    union all
    select 'b' as user_id, '2023-03-03 01:01:52.0' as time, null as id
    union all
    select 'a' as user_id, '2023-03-03 01:02:00.0' as time, null as id
)
select  user_id
       ,`time`
       ,id
from event
group by user_id, `time`, id;

而第一个PTF的结果，将直接作为第二个PTF的输入，可以看成是下面code的结果作为第二个PTF的输入：

SELECT  *
       ,row_number() over (order by `time`) AS TEMP_TABLE
FROM PTF_1;

最后，第二个PTF对最后的row_number()和dense_rank()两个开窗函数进行计算，得到结果，作为第二个PTF函数的输出，然后进行后续计算。

下面一起梳理一下上述过程：

计算group by user_id, time, id
计算row_number() over (order by time)，即if()条件语句中的开窗函数【第一个PTF函数，输入为group by user_id, time, id的结果，输出为一张表】
计算row_number() over (partition by user_id order by time) + dense_rank() over (partition by user_id order by time)【第二个PTF函数，输入为第一个PTF函数输出的表，输出为一张表】
计算if()，并输出结果