sqlserver中怎么使用over()函數(shù)實(shí)現(xiàn)分組統(tǒng)計(jì)-創(chuàng)新互聯(lián)

本篇文章為大家展示了sql server中怎么使用over()函數(shù)實(shí)現(xiàn)分組統(tǒng)計(jì)，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、成都小程序開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了洛隆免費(fèi)建站歡迎大家使用！

需求：求出以產(chǎn)品類別為分組，各個(gè)分組里價(jià)格高的產(chǎn)品信息。

實(shí)現(xiàn)過程如下：

declare @t table(ProductID int,ProductName varchar(20),ProductType varchar(20),Price int)

--測(cè)試數(shù)據(jù)

insert @tselect 1,'name1','P1',3 union allselect 2,'name2','P1',5 union allselect 3,'name3','P2',4 union allselect 4,'name4','P2',4

--做法一：找到每個(gè)組里,價(jià)格較大的值；然后再找出每個(gè)組里價(jià)格等于這個(gè)值的--缺點(diǎn):要進(jìn)行一次join

select t1.* from @t t1 join (select ProductType,  max(Price) Price  from @t  group by ProductType) t2 on t1.ProductType = t2.ProductType where t1.Price = t2.Price order by ProductType

--做法二：利用over()，將統(tǒng)計(jì)信息計(jì)算出來，然后直接篩選結(jié)果集。--over() 可以讓函數(shù)(包括聚合函數(shù))與行一起輸出。

;with cte as( select *, max(Price) over(partition by (ProductType)) MaxPrice from @t)select ProductID,ProductName,ProductType,Price from cte where Price = MaxPrice order by ProductType

--over() 的語法為：over([patition by ] <order by >)。需要注意的是，over() 前面是一個(gè)函數(shù)，如果是聚合函數(shù)，那么order by 不能一起使用。--over() 的另一常用情景是與 row_number() 一起用于分頁。

現(xiàn)在來介紹一下開窗函數(shù)。

窗口函數(shù)OVER（）指定一組行，開窗函數(shù)計(jì)算從窗口函數(shù)輸出的結(jié)果集中各行的值。

開窗函數(shù)不需要使用GROUP BY就可以對(duì)數(shù)據(jù)進(jìn)行分組，還可以同時(shí)返回基礎(chǔ)行的列和聚合列。

1．排名開窗函數(shù)

ROW_NUMBER、DENSE_RANK、RANK、NTILE屬于排名函數(shù)。

排名開窗函數(shù)可以單獨(dú)使用ORDER BY 語句，也可以和PARTITION BY同時(shí)使用。

PARTITION BY用于將結(jié)果集進(jìn)行分組，開窗函數(shù)應(yīng)用于每一組。

ODER BY 指定排名開窗函數(shù)的順序。在排名開窗函數(shù)中必須使用ORDER BY語句。

例如查詢每個(gè)雇員的定單，并按時(shí)間排序

WITH OrderInfo AS( SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY OrderDate) AS Number, OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK)) SELECT Number,OrderID,CustomerID, EmployeeID ,OrderDateFrom OrderInfo WHERE Number BETWEEN 0 AND 10

窗口函數(shù)根據(jù)PARTITION BY語句按雇員ID對(duì)數(shù)據(jù)行分組，然后按照ORDER BY 語句排序，排名函數(shù)ROW_NUMBER()為每一組的數(shù)據(jù)分從1開始生成一個(gè)序號(hào)。

ROW_NUMBER（）為每一組的行按順序生成一個(gè)的序號(hào)

RANK（）也為每一組的行生成一個(gè)序號(hào)，與ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值會(huì)生成相同的序號(hào)，并且接下來的序號(hào)是不連序的。例如兩個(gè)相同的行生成序號(hào)3，那么接下來會(huì)生成序號(hào)5。

DENSE_RANK（）和RANK（）類似，不同的是如果有相同的序號(hào)，那么接下來的序號(hào)不會(huì)間斷。也就是說如果兩個(gè)相同的行生成序號(hào)3，那么接下來生成的序號(hào)還是4。

NTILE (integer_expression) 按照指定的數(shù)目將數(shù)據(jù)進(jìn)行分組，并為每一組生成一個(gè)序號(hào)。

2．聚合開窗函數(shù)

很多聚合函數(shù)都可以用作窗口函數(shù)的運(yùn)算，如SUM,AVG,MAX,MIN。

聚合開窗函數(shù)只能使用PARTITION BY子句或都不帶任何語句，ORDER BY不能與聚合開窗函數(shù)一同使用。

例如，查詢雇員的定單總數(shù)及定單信息

WITH OrderInfo AS(SELECT COUNT(OrderID) OVER(PARTITION BY EmployeeID) AS TotalCount,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK))SELECT OrderID,CustomerID, EmployeeID ,OrderDate,TotalCountFrom OrderInfo ORDER BY EmployeeID

如果窗口函數(shù)不使用PARTITION BY 語句的話，那么就是不對(duì)數(shù)據(jù)進(jìn)行分組，聚合函數(shù)計(jì)算所有的行的值。

WITH OrderInfo AS( SELECT COUNT(OrderID) OVER() AS Count,OrderID,CustomerID, EmployeeID,OrderDate FROM Orders (NOLOCK))

上述內(nèi)容就是sql server中怎么使用over()函數(shù)實(shí)現(xiàn)分組統(tǒng)計(jì)，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

分享題目：sqlserver中怎么使用over()函數(shù)實(shí)現(xiàn)分組統(tǒng)計(jì)-創(chuàng)新互聯(lián)
本文URL：http://muchs.cn/article22/depdjc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、做網(wǎng)站、服務(wù)器托管、網(wǎng)站設(shè)計(jì)、網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)