Menerapkan fungsi jendela untuk analitik

  • 14 menit

Tip

Lihat tab Teks dan gambar untuk detail selengkapnya!

Kueri analitik sering memerlukan perhitungan yang mencakup beberapa baris sambil tetap mengembalikan detail baris individual. Fungsi agregat tradisional menciutkan baris ke dalam grup, kehilangan informasi tingkat baris. Fungsi jendela menyelesaikan tantangan ini dengan melakukan perhitungan di sekumpulan baris yang terkait dengan baris saat ini, tanpa menciutkan kumpulan hasil.

Memahami sintaks fungsi jendela

Fungsi jendela menghitung nilai di seluruh baris "window" yang ditentukan oleh klausa OVER. Tidak seperti fungsi agregat biasa, fungsi jendela tidak mengelompokkan baris ke dalam satu baris output. Sebaliknya, mereka menghitung nilai di seluruh baris terkait sambil mempertahankan semua baris asli dalam hasilnya.

Sintaks umum untuk fungsi jendela adalah:

function_name(arguments) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression [ASC | DESC]]
    [ROWS | RANGE frame_specification]
)

Komponen OVER klausa mengontrol bagaimana jendela didefinisikan:

  • PARTITION BY: Membagi baris menjadi grup (partisi) untuk perhitungan
  • ORDER BY: Menentukan urutan logis baris dalam setiap partisi
  • ROWS/RANGE: Menentukan batas bingkai relatif terhadap baris saat ini

Kueri berikut menunjukkan fungsi jendela sederhana yang menghitung total jumlah pesanan yang berjalan per pelanggan:

SELECT 
    CustomerID,
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal
FROM SalesLT.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Nota

Saat Anda menentukan ORDER BY dalam OVER klausul tanpa spesifikasi bingkai, bingkai defaultnya adalah RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW untuk fungsi agregat. Ini membuat perhitungan kumulatif.

Menggunakan fungsi peringkat

Fungsi peringkat menetapkan angka berurutan ke baris berdasarkan posisinya dalam partisi. SQL Server menyediakan empat fungsi peringkat. Setiap fungsi menangani ikatan secara berbeda:

ROW_NUMBER() menetapkan angka berurutan unik untuk setiap baris, tanpa duplikat bahkan untuk nilai terikat:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ROW_NUMBER() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Kumpulan hasil terlihat seperti ini:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     2
751         Road-150 Red, 48            3578.27     3
771         Mountain-100 Silver, 38     3399.99     4

Kueri ini memberi peringkat semua produk berdasarkan harga dari tertinggi ke terendah. Setiap produk menerima angka unik terlepas dari apakah beberapa produk memiliki harga yang sama.

RANK() menetapkan peringkat yang sama ke nilai terikat, lalu melompati angka untuk memperhitungkan ikatan:

SELECT 
    ProductID,
    Name,
    ListPrice,
    RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Kumpulan hasil terlihat seperti ini:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     4

Ketika dua produk memiliki harga yang identik, keduanya menerima peringkat yang sama. Peringkat produk berikutnya mencerminkan jumlah total produk yang diberi peringkat lebih tinggi, menciptakan celah dalam urutan.

DENSE_RANK() menetapkan peringkat yang sama ke nilai terikat tetapi tidak melewati angka:

SELECT 
    ProductID,
    Name,
    ListPrice,
    DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS PriceRank
FROM SalesLT.Product
WHERE ListPrice > 0;

Kumpulan hasil terlihat seperti ini:

ProductID   Name                        ListPrice   PriceRank
---------   -------------------------   ---------   ---------
749         Road-150 Red, 62            3578.27     1
750         Road-150 Red, 44            3578.27     1
751         Road-150 Red, 48            3578.27     1
771         Mountain-100 Silver, 38     3399.99     2

Seperti RANK(), nilai terikat memiliki peringkat yang sama. Namun, DENSE_RANK() berlanjut dengan angka berturut-turut berikutnya, sehingga Anda dapat menggunakannya untuk menghitung tingkat harga yang berbeda.

NTILE(n) mendistribusikan baris ke dalam jumlah grup yang ditentukan yang kira-kira sama:

SELECT 
    ProductID,
    Name,
    ListPrice,
    NTILE(4) OVER (ORDER BY ListPrice DESC) AS PriceQuartile
FROM SalesLT.Product
WHERE ListPrice > 0;

Kumpulan hasil terlihat seperti ini:

ProductID   Name                          ListPrice   PriceQuartile
---------   ---------------------------   ---------   -------------
749         Road-150 Red, 62              3578.27     1
771         Mountain-100 Silver, 38       3399.99     1
722         LL Road Frame - Black, 58     337.22      2
859         Half-Finger Gloves, S         24.49       4

Kueri ini membagi produk menjadi empat grup berdasarkan harga. Produk dengan harga tertinggi berada di kuartil 1, dan harga terendah berada di kuartil 4. Gunakan NTILE() untuk analisis persentil atau mendistribusikan pekerjaan secara merata.

Menggabungkan PARTITION BY dengan fungsi peringkat memungkinkan peringkat per grup.

SELECT 
    pc.Name AS Category,
    p.Name AS Product,
    p.ListPrice,
    ROW_NUMBER() OVER (
        PARTITION BY p.ProductCategoryID 
        ORDER BY p.ListPrice DESC
    ) AS CategoryPriceRank
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
    ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 0;

Kumpulan hasil terlihat seperti ini:

Category          Product                     ListPrice   CategoryPriceRank
---------------   -------------------------   ---------   -----------------
Road Bikes        Road-150 Red, 62            3578.27     1
Road Bikes        Road-150 Red, 44            3578.27     2
Mountain Bikes    Mountain-100 Silver, 38     3399.99     1
Mountain Bikes    Mountain-100 Black, 38      3374.99     2

Kueri ini memberi peringkat produk dalam setiap kategori secara terpisah. Peringkat dimulai ulang pada 1 untuk setiap kategori, sehingga Anda dapat mengidentifikasi produk termahal di setiap kategori dengan memfilter untuk CategoryPriceRank = 1.

Tip

Gunakan ROW_NUMBER() saat Anda membutuhkan tepat satu baris per peringkat (seperti menemukan N teratas per grup). Gunakan RANK() atau DENSE_RANK() saat Anda perlu mempertahankan informasi tautan untuk tujuan pelaporan.

Menerapkan fungsi jendela analisis agregat

Fungsi agregat standar seperti SUM, , AVGCOUNT, MIN, dan MAX dapat digunakan sebagai fungsi jendela dengan menambahkan OVER klausa. Ini memungkinkan Anda menghitung agregat sambil mempertahankan detail baris individual.

Kueri berikut menunjukkan cara menghitung total yang berjalan dan agregat kumulatif:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    SUM(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningTotal,
    AVG(TotalDue) OVER (ORDER BY OrderDate, SalesOrderID) AS RunningAverage,
    COUNT(*) OVER (ORDER BY OrderDate, SalesOrderID) AS OrderNumber
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate, SalesOrderID;

Kumpulan hasil terlihat seperti ini:

SalesOrderID   OrderDate    TotalDue    RunningTotal   RunningAverage   OrderNumber
------------   ----------   ---------   ------------   --------------   -----------
71774          2008-06-01   972.785     972.785        972.785          1
71776          2008-06-01   87.083      1059.868       529.934          2
71780          2008-06-01   42452.65    43512.518      14504.172        3
71782          2008-06-01   43962.79    87475.308      21868.827        4

Penting

Saat menggunakan fungsi jendela agregat tanpa ORDER BY dalam OVER klausa, fungsi menghitung di seluruh partisi. Menambahkan ORDER BY membuat penghitungan berjalan dari awal partisi ke baris saat ini.

Tentukan bingkai jendela dengan ROWS dan RANGE

Bingkai jendela memungkinkan Anda menentukan dengan tepat baris mana yang relatif terhadap baris saat ini harus disertakan dalam perhitungan. Klausa ROWS menghitung baris fisik, sementara RANGE mengelompokkan baris dengan nilai yang sama.

Batas bingkai dapat ditentukan menggunakan:

  • UNBOUNDED PRECEDING: Dari awal partisi
  • n PRECEDING: n baris sebelum baris ini
  • CURRENT ROW: Baris saat ini
  • n FOLLOWING: n baris setelah baris saat ini
  • UNBOUNDED FOLLOWING: Ke akhir partisi

Kueri berikut menghitung rata-rata bergerak selama tiga pesanan terakhir:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    AVG(TotalDue) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS MovingAvg3Orders
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Kumpulan hasil terlihat seperti ini:

SalesOrderID   OrderDate    TotalDue    MovingAvg3Orders
------------   ----------   ---------   ----------------
71774          2008-06-01   972.785     972.785
71776          2008-06-01   87.083      529.934
71780          2008-06-01   42452.65    14504.172
71782          2008-06-01   43962.79    28834.174

Kueri ini menghitung rata-rata bergerak orde ke-3 dengan mencakup baris saat ini dan dua baris sebelumnya. Untuk baris pertama, hanya satu nilai yang tersedia, sehingga rata-rata sama dengan TotalDue. Pada saat mencapai baris ketiga, jendela sudah mencakup ketiga baris tersebut.

Menggunakan fungsi analitik

Fungsi analitik memungkinkan Anda mengakses data dari baris lain tanpa menggunakan gabungan atau subkueri mandiri. Fungsi-fungsi ini berguna untuk analisis rangkaian waktu, deteksi tren, dan membandingkan nilai saat ini dengan nilai historis atau masa depan. Tidak seperti fungsi jendela agregat yang menghitung ringkasan, fungsi analitik mengambil nilai tertentu dari baris tertentu di jendela.

LAG() dan LEAD() mengakses nilai dari baris sebelumnya atau berikutnya, seperti ini:

SELECT 
    SalesOrderID,
    OrderDate,
    TotalDue,
    LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS PreviousOrderTotal,
    LEAD(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS NextOrderTotal,
    TotalDue - LAG(TotalDue, 1, 0) OVER (ORDER BY OrderDate) AS ChangeFromPrevious
FROM SalesLT.SalesOrderHeader
ORDER BY OrderDate;

Kumpulan hasil terlihat seperti ini:

SalesOrderID   OrderDate    TotalDue    PreviousOrderTotal   NextOrderTotal   ChangeFromPrevious
------------   ----------   ---------   ------------------   --------------   ------------------
71774          2008-06-01   972.785     0                    87.083           972.785
71776          2008-06-01   87.083      972.785              42452.65         -885.702
71780          2008-06-01   42452.65    87.083               43962.79         42365.567
71782          2008-06-01   43962.79    42452.65             0                1510.14

LAG() mengambil nilai dari baris sebelumnya, sementara LEAD() mengambil dari baris berikut. Parameter kedua menentukan berapa banyak baris yang akan dilihat kembali atau di depan (defaultnya adalah 1), dan parameter ketiga memberikan nilai default ketika tidak ada baris (seperti untuk baris pertama dengan LAG()). Gunakan fungsi-fungsi ini untuk menghitung perubahan periode berlebih, mengidentifikasi tren, atau mendeteksi anomali dalam data berurutan.

FIRST_VALUE() dan LAST_VALUE() mengembalikan nilai dari baris pertama atau terakhir dalam bingkai:

SELECT 
    ProductID,
    Name,
    ListPrice,
    ProductCategoryID,
    FIRST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
    ) AS MostExpensiveInCategory,
    LAST_VALUE(Name) OVER (
        PARTITION BY ProductCategoryID 
        ORDER BY ListPrice DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS LeastExpensiveInCategory
FROM SalesLT.Product
WHERE ListPrice > 0;

Kumpulan hasil terlihat seperti ini:

ProductID   Name                        ListPrice   ProductCategoryID   MostExpensiveInCategory    LeastExpensiveInCategory
---------   -------------------------   ---------   -----------------   ------------------------   ------------------------
749         Road-150 Red, 62            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
750         Road-150 Red, 44            3578.27     5                   Road-150 Red, 62           LL Road Frame - Red, 58
722         LL Road Frame - Red, 58     337.22      5                   Road-150 Red, 62           LL Road Frame - Red, 58
771         Mountain-100 Silver, 38     3399.99     6                   Mountain-100 Silver, 38    Mountain-500 Black, 52

FIRST_VALUE() mengembalikan nilai dari baris pertama di jendela yang diurutkan, yang dalam hal ini adalah produk termahal per kategori. LAST_VALUE() mengembalikan yang paling murah, tetapi memerlukan bingkai eksplisit untuk menyertakan semua baris. Fungsi-fungsi ini membantu Anda membandingkan setiap baris dengan nilai tolok ukur seperti nilai tertinggi, terendah, atau garis besar dalam grup.

Nota

LAST_VALUE() memerlukan spesifikasi bingkai eksplisit untuk menyertakan baris setelah baris saat ini. Tanpa itu, bingkai default hanya menyertakan baris hingga baris saat ini, membuat LAST_VALUE() mengembalikan nilai baris saat ini.

PERCENT_RANK() dan CUME_DIST() menghitung posisi relatif dalam partisi:

SELECT 
    Name,
    ListPrice,
    PERCENT_RANK() OVER (ORDER BY ListPrice) AS PercentRank,
    CUME_DIST() OVER (ORDER BY ListPrice) AS CumulativeDistribution
FROM SalesLT.Product
WHERE ListPrice > 0
ORDER BY ListPrice;

Kumpulan hasil terlihat seperti ini:

Name                        ListPrice   PercentRank   CumulativeDistribution
-------------------------   ---------   -----------   ----------------------
Patch Kit/8 Patches         2.29        0.0           0.0081
Road Tire Tube              3.99        0.0081        0.0162
Touring Tire Tube           4.99        0.0162        0.0243
Road-150 Red, 62            3578.27     0.9919        1.0

PERCENT_RANK() mengembalikan nilai antara 0 dan 1 yang menunjukkan persentase baris yang memiliki nilai yang lebih rendah (0 berarti terendah, satu berarti tertinggi). CUME_DIST() memperlihatkan distribusi kumulatif, menunjukkan persentase baris apa yang memiliki nilai kurang dari atau sama dengan baris saat ini. Gunakan fungsi-fungsi ini untuk analisis persentil, mengidentifikasi outlier, atau membuat laporan distribusi.

Untuk informasi selengkapnya tentang fungsi jendela, lihat Fungsi Jendela (Transact-SQL) dan Fungsi Peringkat.