SQL Partition By的完整指南！

SQL数据库运维 2024-03-11

4428

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

1. 介绍

分区是一种将数据划分为可管理的部分的技术。在SQL中，PARTITION BY子句用于按特定的列对结果集进行分区。它通常与窗口函数一起使用，以便在分区内执行计算。

在本文中，我们将探讨SQL的PARTITION BY子句的用法和功能。我们将从基础知识开始，逐渐深入到高级概念。

2. PARTITION BY的6语法

PARTITION BY子句通常位于窗口函数的OVER子句之后。其基本语法如下：

SELECT column1, column2, ..., columnN,
       window_function(column) OVER (PARTITION BY partition_column ORDER BY order_column)
FROM table_name;

column1, column2, …, columnN：要检索的列名。
window_function(column)：要在每个分区中执行的窗口函数。
partition_column：用于分区的列名。
order_column：用于指定计算窗口函数的顺序。

3. 基本示例

让我们通过一个简单的示例来演示PARTITION BY的用法。假设我们有一个名为”orders”的表，其中包含订单的信息，如订单号、顾客ID和订单金额等。

以下是我们的示例数据：

orders表:


order_id | customer_id | order_amount
---------|-------------|-------------
1        | 1           | 100
2        | 2           | 200
3        | 1           | 150
4        | 2           | 300
5        | 3           | 50

现在，假设我们想为每个顾客计算其订单的总金额。我们可以使用PARTITION BY子句将结果集分为不同的顾客，并计算每个顾客的订单总金额。示例如下：

SELECT customer_id, 
       SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_id) as total_order_amount
FROM orders;

运行以上查询将得到以下结果：

customer_id | total_order_amount
------------|-------------------
1           | 100
1           | 250
2           | 200
2           | 500
3           | 50

在这个示例中，我们将结果集按照”customer_id”进行分区，并根据”order_id”的顺序计算每个分区中订单总金额。

4. PARTITION BY的功能

PARTITION BY子句用于在分区内进行计算。以下是PARTITION BY的功能：

4.1 分组计算

使用PARTITION BY可以实现对每个分组内数据的计算。比如，我们可以计算每个顾客的订单总金额，每个部门的销售额等。

示例代码：

SELECT customer_id, 
       SUM(order_amount) OVER (PARTITION BY customer_id) as total_order_amount
FROM orders;

运行以上查询将得到以下结果：

customer_id | total_order_amount
------------|-------------------
1           | 250
1           | 250
2           | 500
2           | 500
3           | 50

4.2 排序计算

通过在PARTITION BY子句中使用ORDER BY子句，我们可以按照特定的顺序计算分区内的数据。

示例代码：

SELECT customer_id, order_amount, 
       SUM(order_amount) OVER (PARTITION BY customer_id ORDER BY order_amount) as running_total_order_amount
FROM orders;

运行以上查询将得到以下结果：

customer_id | order_amount | running_total_order_amount
------------|--------------|----------------------------
1           | 100          | 100
1           | 150          | 250
2           | 200          | 200
2           | 300          | 500
3           | 50           | 50

在这个示例中，我们根据”order_amount”的大小来计算每个分区中订单金额的累计和。

4.3 排名计算

PARTITION BY子句还可以用于计算每个分组中的排名。

示例代码：

SELECT customer_id, 
       order_amount, 
       RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) as rank
FROM orders;

运行以上查询将得到以下结果：

customer_id | order_amount | rank
------------|--------------|-----
1           | 150          | 1
1           | 100          | 2
2           | 300          | 1
2           | 200          | 2
3           | 50           | 1

5. 使用范例

在实际的数据库场景中，PARTITION BY和窗口函数的组合非常有用。下面是一些使用PARTITION BY的常见案例：

5.1 计算移动平均值

你可以使用PARTITION BY子句计算每个分组（例如每个顾客）的移动平均值。

示例代码：

SELECT customer_id, 
       order_amount,
       AVG(order_amount) OVER (PARTITION BY customer_id ORDER BY order_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg
FROM orders;

运行以上查询将得到以下结果：

customer_id | order_amount | moving_avg
------------|--------------|-----------
1           | 100          | 125
1           | 150          | 125
2           | 200          | 250
2           | 300          | 250
3           | 50           | 50

在这个示例中，我们计算了每个顾客的移动平均金额。计算包括当前行的前一行和后一行。

5.2 获取分组内的最大值或最小值

使用PARTITION BY子句，你可以获取每个分组内的最大值、最小值等聚合函数的结果。

示例代码：

SELECT customer_id, 
       order_amount,
       MAX(order_amount) OVER (PARTITION BY customer_id) as max_order_amount,
       MIN(order_amount) OVER (PARTITION BY customer_id) as min_order_amount
FROM orders;

运行以上查询将得到以下结果：

customer_id | order_amount | max_order_amount | min_order_amount
------------|--------------|------------------|------------------
1           | 100          | 150              | 100
1           | 150          | 150              | 100
2           | 200          | 300              | 200
2           | 300          | 300              | 200
3           | 50           | 50               | 50

在这个示例中，我们计算了每个分组中订单金额的最大值和最小值。

6. 总结

在本文中，我们学习了SQL的PARTITION BY子句的用法和功能。我们首先介绍了它的基本语法，然后通过示例代码详细说明了它的功能。

PARTITION BY的功能包括分组计算、排序计算和排名计算等。我们还提供了使用PARTITION BY的几个实例，包括计算移动平均值和获取分组内的最大/最小值等。

通过使用PARTITION BY子句，我们可以在SQL查询中实现更精确的分组计算和对分组内数据的处理。这使得SQL在处理复杂的分析和报表需求时更加灵活和强大。

然而，在使用PARTITION BY时要注意一些性能方面的考虑。如果分区列上的数据较多，可能会导致查询变慢。此外，还要注意使用适当的索引来优化查询性能。

无论是在数据分析、报表生成还是在其他需要对分组数据进行计算的场景中，了解和熟练使用PARTITION BY子句都是非常有帮助的。

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL Partition By的完整指南！

1. 介绍

2. PARTITION BY的6语法

3. 基本示例

4. PARTITION BY的功能

4.1 分组计算

4.2 排序计算

4.3 排名计算

5. 使用范例

5.1 计算移动平均值

5.2 获取分组内的最大值或最小值

6. 总结

评论