MySQL 窗口函数

已于 2023-04-11 17:48:57 修改
阅读量861 收藏 9
点赞数 3
分类专栏： MySQL 文章标签： mysql 数据库
于 2023-04-10 12:38:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/chengyj0505/article/details/130057113
版权

MySQL的窗口函数是一种特殊类型的聚合函数，使用窗口函数可以使查询更高效，因为它们可以避免在多个聚合阶段中重复扫描相同的行。还可以使用窗口函数来计算一些有趣的结果，例如排名、百分比和移动平均值等。

目录

一、认识窗口函数

MySQL 8.0 版本中可以使用窗口函数，它很像分组函数却又区别于分组函数，在使用group by后每组只有一个结果，而窗口函数不论是否分组都是一行一个结果。窗口函数不对数据进行分组，而是按照窗口划分，计算与当前行相关的聚合值，并将计算结果返回给每一行。

注意：

1、窗口的划分并不是通过分组来的，而是通过 over 子句中的 rows between 划分，只不过不加 rows between 的时候，默认的窗口划分形式和分组规则看起来一样(这个一定要理解)。

2、窗口函数通常需要占用比较多的系统资源，因此在使用时需要评估其对性能的影响或采取优化措施。

1、窗口函数的适用场景
  1. 按照某个或某些指标对数据进行排序和排名

例如，使用 rank() 或 dense_rank() 函数计算产品在一个公司的销售额排名，或者使用 row_number() 函数给每个产品进行唯一编号后分类。

  1. 对某个或某些指标进行聚合计算

例如，使用 sum()、avg()、max() 或 min() 函数对分组内的数据进行计算，并将结果返回给每一行。

  1. 滑动窗口聚合

例如，获取与当前行相关的前几行或后几行的值，并将它们作为当前行的一部分输出，或者对数据进行累计求和、求滑动平均等。

例如，在一个在线游戏中，我们可能需要计算所有玩家最近一小时内的获得积分总数。

  1. 分组数据分布情况

窗口函数通过计算每个分组的百分位数，能够反映每个分组的数据分布情况。例如，可以使用 ntile() 函数将每个分组分成n个桶，并返回每个桶的编号。也可以使用 percentile_cont() 函数计算分组内指定列的百分位数。

2、窗口函数与分组函数、聚合函数的区别

实例详见1.1、1.2、1.3。

函数与窗口函数的联系与窗口函数的区别
分组函数都可以进行聚合计算处理数据的方式不同。分组函数会根据指定的列对数据进行分组，从而将数据划分为若干个子集进行聚合计算；而窗口函数则不对数据进行分组，而是按照窗口划分，计算与当前行相关的聚合值，并将计算结果返回给每一行。
聚合函数都是用来对数据进行聚合计算的函数窗口函数可以在不影响原始查询结果的情况下，返回每一行相关的聚合计算结果，而聚合函数只能返回单一的聚合结果。另外，窗口函数可以使用 over 子句来定义分组、排序和滑动窗口等操作，处理数据也更加地灵活。

扩展: 分组函数和聚合函数的联系和区别

他们都是用来对数据进行聚合计算的函数，但是两者所处理的粒度不同。

  • 分组函数（如GROUP BY）是将数据按照指定的列进行分组，并对每个分组内的数据进行聚合计算，返回每个分组的结果；
  • 聚合函数（如SUM、COUNT、AVG、MAX、MIN等）是对整个数据集进行聚合计算，返回一个结果。它可以不分组对整个数据集进行计算。

二、具体方法

MySQL支持多种窗口函数，根据函数功能和使用场景的不同可以将它们分为以下几类：

1、排序窗口函数
具体函数说明
row_number()顺序排序，返回无重复值的排名。（本质：为每行生成一个唯一的数字标识符）
rank()并列排序，返回有间隔重复的排名，例如1，1，3
dense_rank()并列排序，返回无间隔重复的排名，例如1，1，2
2、聚合窗口函数
具体函数说明
sum(expr) over对[窗口内]指定列的expr值进行求和
count(expr) over对[窗口内]指定列的expr值进行计数
avg(expr) over对[窗口内]指定列的expr值进行平均值计算
max(expr) over找到[窗口内]指定列的expr最大值
min(expr) over找到[窗口内]指定列的expr最大值

以上为常用的聚合函数，其余还有group_concat：将指定列中的值连接为一个字符串;std、stddev或stddev_pop：计算指定列或表中数值列的标准差；var、var_pop或var_samp：计算指定列或表中数值列的方差等。

3、滑动窗口函数
分类具体函数或语法说明
固定取值函数
first_value(expr)返回[窗口内]指定列中第一行的expr值
last_value(expr)返回[窗口内]指定列中当前行的expr值
nth_value(expr,n)返回[窗口内]指定列中第n行的expr值
滑动取值函数
lag(expr,n)返回[窗口内]指定列中当前行向前第n行的expr值，不写n,默认n为1
lead(expr,n)返回[窗口内]指定列中当前行向后第n行的expr值，不写n,默认n为1
滑动窗口聚合 rows between frame_start and frame_end返回[窗口内]当前行的前几行或后几行的窗口计算值。例和聚合函数一起使用得到移动平均、累计求和等。
4、分布窗口函数
分类具体函数或语法说明
百分比函数
percent_rank()返回[窗口内]百分比排名值
cume_dist()返回[窗口内]累计分布值
默认分组ntile(n)将行分成n个桶，并返回每个桶的编号

三、语法

窗口函数的语法基本上与普通的聚合函数相同，但需要使用 over 子句来指定窗口函数的作用范围，所以使用窗口函数，要在函数后包含一个 over 子句。

1、窗口函数的两种书写形式
1、窗口直接定义在 over 子句中
函数名([expr]) over (
    [partition by <分组的列>, ... ]
    [order by <排序的列> [asc|desc], ... ]
    [rows between <窗口起始位置 and 窗口结束位置>]
)
2、引用定义在其他地方的命名窗口函数
函数名([expr]) over <窗口函数名称>
...
window <窗口函数名称> as (
    [partition by <分组的列>, ... ]
    [order by <排序的列> [asc|desc], ... ]
    [rows between <窗口起始位置 and 窗口结束位置>]
)

不论是哪种书写形式，over 子句后面圆括号里面的的语法是一样的，具体如下：

function_name：窗口函数的名称，例如row_number、sum、avg等。

expr：可选项，指定窗口函数作用的列或表达式。

partition by：可选的子句，指定分组的列或表达式。

  • 如果省略了partition by，则所有查询行为一组，也就是没有分组的概念。
  • 标准SQL要求 partition by 后面只跟列名。MySQL扩展允许使用表达式，而不仅仅是列名。例如，一个表有一个字段名为 ts 的 timestamp 列，标准SQL允许 partition by ts，但不允许 partition by day(ts)，而MySQL两者都允许。具体见例3.2。

order by：可选的子句，指定排序的列及顺序。

  • 表达式后面可选地跟着 asc 或 desc 来指示排序方向。如果没有指定方向，默认为 asc。升序时先对空值排序，降序时最后对空值排序；如果排序值为空，则不显示，见例2.1和2.2。
  • 如果省略order by，则分区行是无序的，没有暗示处理顺序，所有分区行都是对等的。
  • 若有分组，是组内排序。若没有分组，是将结果集作为一个整体进行排序。

rows between：可选的子句（窗口从句），指定滑动窗口的起始和结束位置。

  • 当 order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded preceding and current row，其中，unbounded preceding 表示窗口从第一行开始，而 current row 表示窗口截止到当前行； 但是和partition by一起使用并且数据有空值的时候上述窗口并不成立，在使用时最好还是加上窗口从句。 具体见实例1.3和动画演示。

  • 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following。具体见动画演示1

  • 不同写法及含义，举例：

    # 1、取前面2行和当前行 
rows between 2 preceding and current row
# 2、取当前行前面的所有行和当前行 
rows between unbounded preceding and current row 
# 3、取当前行后面的所有行
rows between current row and unbounded following 
# 4、取当前行前面的2行和当前行 
rows between 2 preceding and current row
# 5、取当前行前面的2行和后面2行，总共5行（包括当前行）
rows between 2 preceding and 2 following
2、注意
  • 解释

当前行：发生函数求值的行称为当前行。

当前行的窗口：与发生函数求值的当前行相关的查询行组成了当前行的窗口。再次强调窗口函数的本质是按照窗口去计算，而不是对数据分组计算。

  • 使用位置

窗口函数只允许出现在 select 列表和 order by 子句中。

  • 执行顺序

查询结果行由 where、group by 和 having 处理之后的from子句确定，窗口执行发生在 order by 、limit 和 select distinct 之前。

3、动画演示

（1）分组划分

over 语句中 使用 partition by exam_id分组, 不包括 order by 语句，对exam_id计数。

select *,
    count(exam_id) over(partition by exam_id) as count_exam
from exam_record
order by exam_id;

在这里插入图片描述

（2）分组排序累计求和

按照partition by exam_id分组 order by 语句，对score累计求和。

select *,
    sum(score) over(partition by exam_id order by score desc rows between unbounded preceding and current row) as sum_score
from exam_record;

在这里插入图片描述

（3）滑动平均

对exam_id分组后按照score降序，再取分组内score当前行及后一行和前一行求平均。

select *,
    avg(score) over(partition by exam_id order by score desc rows between 1 preceding and 1 following) as sum_score
from exam_record;

在这里插入图片描述

四、实例

已知试卷作答记录表exam_record（uid:用户ID, exam_id:试卷ID, start_time:开始作答时间, submit_time:交卷时间，为空的话则代表未完成, score:得分）:

iduidexam_idstart_timesubmit_timescore
1100690032021-09-06 10:01:012021-09-06 10:21:0284
2100690012021-08-02 12:11:012021-08-02 12:31:0189
3100690022021-06-06 10:01:012021-06-06 10:21:0181
4100690022021-05-06 10:01:012021-05-06 10:21:0181
5100690012021-05-01 12:01:01(NULL)(NULL)
6100190012021-09-05 10:31:012021-09-05 10:51:0181
7100190032021-08-01 09:01:012021-08-01 09:51:1178
8100190022021-07-01 09:01:012021-07-01 09:31:0085
9100190022021-07-01 12:01:012021-07-01 12:31:0185
10100190022021-07-01 12:01:01(NULL)(NULL)

先通过三个实例看一下窗口函数和分组函数、聚合函数的区别和联系。

1、窗口、分组、聚合函数的区别和联系
  • 聚合函数

例1.1 使用 exam_record 表，求所有试卷类型的作答次数。

select 
	count(exam_id) as count_exam_id
from exam_record;

结果：

count_exam_id
10
  • 分组函数

例1.2 求每类试卷的作答次数；并按试卷类型升序

select
	exam_id,
	count(exam_id) as count_exam_id
from exam_record
group by exam_id
order by exam_id;

结果：

exam_idcount_exam_id
90013
90025
90032
  • 窗口函数

例1.3 分别求所有试卷、每类试卷的作答次数；并按试卷类型升序

select
    exam_id,
    count(exam_id) over() as total_exam_id,
    count(exam_id) over(partition by exam_id) as count_exam_id,
    sum(score) over(partition by exam_id order by score desc) as sum_score
from exam_record
order by exam_id;

结果：

exam_idtotal_exam_idcount_exam_idsum_score
900110389
9001103170
9001103170
9002105170
9002105170
9002105332
9002105332
9002105332
900310284
9003102162
2、排序窗口函数

窗口函数可以很方便地对数据进行排序和排名，在处理排名、排行等相关数据时非常有用。

  • row_number() :1、2、3;
  • rank() :1、1、3;
  • dense_rank() :1、1、2。

例2.1 使用 row_number() 、rank() 、dense_rank() 对 exam_record 表中的答题记录按照得分排序；

# 形式一：
select
    score,
    row_number() over(order by score desc) as 'row_number',
    rank() over(order by score desc) as 'rank',
    dense_rank() over(order by score desc) as 'dense_rank'
from exam_record;
# 形式二：
select
    score,
    row_number() over w as 'row_number',
    rank()       over w as 'rank',
    dense_rank() over w as 'dense_rank'
from exam_record
window w as (order by score desc);

结果：

scorerow_numberrankdense_rank
89111
85222
85322
84443
81554
81654
81754
78885

例2.2 使用 row_number() 对 exam_record 表中的每类试卷类型中的记录排序、按照得分排序；

#省略order by 和有order by 的区别
select
    exam_id,
    score,
    row_number() over(partition by exam_id) as row_num1,
    row_number() over(partition by exam_id order by score desc) as row_num2
from exam_record;

结果：

idexam_idscorerow_num1row_num2
290018911
690018132
59001None23
890028531
990028542
390028113
490028124
109002None55
190038411
790037822

省略order by 和有order by 的区别:

省略 order by 默认按照分组内的id升序；

3、聚合窗口函数

窗口函数可以让我们更加灵活地对数据进行聚合计算，避免使用group by 分组后只有一个数据的问题。

  • sum(expr) over : 求和
  • count(expr) over : 计数
  • avg(expr) over : 求平均
  • max(expr) over : 求最大值
  • min(expr) over : 求最小值

例3.1 查询 exam_record 表中每个试卷类型的记录对应的得分总和、个数、平均值、最大值和最小值，最后按照试卷类型升序；

select
    exam_id,
    score,
    sum(score) over w as 'sum',
    count(score) over w as 'count',
    avg(score) over w as 'avg',
    max(score) over w as 'max',
    min(score) over w as 'min'
from exam_record
window w as (partition by exam_id)
order by exam_id;

结果：

exam_idscoresumcountavgmaxmin
900189170285.00008981
9001None170285.00008981
900181170285.00008981
900281332483.00008581
900281332483.00008581
900285332483.00008581
900285332483.00008581
9002None332483.00008581
900384162281.00008478
900378162281.00008478

partition by 多列分组

除了在 over 子句中指定单个列以便进行分组外，还可以使用 partition by关键字同时指定多个列，从而将输入行分成更小的分组。

例3.2 查询 exam_record 表，按试卷类型和开始作答试卷的日期这两个列进行分组，并计算每个分组内得分最高的记录：

select
    exam_id,
    date(start_time) as start_date
    score,
    max(score) over ranking as 'max'
from exam_record
window ranking as (partition by exam_id, date(start_time))
order by exam_id,start_date;

结果：

exam_idstart_datescoremax
90012021-05-01NoneNone
90012021-08-028989
90012021-09-058181
90022021-05-068181
90022021-06-068181
90022021-07-018585
90022021-07-018585
90022021-07-01None85
90032021-08-017878
90032021-09-068484
4、滑动窗口函数

窗口函数可以很方便地实现滑动窗口聚合，即对当前行及其前几行或后几行所构成的一组连续的行进行聚合计算。

（1）固定取值函数
  • first_value(expr) : [窗口内]第一行的值；
  • last_value(expr) : [窗口内]当前行的值；
  • nth_value(expr,n) : [窗口内]第n行的值。

例4.1 求 exam_record 中每个试卷类型的每个用户与第一名、第二名得分的差值(first_dif、nth_dif)

select 
    uid,
    exam_id, 
    score,
    first_value(score) over (partition by exam_id order by score desc) as first_score,
    nth_value(score,2) over (partition by exam_id order by score desc) as nth_score,
    last_value(score) over (partition by exam_id order by score desc) as last_score,
    last_value(score) over (partition by exam_id order by score desc rows between unbounded preceding and unbounded following) as last_score1,
    score - first_value(score)  over (partition by exam_id order by score desc) as first_dif,
    score - nth_value(score,2) over (partition by exam_id order by score desc) as nth_dif
from exam_record;

结果：

uidexam_idscorefirst_scorenth_scorelast_scorelast_score2first_difnth_dif
100690018989None89None0None
1001900181898181None-80
10069001None8981NoneNoneNoneNone
1001900285858585None00
1001900285858585None00
1006900281858581None-4-4
1006900281858581None-4-4
10019002None8585NoneNoneNoneNone
100690038484None84780None
100190037884787878-60
（2）滑动取值函数

lag 和 lead 函数通常用于计算行之间的差异。比如取今天和昨天的某字段差值

  • lag(expr) : [窗口内]当前行的前第n行的值；
  • lead(expr) : [窗口内]当前行的后第n行的值。

例4.2 求 exam_record 表中每个试卷类型当前用户与上一名用户得分的差值（lag_dif）。

select 
    uid,
    exam_id, 
    score,
    lag(score) over w as lag_score,
    lead(score) over w as lead_score,
    score - lag(score,1) over w as lag_dif
from exam_record;
window w as (partition by exam_id order by score desc);

结果：

uidexam_idscorelag_scorelead_scorelag_dif
1006900189None81None
100190018189None-8
10069001None81NoneNone
1001900285None85None
100190028585810
10069002818581-4
100690028181None0
10019002None81NoneNone
1006900384None78None
100190037884None-6
（3）滑动窗口函数

除了计算滑动平均值、累计数外，还可以计算滑动标准差stddev_pop()，他帮助我们更好地掌握数据的变化范围和稳定性。

  • 使用滑动窗口：rows between feame_start and frame_end 当前行的前几行或后几行。

已知11月份销售数据表sales_tb（sales_date：销售日期，user_id：用户编号，item_id：货号，sales_num：销售数量，sales_price:销售金额）：

sales_dateuser_iditem_idsales_numsales_price
2021-11-011A001190
2021-11-012A0022220
2021-11-012B0011120
2021-11-023C0012500
2021-11-024B0011120
2021-11-035C0011240
2021-11-036C0021270
2021-11-047A0031180
2021-11-048B0021140
2021-11-049B0011125
2021-11-0510B0031120
2021-11-0510B0041150
2021-11-0510A0031180
2021-11-0611B0031120
2021-11-0610B0041150

例4.3 根据销售记录表 sales_tb，计算出11月每天的累计销售额和平均销售单价(为了更好地了解销售趋势)

select
	day(sales_date) as 'day', 
	sales_num as 'num', 
	sales_price as 'price',
	sales_num*sales_price as 'total',
	sum(sales_price*sales_num) over w as cumulative_sales,
	avg(sales_price*sales_num) over w as avg_unit_price
from sales_tb
window w as (order by day(sales_date) rows between unbounded preceding and current row);

结果：

daynumpricetotalcumulative_salesavg_unit_price
1190909090.0000
12220440530265.0000
11120120650216.6667
2250010001650412.5000
211201201770354.0000
312402402010335.0000
312702702280325.7143
411801802460307.5000
411401402600288.8889
411251252725272.5000
511201202845258.6364
511501502995249.5833
511801803175244.2308
611201203295235.3571
611501503445229.6667
5、分布窗口函数
（1）将数据按照默认规则分组
  • ntile(n)：将行分成n个桶，并返回每个桶的编号。

例5.1 将 exam_scores 表中数据按照成绩分成4组。

select 
  ntile(4) over (order by score desc) as pack4,
  ntile(2) over (partition by exam_id order by score desc) as pack2,
  exam_id,
  uid,
  score
from exam_record;

结果：

pack4pack2exam_iduidscore
119001100689
319001100181
4290011006None
119002100185
119002100185
219002100681
229002100681
4290021001None
219003100684
329003100178
（2）窗口的百分位数

窗口函数可以很方便地计算每个窗口的百分位数，从而更好地反映每个窗口的数据分布情况。

返回窗口值的百分比，返回值范围为 0 到 1。

  • cume_dist() : [窗口内]数据分布的累计百分比;
    • 计算公式：窗口内 <=当前rank值对应的最大值的行数 / 窗口内总行数
  • percent_rank() ：[窗口内]rank排名百分比值；
    • 计算公式：（窗口内 <=当前rank值的行数 -1） / （窗口内总行数-1）

例5.2 对 exam_record 表中成绩按照升序计算累计百分比和排名百分比。

select
    score,
    row_number()   over w as 'row_num',
    rank()   over w as 'rank',
    cume_dist()    over w as 'cume_dist',
    percent_rank() over w as 'percent_rank'
from exam_record
window w as (order by score);

结果：

scorerow_numrankcume_distpercent_rank解释cume_dist解释percent_rank
None110.2000.0002/10（1-1）/（10-1）
None210.2000.0002/10（1-1）/（10-1）
78330.3000.2223/10（3-1）/（10-1）
81440.6000.3336/10（4-1）/（10-1）
81540.6000.3336/10（4-1）/（10-1）
81640.6000.3336/10（4-1）/（10-1）
84770.7000.6677/10（7-1）/（10-1）
85880.9000.7789/10（8-1）/（10-1）
85980.9000.7789/10（8-1）/（10-1）
8910101.0001.00010/10（10-1）/（10-1）

五、 附录

创建 exam_record 表的代码

-- ----------------------------
-- Table structure for exam_record
-- ----------------------------
DROP TABLE IF EXISTS `exam_record`;
CREATE TABLE `exam_record`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `uid` int(11) NOT NULL COMMENT '用户ID',
  `exam_id` int(11) NOT NULL COMMENT '试卷ID',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `submit_time` datetime NULL DEFAULT NULL COMMENT '提交时间',
  `score` tinyint(4) NULL DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of exam_record
-- ----------------------------
INSERT INTO `exam_record` VALUES (1, 1006, 9003, '2021-09-06 10:01:01', '2021-09-06 10:21:02', 84);
INSERT INTO `exam_record` VALUES (2, 1006, 9001, '2021-08-02 12:11:01', '2021-08-02 12:31:01', 89);
INSERT INTO `exam_record` VALUES (3, 1006, 9002, '2021-06-06 10:01:01', '2021-06-06 10:21:01', 81);
INSERT INTO `exam_record` VALUES (4, 1006, 9002, '2021-05-06 10:01:01', '2021-05-06 10:21:01', 81);
INSERT INTO `exam_record` VALUES (5, 1006, 9001, '2021-05-01 12:01:01', NULL, NULL);
INSERT INTO `exam_record` VALUES (6, 1001, 9001, '2021-09-05 10:31:01', '2021-09-05 10:51:01', 81);
INSERT INTO `exam_record` VALUES (7, 1001, 9003, '2021-08-01 09:01:01', '2021-08-01 09:51:11', 78);
INSERT INTO `exam_record` VALUES (8, 1001, 9002, '2021-07-01 09:01:01', '2021-07-01 09:31:00', 85);
INSERT INTO `exam_record` VALUES (9, 1001, 9002, '2021-07-01 12:01:01', '2021-07-01 12:31:01', 85);
INSERT INTO `exam_record` VALUES (10, 1001, 9002, '2021-07-01 12:01:01', NULL, NULL);

创建 sales_tb 表的代码

-- ----------------------------
-- Table structure for sales_tb
-- ----------------------------
DROP TABLE IF EXISTS `sales_tb`;
CREATE TABLE `sales_tb`  (
  `sales_date` date NOT NULL,
  `user_id` int(10) NOT NULL,
  `item_id` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sales_num` int(10) NOT NULL,
  `sales_price` int(10) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sales_tb
-- ----------------------------
INSERT INTO `sales_tb` VALUES ('2021-11-01', 1, 'A001', 1, 90);
INSERT INTO `sales_tb` VALUES ('2021-11-01', 2, 'A002', 2, 220);
INSERT INTO `sales_tb` VALUES ('2021-11-01', 2, 'B001', 1, 120);
INSERT INTO `sales_tb` VALUES ('2021-11-02', 3, 'C001', 2, 500);
INSERT INTO `sales_tb` VALUES ('2021-11-02', 4, 'B001', 1, 120);
INSERT INTO `sales_tb` VALUES ('2021-11-03', 5, 'C001', 1, 240);
INSERT INTO `sales_tb` VALUES ('2021-11-03', 6, 'C002', 1, 270);
INSERT INTO `sales_tb` VALUES ('2021-11-04', 7, 'A003', 1, 180);
INSERT INTO `sales_tb` VALUES ('2021-11-04', 8, 'B002', 1, 140);
INSERT INTO `sales_tb` VALUES ('2021-11-04', 9, 'B001', 1, 125);
INSERT INTO `sales_tb` VALUES ('2021-11-05', 10, 'B003', 1, 120);
INSERT INTO `sales_tb` VALUES ('2021-11-05', 10, 'B004', 1, 150);
INSERT INTO `sales_tb` VALUES ('2021-11-05', 10, 'A003', 1, 180);
INSERT INTO `sales_tb` VALUES ('2021-11-06', 11, 'B003', 1, 120);
INSERT INTO `sales_tb` VALUES ('2021-11-06', 10, 'B004', 1, 150);
酸菜鱼土豆大侠
关注
  • 3
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
07_MySQL中的窗口函数 + 案例（一看就会）
蕾欧娜与26的博客
06-09 622
文章目录走进窗口函数案例案例：实现排名案例：求员工总数案例：求人数占比了解窗口函数基本语法排名函数排名函数的案例排名函数的区别聚合函数 这篇博客介绍窗口数，窗口函数也是我们在面试数据分析岗位的时候面试官常考察的一个内容。我在学习 SQL 的时候是没有听过窗口函数的，后来才得知窗口函数是 MySQL 8.0 的新特性，在此之前的版本中是没有这个功能的。所以不了解没听过也没关系，本篇博客带你搞定窗口函数。 走进窗口函数 窗口函数类似于 SUM( )、COUNT( )那样的集合函数，但它并不会将多行查询结果合并为
32-视频33MySQL窗口函数
09-13
32--[视频]33MySQL窗口函数
Mysql 窗口函数
热门推荐
菜菜的大数据开发の路
03-04 3万+
一, MySQl 8.0 窗口函数 窗口函数适用场景: 对分组统计结果中的每一条记录进行计算的场景下, 使用窗口函数更好; 可以跟Hive的对比着看: 点我, 特么的花了一晚上整理, 没想到跟Hive 的基本一致, 还不因为好久没复习博客了, 淦 注意: mysql 因为没有array数据结构, 无法像Hive一样 行列进行转换; 1.1 窗口函数分类 MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是
mysql的窗口函数
记录一些工作日常和学习
10-13 212
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理 使用场景：组内排名 窗口函数的基本语法： <窗口函数> over (partition by <分组的列> order by <排序的列>) <窗口函数>的位置，可以放以下两种函数： 1） 专用窗口函数，rank, dense_rank, row_number等专用窗口函数。 2） 聚合函数，如sum. avg,
mysql中的窗口函数
cjl的博客
12-11 409
mysql中的窗口函数1. 窗口函数基本语法2. 窗口函数类别3. 滑动窗口函数3.1 Preceding 移动平均3.2 Following3.3 两者结合4. 实战演练4.1 组内排序问题4.2 连续登陆问题 1. 窗口函数基本语法 <窗口函数> OVER ( [ PARTITION BY <列清单> ]ORDER BY <排序用列清单> ) partition by 划分的范围被称为窗口 order by 决定窗口范围内的数据以什么样的方式排序 至于窗口函数与g
mysql--窗口函数
weixin_42972832的博客
08-25 1382
窗口函数
MySQL窗口函数：展开数据的魔法.md
08-15
MySQL窗口函数：展开数据的魔法.md
mysql-窗口函数概述
最新发布
12-21
mysql中窗口函数的概述以及一些数据练习，可以更好的对窗口函数理解。 什么是窗口函数：可以像聚合函数一样对一组数据进行分析并返回结果，二者的不同之处在于，窗口函数不是将一组数据汇总成单个结果，而是为每一行...
Mysql8.0使用窗口函数解决排序问题
09-08
MySQL 8.0 引入了窗口函数，这是一种强大的数据处理工具，能够在满足特定条件的记录集合（窗口）上执行分析操作，而不仅仅是简单的聚合。窗口函数与传统的聚合函数（如 SUM, AVG, COUNT 等）不同，它们在处理数据时...
MySQL8.0窗口函数入门实践及总结
09-08
MySQL8.0窗口函数是数据库查询中的一个重要特性，它使得在处理复杂的数据分析和统计时变得更加便捷。在MySQL8.0之前，如果要进行类似的数据排名或统计，通常需要借助子查询或自连接等复杂操作，而窗口函数的引入极大...
mysql窗口函数
qq_37627820的博客
08-14 1335
专用的窗口函数 CUME_DIST() CUME_DIST() OVER ( PARTITION BY expr, … ORDER BY expr [ASC | DESC], … ) 计算公式是： ROW_NUMBER() / total_rows 示例： 在这里插入代码片 ROW_NUMBER() / total_rows 3. DENSE_RANK() 4. FIRST_VALUE() 和 LAST_VALUE() 5. LAG() 和 LEAD() 6. NTH_VALUE() 7. N
mysql有窗体_mysql窗口函数
weixin_33298352的博客
01-19 319
含义：窗口函数也叫OLAP函数(Online Anallytical Processing,联机分析处理)，可以对数据进行实时分析处理。作用：解决排名问题，e.g.每个班级按成绩排名解决TOPN问题，e.g.每个班级前两名的学生语法：select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名分类：专用窗口函数：rank()，dense_rank...
linux alsa-lib snd_pcm_open 源代码详细分析
i826056899的专栏
11-19 777
内容已迁移至个人博客:https://blog.whatsroot.xyz/2020/08/11/alsa_snd_open-analysis-1/
mysql中还有窗口函数？这是什么东西？
ouyangzhenxin的博客
07-28 207
在mysql8.0的版本中，新增了一个窗口函数，用他可以实现很多新的查询方式。窗口函数类似于sun()、count()那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行中。什么意思呢？就是说窗口函数是不需要groupby的。...
MySQL窗口函数（MySQL Window Functions）
Vincent的博客
05-17 1万+
百分比排序，返回当前行在组内的百分比位置，返回值范围为[0, 1]，可以用 当前行排序/(行数-1) 计算得出，但与rank不同，这里排序是从0开始而不是从1（相当于rank-1），因此第1行的百分比位置是0%，相当于：（rank-1）/(rows-1)。没有参数，返回当前行在组内的位置编号，从1开始，order by子句会影响行的编号顺序，如果没有order by，那么行的编号是不确定的。另外，即使行完全相同，它们的编号也是不同的，这点和后面的 rank() 和 dense_rank() 不同。
mysql模拟窗口函数功能
jc_benben的专栏
12-12 2691
DROP TABLE IF EXISTS T1; CREATE TABLE T1( ID INT, DATAVALUE INT ); INSERT INTO T1 VALUES(1,80); INSERT INTO T1 VALUES(1,90); INSERT INTO T1 VALUES(2,76); INSERT INTO T1 VALUES...
Mysql -窗口函数详解
cjx_cqupt的博客
03-28 1226
数据分析｜MySQL窗口函数详解与案例实战 1. 窗口函数描述 含义：窗口函数也叫OLAP函数（Online Anallytical Processing，联机分析处理），可对数据进行实时分析处理。 作用：解决排名问题，e.g.每个班级按成绩排名；解决TOPN问题，e.g.每个班级前两名的学生 语法： <窗口函数> over ([partition by <列清单>] order by <排序用列清单>) .
MySQL窗口函数
ZJH
11-27 3708
个人认为跟上面的3.1滑窗筛选单位ROWS和RANGE属于一个包含关系，单位可以选择ROWS行、RANGE范围，都可以筛选一定的范围内，但比较死板，不够灵活，3.2就是这个升级版，足够灵活CURRENT ROW ：当前行UNBOUNDED PRECEDING ：当前行上侧所有行UNBOUNDED FOLLOWING ：当前行下侧所有行expr PRECEDING：当前行上侧expr行(expr可以是数字，也可以是表达式)
MySQL六种窗口函数用法案例
Allen_人工智能与大数据
07-31 4144
MySQL六种窗口函数用法案例
mysql 窗口函数
07-27
MySQL 窗口函数是一种在查询结果集中进行计算和排序的功能。它们能够对查询结果集中的每一行进行计算，而不依赖于单独的聚合函数或分组操作。以下是一些常见的 MySQL 窗口函数： 1. ROW_NUMBER(): 为结果集中的每一行分配一个唯一的整数值。 2. RANK(): 计算结果集中每一行的排名，如果出现相同的值，将会有相同的排名。 3. DENSE_RANK(): 与 RANK() 类似，但是在出现相同值时不会跳过排名。 4. LAG(): 获取当前行之前指定偏移量的行的值。 5. LEAD(): 获取当前行之后指定偏移量的行的值。 6. FIRST_VALUE(): 返回结果集中第一行的指定列的值。 7. LAST_VALUE(): 返回结果集中最后一行的指定列的值。 8. NTILE(n): 将结果集划分为 n 个等大小的桶，并为每个桶分配一个编号。 这些是只是一些常见的 MySQL 窗口函数，还有其他更多的函数可用。你可以在 MySQL 官方文档中找到更多关于窗口函数的详细信息和用法示例。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值