育才网站建设,徐州手机网站建设制作,装修设计方案,固始网站建设详细说明在sql中窗口函数是什么#xff0c;为什么需要窗口函数#xff0c;有普通的聚合函数了那窗口函数的意义在哪#xff0c;窗口函数的执行逻辑是什么#xff0c;over中的字句是如何使用和理解的#xff08;是不是句句戳到你的痛点#xff0c;哼哼#xff5e;#x…详细说明在sql中窗口函数是什么为什么需要窗口函数有普通的聚合函数了那窗口函数的意义在哪窗口函数的执行逻辑是什么over中的字句是如何使用和理解的是不是句句戳到你的痛点哼哼
1.什么是窗口函数
窗口函数Window Functions是SQL中的一种功能强大的工具用于对查询结果集中的每一行进行计算而无需对数据进行分组GROUP BY。与聚合函数不同窗口函数不会将行汇总到一条记录中而是保留原始行并在此基础上添加计算结果。窗口函数常用于排名、累积和、滑动窗口计算等。
2.为什么需要窗口函数为什么普通的聚合函数不行
2.1四点原因
按行计算而不改变行的结构 • 窗口函数能够在不改变行结构的情况下对数据进行计算。与聚合函数如SUM、AVG等不同窗口函数不会将多行数据聚合成一行而是对每一行进行计算并返回结果同时保留原始行。这在许多分析场景中非常有用例如累积和、排名计算等。 支持复杂的分析计算 • 窗口函数可以进行复杂的分析计算包括排名、累积和、滑动平均等。这些计算在财务分析、时间序列分析和数据挖掘中非常常见。例如使用窗口函数可以计算每个销售员的销售排名、某产品在不同时间段的累积销售量等。灵活的分区和排序机制 • 窗口函数支持按分区和排序进行计算提供了极大的灵活性。可以根据需要按特定列进行分区PARTITION BY并在每个分区内按某列排序ORDER BY。这使得可以在多个维度上进行复杂的数据分析。例如可以按客户ID和时间进行分区和排序从而分析每个客户在不同时间段的行为变化。保持数据的上下文关系 • 窗口函数能够保持数据的上下文关系。在进行累积计算或滑动窗口计算时窗口函数能够在当前行的基础上考虑前后多行的数据。这在时间序列分析中尤为重要例如计算滚动平均值、滚动总和等。提高查询的可读性和维护性 • 使用窗口函数可以使SQL查询更加简洁和可读。相比于嵌套的子查询或复杂的JOIN操作窗口函数提供了一种更直观的方式来表达复杂的计算逻辑。这不仅提高了查询的可读性还降低了维护的难度。
2.2举几个例子
例子1:计算每篇文章在每个时间点的累积观看人数
SELECT artical_id,dt,SUM(diff) OVER (PARTITION BY artical_id ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_viewers
FROM combined
ORDER BY artical_id, dt;
在这个例子当中窗口函数可以很方便的选择包括当前行在内的其以前的所有行
例2:计算每个销售员的销售额排名
SELECT salesperson,sales,RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank
FROM sales_data;
在这个例子当中使用窗口函数做一个“开窗”类似单独拖出一个小窗口在这个小窗口中选定一个标准对销售员进行排序在这个小窗口中每一行数据都得到了自己的排名然后带着这个排名回到主查询中这个过程中主查询既没有使用group by分组也没有order by来产生排名也没有使用子查询但是每一个销售员却得到了自己地区中的排名这就是刚刚提到的对每一行进行计算并返回结果同时保留原始行。
3.窗口函数的执行逻辑是什么(over怎么用)
只有了解了窗口函数的执行逻辑才能在复杂的查询中正确运用窗口函数
3.1 生成基础结果集
首先SQL引擎会执行查询语句中的FROM子句、WHERE子句、GROUP BY子句和HAVING子句生成基础结果集。这个结果集包含了所有符合条件的数据。所以窗口函数会在拿到所在主查询的基础结果集后才生效
3.2 分区Partitioning
在执行窗口函数时OVER子句中的PARTITION BY子句会将基础结果集划分为多个分区。每个分区独立处理窗口函数将在每个分区内单独执行。如果没有指定PARTITION BY则视为一个整体分区。注意这个分区是在窗口内单独做的并没有对主查询中的结果或者说每一行产生影响。
3.3 排序Ordering
在每个分区内根据OVER子句中的ORDER BY子句进行排序。排序定义了窗口函数的计算顺序确保计算按指定的顺序进行。注意这个排序也是在窗口内单独做的并没有对主查询中的结果或者说每一行产生影响。
3.4 定义窗口帧Frame
窗口帧定义了窗口函数计算的行范围。窗口帧由ROWS或RANGE子句指定。
从2、3中不难看出这有一个易混淆的点也是本人混淆过的点那就是over中的分区和排序 本质上 是在定义窗口函数的计算方式和主查询中的group by和order by如果有的话并无关系
3.5 举个例子
这个例子是在一个uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间组成的表的基础上统计每篇文章同一时刻最大在看人数 SELECTartical_id,MAX(instant_viewer_cnt) max_uv
FROM (SELECTartical_id,dt,SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) instant_viewer_cntFROM (SELECT artical_id, in_time dt, 1 diffFROM tb_user_logWHERE artical_id ! 0UNION ALLSELECT artical_id, out_time dt, -1 diffFROM tb_user_logWHERE artical_id ! 0) t1 group byartical_id,dt
) t2
GROUP BY 1
ORDER BY 2 DESC
这里t1表是通过编码操作和union操作生成的瞬时动作表我们为了从t1的瞬时动作得到每个时刻的状态表t2所以需要窗口函数来做聚合窗口函数中的order by只是定义sum的计算方式如果要想主查询中也是按照artical_id和dt的顺序来呈现结果那么是需要显式地在真正的order by中写出来的
