برگزیده سر دبیر
پر بیننده ترین ها
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که window functions نام دارد، توابعی را معرفی می‌کند که برای محاسبه مقادیر تجمعی بر اساس گروهی از ردیف‌ها به کار می‌روند و چندین ردیف برای هر گروه بازمی‌گردانند.
|
۳۵-window functionها بخش ۲ |
|||
|
|
در این جلسه که آخرین جلسه از این دوره است بقیه توابع باقیمانده توضیح داده خواهند شد.در جلسه قبلی توابع LAG، FIRST_VALUE، DENSE_RANK، CUME_DIST و LAST_VALUE مورد بررسی قرار گرفتند.
موارد مورد بحث در این دو جلسه به این شرح هستند:
|
نام تابع |
توضیح |
|
CUME_DIST |
توزیع افزوده یک مقدار در یک مجموعه از مقادیر را محاسبه می‌کند. |
|
DENSE_RANK |
یک مقدار رتبه یا Rank برای هر ردیف درون بخشی از یک نتیجه |
|
FIRST_VALUE |
مقدار اولین ردیف در یک قسمت مرتب شده از یک مجموعه نتیجه را به دست می‌آورد. |
|
LAG |
به یک ردیف در یک offset فیزیکی مشخص شده که قبل از ردیف فعلی می‌آید ، دسترسی فراهم می‌کند. |
|
LAST_VALUE |
مقدار آخرین ردیف در یک بخش مرتب شده از یک مجموعه نتیجه را به دست می‌آورد. |
|
بخش ۲ |
|
|
LEAD |
به یک ردیف در یک offset فیزیکی مشخص شده که بعد از ردیف فعلی می‌آید، دسترسی فراهم می‌کند. |
|
NTILE |
ردیف‌های یک بخش مرتب شده را درون تعداد گروه یا bucket توزیع می‌کند. |
|
PERCENT_RANK |
درصد Rank یک مقدار در یک مجموعه از مقادیر را محاسبه می‌کند. |
|
RANK |
یک مقدار rank برای هر ردیف درون یک بخش از یک مجموعه نتیجه اختصاص می‌دهد. |
|
ROW_NUMBER |
یک عدد integer ترتیبی منحصر به فرد برای ردیف‌های درون بخشی از مجموعه نتیجه اختصاص می‌دهد، اولین ردیف از 1 آغاز می‌شود. |
با ما همراه باشید!
تابع LEAD در SQL Server
تابع ()LEAD یک window function است که به یک ردیف در یک offset فیزیکی مشخص شده که پس از ردیف فعلی قرار می‌گیرد، دسترسی فراهم می‌کند.
برای مثال، با استفاده از تابع ()LEAD، از ردیف فعلی می‌توانید به داده‌های ردیف بعدی یا ردیفی که بعد از ردیف بعدی قرار دارد و الی آخر دسترسی داشته باشید.
تابع ()LEAD می‌تواند برای مقایسه مقدار ردیف فعلی با مقدار ردیف بعدی بسیار مفید باشد.
کد زیر، syntax تابع ()LEAD را نشان می‌دهد:
LEAD(return_value ,offset [,default])
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)در این syntax:
- Return_value مقدار بازگشتی ردیف بعدی را بر اساس یک offset مشخص شده بازمی‌گرداند. مقدار بازگشتی باید یک مقدار تکی باشد و نمی‌تواند یک window function دیگر باشد.
- Offset تعداد ردیف‌های بعد از ردیف فعلی است که از طریق آن می‌توان به داده‌های آن‌ها دسترسی داشت. Offset می‌تواند یک عبارت، زیر query یا ستون باشد که مقدار آن یک integer مثبت است.
اگر خودتان مقدار offset را مشخص نکنید، مقدار پیش‌فرض آن برابر با 1 خواهد بود. - اگر offset بیشتر از دامنه پارتیشن شود، آنگاه تابع default را بازمی‌گرداند. اگر مشخص نشده باشد، مقدار پیش‌فرض NULL در نظر گرفته خواهد شد.
- دستور PARTITION BY ردیف‌های مجموعه نتیجه را به پارتیشن‌هایی تقسیم یا توزیع می‌کند که تابع ()LEAD برای آن‌ها اعمال می‌شود.
اگر دستور PARTITION BY را مشخص نکرده باشید، تابع با کل مجموعه نتیجه به عنوان یک پارتیشن کلی رفتار خواهد کرد. - دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشنی که تابع ()LEAD برای آن‌ها اعمال می‌شود را مشخص می‌کند.
مثال‌های تابع ()LEAD در SQL Server
اجازه دهید یک View جدید به نام sales.vw_netsales_brands برای مثال ایجاد کنیم:
CREATE VIEW sales.vw_netsales_brands
AS
SELECT
c.brand_name,
MONTH(o.order_date) month,
YEAR(o.order_date) year,
CONVERT(DEC(10, 0), SUM((i.list_price * i.quantity) * (1 - i.discount))) AS net_sales
FROM sales.orders AS o
INNER JOIN sales.order_items AS i ON i.order_id = o.order_id
INNER JOIN production.products AS p ON p.product_id = i.product_id
INNER JOIN production.brands AS c ON c.brand_id = p.brand_id
GROUP BY c.brand_name,
MONTH(o.order_date),
YEAR(o.order_date);Query زیر داده‌های view ایجاد شده در کد بالا را بازمی‌گرداند:
SELECT
*
FROM
sales.vw_netsales_brands
ORDER BY
year,
month,
brand_name,
net_sales;خروجی به این شکل است:
الف) استفاده از تابع ()LEAD برای یک مجموعه نتیجه
کد زیر، از تابع ()LEAD برای بازگرداندن فروش‌های خالص ماه فعلی و ماه بعدی در سال 2017 استفاده می‌کند:
WITH cte_netsales_2017 AS(
SELECT
month,
SUM(net_sales) net_sales
FROM
sales.vw_netsales_brands
WHERE
year = 2017
GROUP BY
month
)
SELECT
month,
net_sales,
LEAD(net_sales,1) OVER (
ORDER BY month
) next_month_sales
FROM
cte_netsales_2017;خروجی به این شکل است:
در این مثال:
- ابتدا، CTE فروش‌های خالص جمع شده را بر اساس ماه بازمی‌گرداند.
- سپس، query بیرونی از تابع ()LEAD برای بازگرداندن فروش‌های ماه فعلی برای هر ماه استفاده می‌کند.
با انجام این کار، می‌توانید به آسانی فروش‌های ماه فعلی را با ماه بعدی مقایسه کنید.
ب) استفاده از تابع ()LEAD برای پارتیشن‌ها
کد زیر از تابع ()LEAD برای مقایسه فروش‌های ماه فعلی با ماه بعدی هر برند در سال 2018 استفاده می‌کند:
SELECT
month,
brand_name,
net_sales,
LEAD(net_sales,1) OVER (
PARTITION BY brand_name
ORDER BY month
) next_month_sales
FROM
sales.vw_netsales_brands
WHERE
year = 2018;این تصویر خروجی را نشان می‌دهد:
در این مثال:
- دستور PARTITION BY ردیف‌ها را بر اساس نام برند به چند پارتیشن تقسیم می‌کند.
- برای هر پارتیشن (یا نام برند)، دستور ORDER BY ردیف‌ها را بر اساس ماه مرتب می‌کند.
- برای هر ردیف در هر پارتیشن، تابع ()LEAD فروش‌های خالص ردیف بعدی را بازمی‌گرداند.
به این شکل، چگونگی استفاده از تابع ()LEAD برای دسترسی به یک ردیف در یک offset فیزیکی مشخص شده که بعد از ردیف فعلی قرار دارد را آموختید.
تابع NTILE در SQL Server
تابع ()NTILE یک window function است که ردیف‌های یک پارتیشن مرتب شده را به تعداد خاصی از گروه‌ها یا bucketهای تقریبا یکسان و برابر تقسیم یا توزیع می‌کند. این تابع برای هر گروه یک شماره bucket اختصاص می‌دهد که از 1 شروع می‌شود.
برای هر ردیف در یک گروه، تابع ()NTILE یک شماره bucket اختصاص می‌دهد که نشان دهنده گروهی است که ردیف به آن‌ها تعلق دارد.
Syntax تابع ()NTILE به شکل زیر است:
NTILE(buckets) OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)اجازه دهید دقیق syntax را بررسی کنیم:
Bucketها
تعداد bucketها را نشان می‌دهد که در آن‌ها ردیف‌ها تقسیم شده‌اند. Bucketها می‌توانند یک عبارت یا زیر query باشند که مقدار آن‌ها یک عدد مثبت است. همچنین bucketها نمی‌توانند window function باشند.
دستور PARTITION BY
دستور PARTITION BY ردیف‌های یک مجموعه نتیجه را به پارتیشن‌هایی که تابع ()NTILE برای آن‌ها اعمال می‌شود، تقسیم می‌کند.
دستور ORDER BY
دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشنی که تابع ()NTILE برای آن اعمال می‌شود را مشخص می‌کند.
اگر تعداد ردیف‌ها قابل تقسیم شدن توسط bucketها نباشد، تابع ()NTILE گروه‌های دارای دو سایز که تفاوت بین آن‌ها 1 عدد می‌باشد را بازمی‌گرداند. در ترتیب مشخص شده توسط دستور ORDER BY درون تابع ()OVER همیشه گروه‌های بزرگ‌تر قبل از گروه کوچک می‌آیند.
از طرف دیگر، اگر کل ردیف‌ها توسط bucketها قابل تقسیم شدن باشند، تابع ردیف‌ها را به صورت برابر بین bucketها تقسیم می‌کند.
آشنایی بیشتر با تابع ()NTILE
کد زیر، یک جدول جدید به نام ntile_demo ایجاد می‌کند که 10 عدد را در خود ذخیره می‌کند:
CREATE TABLE sales.ntile_demo (
v INT NOT NULL
);
INSERT INTO sales.ntile_demo(v)
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
SELECT * FROM sales.ntile_demo;این کد از تابع ()NTILE برای تقسیم 10 ردیف به سه گروه استفاده می‌کند:
SELECT
v,
NTILE (3) OVER (
ORDER BY v
) buckets
FROM
sales.ntile_demo;خروجی به این شکل است:
همان‌طور که به وضوح در خروجی مشاهده می‌کنید، اولین گروه دارای چهار ردیف است و دو گروه دیگر دارای سه ردیف هستند.
کد زیر از تابع ()NTILE برای تقسیم یا توزیع ردیف‌ها به پنج bucket استفاده می‌کند:
SELECT
v,
NTILE (5) OVER (
ORDER BY v
) buckets
FROM
sales.ntile_demo;خروجی به این ترتیب است:
همان‌طور که مشاهده می‌کنید خروجی دارای پنج گروه با تعداد ردیف مشابه در هر کدام است.
مثال‌های تابع ()NTILE در SQL Server
اجازه دهید یک view ایجاد کنید تا در مثال‌های تابع ()NTILE از آن استفاده کنیم.
کد زیر، یک view ایجاد می‌کند که فروش‌های خالص در سال 2017 را بر اساس ماه بازمی‌گرداند:
CREATE VIEW sales.vw_netsales_2017 AS
SELECT
c.category_name,
DATENAME(month, o.shipped_date) month,
CONVERT(DEC(10, 0), SUM(i.list_price * quantity * (1 - discount))) net_sales
FROM
sales.orders o
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN production.products p on p.product_id = i.product_id
INNER JOIN production.categories c on c.category_id = p.category_id
WHERE
YEAR(shipped_date) = 2017
GROUP BY
c.category_name,
DATENAME(month, o.shipped_date);خروجی به این شکل است:
SELECT
category_name,
[month],
net_sales
FROM
sales.vw_netsales_2017
ORDER BY
category_name,
[month],
net_sales;
الف) استفاده از تابع ()NTILE برای یک query در مجموعه نتایج
مثال زیر، از تابع ()NTILE برای توزیع یا تقسیم ماه‌ها به 4 buucket بر اساس فروش خالص استفاده می‌کند:
WITH cte_by_month AS(
SELECT
month,
SUM(net_sales) net_sales
FROM
sales.vw_netsales_2017
GROUP BY
month
)
SELECT
month,
FORMAT(net_sales,'C','en-US') net_sales,
NTILE(4) OVER(
ORDER BY net_sales DESC
) net_sales_group
FROM
cte_by_month;خروجی به این شکل است:
ب) استفاده از تابع ()NTILE برای پارتیشن‌ها
این مثال از تابع ()NTILE برای تقسیم فروش خالص بر اساس ماه به 4 گروه برای هر دسته‌بندی محصول استفاده می‌کند:
SELECT
category_name,
month,
FORMAT(net_sales,'C','en-US') net_sales,
NTILE(4) OVER(
PARTITION BY category_name
ORDER BY net_sales DESC
) net_sales_group
FROM
sales.vw_netsales_2017;تصویر زیر، بخشی از خروجی را نشان می‌دهد:
به این صورت، چگونگی استفاده از تابع ()NTILE برای توزیع یا تقسیم ردیف‌های یک پارتیشن مرتب شده به تعداد خالص bucket را نیز آموختید.
تابع PERCENT_RANK در SQL Server
تابع ()PERCENT_RANK مشابه تابع ()CUME_DIST است. تابع ()PERCENT_RANK موقعیت قرارگیری نسبی یک مقدار درون یک پارتیشن از یک مجموعه نتیجه را مشخص می‌کند.
کد زیر، syntax تابع ()PERCENT_RANK را نشان می‌دهد:
PERCENT_RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)در این syntax:
- دستور PARTITION BY ردیف‌ها را به چند پارتیشن تقسیم می‌کند که تابع ()PERCENT_RANK برای آن‌ها اعمال می‌شود. دستور PARTITION BY اختیاری است. اگر آن را مشخص نکنید،، تابع با مجموعه نتیجه به عنوان یک پارتیشن کلی رفتار خواهد کرد.
- دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشن را مشخص می‌کند. چون ()PERCENT_RANK حساس به ترتیب است، پس حتماً به دستور ORDER BY نیاز است.
مقدار بازگشتی
نتیجه تابع ()PERCENT_RANK بیشتر از 0 و کمتر یا برابر با 1 است.
0 < PERCENT_RANK() <= 1
اولین ردیف دارای رتبه یا Rank صفر است. مقادیر برابر یا Tie دارای مقدار توزیع افزوده یکسانی هستند.
تابع ()PERCENT_RANK به صورت پیش‌فرض شامل مقادیر NULL است و با این مقادیر به عنوان کمترین مقادیر مثبت رفتار می‌شود.
مثال‌های تابع ()PERCENT_RANK در SQL Server
اجازه دهید چند مثال برای استفاده از تابع ()PERCENT_RANK حل کنیم.
کد زیر یک view جدید به نام sales.vw_staff_sales برای مثال ایجاد می‌کند:
CREATE VIEW sales.vw_staff_sales(
staff_id,
year,
net_sales
) AS
SELECT
staff_id,
YEAR(order_date),
ROUND(SUM(quantity*list_price*(1-discount)),0)
FROM
sales.orders o
INNER JOIN sales.order_items i on i.order_id = o.order_id
WHERE
staff_id IS NOT NULL
GROUP BY
staff_id,
YEAR(order_date);الف) استفاده از تابع ()PERCENT_RANK برای یک مجموعه نتیجه
این مثال از تابع ()PERCENT_RANK برای محاسبه درصد فروش هر کارمند فروش در سال 2016 استفاده می‌کند:
SELECT
CONCAT_WS(' ',first_name,last_name) full_name,
net_sales,
PERCENT_RANK() OVER (
ORDER BY net_sales DESC
) percent_rank
FROM
sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE
YEAR = 2016;خروجی به این ترتیب است:
برای اینکه خروجی خواناتر شود، می‌توانید از تابع ()FORMAT برای فرمت بندی درصدی(%) به درصد rank استفاده کنید:
SELECT
CONCAT_WS(' ',first_name,last_name) full_name,
net_sales,
FORMAT(
PERCENT_RANK() OVER (
ORDER BY net_sales DESC
) ,
'P') percent_rank
FROM
sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE
YEAR = 2016;تصویر زیر، خروجی را نشان می‌دهد:
ب) استفاده از تابع ()PERCENT_RANK برای پارتیشن‌ها
مثال زیر از تابع ()PERCENT_RANK برای محاسبه درصد فروش برای هر کارمند در سال‌های 2016 و 2017 استفاده می‌کند:
SELECT
year,
CONCAT_WS(' ',first_name,last_name) full_name,
net_sales,
FORMAT(
PERCENT_RANK() OVER (
PARTITION BY year
ORDER BY net_sales DESC
) ,
'P') percent_rank
FROM
sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE
YEAR IN (2016,2017);خروجی به این شکل است:
در این مثال:
- دستور PARTITION BY ردیف‌ها را بر اساس سال به دو پارتیشن تقسیم می‌کند، یکی برای سال 2016 و دیگری برای سال 2017.
- دستور ORDER BY ردیف‌ها را در هر پارتیشن بر اساس فروش خالص از زیاد به کم مرتب می‌کند.
- تابع ()PERCENT_RANK برای هر پارتیشن به صورت مجزا اعمال شده است و وقتی یک مقدار از حد و مرز پارتیشن بیشتر می‌شود، مجدداً rank را محاسبه می‌کند.
در نتیجه، چگونگی استفاده از تابع ()PERCENT_RANK برای محاسبه rank نسبی یک ردیف درون گروهی از ردیف‌ها را نیز آموختید.
تابع RANK در SQL Server
تابع ()RANK یک window function است که برای هر ردیف درون یک پارتیشن از یک مجموعه نتیجه، یک rank اختصاص می‌دهد.
ردیف‌های درون یک پارتیشن که دارای مقادیر مشابه هستند، یک Rank مشابه دریافت خواهند کرد. Rank اولین ردیف درون یک پارتیشن برابر با یک است. تابع ()RANK تعداد ردیف‌های مشابه را به rank مشابه اضافه می‌کند تا rank ردیف بعدی محاسبه شود.
در نتیجه، rankها ممکن است به صورت متوالی نباشند.
کد زیر، Syntax تابع ()RANK را نشان می‌دهد:
RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)در این syntax:
- ابتدا، دستور PARTITION BY ردیف‌های پارتیشن‌های مجموعه نتیجه که تابع برای آن‌ها اعمال می‌شود را تقسیم می‌کند.
- سپس، دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشنی که تابع برای آن اعمال می‌شود را مشخص می‌کند.
تابع ()RANK برای ارائه گزارشات N تعداد بالا یا N تعداد پایین بسیار مناسب است.
آشنایی بیشتر با تابع ()RANK
ابتدا، یک جدول جدید به نام sales.rank_demo ایجاد می‌کنیم که دارای یک ستون است:
CREATE TABLE sales.rank_demo (
v VARCHAR(10)
);دوم، چند ردیف به جدول Sales.rank_demo اضافه می‌کنیم:
INSERT INTO sales.rank_demo(v)
VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');سوم، داده‌ها را از جدول sales.rank_demo به دست می‌آوریم:
SELECT
v
FROM
sales.rank_demo;چهار، از تابع ()ROW_NUMBER برای اختصاص دادن Rankها به ردیف‌ها در مجموعه نتایج جدول sales.rank_demo استفاده می‌کنیم:
SELECT
v,
RANK () OVER (
ORDER BY v
) rank_no
FROM
sales.rank_demo;خروجی به این ترتیب است:
همان‌طور که از خروجی پیدا است، دومین و سومین ردیف‌ها دارای rank یکسان هستند، چون دارای مقدار مشترک B هستند. چهارم و پنجمین ردیف‌ها دارای rank یا رتبه 4 هستند، چون تابع ()RANK از rank شماره ۳ رد می‌شود و هر دوی آن‌ها نیز دارای مقادیر یکسان خواهند بود.
مثال‌های تابع ()RANK در SQL Server
از جدول produuction.products برای مثال‌های تابع ()RANK استفاده می‌کنیم:
الف) استفاده از تابع ()RANK برای یک مجموعه نتیجه
مثال زیر از تابع ()RANK برای رتبه دادن به محصول بر اساس قیمت آن‌ها استفاده می‌کند:
SELECT
product_id,
product_name,
list_price,
RANK () OVER (
ORDER BY list_price DESC
) price_rank
FROM
production.products;مجموعه نتیجه به این شکل است:
در این مثال، چون دستور PARTITION BY را ننوشتیم، تابع ()RANK با کل مجموعه نتیجه به عنوان یک پارتیشن کلی برخورد کرد.
تابع ()RANK یک Rank به هر ردیف درون مجموعه نتیجه که بر اساس قیمت از زیاد به کم مرتب شده است، اختصاص می‌دهد.
ب) استفاده از تابع ()RANK برای پارتیشن‌ها
این مثال از تابع ()RANK برای اختصاص دادن یک rank به هر محصول بر اساس قیمت در هر برند استفاده می‌کند و محصولات دارای rank کمتر یا مساوی با سه را بازمی‌گرداند:
SELECT * FROM (
SELECT
product_id,
product_name,
brand_id,
list_price,
RANK () OVER (
PARTITION BY brand_id
ORDER BY list_price DESC
) price_rank
FROM
production.products
) t
WHERE price_rank <= 3;
تصویر زیر، خروجی را نشان می‌دهد:
در این مثال:
- ابتدا، دستور PARTITION BY محصولات را بر اساس Id برند به چند پارتیشن تقسیم می‌کند.
- دوم، دستور ORDER BY محصولات را در هر پارتیشن بر اساس قیمت مرتب می‌کند.
- سوم، query بیرونی، محصولاتی که مقادیر rank آن‌ها کمتر یا مساوی با سه هست را بازمی‌گرداند.
تابع ()RANK برای هر ردیف در هر پارتیشن اعمال می‌شود و وقتی مقداری از حد و مرز پارتیشن بیشتر می‌شود، مجدداً مقدار دهی می‌کند.
به این صورت چگونگی استفاده از تابع ()RANK برای اختصاص دادن یک rank به هر ردیف داخل یک پارتیشن از مجموعه نتیجه را آموختید.
تابع ROW_NUMBER در SQL Server
()ROW_NUMBER یک window function است که یک integer ترتیب را به هر ردیف درون یک پارتیشن از یک مجموعه نتیجه اختصاص می‌دهد. عدد ردیف از 1 برای اولین ردیف در هر پارتیشن شروع می‌شود.
کد زیر، Syntax تابع ()ROW_NUMBER را نشان می‌دهد:
ROW_NUMBER() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)اجازه دهید syntax تابع ()ROW_NUMBER را دقیق بررسی کنیم.
PARTITION BY
دستور PARTITION BY مجموعه نتیجه را به چند پارتیشن تقسیم می‌کند. تابع ()ROW_NUMBER برای هر پارتیشن به صورت مجزا اعمال می‌شود و عدد ردیف را برای هر پارتیشن مقدار دهی مجدد می‌کند.
دستور PARTITION BY اختیاری است. اگر آن را مشخص نکنید، تابع ()ROW_NUMBER با مجموعه نتیجه به عنوان یک پارتیشن تکی کلی برخورد می‌کند.
ORDER BY
دستور ORDER BY ترتیب منطقی ردیف‌های درون هر پارتیشن از مجموعه نتیجه را تعریف می‌کند. دستور ORDER BY الزامی است، چون تابع ()ROW_NUMBER حساس به ترتیب است.
مثال‌های تابع ()ROW_NUMBER در SQL Server
از جدول sales.customers از پایگاه داده نمونه Bikestores برای نمایش مثال تابع ()ROW_NUMBER استفاده می‌کنیم.
الف) استفاده از تابع ()ROW_NUMBER برای یک مجموعه نتیجه
کد زیر، از تابع ()ROW_NUMBER برای اختصاص یک عدد ترتیبی به هر ردیف مشتری استفاده می‌کند:
SELECT
ROW_NUMBER() OVER (
ORDER BY first_name
) row_num,
first_name,
last_name,
city
FROM
sales.customers;بخشی از خروجی به این شکل است:
در این مثال، ما دستور PARTITION BY را به کار نبردیم، در نتیجه، تابع ()ROW_NUMBER با مجموعه نتیجه به عنوان یک پارتیشن کلی برخورد کرد.
ب) استفاده از تابع ()ROW_NUMBER برای پارتیشن‌ها
مثال زیر از تابع ()ROW_NUMBER برای اختصاص دادن یک integer ترتیبی به هر مشتری استفاده می‌کند. وقتی نام شهر تغییر می‌کند، تابع اعداد را ریست می‌کند:
SELECT
first_name,
last_name,
city,
ROW_NUMBER() OVER (
PARTITION BY city
ORDER BY first_name
) row_num
FROM
sales.customers
ORDER BY
city;تصویر زیر، بخشی از خروجی را نشان می‌دهد:
در این مثال، از دستور PARTITION BY برای تقسیم مشتریان به چند پارتیشن بر اساس شهر استفاده کردیم. شماره ردیف زمانی که نام شهر تغییر کرد، مجدداً مقدار دهی اولیه شد.
پ) استفاده از تابع ()ROW_NUMBER برای صفحه‌بندی
تابع ()ROW_NUMBER برای صفحه‌بندی در برنامه‌ها بسیار مفید است. برای مثال می‌توانید لیستی از مشتریان را با استفاده از صفحه نمایش دهید، که هر صفحه دارای ۱۰ ردیف است.
مثال زیر از تابع ()ROW_NUMBER برای بازگرداندن مشتریان ردیف‌های 11 تا 20 که در صفحه دوم است، استفاده می‌کند:
WITH cte_customers AS (
SELECT
ROW_NUMBER() OVER(
ORDER BY
first_name,
last_name
) row_num,
customer_id,
first_name,
last_name
FROM
sales.customers
) SELECT
customer_id,
first_name,
last_name
FROM
cte_customers
WHERE
row_num > 20 AND
row_num <= 30;خروجی به این ترتیب است:
در این مثال:
- ابتدا، CTE از تابع ()ROW_NUMBER برای اختصاص دادن یک عدد ترتیبی به همه ردیف‌های موجود در مجموعه نتیجه استفاده می‌کند.
- سپس، query بیرونی ردیف‌های صفحه دوم که دارای شماره ردیف بین 11 تا 20 هستند را بازمی‌گرداند.
در نتیجه، چگونگی استفاده از تابع ()ROW_NUMBER برای اختصاص دادن یک عدد ترتیبی به هر ردیف درون یک پارتیشن از یک query را نیز آموختید.
خلاصه
و به این شکل آخرین جلسه از مجموعه آموزش جامع SQL Server نیز به اتمام رسید. به جرئت می‌توان ادعا کرد که این مجموعه یکی از کامل‌ترین و مثال‌محور‌ترین مجموعه‌های آموزشی برای یادگیری نوشتن پایگاه داده یا Database در SQL Server با زبان SQL است.
کار را با معرفی نرم‌افزار SQL Server و چگونگی نصب آن آغاز کردیم، تمام مباحث اولیه و توابع ابتدایی را کامل و با مثال توضیح دادیم و سپس مباحث پیشرفته‌تر را نیز به همین شکل توضیح دادیم.
اگر تا آخر همراه ما بودید از شما ممنونیم و تبریک می‌گوییم که توانستید یکی از کارآمدترین و درآمدزاترین زبان‌های برنامه‌نویسی دنیا را کامل بیاموزید. یک جلسه اضافی دیگر هم وجود خواهد داشت که پلی خواهد بودن بین SQL و #C که این دو همیشه جداناشدنی هستند.
با MUG همراه باشید!
منبع: sqlservertutorial
آخرین مطالب
دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...
دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...
بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...
دیدگاه خود را درباره این پست بنویسید