Bagikan melalui


GROUPING_ID (T-SQL)

Berlaku untuk: SQL ServerAzure 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