Oracle 理解 over() 和 partition by

在本文中，我们将介绍 Oracle 数据库中的 over() 和 partition by 语句，并提供一些示例来帮助理解其用法和功能。

什么是 over() 和 partition by？

在 Oracle 数据库中，over() 和 partition by 用于执行窗口函数的操作。通过使用这两个关键字，我们可以在查询结果集上执行聚合函数或分析函数，而不会改变结果集的行数。

over() 语句用于定义窗口或分析域，用于指定要应用窗口函数的行集。partition by 语句用于分组数据，并将窗口函数应用于每个分组内的数据。

窗口函数的示例

下面是一些常用的窗口函数示例：

1. ROW_NUMBER()

SELECT 
    customer_id,
    order_date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM 
    orders;

在上述示例中，ROW_NUMBER() 函数用于为每个顾客的订单按照订单日期进行编号。PARTITION BY 子句指定了以 customer_id 分组，并将 ROW_NUMBER() 应用于每个分组内的数据。

2. SUM()

SELECT 
    customer_id,
    order_total,
    SUM(order_total) OVER (PARTITION BY customer_id) AS total_amount
FROM 
    orders;

这个示例中的 SUM() 函数计算了每个顾客的订单总金额，并使用 PARTITION BY 子句对数据进行分组，以便对每个分组内的订单总金额进行求和。

3. RANK()

SELECT 
    customer_id,
    order_date,
    order_total,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_total DESC) AS rank
FROM 
    orders;

在以上示例中，RANK() 函数将根据顾客的订单总金额对订单进行排名。PARTITION BY 子句按照 customer_id 进行分组，ORDER BY 子句通过 order_total 降序排序数据。

高级用法和功能

除了上述示例中的常见用法外，over() 和 partition by 还有其他高级用法和功能。

1. 指定窗口的范围

可以使用 over() 语句来指定窗口的范围。例如，如果我们想要计算每个顾客订单的累计总金额，可以使用以下查询：

SELECT 
    customer_id,
    order_date,
    order_total,
    SUM(order_total) OVER (PARTITION BY customer_id ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_total
FROM 
    orders;

以上示例中的 ROWS BETWEEN 子句指定了窗口的范围，从未指定的行之前的所有行到当前行。这将导致窗口函数计算每个顾客的订单的累计总金额。

2. 多个分区和排序字段

over() 和 partition by 还可以与多个分区和排序字段一起使用。例如，我们可以在某个时间范围内计算每个顾客每个月的订单总金额：

SELECT 
    customer_id,
    order_date,
    order_total,
    SUM(order_total) OVER (PARTITION BY customer_id, EXTRACT(MONTH FROM order_date) 
        ORDER BY order_date) AS monthly_total
FROM 
    orders;

以上示例中，我们使用了 EXTRACT() 函数从 order_date 字段中提取出月份，并将其作为第二个分区字段。这样，我们可以计算出每个顾客每个月的订单总金额。

总结

通过使用 Oracle 数据库中的 over() 和 partition by 语句，我们可以在查询结果集上执行窗口函数的操作，而不改变结果集的行数。这些窗口函数提供了强大的分析功能，可以帮助我们根据特定的分组和排序条件对数据进行聚合和分析。

以上是 over() 和 partition by 的简要介绍和示例。希望本文能够帮助你更好地理解和应用这两个关键字，从而在 Oracle 数据库中进行高效的数据分析和处理。

参考文献：
– Oracle Documentation: Window Functions (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-62AEB1CF-4C3D-4E4C-95A7-67C8A5D141F5)