بررسی ROWNUMBER و RANK و DENSERANK در SQL
در این نوشتار آموزشی به بررسی نحوه استفاده و تفاوتهای توابع ROW_NUMBER و RANK و DENSE_RANK در پایگاه داده SQL Server می پردازیم. اکثر مبتدیان توابع اساسی SQL را مخلوط می کنند و یا نمی دانند که کدام حالت یا سناریو مفید است. آنها کد کم کیفیت را که به خوبی و صحت کار می کند ارسال می کنند. اما کارایی پرس و جوهای SQL را تحت تاثیر قرار می دهند. این نکته درباره استفاده از تابع در بسیاری از پایگاه های داده است. بنابراین من می خواهم این آموزش را بنویسم که پایه اصلی آن نحوه استفاده صحیح از توابع است. امیدوارم این مقاله به شما در بهبود پرس و جوهای SQL تان کمک کند.در ایتدا جدولی ایجاد می کنیم و یک سری اطلاعات در آن وارد می نماییم:
CREATE TABLE [Employees] (
[Id] INTEGER NOT NULL IDENTITY(1, 1),
[EmpName] VARCHAR(255) NULL,
[EmpDob] VARCHAR(255),
[EmpSalary] INTEGER NULL,
PRIMARY KEY ([Id])
);
GO
INSERT INTO Employees([EmpName],[EmpDob],[EmpSalary]) _
VALUES('Sasha Haynes','11/30/16',80000),('Wing Ryan','01/09/17',5200),_
('Evangeline Fitzpatrick','07/30/16',80000),('Brenden Saunders','05/21/17',8183),_
('Barrett Allison','03/10/17',25000),('Emily Garrett','08/11/16',63711),_
('Piper Chen','06/14/17',68525),('Kristen Juarez','07/30/16',65000),_
('Colton Johns','12/20/16',80000),('Cameron Massey','04/27/17',45000);
INSERT INTO Employees([EmpName],[EmpDob],[EmpSalary]) VALUES('Aimee Jacobson','04/17/17',4500),_
('Rashad Valencia','02/27/17',36000),('Aurelia Morrison','03/29/17',5700),_
('Beck Wood','12/23/16',36754),('Evan Gould','05/14/17',36000),_
('Moana Travis','03/08/17',65252),('Shelly Barron','12/26/16',65047),_
('Quamar Navarro','03/15/17',65000),('Gil Roach','04/18/17',65000),_
('Rosalyn Nieves','07/01/17',36382);
1- بررسی متد Row_Number
این تابع یک عدد منحصر به فرد را برای هر ردیفی که توسط order by واکشی شده است اختصاص می دهد.
SELECT TOP 10 [EmpName] ,[EmpSalary],ROW_NUMBER() OVER (ORDER BY EMPSALARY) AS RowNum FROM [TestDb].[dbo].[Employees]
خروجی:
|
| EmpName | EmpSalary | RowNum |
|
|
|------------------|-----------|--------|
|
|
| Aimee Jacobson | 4500 | 1 |
|
|
| Wing Ryan | 5200 | 2 |
|
|
| Aurelia Morrison | 5700 | 3 |
|
|
| Brenden Saunders | 8183 | 4 |
|
|
| Barrett Allison | 25000 | 5 |
|
2- تابع RANK
این تابع یک رتبه برای هر سطر بر اساس ستون ذکر شده در عبارت Over تعیین می کند.
SELECT EmpName,EmpSalary,RANK() OVER(ORDER BY EmpSalary Desc) as Rank FROM employees
خروجی:
|
| EmpName | EmpSalary | Rank |
|
|
|------------------------|-----------|------|
|
|
| Sasha Haynes | 80000 | 1 |
|
|
| Evangeline Fitzpatrick | 80000 | 1 |
|
|
| Colton Johns | 80000 | 1 |
|
|
| Piper Chen | 68525 | 4 |
|
|
| Moana Travis | 65252 | 5 |
|
|
| Shelly Barron | 65047 | 6 |
|
همانطور که در نتیجه فوق مشاهده مینمایید از سطر سوم تا چهارم یک فاصله بوجود آمده (از عدد یک به عدد 4 منتقل شده). برای رفع این مساله از متد DENSE_RANK استفاده می کنیم.
متد DENSE_RANK:
SELECT EmpName,EmpSalary,DENSE_RANK() OVER(ORDER BY EmpSalary Desc) as Rank FROM employees
خروجی:
|
| EmpName | EmpSalary | Rank |
|
|
|------------------------|-----------|------|
|
|
| Sasha Haynes | 80000 | 1 |
|
|
| Evangeline Fitzpatrick | 80000 | 1 |
|
|
| Colton Johns | 80000 | 1 |
|
|
| Piper Chen | 68525 | 2 |
|
|
| Moana Travis | 65252 | 3 |
|
|
| Shelly Barron | 65047 | 4 |
|
امیدوارم از این مطلب استفاده کافی رو ببرید.