SQL：窗口函数

SQL：窗口函数

窗口函数的语法为：

function(args)over([partitionbyexpression][orderbyexpressionasc/desc][rows/range…])

其中，[]表示可选。

窗口函数大体分为以下几类：

1）function(args)：包括以下两个函数：

2）partitionby：基于对特定字段进行分区，划定窗口特性的使用范围。

3）orderby：指定窗函数覆盖率按照特定字段排序的应用顺序。

4)Row/Range...：指定记录每行对应的计算范围，包括当前行、前n行和后n行。

典型的窗口函数和计算范围是：

注意：排名函数不支持行/范围语句。

案例描述

示例表：这是一张学生表，记录了各个班级学生各科目的成绩。
字段信息包括ID、班级、科目和分数。

1.groupby和partitionby的区别：

groupby按照指定的列对数据进行分组，对每组进行聚合计算，每次一行输出每组的计算结果，不重复数据。
;partitionby将数据按照指定的列进行分区，并使用窗口函数对每个分区进行计算，使原始数据与分区计算的结果相匹配。

2.row_number()、Rank()和Dense_rank()之间的区别：

这三个排名函数不支持row/range语句。

累积和滑动平均值

1。
如果over()没有orderby子句，则不会发生累积。
对各个分区单独进行聚合计算，并对每行所在的分区计算出的值进行补充。
它位于。

2.如何计算移动平均值：使用ROWS2preceding将窗口滑动范围限制为当前行之前的2行。
如果不存在，则不会被计算在内。
这会产生具有固定窗口长度的聚合值。

连续出现

1.lag()和Lead()：获取窗口范围内当前行前后的数据。
lag()获取前n行，Lead()获取接下来的n行。
我们使用这些功能创建辅助列，查找连续的科目和分数，以方便月度和年度增长率计算。

综合应用

使用牛客题库中的SUM(cnt)over(orderbyym)AStotal_cnt，根据ym升序求和。
如果ym的值相同，比如‘2021-05’，则无法准确排序，会导致sum(cnt)结果不准确，导致实际结果被多算。

SQL查询语句-汇总分析

SQL汇总查询语句1.汇总函数SQL中用于汇总数据的函数有：SUM、COUNT、AVG、MAX和MIN。
这些函数可用于计算列的总和、行数、平均值、最大值和最小值。
2、汇总函数示例：要查找某一列的行数，使用COUNT函数；要查找所有列中的行数，请使用COUNT(*)。
要查找一列数据的总和，请使用SUM函数。
要查找一列数据的平均值，请使用AVG函数。
使用MAX和MIN函数查找一列数据的最大值和最小值。
3、SQL查询语句的编写规则在编写SQL查询语句时，必须清楚地了解汇总函数的使用，并确保在使用时遵循SQL的语法和语义规则。
4、总结分析案例查询课程号“0002”的总分，使用SUM函数计算该课程的总分。
询问选择课程的学生人数，并使用COUNT函数统计选择课程的学生人数。
SQL分组查询语句1、查询不同性别的学生人数，使用GROUPBY语句对不同性别进行分组，然后使用COUNT函数统计每组的学生人数。
查询年龄大于“1990-01-01”的不同性别的学生人数，需要在分组条件中使用年龄。
需要添加过滤。
3.分组查询案例要查询每个科目的最高分和最低分，可以使用MAX和MIN函数结合GROUPBY分组。
要查询每门课程注册的学生人数，请使用COUNT函数按课程分组。
要询问男孩和女孩的数量，请按性别分组并使用COUNT函数。
4、为分组结果指定条件，查询平均分大于60分的学生的学号和平均成绩，先用GROUPBY分组，然后在WHERE子句中添加条件。
要询问至少修读两门课程的学生人数，请结合使用GROUPBY和HAVING子句。
要查询同名学生列表并统计同名学生的数量，请使用GROUPBY和COUNT函数。
使用SQL解决业务问题1、计算各科目的平均分2、计算每门课程的平均分，且平均分大于等于80分每门课程的SQL排序查询语句计算平均分，且如果平均分大于或等于80分，则使用AVG函数，然后按等级降序排序。
要指定多个排序列名称，请使用ORDERBY子句。
排序空值。
在SQL中，空值默认排在非空值后面。
这可以通过设置ORDERBY参数来调整。
从查询结果要删除指定的行，请使用LIMIT关键字。
排序查询案例对未通过课程的查询进行排序，并按照课程编号从大到小进行排序。
查询每门课程的平均分，结果按照平均分降序排列；如果平均分相等，则按照课程编号降序排列。
常见错误信息：SQL查询时可能会出现语法错误、逻辑错误、权限错误等。
需要检查SQL语句的语法、数据表结构、权限设置等。
SQLZOOSELECTfromNobelTutorial1、SELECTfromNobelTutorial/zh2、SUMandCOUNT/zh

sql常用函数解析有哪些?

SQL自带了多种函数，大大增强了语言的便利性。
函数就像黑匣子，按照预定义的程序输入参数和返回值。
函数类别包括算术、字符串、日期和转换。
算术功能：ABS（数值）计算绝对值，同时输入NULL返回NULL；字符串函数：CONCAT(str1,str2,str3)连接字符串；LENGTH(string)计算字符串的长度；LOWER/UPPER切换状态；REPLACE(对象字符串、替换前、替换后)实现String(对象字符串、起始位置、字符数)截取字符串；SUBSTRING_INDEX(parentstring,delimiter,n)获取指定分隔符之前或之后的子字符串。
日期函数：EXTRACT（dateelementfromdate）提取日期元素，例如年、月、时、秒，并返回一个数值。
转换函数：CAST（转换前的数据类型值）进行数据类型转换；COALESCE(data1,data2,data3...)返回一个非空值，参数数量是可变的。
SQL函数的种类很多，常用的约有30-50种。
其他不常用的功能，查看文档即可。