ROW_NUMBER (T-SQL)

Artikel
10/31/2024

Berlaku untuk: Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Menjumlahkan output dari kumpulan hasil. Lebih khusus lagi, mengembalikan jumlah baris berurutan dalam partisi kumpulan hasil, dimulai dari 1 untuk baris pertama di setiap partisi.

ROW_NUMBER dan RANK serupa. ROW_NUMBER angka semua baris secara berurutan (misalnya 1, 2, 3, 4, 5). RANK memberikan nilai numerik yang sama untuk ikatan (misalnya 1, 2, 2, 4, 5).

Catatan

ROW_NUMBER adalah nilai sementara yang dihitung saat kueri dijalankan. Untuk mempertahankan angka dalam tabel, lihat Properti IDENTITY dan URUTAN.

Konvensi sintaks transact-SQL

Sintaks

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Argumen

PARTISI MENURUT value_expression
Membagi tataan hasil yang dihasilkan oleh klausul FROM menjadi partisi tempat fungsi ROW_NUMBER diterapkan. value_expression menentukan kolom tempat kumpulan hasil dipartisi. Jika PARTITION BY tidak ditentukan, fungsi memperlakukan semua baris hasil kueri yang ditetapkan sebagai grup tunggal. Untuk informasi selengkapnya, lihat Klausul OVER (Transact-SQL).

order_by_clause
Klausa ORDER BY menentukan urutan di mana baris ditetapkan uniknya ROW_NUMBER dalam partisi tertentu. Hal ini diperlukan. Untuk informasi selengkapnya, lihat Klausul OVER (Transact-SQL).

Jenis Kembalian

bigint

Keterangan Umum

Tidak ada jaminan bahwa baris yang dikembalikan oleh kueri yang menggunakan ROW_NUMBER() akan diurutkan sama persis dengan setiap eksekusi kecuali kondisi berikut ini benar.

Nilai kolom yang dipartisi unik.
ORDER BY Nilai kolom unik.
Kombinasi nilai kolom dan ORDER BY kolom partisi unik.

ORDER BY Jika kolom tidak unik dalam hasil, pertimbangkan untuk menggunakan RANK() atau DENSE_RANK().

ROW_NUMBER() bersifat nondeterministik. Untuk informasi selengkapnya, lihat Fungsi Deterministik dan Nondeterministik.

Contoh

J. Contoh sederhana

Kueri berikut mengembalikan empat tabel sistem dalam urutan alfabet.

SELECT 
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5
ORDER BY name ASC;

Berikut set hasilnya.

nama	recovery_model_desc
master	SEDERHANA
model	FULL
msdb	SEDERHANA
tempdb	SEDERHANA

Untuk menambahkan kolom nomor baris di depan setiap baris, tambahkan kolom dengan ROW_NUMBER fungsi , dalam hal ini bernama Row#. Anda harus memindahkan ORDER BY klausa ke OVER klausa.

SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5;

Berikut set hasilnya.

Mendayung#	nama	recovery_model_desc
1	master	SEDERHANA
2	model	FULL
3	msdb	SEDERHANA
4	tempdb	SEDERHANA

Klausa PARTITION BY pada recovery_model_desc kolom, memulai ulang penomoran saat recovery_model_desc nilai berubah.

SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

Berikut set hasilnya.

Mendayung#	nama	recovery_model_desc
1	model	FULL
1	master	SEDERHANA
2	msdb	SEDERHANA
3	tempdb	SEDERHANA

B. Mengembalikan nomor baris untuk tenaga penjualan

Contoh berikut menghitung nomor baris untuk tenaga penjualan di Adventure Works Cycles berdasarkan peringkat penjualan tahunan hingga saat ini.

USE AdventureWorks2022;   
GO  
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"   
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Berikut set hasilnya.

  
Row FirstName    LastName               SalesYTD  
--- -----------  ---------------------- -----------------  
1   Linda        Mitchell               4251368.54  
2   Jae          Pak                    4116871.22  
3   Michael      Blythe                 3763178.17  
4   Jillian      Carson                 3189418.36  
5   Ranjit       Varkey Chudukatil      3121616.32  
6   José         Saraiva                2604540.71  
7   Shu          Ito                    2458535.61  
8   Tsvi         Reiter                 2315185.61  
9   Rachel       Valdez                 1827066.71  
10  Tete         Mensa-Annan            1576562.19  
11  David        Campbell               1573012.93  
12  Garrett      Vargas                 1453719.46  
13  Lynn         Tsoflias               1421810.92  
14  Pamela       Ansman-Wolfe           1352577.13

C. Mengembalikan subset baris

Contoh berikut menghitung nomor baris untuk semua baris dalam tabel dalam SalesOrderHeader urutan OrderDate dan hanya mengembalikan baris 50 ke 60 inklusif.

USE AdventureWorks2022;  
GO  
WITH OrderedOrders AS  
(  
    SELECT SalesOrderID, OrderDate,  
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber  
    FROM Sales.SalesOrderHeader   
)   
SELECT SalesOrderID, OrderDate, RowNumber    
FROM OrderedOrders   
WHERE RowNumber BETWEEN 50 AND 60;

D. Menggunakan ROW_NUMBER() dengan PARTISI

Contoh berikut menggunakan PARTITION BY argumen untuk mempartisi hasil kueri yang ditetapkan oleh kolom TerritoryName. Klausa ORDER BY yang ditentukan dalam OVER klausa mengurutkan baris di setiap partisi menurut kolom SalesYTD. Klausa ORDER BY dalam SELECT pernyataan mengurutkan seluruh hasil kueri yang ditetapkan oleh TerritoryName.

USE AdventureWorks2022;  
GO  
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,  
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) 
  AS Row  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0  
ORDER BY TerritoryName;

Berikut set hasilnya.

  
FirstName  LastName             TerritoryName        SalesYTD      Row  
---------  -------------------- ------------------   ------------  ---  
Lynn       Tsoflias             Australia            1421810.92    1  
José       Saraiva              Canada               2604540.71    1  
Garrett    Vargas               Canada               1453719.46    2  
Jillian    Carson               Central              3189418.36    1  
Ranjit     Varkey Chudukatil    France               3121616.32    1  
Rachel     Valdez               Germany              1827066.71    1  
Michael    Blythe               Northeast            3763178.17    1  
Tete       Mensa-Annan          Northwest            1576562.19    1  
David      Campbell             Northwest            1573012.93    2  
Pamela     Ansman-Wolfe         Northwest            1352577.13    3  
Tsvi       Reiter               Southeast            2315185.61    1  
Linda      Mitchell             Southwest            4251368.54    1  
Shu        Ito                  Southwest            2458535.61    2  
Jae        Pak                  United Kingdom       4116871.22    1

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Mengembalikan nomor baris untuk tenaga penjualan

Contoh berikut mengembalikan ROW_NUMBER untuk perwakilan penjualan berdasarkan kuota penjualan yang ditetapkan.

-- Uses AdventureWorks  
  
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) 
    AS RowNumber,  
    FirstName, LastName,   
    CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq  
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE e.SalesPersonFlag = 1  
GROUP BY LastName, FirstName;

Berikut adalah kumpulan hasil parsial.


RowNumber  FirstName  LastName            SalesQuota  
---------  ---------  ------------------  -------------  
1          Jillian    Carson              12,198,000.00  
2          Linda      Mitchell            11,786,000.00  
3          Michael    Blythe              11,162,000.00  
4          Jae        Pak                 10,514,000.00

F. Menggunakan ROW_NUMBER() dengan PARTISI

Contoh berikut menunjukkan penggunaan ROW_NUMBER fungsi dengan PARTITION BY argumen . Hal ini menyebabkan ROW_NUMBER fungsi menomori baris di setiap partisi.

-- Uses AdventureWorks  
  
SELECT ROW_NUMBER() OVER(PARTITION BY SalesTerritoryKey 
        ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,  
    LastName, SalesTerritoryKey AS Territory,  
    CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq  
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE e.SalesPersonFlag = 1  
GROUP BY LastName, FirstName, SalesTerritoryKey;

Berikut adalah kumpulan hasil parsial.

 
RowNumber  LastName            Territory  SalesQuota  
---------  ------------------  ---------  -------------  
1          Campbell            1           4,025,000.00  
2          Ansman-Wolfe        1           3,551,000.00  
3          Mensa-Annan         1           2,275,000.00  
1          Blythe              2          11,162,000.00  
1          Carson              3          12,198,000.00  
1          Mitchell            4          11,786,000.00  
2          Ito                 4           7,804,000.00

Lihat Juga

RANK (T-SQL)
DENSE_RANK (T-SQL)
NTILE (Transact-SQL)

Bagikan melalui

ROW_NUMBER (T-SQL)

Sintaks

Argumen

Jenis Kembalian

Keterangan Umum

Contoh

J. Contoh sederhana

B. Mengembalikan nomor baris untuk tenaga penjualan

C. Mengembalikan subset baris

D. Menggunakan ROW_NUMBER() dengan PARTISI

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Mengembalikan nomor baris untuk tenaga penjualan

F. Menggunakan ROW_NUMBER() dengan PARTISI

Lihat Juga

Saran dan Komentar

Sumber Daya Tambahan: