无锡网站制作楚天软件,腾达建设哪里的,电商十大运营平台,河源建设工程交易中心网站在这篇文章中#xff0c;我将详细介绍 PostgreSQL 中的公用表表达式#xff08;CTE#xff09;和窗口函数#xff0c;帮助你理解如何使用它们进行复杂的数据分析。我将通过具体的示例来演示这些概念的实际应用#xff0c;并在每个示例中提供详细的解释和注释。
1. 公用表…在这篇文章中我将详细介绍 PostgreSQL 中的公用表表达式CTE和窗口函数帮助你理解如何使用它们进行复杂的数据分析。我将通过具体的示例来演示这些概念的实际应用并在每个示例中提供详细的解释和注释。
1. 公用表表达式CTE
1.1 什么是 CTE
公用表表达式Common Table ExpressionCTE是一种在 SQL 查询中定义临时结果集的方式可以在主查询中多次引用。
CTE 可以提高查询的可读性和结构性尤其是在处理复杂的查询时。
使用 CTE可以避免使用嵌套查询从而使 SQL 代码更清晰。
1.2 CTE 的基本语法
CTE 的基本语法如下
WITH cte_name AS (SELECT columnsFROM tableWHERE conditions
)
SELECT *
FROM cte_name;WITH 关键字用于定义 CTE。cte_name 是 CTE 的名称可以在后续查询中使用。CTE 内部的 SELECT 查询定义了临时结果集。
1.3 示例使用 CTE 进行复杂查询
假设有一个名为 sales 的表记录了销售数据结构如下
CREATE TABLE sales (id SERIAL PRIMARY KEY, -- 唯一标识每一条销售记录product_name VARCHAR(100), -- 产品名称sale_date DATE, -- 销售日期amount DECIMAL, -- 销售金额quantity INT -- 销售数量
);插入一些示例数据
INSERT INTO sales (product_name, sale_date, amount, quantity) VALUES
(Product A, 2023-01-01, 100.00, 1),
(Product B, 2023-01-02, 200.00, 2),
(Product A, 2023-01-03, 150.00, 1),
(Product C, 2023-01-04, 300.00, 3),
(Product B, 2023-01-05, 250.00, 1);示例 1计算每个产品的总销售额
想要计算每个产品的总销售额可以使用 CTE 来先计算每个产品的销售额然后再进行汇总。
WITH sales_summary AS (SELECTproduct_name, -- 选择产品名称SUM(amount) AS total_sales -- 计算每个产品的总销售额FROMsalesGROUP BYproduct_name -- 按产品名称分组
)
SELECTproduct_name,total_sales
FROMsales_summary -- 从 CTE 中查询结果
ORDER BYtotal_sales DESC; -- 按总销售额降序排列注释
在 CTE sales_summary 中使用 SUM(amount) 来计算每个产品的总销售额并使用 GROUP BY 子句按 product_name 进行分组。主查询从 CTE 中获取结果并根据 total_sales 降序排列以便查看销售额最高的产品。
1.4 CTE 的递归查询
CTE 还支持递归查询适用于层级结构的数据如组织结构、分类等。
示例 2递归 CTE 示例
假设有一个员工表 employees结构如下
CREATE TABLE employees (employee_id SERIAL PRIMARY KEY, -- 员工唯一标识employee_name VARCHAR(100), -- 员工姓名manager_id INT -- 上级员工的 ID
);插入一些示例数据
INSERT INTO employees (employee_name, manager_id) VALUES
(Alice, NULL), -- Alice 是顶层管理者没有上级
(Bob, 1), -- Bob 是 Alice 的下属
(Charlie, 1), -- Charlie 也是 Alice 的下属
(David, 2), -- David 是 Bob 的下属
(Eve, 2); -- Eve 也是 Bob 的下属想要查询所有员工及其上级可以使用递归 CTE
WITH RECURSIVE employee_hierarchy AS (SELECTemployee_id, -- 选择员工 IDemployee_name, -- 选择员工姓名manager_id, -- 选择上级员工 ID0 AS level -- 级别顶层管理者的级别为 0FROMemployeesWHEREmanager_id IS NULL -- 从顶层管理者开始UNION ALLSELECTe.employee_id, -- 选择下属员工 IDe.employee_name, -- 选择下属员工姓名e.manager_id, -- 选择下属的上级员工 IDeh.level 1 -- 级别加 1FROMemployees eJOINemployee_hierarchy eh ON e.manager_id eh.employee_id -- 连接下属和上级
)
SELECTemployee_name,level
FROMemployee_hierarchy
ORDER BYlevel, employee_name; -- 按级别和姓名排序注释
递归 CTE employee_hierarchy 的第一部分选择顶层管理者没有上级的员工并初始化级别为 0。第二部分通过 JOIN 连接员工表和 CTE以查找每个员工的下属并将级别加 1。最后查询 CTE返回员工姓名及其层级并按层级和姓名排序。
2. 窗口函数
2.1 什么是窗口函数
窗口函数是一种在结果集的每一行上执行计算的函数它允许我们在不分组的情况下进行聚合计算。
窗口函数通常用于计算排名、移动平均、累计和等。
与普通的聚合函数不同窗口函数不会减少结果集的行数。
2.2 窗口函数的基本语法
窗口函数的基本语法如下
SELECT columns,window_function() OVER (PARTITION BY column ORDER BY column)
FROM table;window_function() 是要使用的窗口函数如 SUM(), RANK(), ROW_NUMBER() 等。OVER 子句定义了窗口的分区和排序方式。PARTITION BY 用于将结果集分成不同的组类似于 GROUP BY而 ORDER BY 用于在每个组内排序。
2.3 示例使用窗口函数进行数据分析
示例 3计算每个产品的销售排名
可以使用窗口函数来计算每个产品的销售排名。
SELECTproduct_name,SUM(amount) AS total_sales, -- 计算每个产品的总销售额RANK() OVER (ORDER BY SUM(amount) DESC) AS sales_rank -- 计算销售排名
FROMsales
GROUP BYproduct_name
ORDER BYsales_rank; -- 按销售排名排序注释
在这个查询中SUM(amount) 计算每个产品的总销售额并使用 RANK() 函数为每个产品分配一个排名排名基于总销售额的降序。最后结果按销售排名排序。
示例 4计算累计销售额
还可以使用窗口函数计算累计销售额。
SELECTsale_date,product_name,amount,SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sales -- 计算累计销售额
FROMsales
ORDER BYsale_date; -- 按销售日期排序注释
在这个查询中SUM(amount) OVER (ORDER BY sale_date) 计算截至每个销售日期的累计销售额。结果按销售日期排序显示每个日期的销售额和累计销售额。
3. 综合示例结合 CTE 和窗口函数
现在结合 CTE 和窗口函数进行一个更复杂的分析计算每个产品的总销售额、排名以及累计销售额。
WITH sales_summary AS (SELECTproduct_name,SUM(amount) AS total_sales -- 计算每个产品的总销售额FROMsalesGROUP BYproduct_name -- 按产品名称分组
)
SELECTproduct_name,total_sales,RANK() OVER (ORDER BY total_sales DESC) AS sales_rank, -- 计算销售排名SUM(total_sales) OVER (ORDER BY total_sales DESC) AS cumulative_sales -- 计算累计销售额
FROMsales_summary
ORDER BYsales_rank; -- 按销售排名排序注释
在这个综合示例中首先使用 CTE sales_summary 计算每个产品的总销售额。然后在主查询中使用窗口函数 RANK() 计算销售排名并使用 SUM(total_sales) OVER (ORDER BY total_sales DESC) 计算累计销售额。最后结果按销售排名排序展示每个产品的总销售额、排名和累计销售额。
4. 总结
本文详细介绍了 PostgreSQL 中的公用表表达式CTE和窗口函数。通过具体的示例实操展示了如何使用这些功能进行复杂的数据分析。
希望这篇文章能帮助你掌握 CTE 和窗口函数可以帮助你编写更清晰、灵活的 SQL 查询进行深入的数据分析。
