SQL窗口函数与聚合表达式

SQL窗口函数与聚合表达式

---

以下梳理SQL中常用的两种分析函数的使用，窗口函数和聚合表达式，这里把用于分组计算、排序、提取并需要在函数后直接使用over开窗的分析函数归为窗口函数，而使用聚合函数的分组、排序等语句称为聚合表达式（有些聚合表达式后面也可以用over来划定范围，另外，一些用于统计等功能的聚合函数如corr、stddev等由于使用方式与聚合函数基本相同也没有在这篇中进行整理），以上是根据学习习惯进行的分类，实际使用中这些函数常被称为“分析函数”。对不同的数据库这些函数支持的情况不同，这里主要参考Hive和PostgreSQL中的相关函数。

1. 窗口函数

窗口函数（Window Function）是主要用于分组聚合计算或排序，“窗口”的含义是“范围”，即先按照某列进行圈定，在根据函数的功能进行计算排序等。

窗口函数的基本形式

# 函数 over 窗口
<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)
                <起止行描述的窗口子句>

over后面的部分用于“开窗”——指定范围和顺序，有两种方式 over(distribute by…sort by…) 和 over(partition by…order by…)，它们是固定搭配。
order by 或 sort by 后面是窗口子句，一般以 rows between 开始，用于描述选取行的范围，只能在order by后面而不能单独出现，主要有：

• PRECEDING：往前
• FOLLOWING：往后
• CURRENT ROW：当前行
• UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING：表示到后面的终点

例如 rows between 3 PRECEDING and 1 FOLLOWING就表示向前取3行及向后取1行，rows between CURRENT ROW and UNBOUNDED FOLLOWING就表示从当前行开始取到之后所有行。

1.1 基本聚合函数的窗口计算

窗口函数与基本的聚合计算（聚合函数 + group by）的最大不同是窗口函数不改变原表中记录的数量（行数），以count()函数为例：

-- 如果用聚合函数和 group by，聚合字段每一类会形成一行记录，返回一条结果
select company, count(staff_id) from com_info where c_time = '2020-02-29' group by company;

-- 如果使用窗口函数，则返回记录的数量与原表的条数是一样的
select company, count(staff_id)  over （partition by company order by district）
from com_info where c_time = '2020-02-29';

1.2 排序功能的窗口函数

rank()、dense_rank()和 row_number()是用于排序的窗口函数，这些函数的over中不能写窗口子句。

• row_number()从1开始，按照顺序，生成分组内记录的序列号，row_number()值不会重复，当排序值相同时，按照表中记录的顺序进行排序；
• rank() 生成数据项在分组中的排名，排名相等会占用下一名次的位置（排名如：1,2,2,4）；
• dence_rank() 生成数据项在分组中的排名，排名相等不占用下一名次的位置（排名如：1,2,2,3）

select staff_id, rank() over(partition by department order by staff_his_perf) as perf_rank
from stf_pr_info where c_time = '2020-02-01_2020-02-29'

一个示例，问题是：用户登录日志表为user_id, log_id, session_id, plat 用sql查询近30天每天平均登录用户数量，以及用sql查询出近30天连续访问7天以上的用户数量

select user_id,max(count_date_on)
from(
(select user_id, count(date_on) count_date_on
from 
(select user_id,log_id,row_number() over(partition by user_id order by log_id desc) rnk,log_id-(