数据库的over函数简介及应用场景 (数据库over函数)

在数据库中，over函数是一个非常重要的函数，可以实现对数据的分组运算、窗口函数计算等功能，极大地方便了数据分析和处理。本篇文章主要介绍over函数的基本概念及其在实践中的应用场景。

一、over函数简介

over函数是一种用于窗口函数的函数，它可以在结果集中为结果集的每个行返回一个累计函数值。该函数使用的语法如下：

OVER ([PARTITION BY partition_col1, partition_col2, …]

ORDER BY sort_col1 [ASC | DESC], sort_col2 [ASC | DESC], …

ROWS [{N} | RANGE {UNBOUNDED | CURRENT ROW}])

其中，partition是使用分区功能分组查询时的一种机制；order by用于指定排序的方式；rows或range用于指定计算窗口的方式。over函数可以配合其他的聚合函数，例如sum、avg、count等，达到更为灵活、精确的计算目的。

二、over函数的应用场景

1. 计算总和、平均数等

在某些情况下，需要对一些数据进行总和、平均数计算。可以使用over函数来实现，如下所示：

SELECT Salesperson, Sales,

SUM(Sales) OVER(PARTITION BY Salesperson) AS ‘Total Sales’,

AVG(Sales) OVER(PARTITION BY Salesperson) AS ‘Average Sales’

FROM SalesTable;

上述查询结果中，可以实现对每个销售员的销售额进行汇总、计算平均值。

2. 排名

经常需要对数据进行排名，例如求出销售额排名前十的商品。以下查询可实现此目的：

SELECT ProductName, Sales,

RANK() OVER(ORDER BY Sales DESC) AS ‘Sales Rank’

FROM SalesTable;

以上命令将根据每个产品的销售额进行排名，按照销售额排序后，将每个产品的排名值取出来。

3. 分组计算

有时候需要按照某些特定条件拆分成若干组进行计算，例如按月份分别计算销售额、毛利润等指标。以下查询可以做到：

SELECT OrderDate, Product,

SUM(Sales) OVER(PARTITION BY MONTH(OrderDate)) AS ‘Monthly Sales’,

SUM(Profit) OVER(PARTITION BY MONTH(OrderDate)) AS ‘Monthly Profits’

FROM SalesTable;

以上语句使用了over函数的partition子句，将数据按月份分组，并对相应的指标进行计算。

4. 窗口函数

窗口函数是一种灵活的计算方式，可用于计算过去一定时间的销售额、仓库中库存量、历史销售额等等。以下语句展示了如何计算城市中的月度累计销售额：

SELECT City, Sales,

SUM(Sales) OVER(PARTITION BY City ORDER BY Month) AS ‘Cumulative Sales’

FROM SalesTable;

以上sql语句使用了over函数的partition和order by子句，首先将数据按照城市和月份进行分组排序，然后计算出城市中每个月的累计销售额。

5. 分位数/百分位数计算

分位数/百分位数是对数据的划分。例如第50个百分位是中位数，第75个百分位就刚好是中前75%的数字。以下语句可以实现对数据的分位数计算：

SELECT Data, NTILE(100) OVER(ORDER BY Data) AS ‘Percentile’

FROM DataTable;

以上命令使用了over函数中的order by子句，按数字将数据排序并将数字划分为100个百分位。

over函数在数据库中是一个非常重要的函数，它可以灵活的应用于各种计算场景。学好over函数的应用，是提高数据分析和处理能力的一项重要技能。

相关问题拓展阅读：

• oracle分析函数over partition by 和group by的区别

oracle分析函数over partition by 和group by的区别

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

分析函数的形式：分析函燃含数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) 。

使用形式如下：over(partition by xxx order by yyy rows between zzz)。

案例：

案例1：显示各部门员工的工资，并附带显示该部门的更高工资。

SELECT E.Department_Id,

E.Employee_Id,

E.Last_Name,

E.Salary,

LAST_VALUE(E.Salary) OVER(PARTITION BY E.Department_Id ORDER BY E.Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL

  FROM employees E;

–unbounded preceding and unbouned following针对当前所有记录的前一条、后一皮高笑条记录，也就是按Department_Id分组之后的,所有该小组的记录

–unbounded：不受控制的念敏，无限的

–preceding：在…之前，

–following：在…之后

数据库over函数的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于数据库over函数,数据库的over函数简介及应用场景,oracle分析函数over partition by 和group by的区别的信息别忘了在本站进行查找喔。

香港服务器首选树叶云，2H2G首月10元开通。
树叶云（shuyeidc.com）提供简单好用，价格厚道的香港/美国云服务器和独立服务器。IDC+ISP+ICP资质。ARIN和APNIC会员。成熟技术团队15年行业经验。