آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
1398/04/30 14:46 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که window functions نام دارد، توابعی را معرفی می‌کند که برای محاسبه مقادیر تجمعی بر اساس گروهی از ردیف‌ها به کار می‌روند و چندین ردیف برای هر گروه بازمی‌گردانند.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

در این جلسه که آخرین جلسه از این دوره است بقیه توابع باقیمانده توضیح داده خواهند شد.در جلسه قبلی توابع 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 استفاده می‌کنیم:

جدول produuction.products

الف) استفاده از تابع ()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 استفاده می‌کنیم.

جدول sales.customers

الف) استفاده از تابع ()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

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات