窗口函数（SQL和pandas）

时间：2024-10-03 07:11:23

文章目录

  • 一、聚合函数和窗口函数的区别
  • 二、SQL
    • 1 、sql窗口函数
      • 1.1 函数名
      • 1.2 partition by
      • 1.3 order by
      • 1.3 rows/range
  • 三、pandas
    • 1、分组聚合开窗
    • 2、分组排名开窗

一、聚合函数和窗口函数的区别

聚合函数是将多条数据聚合成一行数据，而窗口函数是为每一行数据返回一个结果。

二、SQL

1 、sql窗口函数

函数名(参数) over(
			[partition by 字段 ]
			[order by 语句 ]
			[rows/range 语句]
			)
  • 1
  • 2
  • 3
  • 4
  • 5

1.1 函数名

sum,avg,max,min等聚合函数 或 lead,lag行位移函数 或 row_number、rank、dense_rank等排名函数 或 percent_rank()、cume_dist()分布函数 或 first_value()、last_value()、nth_value()取数函数 或 cume_dist 累积分布函数

（1）聚合函数

sum(字段名) ：统计总和，并返回行数相同的数据。如果over中搭配了order by 会达到累加的效果

（2）位移函数

lag(字段名,offset,defval) ：数据整体向下偏移N位
lead(字段名,offset,defval)：数据整体向上偏移N位
offset：偏移量；defval：默认值

在这里插入图片描述

一般平移函数可以用来解决用户连续登录天数：

（3）排名函数

函数 特点 结果
row_number() 每一行记录生成一个序号，依次排序且不会重复 1234567/1234567
rank() 字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个 11335668 / 123356779
dense_rank() 与rank()类似，生成序号时是连续的 122334445 / 123445

注意：排名函数不能跟窗口框架一起使用

（4）极值函数
取数函数必须搭配order by 使用

first_value(指定字段)： 根据order by的字段排序取第一条数据中的指定字段，允许搭配窗口框架
last_value(指定字段)： 根据order by的字段排序取最后一条数据中的指定字段，允许搭配窗口框架
nth_value(指定字段,number)： 根据order by的字段排序取第N行数据中的指定字段，允许搭配窗口框架

（5）累积分布函数

cume_dict()： 根据分区中的顺序计算每行的累积分布值，row_number() / total_rows

1.2 partition by

对指定字段进行分组

1.3 order by

排序，若搭配partition by时会先分组再组内排序

1.3 rows/range

必须和order by 子句同时使用，操作窗口内数据范围

rows/range的关键字

  1. preceding 表示在…之前
    n preceding：表示当前记录的前n条记录
    unbounded preceding：无分组时表示所有记录的第1条记录；有分组时表示分组后，组内的第1条记录

  2. following 表示在…之后
    n following：表示当前记录的后n条记录
    unbounded following：无分组时表示所有记录的最后一条记录；有分组时表示分组后，组内的最后一条记录

  3. 组合使用
    rows between unbounded preceding and unbounded following：指所有记录
    rows between unbounded preceding and current row：[第一条记录，当前记录]
    rows between current row and unbounded following：[当前记录，最后一条记录]
    rows between N preceding and current row：[当前记录的前N行记录，当前行记录]
    rows between current row and N following：[当前记录，当前行后N行记录]
    rows between 2 preceding and 4 following：[当前记录的前2条记录 ，当前行记录的后4条记录]

在这里插入图片描述

三、pandas

1、分组聚合开窗

语法： ('分组字段')['处理的列名'].transform('聚合函数'[,axis=0])
功能描述： 传入指定字段返回行数不变经聚合函数处理后的结果集

2、分组排名开窗

语法： ('分组字段')['处理的列名'].rank(axis=0, method= 'average', numeric_only= None, na_option= 'keep', ascending= True, pct= False)
功能描述：
参数说明：
method：排名的数据统计依据{‘average’, ‘min’, ‘max’, ‘first’, ‘dense’}
numeric_only：{‘keep’, ‘top’, ‘bottom’}
na_option：
ascending：
pct：

脑子不在线！下次再补

相关文章