GROUPING_ID (T-SQL)

Artikel
10/16/2024

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

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.

Konvensi sintaks transact-SQL

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 = 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 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 Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

J. Menggunakan GROUPING_ID untuk mengidentifikasi tingkat pengelompokan

Contoh berikut mengembalikan jumlah karyawan menurut Name dan Title, dan total perusahaan dalam AdventureWorks2022 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	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	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	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	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

Bagikan melalui

GROUPING_ID (T-SQL)

Sintaks

Argumen

<column_expression>

Jenis yang dikembalikan

Keterangan

Membandingkan GROUPING_ID() dengan GROUPING()

Definisi teknis GROUPING_ID()

GROUPING_ID() setara

Pernyataan A

Pernyataan B

Contoh

J. Menggunakan GROUPING_ID untuk mengidentifikasi tingkat pengelompokan

B. Menggunakan GROUPING_ID untuk memfilter kumpulan hasil

Contoh dasar

Contoh kompleks

C. Gunakan GROUPING_ID() dengan ROLLUP dan CUBE untuk mengidentifikasi tingkat pengelompokan

Contoh ROLLUP

Contoh KUBUS

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

GROUPING_ID (T-SQL)

Sintaks

Argumen

<column_expression>

Jenis yang dikembalikan

Keterangan

Membandingkan GROUPING_ID() dengan GROUPING()

Definisi teknis GROUPING_ID()

GROUPING_ID() setara

Pernyataan A

Pernyataan B

Contoh

J. Menggunakan GROUPING_ID untuk mengidentifikasi tingkat pengelompokan

B. Menggunakan GROUPING_ID untuk memfilter kumpulan hasil

Contoh dasar

Contoh kompleks

C. Gunakan GROUPING_ID() dengan ROLLUP dan CUBE untuk mengidentifikasi tingkat pengelompokan

Contoh ROLLUP

Contoh KUBUS

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: