Mysql聚合函数与窗口函数汇总

常用的聚合函数

AVG、COUNT、MAX、MIN、SUM 以及 GROUP_CONCAT  常用group by 一起使用。
聚合函数的作用就是对分组的数据进行汇总计算，并且返回各组的分析结果。

主要说一下group_concat 这个作用是分组拼接

语法：group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分隔符’] )
案例：
原始数据

# 原始数据
select data_date,platform From `promotion_fee_jingdong` where data_date <= '2023-11-17'

使用group_concat对platform进行拼接：

SELECT data_date,group_concat(platform) as platform_concat FROM `promotion_fee_jingdong`  where data_date <= '2023-11-17' group by data_date

添加distinct进行去重拼接、按照platform进行倒序排序、按照$号进行分割：

SELECT data_date,group_concat(distinct platform order by platform asc separator '$') as platform_concat FROM `promotion_fee_jingdong`  where data_date <= '2023-11-17' group by data_date

聚合函数与窗口函数的区别

聚合函数与窗口函数的区别：
窗口函数和聚合函数类似之处在于它也是对一组数据进行分析；但是，窗口函数不是将一组数据汇总为单个结果；而是针对查询中的每一行数据，基于和它相关的一组数据计算出一个结果。下图演示了聚合函数和窗口函数的区别：

同时出现分组函数与窗口函数时，窗口函数就是根据分组后的结果进行开窗

select 
    uid,
    year(start_time) as start_yeaer,
    count(uid) over(partition by year(start_time)) as start_count
from 
    exam_record
group by
    year(start_time),uid

例如上述列子，及时先通过年份、uid进行分组，然后在分组结果的基础上进行开窗计数的

常用窗口函数汇总

窗口函数是mysql8.0版本之后才支持的

窗口函数定义

window_function ( expr ) OVER (
    PARTITION BY ...
    ORDER BY ...
    frame_clause
)

分区（PARTITION BY）

用于为数据分区

排序（ORDER BY）

用于对分区内的数据进行排序，用于计算

窗口大小（frame_clause）可以在下main找到

frame_clause是在分区中设定一个计算窗口，设定了窗口大小，分析函数就不是拿整个分区作为范围去计算，而是该计算窗口，该计算窗口是与当前行相关联，计算窗口随着当前行的变化而滑动变化

常用场景：
1.计算从分区的第一行开始，到当前行的累计值
2.定义当前行前N行的数据和当前后N行的数据作为计算窗口，可以计算固定宽度滑动平均值

窗口函数分类

1.聚合窗口函数
聚合函数也支持使用窗口函数的方式
sum()、count()、avg()、min()、max()、group_concat()

2.排名窗口函数
用于对函数进行分组排名
row_number()：为分区内的每行数据根据排序规则分配一个序列号，从1开始
rank()：计算分区内每行数据根据排序规则的排名值，出现重复排名值，后续排名将会跳跃
dense_rank()：计算分区内每行数据根据排序规则的排名值，出现重复排名值，后续排名不会跳跃
percent_rank()：计算每行数据的排名值以百分比的形式，出现重复排名值，后续排名会跳跃
CUME_DIST，计算每行数据在其分区内的累积分布，也就是该行数据及其之前的数据的比率；取值范围大于 0 并且小于等于 1。
NTILE，将分区内的数据分为 N 等份，为每行数据计算其所在的位置。

3.取值窗口函数
FIRST_VALUE，返回窗口内第一行的数据。
LAST_VALUE，返回窗口内最后一行的数据。
NTH_VALUE，返回窗口内第 N 行的数据。
LAG，返回分区中当前行之前的第 N 行的数据。
LEAD，返回分区中当前行之后第 N 行的数据

4.窗口函数取数框架限定
相关边界规则说明：
CURRENT ROW：表示当前行
UNBOUNDED PRECEDING：表示前面的起点
UNBOUNDED FOLLOWING：表示后面的终点
n PRECEDING：往前n行数据，加上自身行
n FOLLOWING：往后n行数据，加上自身行

使用方式：
方式1：rows n perceding：表示从当前行到前n行（共n+1行）
方式2：range/rows between 边界规则1 and 边界规则2 其中range表示按照值的范围进行定义框架，rows表示按照行的返回进行定义框架

# 使用方式1：rows  n perceding：表示从当前行到前n行（共n+1行）
-- 计算每个tag的当前数据到前三行数据的if_like值的汇总，
-- partition by 来给每个tag设置单独分区，
-- order by 对每个分区按照date_format(a.start_time,'%Y-%m-%d')  来排序，
-- rows 3 preceding  用来控制每个分区中每行数据计算时候的窗口大小，这个表示当前行+前三行数据
-- 窗口大小设置：从分区的起点行到当前行，将rows  3 preceding 改成 rows unbounded preceding; 从当前行到分区的终点行，将rows 3 preceding 改成 rows between current row and unbounded following
select
    b.tag,
    date_format(a.start_time,'%Y-%m-%d') as dt,
    sum(if_like) over(partition by b.tag order by date_format(a.start_time,'%Y-%m-%d') rows 3 PRECEDING) as like_amount
from
    tb_user_video_log a 
left join 
    tb_video_info b using(video_id)