Oracle查询中OVER (PARTITION BY ..)用法

当我们需要在查询结果中使用聚合函数并且对聚合函数结果进行分组时，就需要使用Oracle中的OVER (PARTITION BY ...)用法。OVER (PARTITION BY ...)用法可以让聚合函数仅在每个分组内计算，从而得到准确的结果。下面我会详细讲解OVER (PARTITION BY ...)的用法，并提供两个示例用户参考。

OVER (PARTITION BY ...)的语法

OVER (PARTITION BY ...)语法通常用于以下情形：

• 在查询结果中使用聚合函数并且对聚合函数结果进行分组；
• 在查询结果中按照特定列的值进行排序。

以下是该语法的基本语法：

SELECT column1, column2, ..., aggregate_function(column)
OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ... [NULLS FIRST|NULLS LAST]]
) AS alias
FROM table_name;

OVER (PARTITION BY ...)的用法示例

示例1：计算每个城市的销售总额

假设我们有一个名为sales的数据表，其中包含订单号、城市、销售额和订单日期4列数据。下面的示例演示了如何使用OVER (PARTITION BY ...)计算每个城市的销售总额。

SELECT
    city,
    SUM(sales) OVER (PARTITION BY city) AS total_sales
FROM sales;

在这个示例中，SUM函数是聚合函数，我们使用了OVER (PARTITION BY city)子句对其进行了限制。这样可以确保SUM函数只在每个城市的销售额上执行，而不是在整个数据集上执行。结果是每行都列出了城市名称和对应的销售总额。

示例2：计算每个城市的销售总额及其在同一天内的排名

下面的示例演示了如何使用OVER (PARTITION BY ...)来计算相同城市在同一天内的排名。

SELECT
    city,
    sales,
    order_date,
    RANK() OVER (PARTITION BY city, order_date ORDER BY sales DESC) AS rank
FROM sales;

在这个示例中，我们使用RANK()作为聚合函数计算城市、订单日期和销售额三个列数据的组合情况下，每个城市销售额排名。OVER (PARTITION BY city, order_date) 限制了聚合函数RANK()在相同城市和订单日期的分组中执行，并使用ORDER BY 子句按照销售额降序排序。

在结果集中，每行显示了城市、销售额、订单日期和该销售额在同一天内排名。这个排名次序的计算是针对每个城市和订单日期组合信息下的，而不是整个数据集。