wordpress开启全站ssl,网站开发实践意义,企业内训机构,福建城乡建设网站1、SQL窗口函数
SQL窗口函数#xff08;Window Functions#xff09;是一种强大的数据分析工具#xff0c;它们允许你在结果集的行上执行计算#xff0c;而不需要将这些行分组到单独的输出行中。窗口函数通常与OVER()子句一起使用#xff0c;该子句定义了窗口或分区…1、SQL窗口函数
SQL窗口函数Window Functions是一种强大的数据分析工具它们允许你在结果集的行上执行计算而不需要将这些行分组到单独的输出行中。窗口函数通常与OVER()子句一起使用该子句定义了窗口或分区以及窗口内的排序规则。以下是SQL窗口函数的一般语法
window_function() OVER ([PARTITION BY partition_expression, ...][ORDER BY order_expression [ASC|DESC], ...][ROWS or RANGE frame_clause]
)window_function()这是你要应用的窗口函数如ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、MIN()、MAX()等。注意虽然SUM()、AVG()、MIN()和MAX()通常是聚合函数但当它们与OVER()子句一起使用时它们就变成了窗口函数。 OVER()这个子句定义了窗口函数的操作范围。 PARTITION BY partition_expression可选。这个子句将结果集划分为分区窗口函数将在每个分区内独立计算。如果没有PARTITION BY则整个结果集被视为一个单一的分区。 ORDER BY order_expression通常必需但某些窗口函数可能不需要。这个子句定义了窗口内行的排序顺序。这对于计算如排名或累计和等窗口函数是必需的。 ROWS or RANGE frame_clause可选。这个子句定义了窗口帧frame即窗口函数将考虑的行集。ROWS基于物理行数而RANGE基于值的范围。常见的帧子句包括ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区开始到当前行和RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW基于值的范围但具体行为可能因数据库而异。
以下是一些常见的窗口函数及其用途
ROW_NUMBER()为窗口内的每一行分配一个唯一的序号。RANK()为窗口内的行分配排名跳过相同的值并留下空位。DENSE_RANK()为窗口内的行分配排名不跳过相同的值。SUM()、AVG()、MIN()、MAX()计算窗口内行的总和、平均值、最小值和最大值。
例如以下查询使用ROW_NUMBER()窗口函数为按销售额排序的每个销售人员的销售记录分配一个序号
SELECTsalesperson_id,sale_date,amount,ROW_NUMBER() OVER (PARTITION BY salesperson_id ORDER BY sale_date) AS sale_rank
FROMsales;在这个查询中ROW_NUMBER()函数在salesperson_id分区内按sale_date排序为每行分配一个序号sale_rank。
窗口函数Window Functions是SQL中的一种强大工具用于在查询结果集的行上执行计算而不需要将结果集分组为单独的输出行。这些函数通常用于执行诸如排名、累计总和、移动平均等操作。
以下是一个示例展示如何使用窗口函数来计算每个员工在部门内的工资排名和累计工资总和。
假设我们有一个名为 employees 的表结构如下
CREATE TABLE employees (employee_id INT PRIMARY KEY,employee_name VARCHAR(100),department_id INT,salary DECIMAL(10, 2)
);并且表中有以下数据
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(1, Alice, 1, 70000),
(2, Bob, 1, 50000),
(3, Charlie, 1, 60000),
(4, David, 2, 80000),
(5, Eve, 2, 90000),
(6, Frank, 2, 75000);现在我们希望计算每个员工在其部门内的工资排名按降序排列和累计工资总和。
可以使用以下SQL查询
SELECTemployee_id,employee_name,department_id,salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROMemployees;解释 RANK() OVER (PARTITION BY department_id ORDER BY salary DESC): RANK() 是窗口函数之一用于计算排名。PARTITION BY department_id 表示将结果集按部门分区。ORDER BY salary DESC 表示在每个分区内按工资降序排列。结果是每个员工在其部门内的工资排名。 SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW): SUM() 是聚合函数之一但在这里用作窗口函数。PARTITION BY department_id 同样表示将结果集按部门分区。ORDER BY salary DESC 表示在每个分区内按工资降序排列。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示累计从分区开始到当前行的所有行的工资总和。结果是每个员工在其部门内的累计工资总和。
运行上述查询后结果将如下所示
employee_id | employee_name | department_id | salary | salary_rank | cumulative_salary
------------|---------------|---------------|--------|-------------|-------------------
4 | David | 2 | 80000 | 1 | 80000
5 | Eve | 2 | 90000 | 2 | 170000
6 | Frank | 2 | 75000 | 3 | 245000
1 | Alice | 1 | 70000 | 1 | 70000
3 | Charlie | 1 | 60000 | 2 | 130000
2 | Bob | 1 | 50000 | 3 | 180000这样我们就成功地使用了窗口函数来计算每个员工在其部门内的工资排名和累计工资总和。
当然可以。以下是一些使用SQL窗口函数的实际案例这些案例展示了窗口函数在不同场景下的应用。
案例一累计销售额计算
假设有一个销售数据表 sales包含以下字段date日期、amount销售额。我们需要计算到当前日期为止的累计销售额。
SELECTdate,amount,SUM(amount) OVER (ORDER BY date) AS cumulative_total
FROMsales;在这个查询中SUM(amount) OVER (ORDER BY date) 是一个窗口函数它按照日期的顺序对销售额进行累计求和。结果集将包含每一天的销售额以及到该天为止的累计销售额。
案例二部门内工资排名
考虑一个员工表 employees包含以下字段employee_id员工ID、name姓名、department_id部门ID、salary工资。我们希望计算每个员工在其部门内的工资排名。
SELECTemployee_id,name,department_id,salary,RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROMemployees;在这个查询中RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) 是一个窗口函数它首先按部门对员工进行分区然后在每个分区内按工资降序排列并计算排名。结果集将包含每个员工的ID、姓名、部门ID、工资以及在其部门内的工资排名。
案例三累计计数
假设我们想要计算到当前日期为止的累计销售次数可以使用 COUNT() 窗口函数。
SELECTdate,COUNT(*) OVER (ORDER BY date) AS cumulative_count
FROMsales;这个查询将返回每一天的日期以及到该天为止的累计销售次数。
案例四获取前一行和后一行的数据
有时候我们需要获取当前行前一行或后一行的数据。这可以使用 LAG() 和 LEAD() 窗口函数来实现。
SELECTemployee_id,salary,LAG(salary, 1) OVER (ORDER BY salary) AS previous_salary,LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROMemployees;在这个查询中LAG(salary, 1) OVER (ORDER BY salary) 返回当前行前一行的工资而 LEAD(salary, 1) OVER (ORDER BY salary) 返回当前行后一行的工资。结果集将包含每个员工的ID、工资以及前一行和后一行的工资。
案例五分组内的累计平均值
假设我们有一个表 student_scores包含以下字段student_id学生ID、course_id课程ID、score成绩。我们希望计算每个学生在每门课程内的累计平均成绩。
SELECTstudent_id,course_id,score,AVG(score) OVER (PARTITION BY student_id, course_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_average
FROMstudent_scores;在这个查询中AVG(score) OVER (PARTITION BY student_id, course_id ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 是一个窗口函数它首先按学生和课程进行分区然后在每个分区内按成绩顺序计算累计平均成绩。结果集将包含每个学生的ID、课程ID、成绩以及在该课程内的累计平均成绩。 这些案例展示了窗口函数在SQL中的广泛应用它们可以极大地简化复杂的数据分析任务。
