Oracle中的分析函数汇总

简介

在Oracle中的分析函数，又称为窗口函数，是一种可在结果集中进行计算的函数。它们提供了强大的聚合、排序和排名功能，能够使SQL查询更加灵活，且比使用传统的GROUP BY方法更高效。

基本语法

Oracle中分析函数的基本语法如下：

SELECT [DISTINCT] <select_list>
  [,<analytic_function()>] OVER ([partition_by_clause] [order_by_clause] [windowing_clause])
FROM table_name;

其中：

• <select_list>：要查询的字段列表；
• <analytic_function>：要执行的分析函数；
• OVER：关键词，表示要对整个结果集执行分析函数；
• partition_by_clause：可选语句，指定要分区的列；
• order_by_clause：可选语句，指定要排序的列；
• windowing_clause：可选语句，指定要计算的窗口大小。

分析函数示例

以下是两个使用Oracle中分析函数的示例：

示例1：计算总销售额和占比

假设我们有一个销售订单表sales_order，其中包含以下列：order_id 订单ID、product_id 商品ID、product_name 商品名称、order_quantity 订单数量、order_price 订单单价、order_date 订单日期。

现在我们想要查询每个商品的总销售额和占比，并按照占比从高到低排列。可以使用以下SQL语句：

SELECT
  product_id,
  SUM(order_quantity*order_price) OVER (PARTITION BY product_id) AS total_sales,
  ROUND(
    100*SUM(order_quantity*order_price) OVER (PARTITION BY product_id)
    /SUM(order_quantity*order_price) OVER () -- 计算总销售额
    ,2
  ) AS sales_percentage
FROM sales_order
ORDER BY sales_percentage DESC;

解析：

• SUM(order_quantity*order_price) OVER (PARTITION BY product_id)：计算每个商品的总销售额；
• SUM(order_quantity*order_price) OVER ()：计算整个结果集的总销售额；
• 100*SUM(order_quantity*order_price) OVER (PARTITION BY product_id)/SUM(order_quantity*order_price) OVER ()：计算每个商品的占比；
• ROUND()：四舍五入保留两位小数；
• ORDER BY sales_percentage DESC：按照占比从高到低排列。

示例2：计算每日订单排名

假设我们有一个销售订单表sales_order，其中包含以下列：order_id 订单ID、product_id 商品ID、product_name 商品名称、order_quantity 订单数量、order_price 订单单价、order_date 订单日期。

现在我们想要查询每个日期的订单排名，并按照日期和订单数量排列。可以使用以下SQL语句：

SELECT
  order_date,
  order_id,
  order_quantity,
  RANK() OVER (PARTITION BY order_date ORDER BY order_quantity DESC) AS order_rank
FROM sales_order
ORDER BY order_date, order_quantity DESC;

解析：

• RANK() OVER (PARTITION BY order_date ORDER BY order_quantity DESC)：对每个日期的订单按照订单数量从高到低排名。

总结

Oracle中的分析函数可以大大提高SQL查询的灵活性和效率。在使用分析函数时，需要注意分析函数的语法和使用方法，以充分发挥其功能。