GROUPING_ID (Transact-SQL)
Gruplandırma düzey hesaplayan bir işlev olarak belirtilir.GROUPING_ID SEÇME yalnızca kullanılabilir. <SELECT> HAVING, liste veya yan tümceleri, GROUP BY belirtildiğinde ORDER.
GROUPING_ID ( <column_expression>[ ,...n ] )
Bağımsız değişkenler
- <column_expression>
Iş bir column_expression ' de bir GÖRE GRUPLANDIR yan tümce.
Dönüş Türü
int
Remarks
GROUPING_ID <column_expression> GROUP BY listesinde ifade tam olarak eşleşmelidir. Örneğin, DATEPART (yyyy, göre gruplandırıyorsanız <column name>), (DATEPART (yyyy, GROUPING_ID kullanın. <column name>)); veya göre gruplandırıyorsanız <column name>, GROUPING_ID () kullanın.<column name>).
gruplandırma (') için GROUPING_ID ()'ni karşılaştırma
GROUPING_ID (<column_expression> [ ,...n ]) girişleri (< column_expression >) gruplandırma eşdeğeridir dönmek her çıktı satır, sütun listesinden her sütun için bir dize olarak yorumlar ve sıfır. GROUPING_ID bu dizeyi 2 tabanındaki bir sayı olarak yorumlar ve eşdeğer bir tamsayý gönderir.Örneğin aşağıdaki deyim göz önünde bulundurun: SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>.The following table shows the GROUPING_ID () input and output values.
Toplanan sütunlar |
Giriş GROUPING_ID (a, b, c) gruplandırma(a) + gruplandırma(b) + gruplandırma(c) = |
GROUPING_ID () çıkış |
---|---|---|
a |
100 |
4 |
b |
010 |
2 |
c |
001 |
1 |
ab |
110 |
6 |
ac |
101 |
5 |
bc |
011 |
3 |
abc |
111 |
7 |
Teknik tanımı GROUPING_ID (.)
Her GROUPING_ID bağımsız değişkeni, GROUP BY listesinde bir öğe olmalıdır.GROUPING_ID () işlevi bir integer bit eşlemi, en düşük N bit LED. Bir bölme bit gruplama sütun belirli bir çıkış satırına karşılık gelen bağımsız değişkeni olduğunu gösterir. En düşük-sipariş bit N ve N-1, bağımsız değişkenine karşılık gelirTH en düşük sıra bit 1 bağımsız değişkenine karşılık gelir.
Eşdeğerleri GROUPING_ID)
(Tek bir gruplandırma sorgusu, GRUPLANDıRMA<column_expression>) için GROUPING_ID (eşdeğerdir<column_expression>), ve her ikisi de 0 döndürür.
Örneğin, aşağıdaki ifadeler birbirleriyle eşdeğerdir:
|
|
Örnekler
C.Gruplandırma düzeyi belirlemek için GROUPING_ID kullanma
The following example returns the count of employees by Name and Title, Name, and company total.GROUPING_ID() is used to create a value for each row in the Title column that identifies its level of aggregation.
USE AdventureWorks;
GO
SELECT D.Name
,CASE
WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name
WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
ELSE N'Unknown'
END AS N'Title'
,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.EmployeeID = DH.EmployeeID
INNER JOIN HumanResources.Department D
ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title);
b.Bir sonuç süzmek için GROUPING_ID kullanarak küme
Basit örneği
Aşağıdaki kodda, başlığa göre çalışanların sayısı olan satırları dönmek için , açıklama karakterlerinden kaldırın. HAVING GROUPING_ID(D.Name, E.Title); = 0. Bölüme göre yalnızca çalışanların sayısı olan satırları dönmek için , açıklama karakterlerinden kaldırın. HAVING GROUPING_ID(D.Name, E.Title) = 1;.
USE AdventureWorks;
GO
SELECT D.Name
,E.Title
,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.EmployeeID = DH.EmployeeID
INNER JOIN HumanResources.Department D
ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 1; --Group by Name
Filtre uygulanmamış sonucu işte küme.
Name |
Başlık |
Gruplandırma düzey |
Çalışan sayısı |
Name |
---|---|---|---|---|
Belge denetimi |
Denetim uzmanı |
0 |
2 |
Belge denetimi |
Belge denetimi |
Belge denetimi Yardımcısı |
0 |
2 |
Belge denetimi |
Belge denetimi |
Belge Denetimi Yöneticisi |
0 |
1 |
Belge denetimi |
Belge denetimi |
null |
1 |
5 |
Belge denetimi |
Tesis ve Bakım |
Özellikleri Yönetimsel Yardımcısı |
0 |
1 |
Tesis ve Bakım |
Tesis ve Bakım |
Tesisler Yöneticisi |
0 |
1 |
Tesis ve Bakım |
Tesis ve Bakım |
Janitor |
0 |
4 |
Tesis ve Bakım |
Tesis ve Bakım |
Bakım gözetmen |
0 |
1 |
Tesis ve Bakım |
Tesis ve Bakım |
null |
1 |
7 |
Tesis ve Bakım |
null |
null |
3 |
12 |
null |
Karmaşık örnek
Aşağıdaki örnekte GROUPING_ID() bir sonuç süzmek için kullanılan küme birden çok gruplama düzey gruplandırma düzey olarak içerir. Benzer bir kod olan birden fazla grup düzey gruplandırma düzeyine göre görünüm süzgeçleri bir parametre ileterek görünümü çağıran bir saklı yordam bir görünümünü oluşturmak için kullanılır.
USE AdventureWorks;
GO
DECLARE @Grouping nvarchar(50);
DECLARE @GroupingLevel smallint;
SET @Grouping = N'CountryRegionCode Total';
SELECT @GroupingLevel = (
CASE @Grouping
WHEN N'Grand Total' THEN 15
WHEN N'SalesPerson Total' THEN 14
WHEN N'Store Total' THEN 13
WHEN N'Store SalesPerson Total' THEN 12
WHEN N'CountryRegionCode Total' THEN 11
WHEN N'Group Total' THEN 7
ELSE N'Unknown'
END);
SELECT
T.[Group]
,T.CountryRegionCode
,S.Name AS N'Store'
,(SELECT C.FirstName + ' ' + C.LastName
FROM Person.Contact C
WHERE C.ContactId = H.SalesPersonID)
AS N'Sales Person'
,SUM(TotalDue)AS N'TotalSold'
,CAST(GROUPING(T.[Group])AS char(1)) +
CAST(GROUPING(T.CountryRegionCode)AS char(1)) +
CAST(GROUPING(S.Name)AS char(1)) +
CAST(GROUPING(H.SalesPersonID)AS char(1))
AS N'GROUPING base-2'
,GROUPING_ID((T.[Group])
,(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
) AS N'GROUPING_ID'
,CASE
WHEN GROUPING_ID(
(T.[Group]),(T.CountryRegionCode)
,(S.Name),(H.SalesPersonID)
) = 15 THEN N'Grand Total'
WHEN GROUPING_ID(
(T.[Group]),(T.CountryRegionCode)
,(S.Name),(H.SalesPersonID)
) = 14 THEN N'SalesPerson Total'
WHEN GROUPING_ID(
(T.[Group]),(T.CountryRegionCode)
,(S.Name),(H.SalesPersonID)
) = 13 THEN N'Store Total'
WHEN GROUPING_ID(
(T.[Group]),(T.CountryRegionCode)
,(S.Name),(H.SalesPersonID)
) = 12 THEN N'Store SalesPerson Total'
WHEN GROUPING_ID(
(T.[Group]),(T.CountryRegionCode)
,(S.Name),(H.SalesPersonID)
) = 11 THEN N'CountryRegionCode Total'
WHEN GROUPING_ID(
(T.[Group]),(T.CountryRegionCode)
,(S.Name),(H.SalesPersonID)
) = 7 THEN N'Group Total'
ELSE N'Error'
END AS N'Level'
FROM Sales.Customer C
INNER JOIN Sales.Store S
ON C.CustomerID = S.CustomerID
INNER JOIN Sales.SalesTerritory T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader H
ON S.CustomerID = H.CustomerID
GROUP BY GROUPING SETS ((S.Name,H.SalesPersonID)
,(H.SalesPersonID),(S.Name)
,(T.[Group]),(T.CountryRegionCode),()
)
HAVING GROUPING_ID(
(T.[Group]),(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
) = @GroupingLevel
ORDER BY
GROUPING_ID(S.Name,H.SalesPersonID),GROUPING_ID((T.[Group])
,(T.CountryRegionCode)
,(S.Name)
,(H.SalesPersonID))ASC;
c.Gruplandırma düzeyi belirlemek için GROUPING_ID (') küp ROLLUP ile kullanma
The code in the following examples show using GROUPING() to compute the Bit Vector(base-2) column.GROUPING_ID() is used to compute the corresponding Integer Equivalent column.Sütun sırasını GROUPING_ID() işlev tarafından birleştirilmiş sütun sütun sırasını tersidir GROUPING() Function.
Bu örneklerde, GROUPING_ID() Her satır için bir değer oluşturmak için kullanılan Grouping Level sütun tanımlamak için düzey gruplandırmanın. Gruplandırma düzeyi her zaman tamsayı (0, 1, 2,... 1 ile başlayan ardışık bir listesi değildir n).
Not
gruplandırma ve gruplandırma_ID kullanılan n bir HAVING yan tümce, sonuç süzülecek olabilir küme.
ROLLUP örneği
Bunlar aşağıdaki küp örnekte olduğu gibi bu örnekte, tüm gruplandırma düzeyi görünmüyor.Varsa sütunların düzenini ROLLUP liste, düzey değerleri değiştiğinde Grouping Level sütun da değiştirilmesi gerekir.
USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,DATEPART(mm,OrderDate) AS N'Month'
,DATEPART(dd,OrderDate) AS N'Day'
,SUM(TotalDue) AS N'Total Due'
,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) +
CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) +
CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1))
AS N'Bit Vector(base-2)'
,GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
AS N'Integer Equivalent'
,CASE
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 0 THEN N'Year Month Day'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 1 THEN N'Year Month'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 2 THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 3 THEN N'Year'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 4 THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 5 THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 6 THEN N'not used'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 7 THEN N'Grand Total'
ELSE N'Error'
END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
AND DATEPART(mm,OrderDate) IN(1,2)
AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY ROLLUP(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm,OrderDate)
,DATEPART(yyyy,OrderDate)
,DATEPART(dd,OrderDate)
)
,DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate);
Işte, kısmi bir sonuç kümesidir.
Yıl |
Ay |
Günü |
Toplam süre |
Bit vektörü (2 tabanı) |
Tamsayı eşdeğeri |
Gruplandırma düzey |
---|---|---|---|---|---|---|
2003 |
1 |
1 |
1762381 |
000 |
0 |
Yıl ay gün |
2003 |
1 |
2 |
21772.35 |
000 |
0 |
Yıl ay gün |
2003 |
2 |
1 |
3185233 |
000 |
0 |
Yıl ay gün |
2003 |
2 |
2 |
21684.41 |
000 |
0 |
Yıl ay gün |
2004 |
1 |
1 |
2239208 |
000 |
0 |
Yıl ay gün |
2004 |
1 |
2 |
46458.07 |
000 |
0 |
Yıl ay gün |
2004 |
2 |
1 |
3653194 |
000 |
0 |
Yıl ay gün |
2004 |
2 |
2 |
54598.55 |
000 |
0 |
Yıl ay gün |
2003 |
1 |
null |
1784153 |
100 |
1 |
Yıl ve ay |
2003 |
2 |
null |
3206917 |
100 |
1 |
Yıl ve ay |
2004 |
1 |
null |
2285666 |
100 |
1 |
Yıl ve ay |
2004 |
2 |
null |
3707793 |
100 |
1 |
Yıl ve ay |
2003 |
null |
null |
4991070 |
110 |
3 |
Yıl |
2004 |
null |
null |
5993459 |
110 |
3 |
Yıl |
null |
null |
null |
10984529 |
111 |
7 |
Genel Toplam |
küp örneği
Bu örnekte, GROUPING_ID() Her satır için bir değer oluşturmak için kullanılan işlev Grouping Level sütun, gruplandırma düzeyini tanımlamak için.
Farklı ROLLUP Önceki örnekte, CUBE Tüm gruplandırma düzeyi çıkarır. Varsa sütunların düzenini CUBE liste, düzey değerleri değiştiğinde Grouping Level sütun da değiştirilmesi gerekir.
USE AdventureWorks;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
,DATEPART(mm,OrderDate) AS N'Month'
,DATEPART(dd,OrderDate) AS N'Day'
,SUM(TotalDue) AS N'Total Due'
,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) +
CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) +
CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1))
AS N'Bit Vector(base-2)'
,GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
AS N'Integer Equivalent'
,CASE
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 0 THEN N'Year Month Day'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 1 THEN N'Year Month'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 2 THEN N'Year Day'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 3 THEN N'Year'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 4 THEN N'Month Day'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 5 THEN N'Month'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 6 THEN N'Day'
WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
) = 7 THEN N'Grand Total'
ELSE N'Error'
END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2003',N'2004')
AND DATEPART(mm,OrderDate) IN(1,2)
AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY CUBE(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate)
)
,DATEPART(yyyy,OrderDate)
,DATEPART(mm,OrderDate)
,DATEPART(dd,OrderDate);
Işte, kısmi bir sonuç kümesidir.
Yıl |
Ay |
Günü |
Toplam süre |
Bit vektörü (2 tabanı) |
Tamsayı eşdeğeri |
Gruplandırma düzey |
---|---|---|---|---|---|---|
2003 |
1 |
1 |
1762381 |
000 |
0 |
Yıl ay gün |
2003 |
1 |
2 |
21772.35 |
000 |
0 |
Yıl ay gün |
2003 |
2 |
1 |
3185233 |
000 |
0 |
Yıl ay gün |
2003 |
2 |
2 |
21684.41 |
000 |
0 |
Yıl ay gün |
2004 |
1 |
1 |
2239208 |
000 |
0 |
Yıl ay gün |
2004 |
1 |
2 |
46458.07 |
000 |
0 |
Yıl ay gün |
2004 |
2 |
1 |
3653194 |
000 |
0 |
Yıl ay gün |
2004 |
2 |
2 |
54598.55 |
000 |
0 |
Yıl ay gün |
2003 |
1 |
null |
1784153 |
100 |
1 |
Yıl ve ay |
2003 |
2 |
null |
3206917 |
100 |
1 |
Yıl ve ay |
2004 |
1 |
null |
2285666 |
100 |
1 |
Yıl ve ay |
2004 |
2 |
null |
3707793 |
100 |
1 |
Yıl ve ay |
2003 |
null |
1 |
4947613 |
010 |
2 |
Yılın günü |
2003 |
null |
2 |
43456.76 |
010 |
2 |
Yılın günü |
2004 |
null |
1 |
5892402 |
010 |
2 |
Yılın günü |
2004 |
null |
2 |
101056.6 |
010 |
2 |
Yılın günü |
2003 |
null |
null |
4991070 |
110 |
3 |
Yıl |
2004 |
null |
null |
5993459 |
110 |
3 |
Yıl |
null |
1 |
1 |
4001589 |
001 |
4 |
Ayın günü |
null |
1 |
2 |
68230.42 |
001 |
4 |
Ayın günü |
null |
2 |
1 |
6838427 |
001 |
4 |
Ayın günü |
null |
2 |
2 |
76282.96 |
001 |
4 |
Ayın günü |
null |
1 |
null |
4069819 |
101 |
5 |
Ay |
null |
2 |
null |
6914710 |
101 |
5 |
Ay |
null |
null |
1 |
10840016 |
011 |
6 |
Günü |
null |
null |
2 |
144513.4 |
011 |
6 |
Günü |
null |
null |
null |
10984529 |
111 |
7 |
Genel Toplam |