在數(shù)據(jù)庫中，over函數(shù)是一個非常重要的函數(shù)，可以實現(xiàn)對數(shù)據(jù)的分組運算、窗口函數(shù)計算等功能，極大地方便了數(shù)據(jù)分析和處理。本篇文章主要介紹over函數(shù)的基本概念及其在實踐中的應(yīng)用場景。

一、over函數(shù)簡介

over函數(shù)是一種用于窗口函數(shù)的函數(shù)，它可以在結(jié)果集中為結(jié)果集的每個行返回一個累計函數(shù)值。該函數(shù)使用的語法如下：

OVER ([PARTITION BY partition_col1, partition_col2, …]

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

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

其中，partition是使用分區(qū)功能分組查詢時的一種機(jī)制；order by用于指定排序的方式；rows或range用于指定計算窗口的方式。over函數(shù)可以配合其他的聚合函數(shù)，例如sum、avg、count等，達(dá)到更為靈活、精確的計算目的。

二、over函數(shù)的應(yīng)用場景

1. 計算總和、平均數(shù)等

在某些情況下，需要對一些數(shù)據(jù)進(jìn)行總和、平均數(shù)計算。可以使用over函數(shù)來實現(xiàn)，如下所示：

SELECT Salesperson, Sales,

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

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

FROM SalesTable;

上述查詢結(jié)果中，可以實現(xiàn)對每個銷售員的銷售額進(jìn)行匯總、計算平均值。

2. 排名

經(jīng)常需要對數(shù)據(jù)進(jìn)行排名，例如求出銷售額排名前十的商品。以下查詢可實現(xiàn)此目的：

SELECT ProductName, Sales,

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

FROM SalesTable;

以上命令將根據(jù)每個產(chǎn)品的銷售額進(jìn)行排名，按照銷售額排序后，將每個產(chǎn)品的排名值取出來。

3. 分組計算

有時候需要按照某些特定條件拆分成若干組進(jìn)行計算，例如按月份分別計算銷售額、毛利潤等指標(biāo)。以下查詢可以做到：

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函數(shù)的partition子句，將數(shù)據(jù)按月份分組，并對相應(yīng)的指標(biāo)進(jìn)行計算。

4. 窗口函數(shù)

窗口函數(shù)是一種靈活的計算方式，可用于計算過去一定時間的銷售額、倉庫中庫存量、歷史銷售額等等。以下語句展示了如何計算城市中的月度累計銷售額：

SELECT City, Sales,

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

FROM SalesTable;

以上sql語句使用了over函數(shù)的partition和order by子句，首先將數(shù)據(jù)按照城市和月份進(jìn)行分組排序，然后計算出城市中每個月的累計銷售額。

5. 分位數(shù)/百分位數(shù)計算

分位數(shù)/百分位數(shù)是對數(shù)據(jù)的劃分。例如第50個百分位是中位數(shù)，第75個百分位就剛好是中前75%的數(shù)字。以下語句可以實現(xiàn)對數(shù)據(jù)的分位數(shù)計算：

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

FROM DataTable;

以上命令使用了over函數(shù)中的order by子句，按數(shù)字將數(shù)據(jù)排序并將數(shù)字劃分為100個百分位。

over函數(shù)在數(shù)據(jù)庫中是一個非常重要的函數(shù)，它可以靈活的應(yīng)用于各種計算場景。學(xué)好over函數(shù)的應(yīng)用，是提高數(shù)據(jù)分析和處理能力的一項重要技能。

相關(guān)問題拓展閱讀：

• oracle分析函數(shù)over partition by 和group by的區(qū)別

oracle分析函數(shù)over partition by 和group by的區(qū)別

普通的聚合函數(shù)用group by分組，每個分組返回一個統(tǒng)計值，而分析函數(shù)采用partition by分組，并且每組每行都可以返回一個統(tǒng)計值。

分析函數(shù)的形式：分析函燃含數(shù)帶有一個開窗函數(shù)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針對當(dāng)前所有記錄的前一條、后一皮高笑條記錄，也就是按Department_Id分組之后的,所有該小組的記錄

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

–preceding：在…之前，

–following：在…之后

數(shù)據(jù)庫over函數(shù)的介紹就聊到這里吧，感謝你花時間閱讀本站內(nèi)容，更多關(guān)于數(shù)據(jù)庫over函數(shù),數(shù)據(jù)庫的over函數(shù)簡介及應(yīng)用場景,oracle分析函數(shù)over partition by 和group by的區(qū)別的信息別忘了在本站進(jìn)行查找喔。

創(chuàng)新互聯(lián)服務(wù)器托管擁有成都T3+級標(biāo)準(zhǔn)機(jī)房資源，具備完善的安防設(shè)施、三線及BGP網(wǎng)絡(luò)接入帶寬達(dá)10T，機(jī)柜接入千兆交換機(jī)，能夠有效保證服務(wù)器托管業(yè)務(wù)安全、可靠、穩(wěn)定、高效運行；創(chuàng)新互聯(lián)專注于成都服務(wù)器托管租用十余年，得到成都等地區(qū)行業(yè)客戶的一致認(rèn)可。

網(wǎng)站標(biāo)題：數(shù)據(jù)庫的over函數(shù)簡介及應(yīng)用場景(數(shù)據(jù)庫over函數(shù))
網(wǎng)頁地址：http://uogjgqi.cn/article/codihdj.html