GROUPING_ID (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Adalah fungsi yang menghitung tingkat pengelompokan. GROUPING_ID hanya dapat digunakan dalam daftar pilih> SELECT<, HAVING, atau klausul ORDER BY saat GROUP BY ditentukan.
Sintaksis
GROUPING_ID ( <column_expression>[ ,...n ] )
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
<column_expression>
Adalah column_expression dalam klausa GROUP BY .
Jenis Hasil
int
Keterangan
GROUPING_ID <column_expression> harus sama persis dengan ekspresi dalam daftar GROUP BY. Misalnya, jika Anda mengelompokkan menurut DATEPART (yyyy, <nama> kolom), gunakan GROUPING_ID (DATEPART (yyyy,< nama> kolom)); atau jika Anda mengelompokkan menurut <nama> kolom, gunakan GROUPING_ID (<nama> kolom).
Membandingkan GROUPING_ID () dengan PENGELOMPOKAN ()
GROUPING_ID (<column_expression> [ , , ...n ]) memasukkan setara dengan pengembalian GROUPING (<column_expression>) untuk setiap kolom dalam daftar kolomnya di setiap baris output sebagai string yang satu dan nol. GROUPING_ID menginterpretasikan string tersebut sebagai angka base-2 dan mengembalikan bilangan bulat yang setara. Misalnya, pertimbangkan pernyataan berikut: SELECT a, b, c, SUM(d),``GROUPING_ID(a,b,c)``FROM T GROUP BY <group by list>. Tabel berikut ini memperlihatkan nilai input dan output GROUPING_ID ().
| Kolom diagregasi | GROUPING_ID (a, b, c) input = GROUPING(a) + GROUPING(b) + GROUPING(c) | output GROUPING_ID () |
|---|---|---|
a |
100 |
4 |
b |
010 |
2 |
c |
001 |
1 |
ab |
110 |
6 |
ac |
101 |
5 |
bc |
011 |
3 |
abc |
111 |
7 |
Definisi Teknis GROUPING_ID ()
Setiap argumen GROUPING_ID harus merupakan elemen dari daftar GROUP BY. GROUPING_ID () mengembalikan bitmap bilangan bulat yang bit N terendahnya mungkin menyala. Bit terang menunjukkan argumen yang sesuai bukan kolom pengelompokan untuk baris output yang diberikan. Bit urutan terendah sesuai dengan argumen N, dan bit urutan terendah ke-N-1 sesuai dengan argumen 1.
GROUPING_ID () Setara
Untuk kueri pengelompokan tunggal, GROUPING (<column_expression>) setara dengan GROUPING_ID (<column_expression>), dan keduanya mengembalikan 0.
Misalnya, pernyataan berikut setara:
Pernyataan A:
SELECT GROUPING_ID(A,B)
FROM T
GROUP BY CUBE(A,B)
Pernyataan B:
SELECT 3 FROM T GROUP BY ()
UNION ALL
SELECT 1 FROM T GROUP BY A
UNION ALL
SELECT 2 FROM T GROUP BY B
UNION ALL
SELECT 0 FROM T GROUP BY A,B
Contoh
J. Menggunakan GROUPING_ID untuk mengidentifikasi tingkat pengelompokan
Contoh berikut mengembalikan jumlah karyawan menurut Name dan Title, Name, dan total perusahaan dalam database AdventureWorks2022. GROUPING_ID() digunakan untuk membuat nilai untuk setiap baris dalam Title kolom yang mengidentifikasi tingkat agregasinya.
SELECT D.Name
,CASE
WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle
WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name
WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:'
ELSE N'Unknown'
END AS N'Job Title'
,COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.BusinessEntityID = DH.BusinessEntityID
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.JobTitle);
B. Menggunakan GROUPING_ID untuk memfilter kumpulan hasil
Contoh sederhana
Dalam kode berikut, untuk mengembalikan hanya baris yang memiliki jumlah karyawan berdasarkan judul, hapus karakter komentar dari HAVING GROUPING_ID(D.Name, E.JobTitle); = 0 dalam database AdventureWorks2022. Untuk mengembalikan hanya baris dengan jumlah karyawan menurut departemen, hapus karakter komentar dari HAVING GROUPING_ID(D.Name, E.JobTitle) = 1;.
SELECT D.Name
,E.JobTitle
,GROUPING_ID(D.Name, E.JobTitle) AS 'Grouping Level'
,COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee AS E
INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH
ON E.BusinessEntityID = DH.BusinessEntityID
INNER JOIN HumanResources.Department AS D
ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.JobTitle)
--HAVING GROUPING_ID(D.Name, E.JobTitle) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.JobTitle) = 1; --Group by Name;
Berikut adalah tataan hasil yang tidak difilter.
| Nama | Judul | Tingkat Pengelompokan | Jumlah Karyawan | Nama |
|---|---|---|---|---|
| Kontrol Dokumen | Spesialis Kontrol | 0 | 2 | Kontrol Dokumen |
| Kontrol Dokumen | Asisten Kontrol Dokumen | 0 | 2 | Kontrol Dokumen |
| Kontrol Dokumen | Pengelola Kontrol Dokumen | 0 | 1 | Kontrol Dokumen |
| Kontrol Dokumen | NULL | 1 | 5 | Kontrol Dokumen |
| Fasilitas dan Pemeliharaan | Asisten Administratif Fasilitas | 0 | 1 | Fasilitas dan Pemeliharaan |
| Fasilitas dan Pemeliharaan | Manajer Fasilitas | 0 | 1 | Fasilitas dan Pemeliharaan |
| Fasilitas dan Pemeliharaan | Petugas kebersihan | 0 | 4 | Fasilitas dan Pemeliharaan |
| Fasilitas dan Pemeliharaan | Pengawas Pemeliharaan | 0 | 1 | Fasilitas dan Pemeliharaan |
| Fasilitas dan Pemeliharaan | NULL | 1 | 7 | Fasilitas dan Pemeliharaan |
| NULL | NULL | 3 | 12 | NULL |
Contoh Kompleks
Contoh berikut menggunakan GROUPING_ID() untuk memfilter kumpulan hasil yang berisi beberapa tingkat pengelompokan menurut tingkat pengelompokan. Kode serupa dapat digunakan untuk membuat tampilan yang memiliki beberapa tingkat pengelompokan dan prosedur tersimpan yang memanggil tampilan dengan meneruskan parameter yang memfilter tampilan dengan mengelompokkan tingkat. Contohnya menggunakan database AdventureWorks2022.
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 P.FirstName + ' ' + P.LastName
FROM Person.Person AS P
WHERE P.BusinessEntityID = 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 AS C
INNER JOIN Sales.Store AS S
ON C.StoreID = S.BusinessEntityID
INNER JOIN Sales.SalesTerritory AS T
ON C.TerritoryID = T.TerritoryID
INNER JOIN Sales.SalesOrderHeader AS H
ON C.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. Menggunakan GROUPING_ID () dengan ROLLUP dan CUBE untuk mengidentifikasi tingkat pengelompokan
Kode dalam contoh berikut menunjukkan penggunaan GROUPING() untuk menghitung Bit Vector(base-2) kolom. GROUPING_ID() digunakan untuk menghitung kolom yang Integer Equivalent sesuai. Urutan kolom dalam GROUPING_ID() fungsi adalah kebalikan dari urutan kolom kolom yang digabungkan oleh GROUPING() fungsi.
Dalam contoh ini, GROUPING_ID() digunakan untuk membuat nilai untuk setiap baris dalam Grouping Level kolom untuk mengidentifikasi tingkat pengelompokan. Tingkat pengelompokan tidak selalu merupakan daftar bilangan bulat berturut-turut yang dimulai dengan 1 (0, 1, 2,...n).
Catatan
PENGELOMPOKAN dan GROUPING_ID dapat digunakan n klausa HAVING untuk memfilter kumpulan hasil.
Contoh ROLLUP
Dalam contoh ini, semua tingkat pengelompokan tidak muncul seperti yang mereka lakukan dalam contoh CUBE berikut. Jika urutan kolom dalam ROLLUP daftar diubah, nilai tingkat dalam Grouping Level kolom juga harus diubah. Contohnya menggunakan database AdventureWorks2022.
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'2007',N'2008')
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);
Berikut adalah kumpulan hasil parsial.
| Year | Month | Day | Total Jatuh Tempo | Bit Vector (base-2) | Setara Bilangan Bulat | Tingkat Pengelompokan |
|---|---|---|---|---|---|---|
| 2007 | 1 | 1 | 1497452.6066 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 1 | 2 | 21772.3494 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 2 | 1 | 2705653.5913 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 2 | 2 | 21684.4068 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 1 | 1 | 1908122.0967 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 1 | 2 | 46458.0691 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 2 | 1 | 3108771.9729 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 2 | 2 | 54598.5488 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 1 | NULL | 1519224.956 | 100 | 1 | Bulan Tahun |
| 2007 | 2 | NULL | 2727337.9981 | 100 | 1 | Bulan Tahun |
| 2008 | 1 | NULL | 1954580.1658 | 100 | 1 | Bulan Tahun |
| 2008 | 2 | NULL | 3163370.5217 | 100 | 1 | Bulan Tahun |
| 2007 | NULL | NULL | 4246562.9541 | 110 | 3 | Year |
| 2008 | NULL | NULL | 5117950.6875 | 110 | 3 | Year |
| NULL | NULL | NULL | 9364513.6416 | 111 | 7 | Total Keseluruhan |
Contoh KUBUS
Dalam contoh ini, GROUPING_ID() fungsi digunakan untuk membuat nilai untuk setiap baris dalam Grouping Level kolom untuk mengidentifikasi tingkat pengelompokan.
Tidak seperti ROLLUP dalam contoh sebelumnya, CUBE menghasilkan semua tingkat pengelompokan. Jika urutan kolom dalam CUBE daftar diubah, nilai tingkat dalam Grouping Level kolom juga harus diubah. Contoh menggunakan database AdventureWorks2022
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'2007',N'2008')
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);
Berikut adalah kumpulan hasil parsial.
| Year | Month | Day | Total Jatuh Tempo | Bit Vector (base-2) | Setara Bilangan Bulat | Tingkat Pengelompokan |
|---|---|---|---|---|---|---|
| 2007 | 1 | 1 | 1497452.6066 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 1 | 2 | 21772.3494 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 2 | 1 | 2705653.5913 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 2 | 2 | 21684.4068 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 1 | 1 | 1908122.0967 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 1 | 2 | 46458.0691 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 2 | 1 | 3108771.9729 | 000 | 0 | Hari Bulan Tahun |
| 2008 | 2 | 2 | 54598.5488 | 000 | 0 | Hari Bulan Tahun |
| 2007 | 1 | NULL | 1519224.956 | 100 | 1 | Bulan Tahun |
| 2007 | 2 | NULL | 2727337.9981 | 100 | 1 | Bulan Tahun |
| 2008 | 1 | NULL | 1954580.1658 | 100 | 1 | Bulan Tahun |
| 2008 | 2 | NULL | 3163370.5217 | 100 | 1 | Bulan Tahun |
| 2007 | NULL | 1 | 4203106.1979 | 010 | 2 | Hari Tahun |
| 2007 | NULL | 2 | 43456.7562 | 010 | 2 | Hari Tahun |
| 2008 | NULL | 1 | 5016894.0696 | 010 | 2 | Hari Tahun |
| 2008 | NULL | 2 | 101056.6179 | 010 | 2 | Hari Tahun |
| 2007 | NULL | NULL | 4246562.9541 | 110 | 3 | Year |
| 2008 | NULL | NULL | 5117950.6875 | 110 | 3 | Year |
| NULL | 1 | 1 | 3405574.7033 | 001 | 4 | Bulan Hari |
| NULL | 1 | 2 | 68230.4185 | 001 | 4 | Bulan Hari |
| NULL | 2 | 1 | 5814425.5642 | 001 | 4 | Bulan Hari |
| NULL | 2 | 2 | 76282.9556 | 001 | 4 | Bulan Hari |
| NULL | 1 | NULL | 3473805.1218 | 101 | 5 | Month |
| NULL | 2 | NULL | 5890708.5198 | 101 | 5 | Month |
| NULL | NULL | 1 | 9220000.2675 | 011 | 6 | Day |
| NULL | NULL | 2 | 144513.3741 | 011 | 6 | Day |
| NULL | NULL | NULL | 9364513.6416 | 111 | 7 | Total Keseluruhan |
Lihat Juga
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk