实验前测试数据
- mysql 开窗函数需要 mysql 8.0以后才能用
- 分组是组内数据统计合并成一行
- 开窗函数是在分区内,统计当前行和前后数据行的关系,不会合并成一行
- 开窗函数格式
开窗取值函数 over(partition by 分区字段 order by 排序字段 函数取值范围 ) - row_number() 区内生成唯一的编号
- select *,row_number() over( partition by type order by sale ) as temp from test;
- rank() 区内生成编号,分区字段值一样的生成一样的编号,并且编号不是连续的
- select *,rank() over( partition by type order by sale ) as temp from test;
- dense_rank() 区内生成不间断的编号,分区字段并且值一样的生成相同的编号
- select *, dense_rank() over( partition by type order by sale ) as temp from test;
- lead(fieldName,n,"默认值") 取后面的第几个字段。不带n默认是下一个,第一个字段可以是一个固定值
select *, lead(id) over( partition by type order by sale ) as temp from test;
select *, lead(id,2) over( partition by type order by sale ) as temp from test;
select *, lead(id,2,"默认值") over( partition by type order by sale ) as temp from test;
- lag(fieldName,n,"默认值") 取前面的第几个字段。不带n默认是上一个,第一个字段可以是一个固定值
- select *, lag(id) over( partition by type order by sale ) as temp from test;
select *, lag(id,2) over( partition by type order by sale ) as temp from test;
select *, lag(id,2,"默认值") over( partition by type order by sale ) as temp from test; - first_value(fieldName),取分区内第一个值,这里需要注意这里的取值范围是 第一个到当前行(并且如果当前当前分区有多个值,会取到这个值的最后一行)
- select *, first_value(id) over( partition by type order by sale ) as temp from test;
- last_value(fieldName),取分区内最后一个值,这里需要注意这里的取值范围是 第一个到当前行(并且如果当前当前分区有多个值,会取到这个值的最后一行)
select *, last_value(id) over( partition by type order by sale ) as temp from test;
- nth_value(id,2,"默认值"),取当前分区的第几行的值,取值范围默认是第一行到当前行(并且如果当前当前分区有多个值,会取到这个值的最后一行)
- select *, nth_value(id,2) over( partition by type order by sale ) as temp from test;
- ntile(n) 把分区内数据分成多少分,分不均的时候,会依次分给前面的
- select *, ntile(3) over( partition by type order by sale ) as temp from test;
- max(fieldName) 分区内取最大值,范围默认是第一个到当前行,同值那行取到最后一个
- select *, max(id) over( partition by type order by sale ) as temp from test;
- min(fieldName),最大值 ,范围默认是第一个到当前行,同值那行取到最后一个
- select *, min(id) over( partition by type order by sale ) as temp from test;
- avg,分区内取平均值,范围默认是第一个到当前行,同值那行取到最后一个
- select *, avg(id) over( partition by type order by sale ) as temp from test;
- sum,分区内求和,范围默认是第一个到当前行,同值那行取到最后一个
- select *, sum(id) over( partition by type order by sale ) as temp from test;
- count,分区内求数量,范围默认是第一个到当前行,同值那行取到最后一个
- select *, count(id) over( partition by type order by sale ) as temp from test;
- 开窗从句默认是第一个到当前行,同值那行取到最后一个, 等价于 range between unbounded preceding and current row
- select *, count(id) over( partition by type order by sale range between unbounded preceding and current row ) as temp from test;
- row 和 range的不同在于处理相同值排序的时候 ,价格相同range取得最后一个id,rows取的当前那个id
- select *, count(id) over( partition by type order by sale rows between unbounded preceding and current row ) as temp from test;
- 开窗从句,控制分区内,函数取值范围的。 格式 range|rows between 开始位置 and 结束位置
- 取第一个 unbounded preceding
- 取当前位置 current ROW
- 取最后一个 unbounded following
- 取当前位置前面n preceding
- 取当前位置后面第n following
- 开窗从句,取前一个到当前
select *, sum(id) over( partition by type order by sale rows between 1 preceding and current row ) as temp from test; - 开窗从句,取前一个到最后
select *, sum(id) over( partition by type order by sale rows between 1 preceding and unbounded following ) as temp from test; - 开窗从句,取当前到当前
select *, sum(id) over( partition by type order by sale rows between current ROW and current ROW ) as temp from test; - 开窗从句,取全部
select *, sum(id) over( partition by type order by sale rows between unbounded preceding and unbounded following ) as temp from test;