آموزش جامع SQL Server (جلسه ۳۴: Window Functionها – بخش ۱)
1398/04/29 15:52 , میلاد صاحب نظر

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

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

۱- نصب و آشنایی با 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 بخش۱

 

این توابع را همراه با توضیحات مختصر در جدول زیر مشاهده می‌کنید که در دو جلسه متوالی پوشش داده خواهند شد.

پس از این دو جلسه یک قسمت اضافی برای آموزش چگونگی اتصال یک پایگاه داده به یک برنامه نوشته شده با زبان برنامه‌نویسی #C قرار خواهد گرفت که پلی خواهد بود برای مرتبط کردن این دو زبان جدا ناشدنی. با ما همراه باشید!

بخش ۱

نام تابع

توضیح

CUME_DIST

توزیع افزوده یک مقدار در یک مجموعه از مقادیر را محاسبه می‌کند.

DENSE_RANK

یک مقدار رتبه یا Rank برای هر ردیف درون بخشی از یک نتیجه

FIRST_VALUE

مقدار اولین ردیف در یک قسمت مرتب شده از یک مجموعه نتیجه را به دست می‌آورد.

LAG

به یک ردیف در یک offset فیزیکی مشخص شده که قبل از ردیف فعلی می‌آید ، دسترسی فراهم می‌کند.

LAST_VALUE

مقدار آخرین ردیف در یک بخش مرتب شده از یک مجموعه نتیجه را به دست می‌آورد.

بخش ۲

LEAD

به یک ردیف در یک offset فیزیکی مشخص شده که بعد از ردیف فعلی می‌آید،  دسترسی فراهم می‌کند.

NTILE

ردیف‌های یک بخش مرتب شده را درون تعداد گروه یا bucket توزیع می‌کند.

PERCENT_RANK

درصد Rank یک مقدار در یک مجموعه از مقادیر را محاسبه می‌کند.

RANK

یک مقدار rank برای هر ردیف درون یک بخش از یک مجموعه نتیجه اختصاص می‌دهد.

ROW_NUMBER

یک عدد integer ترتیبی منحصر به فرد برای ردیف‌های درون بخشی از مجموعه نتیجه اختصاص می‌دهد، اولین ردیف از 1 آغاز می‌شود.

 

در ادامه به توضیح این توابع می‌پردازیم.

تابع CUME_DIST در SQL Server

گاهی، می‌خواهید گزارشی درست کنید که شامل x درصد بالا یا پایین از یک مجموعه داده باشد (مثلا 5 درصد کارکنان فروش بر اساس فروش خالص). یک روش برای دستیابی به این امر در SQL Server، استفاده از تابع ()CUME_DIST است.

تابع ()CUME_DIST توزیع افزوده یک مقدار درون گروهی از مقادیر را محاسبه می‌کند. به زبان ساده، موقعیت نسبی یک مقدار در گروهی از مقادیر را محاسبه می‌کند.

کد زیر، syntax تابع ()CUME_DIST را نشان می‌دهد:

CUME_DIST() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

اجازه دهید این syntax را دقیق‌تر بررسی کنیم.

دستور PARTITION BY

دستور PARTITION BY ردیف‌ها را درون بخش یا پارتیشن‌هایی که تابع ()CUME_DIST برای آن‌ها اعمال می‌شود، توزیع می‌کند.

دستور PARTITION BY اختیاری است. اگر دستور PARTITION BYرا حذف کنید، تابع ()CUME_DIST با کل مجموعه نتیجه به عنوان یک تک بخش یا تک پارتیشن برخورد می‌کند.

دستور ORDER BY

دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشنی که تابع ()CUME_DIST برای آن اعمال می‌شود، مشخص می‌کند. دستور ORDER BY مقادیر NULL را به عنوان کمترین مقادیر ممکن در نظر می‌گیرد.

مقدار Return

نتیجه تابع ()CUME_DIST بیشتر از 0 و کمتر یا مساوی با 1 است.

0 < CUME_DIST() <= 1

این تابع برای مقادیر مساوی و یکسان،  مقدار مشابهی از جمع مقادیر توزیع شده را بازمی‌گرداند.

مثال‌های تابع ()CUME_DIST در SQL Server

اجازه دهید چند مثال برای استفاده از تابع ()CUME_DIST حل کنیم.

الف) استفاده از تابع ()CUME_DIST برای یک مجموعه نتیجه

کد زیر، درصد فروش برای هر کارمند فروش را در سال 2017 محاسبه می‌کند:

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    CUME_DIST() OVER (
        ORDER BY net_sales DESC
    ) cume_dist
FROM 
    sales.vw_staff_sales t
INNER JOIN sales.staffs m on m.staff_id = t.staff_id
WHERE 
    year = 2017;

خروجی به این شکل است:

نتایج

همانطور که در خروجی مشاهده می‌کنید، ۵۰ درصد از کارمندان فروش دارای فروش خالص بیشتر از 285K هستند.

ب) استفاده ز تابع ()CUME_DIST برای یک پارتیشن یا بخش

این مثال، از تابع ()CUME_DIST برای محاسبه درصد فروش برای هر کارمند فروش در سال‌های 2016 و 2017 استفاده می‌کند:

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    net_sales, 
    year,
    CUME_DIST() OVER (
        PARTITION BY year
        ORDER BY net_sales DESC
    ) cume_dist
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 ردیف‌هایی که تابع ()CUME_DIST برای آن‌ها اعمال می‌شود را در هر پارتیشن بر اساس فروش خالص از زیاد به کم مرتب می‌کند.

برای به دست آوردن ۲۰ درصد بالای کارمندان فروش بر اساس فروش خالص در سال 2016 و 2017، می‌توانید از query زیر استفاده کنید:

WITH cte_sales AS (
    SELECT 
        CONCAT_WS(' ',first_name,last_name) full_name,
        net_sales, 
        year,
        CUME_DIST() OVER (
            PARTITION BY year
            ORDER BY net_sales DESC
        ) cume_dist
    FROM 
        sales.vw_staff_sales t
        INNER JOIN sales.staffs m  
            ON m.staff_id = t.staff_id
    WHERE 
        year IN (2016,2017)
)
SELECT 
    * 
FROM 
    cte_sales
WHERE 
    cume_dist <= 0.20;

تصویر زیر، خروجی را نشان می‌دهد:

نتایج

به این شکل، چگونگی استفاده از تابع  ()CUME_DIST برای محاسبه توزیع افزوده یک مقدار در یک گروه از مقادیر را آموختید.

تابع DENSE_RANK در SQL Server

تابع ()DENSE_RANK یک window function است که یک rank یا رتبه به هر ردیف درون یک پارتیشن از یک مجموعه نتیجه، اختصاص می‌دهد. بر خلاف تابع ()RANK تابع ()DENSE_RANK مقادیر rank متوالی را بازمی‌گرداند.

اگر ردیف‌ها در هر پارتیشن دارای مقادیر یکسان باشند، rankهای مشابهی دریافت می‌کنند.

Syntax تابع ()DENSE_RANK به شکل زیر است:

DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

تابع ()DENSE_RANK برای ردیف‌های هر پارتیشن تعریف شده توسط دستور PARTITION BY، که دارای یک ترتیب مشخص شده توسط دستور ORDER BY هستند، اعمال می‌شوند. وقتی یک مقدار از مرز و سر حد پارتیشن عبور می‌کند، تابع rank را ریست می‌کند.

دستور PARTITION BY اختیاری است. اگر آن را حذف کنید، تابع با کل مجموعه نتیجه به عنوان یک پارتیشن کلی رفتار می‌کند.

آشنایی بیشتر با تابع ()DENSE_RANK

کد زیر، یک جدول جدید به نام dense_rank_demo ایجاد کرده و چند ردیف به آن جدول اضافه می‌کند:

CREATE TABLE sales.dense_rank_demo (
 v VARCHAR(10)
);
 
INSERT INTO sales.dense_rank_demo(v)
VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');
 
 
SELECT 
 *
FROM
 sales.dense_rank_demo;

کد زیر، از هر دو توابع ()DENSE_RANK و ()RANK برای اختصاص دادن یک Rank به هر ردیف از مجموعه نتیجه، استفاده می‌کند:

SELECT
 v,
 DENSE_RANK() OVER (
 ORDER BY v
 ) my_dense_rank,
 RANK() OVER (
 ORDER BY v
 ) my_rank
FROM
 sales.dense_rank_demo;

خروجی به این ترتیب است:

SELECT
 v,
 DENSE_RANK() OVER (
 ORDER BY v
 ) my_dense_rank,
 RANK() OVER (
 ORDER BY v
 ) my_rank
FROM
 sales.dense_rank_demo;

مثال‌های تابع ()DENSE_RANK در SQL Server

از جدول production.products برای مثال‌های تابع ()DENSE_RANK استفاده می‌کنیم:

جدول production.products

الف) استفاده از تابع ()DENSE_RANK برای یک مجموعه نتیجه

مثال زیر، از تابع ()DENSE_RANK برای رتبه‌دهی یا rank دادن به محصولات بر اساس قیمت‌ها استفاده می‌کند:

SELECT
 product_id,
 product_name,
 list_price,
 DENSE_RANK () OVER ( 
 ORDER BY list_price DESC
 ) price_rank 
FROM
 production.products;

خروجی به این ترتیب است:

نتایج

ب) استفاده از تابع ()DENSE_RANK برای پارتیشن‌ها

کد زیر، محصولات موجود در هر دسته‌بندی را بر اساس قیمت‌ها رتبه‌دهی یا Rank دهی می‌کند. این کد فقط 3 محصول بالایی از هر دسته‌بندی را بر اساس قیمت‌ها بازمی‌گرداند.

SELECT * FROM (
 SELECT
 product_id,
 product_name,
 category_id,
 list_price,
 DENSE_RANK () OVER ( 
 PARTITION BY category_id
 ORDER BY list_price DESC
 ) price_rank 
 FROM
 production.products
) t
WHERE price_rank < 3;

تصویر زیر، خروجی را نشان می‌دهد:

نتایج

در نتیجه، چگونگی استفاده از تابع ()DENSE_RANK برای اختصاص دادن یک Rank‌ یا رتبه به هر ردیف درون یک پارتیشن از یک مجموعه نتیجه(که هیچ فاصله یا gap در مقادیر rank وجود ندارد) را نیز آموختید.

تابع FIRST_VALUE در SQL Server

تابع ()FIRST_VALUE یک window function است که اولین مقدار در یک پارتیشن مرتب شده از یک مجموعه نتیجه را بازمی‌گرداند.

کد زیر، syntax تابع ()FIRST_VALUE را نشان می‌دهد:

FIRST_VALUE ( scalar_expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [rows_range_clause]
)  

در این syntax:

  • Scalar_expression یک عبارت است که مقدار آن عبارت است از مقدار اولین ردیف از پارتیشن مرتب شده از یک مجموعه نتیجه. Scalar_expression می‌تواند یک ستون، یک زیر Query یا یک عبارت باشد که نتیجه و مقدار آن یک مقدار تکی باشد. این کد نمی‌تواند یک window function باشد.

  • دستور PARTITION BY ردیف‌های مجموعه نتیجه را به پارتیشن‌هایی توزیع می‌کند که تابع ()FIRST_VALUE برای آن‌ها اعمال می‌شود. اگر دستور PARTITION BY را ننویسید، آنگاه تابع ()FIRST_VALUE با کل مجموعه نتیجه، به عنوان یک پارتیشن کلی رفتار می‌کند.

  • دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشن که تابع ()FIRST_VALUE برای آن اعمال می‌شود را مشخص می‌کند.

  • کد rows_range_clause با تعریف نقاط آغاز و پایان، ردیف‌های درون پارتیشن را بیشتر محدود می‌کند.

مثال‌های تابع ()FIRST_VALUE در SQL Server

دستور زیر، یک view جدید به نام sales.vw_category_sales_volume ایجاد می‌کند که تعداد محصولات فروخته شده را بر اساس دسته‌بندی محصول و سال بازمی‌گرداند.

CREATE VIEW 
    sales.vw_category_sales_volume 
AS
SELECT 
    category_name, 
    YEAR(order_date) year, 
    SUM(quantity) qty
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.product_id
GROUP BY 
    category_name, 
    YEAR(order_date);

داده‌های درون view عبارت‌اند از:

SELECT 
    *
FROM 
    sales.vw_category_sales_volume
ORDER BY 
    year, 
    category_name, 
    qty;

نتایج

الف) استفاده از تابع ()FIRST_VALUE برای یک مجموعه نتیجه

این مثال از تابع ()FIRST_VALUE برای بازگرداندن نام دسته‌بندی دارای کمترین میزان فروش در سال 2017 را بازمی‌گرداند:

SELECT 
    category_name,
    year,
    qty,
    FIRST_VALUE(category_name) OVER(
        ORDER BY qty
    ) lowest_sales_volume
FROM 
    sales.vw_category_sales_volume
WHERE
    year = 2017;

خروجی به این شکل است:

نتایج

در این مثال:

  • دستور PARTITION BY به کار نرفته است، بنابراین کل مجموعه نتیجه به عنوان یک پارتیشن کلی در نظر گرفته شده است.

  • دستور ORDER BY ردیف‌ها را بر اساس مقدار (qty) از کم به زیاد در هر پارتیشن ذخیره کرده است.
ب) استفاده از تابع ()FIRST_VALUE برای پارتیشن‌ها

مثال زیر از تابع ()FIRST_VALUE برای بازگرداندن دسته‌بندی‌های محصول دارای کمترین میزان فروش در سال 2016 و 2017 استفاده می‌کند:

SELECT 
    category_name,
    year,
    qty,
    FIRST_VALUE(category_name) OVER(
        `PARTITION BY` year
        ORDER BY qty
    ) lowest_sales_volume
FROM 
    sales.vw_category_sales_volume
WHERE
    year BETWEEN 2016 AND 2017;

تصویر زیر، خروجی را نشان می‌دهد:

نتایج

در این مثال:

  • دستور PARTITION BY ردیف‌ها را بر اساس سال به دو پارتیشن توزیع یا تقسیم کرده است، یکی برای سال 2016 و دیگری برای سال 2017.

  • دستور ORDER BY ردیف‌ها را در هر پارتیشن بر اساس مقدار (qty) مرتب کرده است.

  • تابع ()FIRST_VALUE برای هر پارتیشن به صورت مجزا اعمال شده است. برای اولین پارتیشن، مقدار Electric Bikes و برای دومین پارتیشن مقدار Cofort Bicycles را بازمی‌گرداند، چون این دسته‌بندی‌ها اولین ردیف‌ها در هر پارتیشن هستند.

به این شکل، چگونگی استفاده از تابع ()FIRST_VALUE برای بازگرداندن اولین مقدار در هر پارتیشن مرتب شده از یک مجموعه نتیجه را نیز آموختید.

تابع LAG در SQL Server

تابع ()LAG یک window function است که به یک ردیف در یک offset فیزیکی مشخص شده که قبل از ردیف فعلی می‌آید، دسترسی فراهم می‌کند.

به عبارت دیگر، با استفاده از تابع ()LAG، از ردیف فعلی، می‌توانید به داده‌های ردیف قبلی یا ردیفی که قبل از ردیف قبلی قرار دارد (و الی آخر) دسترسی پیدا کنید.

تابع ()LAG می‌تواند برای مقایسه مقدار ردیف فعلی با مقدار ردیف قبلی بسیار مفید باشد.

کد زیر، syntax تابع ()LAG را نشان می‌دهد:

LAG(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 است.

  • کد default مقداری است که اگر offset بیشتر از محدوده یا دامنه پارتیشن شود، بازگردانده خواهد شد. اگر خودتان آن را مشخص نکنید، مقدار پیش‌فرض آن برابر با NULL است.

  • دستور PARTITION BY ردیف‌های مجموعه نتیجه را به پارتیشن‌هایی که تابع ()LAG برای آن‌ها اعمال می‌شود، توزیع یا تقسیم می‌کند. اگر دستور PARTITION BY را حذف کنید، تابع با کل مجموعه نتیجه، به عنوان یک پارتیشن کلی رفتار خواهد کرد.

  • دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشنی که تابع ()LAG برای آن اعمال می‌شود، مشخص می‌کند.

مثال‌های تابع ()LAG در SQL Server

دوباره از view به نام sales.vw_netsales_brands ایجاد شده در قسمت تابع ()FIRST_VALUE برای این مثال استفاده می‌کنیم.

Query زیر، داده‌های view را نشان می‌دهد:

SELECT 
 *
FROM 
 sales.vw_netsales_brands
ORDER BY 
 year, 
 month, 
 brand_name, 
 net_sales;

خروجی به این شکل است:

نتایج

الف) استفاده از تابع ()LAG برای یک مجموعه نتیجه

این مثال از تابع ()LAG برای بازگرداندن فروش‌های خالص ماه فعلی و ماه قبلی در سال 2018 را بازمی‌گرداند:

WITH cte_netsales_2018 AS(
 SELECT 
 month, 
 SUM(net_sales) net_sales
 FROM 
 sales.vw_netsales_brands
 WHERE 
 year = 2018
 GROUP BY 
 month
)
SELECT 
 month,
 net_sales,
 LAG(net_sales,1) OVER (
 ORDER BY month
 ) previous_month_sales
FROM 
 cte_netsales_2018;

خروجی به این ترتیب است:

نتایج

در این مثال:

  • ابتدا، CTE فروش‌های خالص جمع شده را بر اساس ماه بازمی‌گرداند.

  • سپس، qery بیرونی از تابع ()LAG برای بازگرداندن فروش‌های ماه قبلی استفاده می‌کند.
ب) استفاده از تابع ()LAG برای پارتیشن‌ها

کد زیر از تابع ()LAG برای مقایسه فروش‌های ماه فعلی با ماه قبل از هر برند در سال 2018 استفاده می‌کند:

SELECT 
 month,
 brand_name,
 net_sales,
 LAG(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 ردیف‌ها را بر اساس ماه مرتب می‌کند.

  • برای هر ردیف در هر پارتیشن، تابع ()LAG فروش‌های خالص ردیف قبلی را بازمی‌گرداند.

برای مقایسه فروش‌های ماه فعلی با ماه قبلی بر اساس برند در سال 2018، می‌توانید از QUERY زیر استفاده کنید:

WITH cte_sales AS (
 SELECT 
 month,
 brand_name,
 net_sales,
 LAG(net_sales,1) OVER (
 PARTITION BY brand_name
 ORDER BY month
 ) previous_sales
 FROM 
 sales.vw_netsales_brands
 WHERE
 year = 2018
)
SELECT 
 month, 
 brand_name,
 net_sales, 
 previous_sales,
 FORMAT(
 (net_sales - previous_sales)  / previous_sales,
 'P'
 ) vs_previous_month
FROM
 cte_sales;

خروجی به این شکل است:

نتایج

به این صورت، چگونگی استفاده از تابع ()LAG برای دسترسی به یک ردیف  در یک offset فیزیکی مشخص شده که قبل از ردیف فعلی می‌آید را نیز آموختید.

تابع LAST_VALUE در SQL Server

تابع ()LAST_VALUE یک window function است که آخرین مقدار در یک پارتیشن مرتب شده از یک مجموعه نتیجه را بازمی‌گرداند.

کد زیر، syntax تابع ()LAST_VALUE را نشان می‌دهد:

LAST_VALUE ( scalar_expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    [rows_range_clause]
)  

در این syntax:

  • Scalar_expression یک عبارت است که مقدار آخرین ردف در یک پارتیشن مرتب از یک مجموعه نتیجه را نگه می‌دارد. scalar_expression می‌تواند یک ستون، زیر query یا عبارت باشد که یک تک مقدار را نگه می‌دارد. همچنین نمی‌تواند یک window function باشد.

  • دستور PARTITION BY ردیف‌های مجموعه نتیجه را به پارتیشن‌هایی که تابع ()LAST_VALUE برای آن‌ها اعمال می‌شود، توزیع یا تقسیم می‌کند. اگر دستور PARTITION BY را ننویسید، آنگاه تابع ()LAST_VALUE با مجموعه نتیجه به عنوان یک پارتیشن کلی برخورد می‌کند.

  • دستور ORDER BY ترتیب منطقی ردیف‌ها در هر پارتیشنی که تابع ()LAST_VALUE برای آن اعمال می‌شود را مشخص می‌کند.

  • کد rows_range_clause ردیف‌های درون یک پارتیشن را با تعریف نقاط آغاز و پایان، محدودتر می‌کند.

مثال‌های تابع ()LAST_VALUE در SQL Server

از view به نام salse.vw_category_sales_volume که در قسمت تابع ()FIRST_VALUE ایجحاد کردیم برای مثال‌های تابع ()LAST_VALUE استفاده می‌کنیم.

Query زیر داده‌های view را نشان می‌دهد:

SELECT 
    category_name, 
    year, 
    qty
FROM 
    sales.vw_category_sales_volume
ORDER BY 
    year, 
    category_name, 
    qty;

نتایج

الف) استفاده از تابع ()LAST_VALUE برای یک مجموعه نتیجه

این مثال از تابع ()LAST_VALUE برای بازگرداندن نام دسته‌بدی که دارای بیشترین میزان فروش است، استفاده می‌کند:

SELECT 
    category_name,
    year,
    qty,
    LAST_VALUE(category_name) OVER(
        ORDER BY qty
         RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) highest_sales_volume
FROM 
    sales.vw_category_sales_volume
WHERE
    year = 2016;

خروجی به این شکل است:

نتایج

در این مثال:

  • دستور PARTITION BY به کار نرفته است، بنابراین کل مجموعه نتیجه به عنوان یک پارتیشن در نظر گرفته شده است.

  • دستور ORDER BY ردیف‌ها را در هر پارتیشن بر اساس مقدار (qty) از کم به زیاد مرتب کرده است.

  • دستور RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING قالب درون پارتیشن را تعریف می‌کند که از اولین ردیف شروع شده و در آخرین ردیف پایان می‌یابد.
ب) استفاده از تابع ()LAST_VALUE برای پارتیشن‌ها

مثال زیر از تابع ()LAST_VALUE برای بازگرداندن دسته‌بندی‌های محصول که دارای بیشترین میزان فروش در سال‌های 2016 و 2017 هستند، استفاده می‌کند.

SELECT 
    category_name,
    year,
    qty,
    LAST_VALUE(category_name) OVER(
 PARTITION BY year
        ORDER BY qty
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) highest_sales_volume
FROM 
    sales.vw_category_sales_volume
WHERE
    year IN (2016,2017);

تصویر زیر، خروجی را نشان می‌دهد:

نتایج

در این مثال:

  • دستور PARTITION BY ردیف‌ها را بر اساس سال به دو پارتیشن تقسیم می‌کند، یکی برای 2016 دیگری برای 2017.

  • دستور ORDER BY ردیف‌ها را در هر پارتیشن بر اساس مقدار (qty) از کم به زیاد مرتب می‌کند.

  • دستور RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING قالب را تعریف می‌کند که از اولین ردیف شروع شده و در آخرین ردیف پارتیشن پایان می‌یابد.

  • تابع ()LAST_VALUE برای هر پارتیشن به صورت مجزا اعمال شده است. برای اولین پارتیشن، مقدار Electric Bikes و برای دومین پارتیشن مقدار Comfort Bicycles را بازمی‌گرداند، چون این دسته‌بندی‌ها، آخرین ردیف‌ها در هر پارتیشن هستند.

در نتیجه، چگونگی استفاده از تابع ()LAST_VALUE برای بازگرداندن آخرین مقدار در یک پارتیشن مرتب شده از یک مجموعه نتیجه را نیز آموختید.

خلاصه

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

در این بخش توابع LAG، FIRST_VALUE، DENSE_RANK، CUME_DIST و LAST_VALUE مورد بررسی قرار گرفتند.

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

با 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 در جلسه قبلی بررسی شد. این مبحث که ...

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

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

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