Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceDatabase SQL di Microsoft Fabric
GROUPING_ID Adalah fungsi yang menghitung tingkat pengelompokan.
GROUPING_ID hanya dapat digunakan dalam SELECT <select> daftar, HAVING, atau ORDER BY klausa ketika GROUP BY ditentukan.
Sintaks
GROUPING_ID ( <column_expression> [ , ...n ] )
Argumen
<column_expression>
Column_expression dalam klausa SELECT - GROUP BY.
Jenis yang dikembalikan
int
Keterangan
GROUPING_ID <column_expression> harus sama persis dengan ekspresi dalam GROUP BY daftar. Misalnya, jika Anda mengelompokkan menurut DATEPART (yyyy, <column name>), gunakan GROUPING_ID (DATEPART (yyyy, <column name>)); atau jika Anda mengelompokkan menurut <column name>, gunakan GROUPING_ID (<column name>).
Membandingkan GROUPING_ID() dengan GROUPING()
GROUPING_ID (<column_expression> [ , ...n ]) memasukkan yang setara GROUPING (<column_expression>) dengan pengembalian untuk setiap kolom dalam daftar kolomnya di setiap baris output, sebagai string yang satu dan nol.
GROUPING_ID menafsirkan 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 ini memperlihatkan GROUPING_ID() nilai input dan output.
| Kolom diagregasi | GROUPING_ID (a, b, c) input = PENGELOMPOKAN(a) + PENGELOMPOKAN(b) + PENGELOMPOKAN(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 GROUPING_ID argumen harus merupakan elemen dari GROUP BY daftar.
GROUPING_ID()mengembalikan bitmap bilangan bulat yang bit n terendahnya mungkin menyala. Bit menyala 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
Sampel kode dalam artikel ini menggunakan database sampel AdventureWorks2025 atau AdventureWorksDW2025, yang dapat Anda unduh dari halaman beranda Sampel dan Proyek Komunitas Microsoft SQL Server.
J. Menggunakan GROUPING_ID untuk mengidentifikasi tingkat pengelompokan
Contoh berikut mengembalikan jumlah karyawan menurut Name dan Title, dan total perusahaan dalam AdventureWorks2025 database.
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 dasar
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;. 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 [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 tingkat pengelompokan.
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. Gunakan 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
GROUPING dan GROUPING_ID dapat digunakan dalam HAVING klausul untuk memfilter tataan hasil.
Contoh ROLLUP
Dalam contoh ini, semua tingkat pengelompokan tidak muncul seperti yang mereka lakukan dalam contoh berikut CUBE . Jika urutan kolom dalam ROLLUP daftar diubah, nilai tingkat dalam Grouping Level kolom juga harus diubah.
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 hasil parsial yang ditetapkan.
| Tahun | Month | Hari | Total Jatuh Tempo | Vektor Bit (basis-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 | Tahun |
| 2008 | NULL |
NULL |
5117950.6875 | 110 | 3 | Tahun |
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.
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 hasil parsial yang ditetapkan.
| Tahun | Month | Hari | Total Jatuh Tempo | Vektor Bit (basis-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 | Tahun |
| 2008 | NULL |
NULL |
5117950.6875 | 110 | 3 | Tahun |
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 | Hari |
NULL |
NULL |
2 | 144513.3741 | 011 | 6 | Hari |
NULL |
NULL |
NULL |
9364513.6416 | 111 | 7 | Total Keseluruhan |