全栈工程师-数据库管理-SQL_SQL高级主题：窗口函数与递归查询.docx

PAGE1

PAGE1

SQL高级主题：窗口函数与递归查询简介

1窗口函数概述

窗口函数（WindowFunction）是SQL中一种强大的功能，它允许在查询中对一组行进行操作，而不仅仅是单行。窗口函数可以用于计算排名、行号、累计和、移动平均等复杂的数据分析需求，而无需使用子查询或连接操作。窗口函数的使用通常涉及三个关键部分：窗口函数本身、OVER子句和窗口框架。

1.1窗口函数的类型

窗口函数主要分为以下几类：

排名函数：如RANK(),DENSE_RANK(),ROW_NUMBER()等，用于计算行在特定排序中的位置。

分析函数：如SUM()OVER,AVG()OVER,COUNT()OVER等，用于在窗口内进行聚合计算。

偏移函数：如LAG(),LEAD()，用于访问窗口内当前行前后的行数据。

1.2窗口函数的语法

窗口函数的基本语法如下：

SELECTcolumn_name,window_function()OVER(window_specification)

FROMtable_name;

其中，window_specification可以进一步细化为：

OVER([PARTITIONBYcolumn_name(s)]

[ORDERBYcolumn_name(s)[ASC|DESC]]

[ROWSBETWEENstartANDend]);

1.3示例：使用窗口函数计算累计销售额

假设我们有一个销售记录表sales，包含sale_date,product_id,amount等字段。我们想要计算每个产品的累计销售额。

--创建示例数据

CREATETABLEsales(

sale_dateDATE,

product_idINT,

amountDECIMAL(10,2)

);

INSERTINTOsales(sale_date,product_id,amount)

VALUES(2023-01-01,1,100.00),

(2023-01-02,1,200.00),

(2023-01-03,1,150.00),

(2023-01-01,2,300.00),

(2023-01-02,2,100.00),

(2023-01-03,2,250.00);

--使用窗口函数计算累计销售额

SELECTsale_date,product_id,amount,

SUM(amount)OVER(PARTITIONBYproduct_idORDERBYsale_date)AScumulative_sales

FROMsales;

在这个例子中，SUM(amount)OVER(PARTITIONBYproduct_idORDERBYsale_date)计算了每个产品按日期排序的累计销售额。

2递归查询基础

递归查询（RecursiveQuery）在SQL中用于处理具有层次结构或递归关系的数据。它通过使用WITHRECURSIVE语句来实现，允许查询结果集自身作为查询的一部分，从而可以迭代地处理数据，直到满足某个停止条件。

2.1递归查询的语法

递归查询的基本语法如下：

WITHRECURSIVEcte_name(column_name(s))AS(

(SELECTcolumn_name(s)FROMtable_nameWHEREcondition)--初始查询

UNIONALL

(SELECTcolumn_name(s)FROMcte_nameJOINtable_nameONjoin_conditionWHEREcondition)--递归查询

)

SELECTcolumn_name(s)FROMcte_name;

2.2示例：查询员工的直接和间接下属

假设我们有一个员工表employees，包含employee_id,name,manager_id等字段。我们想要查询某个员工的所有直接和间接下属。

--创建示例数据

CREATETABLEemployees(

employee_idINT,

nameVARCHAR(50),

manager_idINT

);

INSERTINTOemployees(employee_id,name,manager_id)

VALUES(1,Alic