Menata kueri dengan ekspresi tabel umum

Saat bekerja dengan kueri kompleks, Anda sering kali perlu memecah logika menjadi bagian yang dapat dikelola atau mereferensikan subkueri yang sama beberapa kali. Ekspresi Tabel Umum (CTA) menyediakan cara untuk menentukan kumpulan hasil bernama sementara yang hanya ada selama satu kueri, membuat kode Anda lebih mudah dibaca dan dipertahankan.

Memahami sintaks CTE

Ekspresi Tabel Umum ditentukan menggunakan WITH klausa, diikuti dengan nama CTE, daftar kolom opsional, dan kueri yang menentukan kumpulan hasil. CTE kemudian dapat dirujuk dalam pernyataan SELECT, INSERT, UPDATE, atau DELETE berikutnya.

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

CTA menawarkan beberapa keuntungan dibandingkan dengan tabel turunan dan subkueri:

• Keterbacaan yang ditingkatkan: Kueri kompleks menjadi lebih mudah dipahami saat dipecah menjadi bagian logis bernama
• Kemampuan referensi mandiri: CTE rekursif dapat mereferensikan dirinya sendiri, memungkinkan traversal data hierarkis
• Beberapa referensi: Mereferensikan CTE yang sama beberapa kali di kueri luar tanpa mendefinisikannya kembali
• Desain modular: Membangun kueri kompleks secara bertahap dengan menentukan beberapa CTE

Membuat CTEs nonrekursif

CTE nonrekursif menentukan kumpulan hasil berdasarkan kueri sederhana yang tidak mengacu pada dirinya sendiri. Pola ini berguna untuk menyederhanakan gabungan yang kompleks, memecah perhitungan multi-langkah, atau meningkatkan organisasi kode.

Contoh berikut menggunakan CTE untuk menghitung metrik penjualan sebelum bergabung dengan informasi produk:

WITH SalesSummary AS
(
    SELECT 
        ProductID,
        SUM(OrderQty) AS TotalQuantity,
        SUM(LineTotal) AS TotalRevenue,
        COUNT(DISTINCT SalesOrderID) AS OrderCount
    FROM SalesLT.SalesOrderDetail
    GROUP BY ProductID
)
SELECT 
    p.Name AS ProductName,
    p.ProductNumber,
    p.ListPrice,
    ss.TotalQuantity,
    ss.TotalRevenue,
    ss.OrderCount,
    ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
    ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;

Kueri ini pertama-tama membuat CTE bernama SalesSummary yang menggabungkan detail pesanan berdasarkan produk, menghitung jumlah total yang terjual, pendapatan total, dan jumlah pesanan. Kueri utama kemudian menggabungkan CTE ini dengan Product tabel untuk menampilkan nama produk bersama metrik penjualan mereka. Fungsi ini NULLIF mencegah pembagian dengan nol saat menghitung harga satuan rata-rata.

Anda dapat menentukan beberapa CTE dalam satu WITH klausa dengan memisahkannya dengan koma. Nantinya CTEs dapat mereferensikan yang lebih lama, memungkinkan transformasi data progresif:

WITH CategorySales AS
(
    SELECT 
        p.ProductCategoryID,
        SUM(sod.LineTotal) AS CategoryRevenue
    FROM SalesLT.Product AS p
    INNER JOIN SalesLT.SalesOrderDetail AS sod
        ON p.ProductID = sod.ProductID
    GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
    SELECT 
        ProductCategoryID,
        CategoryRevenue,
        RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
    FROM CategorySales
)
SELECT 
    pc.Name AS CategoryName,
    rc.CategoryRevenue,
    rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
    ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;

Membuat CTE rekursif

CTE rekursif mereferensikan dirinya sendiri untuk memproses struktur data hierarkis atau mirip grafik. CTE rekursif terdiri dari dua bagian: anggota jangkar yang menyediakan set hasil awal, dan anggota rekursif yang mereferensikan CTE untuk membangun berdasarkan hasil sebelumnya.

Sintaks umum untuk CTE rekursif adalah:

WITH RecursiveCTE AS
(
    -- Anchor member: starting point
    SELECT columns
    FROM table
    WHERE starting_condition

    UNION ALL

    -- Recursive member: references the CTE
    SELECT columns
    FROM table
    INNER JOIN RecursiveCTE
        ON join_condition
)
SELECT * FROM RecursiveCTE;

Contoh penggunaan umum adalah menelusuri hierarki organisasi. Pertimbangkan tabel karyawan di mana setiap karyawan memiliki manajer:

WITH EmployeeHierarchy AS
(
    -- Anchor: Start with top-level managers (no manager)
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: Find employees who report to previously found employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM HumanResources.Employee AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

Hasilkan urutan dengan CTEs rekursif

CTEs rekursif unggul dalam menghasilkan urutan angka atau tanggal tanpa memerlukan tabel angka fisik. Teknik ini berguna untuk membuat rentang tanggal, mengisi celah dalam data, atau menghasilkan data pengujian:

-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
    -- Anchor: Get the first day of the current month
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue

    UNION ALL

    -- Recursive: Add one day until we reach the end of the month
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31);  -- Allow up to 31 iterations (max days in a month)

Anda dapat menggabungkan urutan yang dihasilkan dengan data aktual untuk mengidentifikasi celah atau membuat laporan ringkasan:

-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
    -- Anchor: Start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: Increment until we reach 1000
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
    SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
    FROM Numbers
    WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT 
    dr.OrderDate,
    COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
    ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366);  -- Allow up to 366 iterations (leap year)

Gunakan CTE dengan pernyataan modifikasi data

CTEs dapat digunakan dengan INSERT, UPDATE, dan DELETE pernyataan, bukan hanya SELECT. Kemampuan ini berguna ketika logika modifikasi memerlukan pemfilteran atau perhitungan yang kompleks:

-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
    SELECT ProductID
    FROM SalesLT.Product
    WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
        AND ProductID NOT IN (
            SELECT DISTINCT ProductID 
            FROM SalesLT.SalesOrderDetail
            WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
        )
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);

Kueri ini menggunakan CTE untuk mengidentifikasi produk yang harus ditandai sebagai dihentikan. CTE menemukan produk di mana tanggal akhir penjualan lebih dari dua tahun yang lalu dan yang tidak muncul dalam rincian pesanan mana pun yang dimodifikasi dalam setahun terakhir. Pernyataan UPDATE kemudian menetapkan DiscontinuedDate untuk produk-produk tersebut. Dengan memisahkan logika pemilihan menjadi CTE, kueri menjadi lebih mudah dibaca dan diuji secara independen.

Untuk informasi selengkapnya tentang Ekspresi Tabel Umum, lihat WITH common_table_expression (Transact-SQL) dan Kueri Rekursif Menggunakan Ekspresi Tabel Umum.