sql高阶应用-窗口函数与公用表

1.窗口函数的认识

SQL中的窗口函数（也称为分析函数或OLAP函数）是一种强大的工具，它允许你在数据集的子集上执行计算，这个子集被称为“窗口”。与普通的SQL聚合函数（如SUM()、COUNT()、AVG()等）不同，窗口函数不会将行分组到单个输出行中，而是会为每个行保留其自身的身份，并根据窗口计算额外的值。

窗口函数通常与OVER()子句一起使用，这个子句定义了窗口的大小和行的排序方式。窗口函数可以用于许多不同的任务，包括计算累计统计数据、排名、行号和移动平均等。

以下是一些常用的窗口函数及其用途：

1. ROW_NUMBER()：为每一行分配一个唯一的连续整数。
2. RANK()和DENSE_RANK()：为每一行分配一个排名，RANK()在遇到相同值时会跳过下一个排名，而DENSE_RANK()则不会。
3. NTILE(n)：将结果集分为n个大致相等的部分，并为每一行分配一个桶号。
4. SUM(), AVG(), MIN(), MAX()：计算窗口中的总和、平均值、最小值和最大值。
5. LEAD()和LAG()：访问窗口中当前行之后或之前的行的数据。

以下是一些窗口函数的基本示例：

例1：使用ROW_NUMBER()为表中的行分配行号。

SELECT
  id,
  data,
  ROW_NUMBER() OVER (ORDER BY id) AS row_number
FROM
  your_table;

这将根据id列的顺序为每一行分配一个行号。

例2：计算累计总和。

SELECT
  id,
  data,
  SUM(data) OVER (ORDER BY id) AS cumulative_sum
FROM
  your_table;

这将根据id列的顺序计算每行的累计总和。

例3：使用LAG()获取前一行的数据。

SELECT
  id,
  data,
  LAG(data, 1) OVER (ORDER BY id) AS previous_data
FROM
  your_table;

这将返回每一行之前的一行的数据值。

例4：使用RANK()进行排名。

SELECT
  id,
  data,
  RANK() OVER (ORDER BY data DESC) AS rank
FROM
  your_table;

这将根据data列的值对行进行排名，最大的值将排名第一。

请注意，窗口函数的OVER()子句可以包含PARTITION BY，这允许你在执行窗口函数之前，根据一个或多个列的值将结果集分区。例如：

SUM(data) OVER (PARTITION BY category ORDER BY id) AS cumulative_sum

这将在每个category内部计算累计总和。

2. 分区 partition by

在SQL中，PARTITION BY子句通常与窗口函数（也称为OLAP函数）一起使用，它允许你将结果集分成多个分区，并且对每个分区独立地应用窗口函数。这意味着窗口函数将对每个分区内的行进行计算，而不是对整个查询结果集进行计算。

PARTITION BY子句的用法如下：

SELECT column_name, 
       window_function(column_name) OVER (
           PARTITION BY column_name_to_partition_by
           ORDER BY column_name_to_order_by
       ) AS alias_name
FROM table_name;

在这个例子中：

• column_name是你想在SELECT语句中选择的列。
• window_function是窗口函数，如ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()等。
• PARTITION BY column_name_to_partition_by定义了如何将数据分成分区。根据column_name_to_partition_by列中的值，相同值的行会被分配到同一个分区。
• ORDER BY column_name_to_order_by定义了每个分区内的行如何排序，这对于某些窗口函数（如ROW_NUMBER()）是必要的，因为它们的计算依赖于行的顺序。
• alias_name是列的别名，用于在结果集中显示窗口函数的结果。

例如，如果你有一个销售数据表，并且想要计算每个销售员的每日截止当前日的累计销售额，你可以使用PARTITION BY子句按销售员分区，并应用SUM()窗口函数：

SELECT salesperson_id, 
       sale_date, 
       sale_amount, 
       SUM(sale_amount) OVER (
           PARTITION BY salesperson_id 
           ORDER BY sale_date
       ) AS cumulative_sales
FROM sales;

在这个查询中，SUM(sale_amount)窗口函数将计算每个销售员（由salesperson_id列分区）的累计销售额，ORDER BY sale_date确保了销售额是按日期顺序累计的。每个销售员的销售额将从他们的第一笔销售开始累积，直到当前行的日期。

与group by （ sum（））不同的是，分组一个组只有一个结果输出，而分区不会改变行数一个区内原本有多少行就会有多少行的累计数据输出

3.公用表 （CET）

在SQL中，WITH子句通常用来定义一个或多个临时的命名结果集，这些结果集在SQL查询中被称为公用表表达式（Common Table Expressions，简称CTE）。CTE并不是视图，因为它们不是数据库中的持久对象。CTE是在查询执行时临时创建的，并且通常只在定义它们的查询中可见。

WITH子句后面的XXX AS ()部分定义了一个CTE，其中XXX是你给CTE指定的名称，而括号内的部分是一个SQL查询，它定义了CTE的内容。CTE可以在后续的SELECT、INSERT、UPDATE或DELETE语句中被引用，就像它是一个实际的表或视图一样。

以下是使用WITH子句定义CTE的一个例子：

WITH SalesSummary AS (
    SELECT salesperson_id, 
           SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
)
SELECT s.salesperson_id, 
       s.total_sales, 
       e.name
FROM SalesSummary s
JOIN employees e ON s.salesperson_id = e.employee_id;

在这个例子中，SalesSummary是一个CTE，它包含了每个销售员的总销售额。在WITH子句之后的SELECT语句中，SalesSummary被用作一个临时的命名结果集，它与employees表进行了连接以获取每个销售员的名称。

CTE的作用域仅限于包含它的那个查询，一旦该查询执行完毕，CTE就会消失。这使得CTE在处理复杂查询和递归查询时非常有用，因为它可以帮助提高查询的可读性和维护性。与视图不同，CTE不会在数据库中保存数据定义或占用存储空间。