Mastering SQL: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries Using a Hospital Database

本文为原文前 6,000 字符的节选翻译，完整内容请查看原文。

Mastering SQL: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries Using a Hospital Database

Introduction Structured Query Language (SQL) is a standard programming language used to store, retrieve, manipulate, and analyze data in relational databases. In a hospital environment, SQL helps manage patient records, appointments, doctors, departments, medical bills, and payments. But, as hospital databases grow, basic queries using SELECT, WHERE, and ORDER BY may not be sufficient for complex analytical tasks. Some example are, a hospital administrator may need to Rank doctors by revenue. Identify patients with outstanding balances. Determine which departments have the highest number of appointments. Compare monthly payments. SQL provides several advanced techniques for solving these problems: Window functions perform calculations across related rows without combining them into a single result row. SQL joins combine information from two or more tables using related columns. Common Table Expressions (CTEs) create named query results that simplify complex SQL statements. Subqueries allow one SQL query to be embedded inside another query. This article explains these concepts, their different types, their practical applications, and their differences using a hospital database in PostgreSQL.

简介 结构化查询语言 (SQL) 是一种用于在关系数据库中存储、检索、操作和分析数据的标准编程语言。在医院环境中，SQL 有助于管理患者记录、预约、医生、科室、医疗账单和付款。但是，随着医院数据库的增长，使用 SELECT、WHERE 和 ORDER BY 的基本查询可能不足以处理复杂的分析任务。例如，医院管理员可能需要按收入对医生进行排名、识别有未结余额的患者、确定哪些科室的预约数量最多，或比较每月付款。SQL 提供了几种解决这些问题的高级技术：窗口函数可以在不将相关行合并为单个结果行的情况下跨行执行计算；SQL 连接使用相关列组合来自两个或多个表的信息；公用表表达式 (CTE) 创建可简化复杂 SQL 语句的命名查询结果；子查询允许将一个 SQL 查询嵌入到另一个查询中。本文将使用 PostgreSQL 中的医院数据库来解释这些概念、它们的类型、实际应用及其区别。

SQL Window Functions What are window functions? SQL window functions (also known as analytic functions) perform calculations across a set of table rows that are related to the current row, without collapsing the result set into a single output row. This set of rows is called a window. Unlike a standard GROUP BY clause, which compresses your rows to show only the summaries, window functions preserve the original granularity of your data. Every single record remains visible, and the calculated value appears as a new column alongside your existing details. Window functions are useful for: Ranking doctors by revenue. Finding the previous or next patient payment. Calculating running totals. Comparing current payments with earlier payments. Identifying the highest and lowest payments. Dividing patients into analytical groups. The general syntax is: SELECT column_name, window_function() OVER ( PARTITION BY grouping_column ORDER BY sorting_column ) AS result FROM table_name; The OVER() clause defines the window over which the function operates.

SQL 窗口函数 什么是窗口函数？SQL 窗口函数（也称为分析函数）可以在与当前行相关的一组表行上执行计算，而不会将结果集折叠为单个输出行。这组行被称为窗口。与仅显示摘要的压缩行的标准 GROUP BY 子句不同，窗口函数保留了数据的原始粒度。每一条记录都保持可见，计算出的值作为新列出现在现有详细信息旁边。窗口函数适用于：按收入对医生进行排名、查找上一次或下一次患者付款、计算累计总额、比较当前付款与早期付款、识别最高和最低付款，以及将患者划分为分析组。其通用语法为：SELECT column_name, window_function() OVER ( PARTITION BY grouping_column ORDER BY sorting_column ) AS result FROM table_name; 其中 OVER() 子句定义了函数运行的窗口。

Understanding OVER(), PARTITION BY, ORDER BY, and window frames These components determine how a window function processes data. A. OVER() The OVER() clause in SQL defines a “window” of rows around the current row to perform a calculation. It turns standard functions (like SUM, AVG, or ROW_NUMBER) into window functions, allowing you to calculate aggregates or rankings without losing your underlying row-level data. B. PARTITION BY In SQL, the PARTITION BY clause is a sub-clause used inside window functions (via the OVER() clause) to divide a query’s result set into smaller, isolated groups—called partitions. The defining characteristic of PARTITION BY is that it performs group-level calculations while keeping every individual row intact. Calculations (like sums, averages, or rankings) are calculated independently within each partition and reset when moving to the next group.

理解 OVER()、PARTITION BY、ORDER BY 和窗口框架 这些组件决定了窗口函数如何处理数据。A. OVER()：SQL 中的 OVER() 子句定义了围绕当前行的“窗口”以执行计算。它将标准函数（如 SUM、AVG 或 ROW_NUMBER）转换为窗口函数，使您能够在不丢失底层行级数据的情况下计算聚合或排名。B. PARTITION BY：在 SQL 中，PARTITION BY 子句是窗口函数内部（通过 OVER() 子句）使用的子句，用于将查询的结果集划分为更小的、隔离的组，称为分区。PARTITION BY 的定义特征是它在保持每一行完整的同时执行组级计算。计算（如总和、平均值或排名）在每个分区内独立计算，并在移动到下一组时重置。

C. ORDER BY The SQL ORDER BY clause is used to sort the result set of a query in either ascending or descending order. Without it, a database does not guarantee the order of the returned rows, meaning they can appear random or change between query runs. 1. Basic Syntax: The ORDER BY clause always goes at the very end of your SQL query (or right before a LIMIT / TOP clause if you are restricting rows). SELECT column1, column2 FROM table_name ORDER BY column_name [ASC | DESC]; • ASC (Ascending): Sorts data from smallest to largest (A to Z, 0 to 9, oldest date to newest). This is the default setting, so you do not have to explicitly type it. • DESC(Descending): Sorts data from largest to smallest (Z to A, 9 to 0, newest date to oldest). 2. Order of Execution: It helps to know that SQL doesn’t read your query from top to bottom. The database processes ORDER BY near the very end of its execution cycle.

C. ORDER BY SQL ORDER BY 子句用于按升序或降序对查询结果集进行排序。如果没有它，数据库不保证返回行的顺序，这意味着它们可能看起来是随机的，或者在不同的查询运行之间发生变化。1. 基本语法：ORDER BY 子句总是位于 SQL 查询的最末尾（或者在限制行数时位于 LIMIT / TOP 子句之前）。SELECT column1, column2 FROM table_name ORDER BY column_name [ASC | DESC]; • ASC（升序）：将数据从小到大排序（A 到 Z，0 到 9，从最旧日期到最新日期）。这是默认设置，因此无需显式输入。• DESC（降序）：将数据从大到小排序（Z 到 A，9 到 0，从最新日期到最旧日期）。2. 执行顺序：了解 SQL 并非从上到下读取查询很有帮助。数据库在执行周期的最后阶段处理 ORDER BY。

D. Window frames A window frame in SQL is a sub-selection of rows within a partition that determines exactly which records are included in a window function’s calculation for the current row. While PARTITION BY groups the data and ORDER BY sorts it, the window frame clause defines a sliding or fixed boundary around the current row. As SQL evaluates the query row-by-row, this “frame” shifts to look backward, forward, or at a fixed range of data. 1. The Anatomy of a Window Frame: A window frame is written at the very end of the OVER() clause using this basic syntax: <window_function>() OVER ( PARTITION BY column1 ORDER BY column2 [ROWS|RANGE|GROUPS] BETWEEN <start_boundary> AND <end_boundary> ). The Boundaries: To set the frame, you mix and match these five boundary markers: • UNBOUNDED PRECEDING: The very first row of the partition. • N PRECEDING: N rows before the current row. • CURRENT ROW: The row currently being processed. • N FOLLOWING: N rows after the current row. • UNBOUNDED FOLLOWING: The very last row of the partition.

D. 窗口框架 SQL 中的窗口框架是分区内行的子选择，它准确确定了当前行的窗口函数计算中包含哪些记录。虽然 PARTITION BY 对数据进行分组，ORDER BY 对其进行排序，但窗口框架子句定义了围绕当前行的滑动或固定边界。当 SQL 逐行评估查询时，此“框架”会移动以查看向后、向前或固定范围的数据。1. 窗口框架的结构：窗口框架写在 OVER() 子句的最末尾，使用以下基本语法：<window_function>() OVER ( PARTITION BY column1 ORDER BY column2 [ROWS|RANGE|GROUPS] BETWEEN <start_boundary> AND <end_boundary> )。边界：要设置框架，您可以混合搭配这五个边界标记：• UNBOUNDED PRECEDING：分区的第一行。• N PRECEDING：当前行之前的 N 行。• CURRENT ROW：当前正在处理的行。• N FOLLOWING：当前行之后的 N 行。• UNBOUNDED FOLLOWING：分区的最后一行。