آموزش جامع SQL Server (جلسه ۲۶: توابع تجمعی)
1398/04/15 09:17 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۲۶: توابع تجمعی)

یک تابع تجمعی، محاسبات یک یا چند مقدار را انجام می‌دهد و فقط یک مقدار بازمی‌گرداند. تابع تجمعی گاهی همراه با دستورات GROUP BY و HAVING که در داخل عبارت SELECT قرار گرفته باشند، می‌آیند.

جدول زیر توابع تجمعی در SQL Server را نمایش می‌دهد:

تابع تجمعی

توضیح

AVG

تابع تجمع ()AVG متوسط مقادیر غیر NULL را در یک مجموعه محاسبه می‌کند.

CHECKSUM_AGG

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

COUNT

تابع تجمع ()COUNT تعداد ردیف‌ها در یک گروه را بازمی‌گرداند، از جمله ردیف‌های دارای مقادیر NULL.

COUNT_BIG

تابع تجمع ()COUNT_BIG تعداد ردیف‌ها در یک گروه (که دارای نوع داده BIGINT هستند) را بازمی‌گرداند، به خصوص ردیف‌های دارای مقادیر NULL.

MAX

تابع تجمع ()MAX بیشترین مقدار (ماکسیمم) از یک مجموعه مقادیر غیر NULL بازمی‌گرداند.

MIN

تابع تجمع ()MIN کمترین مقدار (مینیمم) از یک مجموعه مقادیر غیر NULL را بازمی‌گرداند.

STDEV

تابع ()STDEV انحراف استاندارد آماری تمام مقادیر فراهم شده در عبارت را بر اساس یک نمونه از جمعیت داده بازمی‌گرداند.

STDEVP

تابع ()STDEVP نیز انحراف استاندارد برای تمام مقادیر فراهم شده در عبارت را بازمی‌گرداند، اما این کار را بر اساس کل جمعیت داده انجام می‌دهد.

SUM

تابع تجمع ()SUM مجموع مقادیر غیر NULL در یک مجموعه را بازمی‌گرداند.

VAR

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

VARP

تابع ()VARP واریانس آماری مقادیر در یک عبارت را بازمی‌گرداند، اما این کار را بر اساس کل جمعیت داده انجام می‌دهد.

 

Syntax تابع تجمعی در SQL Server

کد زیر syntax یک تابع تجمعی را نشان می‌دهد:

aggregate_function_name(DISTINCT | ALL expression)

در این syntax:

  • ابتدا، نام یک تابع تجمعی که می‌خواهیم استفاده کنیم (مانند SUM، AVG و MAX) را مشخص می‌کنیم.

  • دوم، اگر می‌خواهیم فقط مقادیر distinct (مجزا) در محاسبه لحاظ شوند از DISTINCT یا اگر می‌خواهیم تمام مقادیر در محاسبه لحاظ شوند از ALL استفاده می‌کنیم. به طور پیش‌فرض، ALL در صورتی استفاده می‌شود که هیچ تغییر‌ دهنده یا modifier مشخص نکنیم.

  • سوم، expression می‌تواند یک ستون از یک جدول یا یک عبارت باشد که شامل چندین ستون دارای عملگرهای ریاضی می‌باشند.

مثال‌های تابع تجمعی در SQL Server

برای مثال‌ها از جدول products از پایگاه داده نمونه Bikestores استفاده می‌کنیم.

جدول products

مثال AVG

کد زیر از تابع ()AVG برای بازگرداندن قیمت متوسط تمام محصولات در جدول products استفاده می‌کند:

SELECT
    AVG(list_price) avg_product_price
FROM
    production.products;

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

نتایج

چون قیمت به دلار است، پس حداکثر باید دو رقم اعشار داشته باشد. در نتیجه، باید نتیجه را به یک عدد دارای دو رقم اعشار گرد کنید. برای این منظور، از توابع ROUND و CAST به شکلی که در کد زیر می‌بینید استفاده می‌کنیم:

SELECT
    CAST(ROUND(AVG(list_price),2) AS DEC(10,2))
    avg_product_price
FROM
    production.products;

نتایج

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

مثال COUNT

کد زیر از تابع ()COUNT برای بازگرداندن تعداد محصولاتی که قیمت آن‌ها بزرگ‌تر از ۵۰۰ است، استفاده می‌کند:

SELECT
    COUNT(*) product_count
FROM
    production.products
WHERE
    list_price > 500;

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

نتایج

در این مثال:

  • ابتدا، دستور WHERE محصولاتی که قیمت آن‌ها بزرگ‌تر از ۵۰۰ هست را می‌یابد.

  • سپس، تابع COUNT تعداد محصولات دارای قیمت بیشتر از ۵۰۰ را بازمی‌گرداند.
مثال MAX

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

SELECT
    MAX(list_price) max_list_price
FROM
    production.products;

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

نتایج

مثال MIN

به طور مشابه، کد زیر از تابع ()MIN برای بازگرداندن کمترین قیمت از بین تمام محصولات استفاده می‌کند:

SELECT
    MIN(list_price) min_list_price
FROM
    production.products;     

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

نتایج

مثال SUM

برای نمایش عملکرد تابع ()SUM، از جدول stocks واقع در پایگاه داده نمونه Bikestores استفاده می‌کنیم.

جدول stocks

کد زیر از تابع ()SUM برای محاسبه کل میزان موجود محصولات از طریق id محصول در تمام انبارها استفاده می‌کند:

SELECT 
    product_id, 
    SUM(quantity) stock_count
FROM 
    production.stocks
GROUP BY
    product_id
ORDER BY 
    stock_count DESC;

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

نتایج

نحوه عملکرد کد به این شکل است:

  • ابتدا، دستور GROUP BY ردیف‌ها را با استفاده از id محصول در چند گروه جمع می‌کند.

  • سپس، تابع ()SUM مجموع مقادیر برای هر گروه را محاسبه می‌کند.
مثال STDEV

کد زیر از تابع ()STDEV برای محاسبه انحراف استاندارد آماری تمام قیمت‌ها استفاده می‌کند:

SELECT
    CAST(ROUND(STDEV(list_price),2) as DEC(10,2)) stdev_list_price
FROM
    production.products;

در ادامه به توضیح کامل و مفصل برخی توابع تجمعی خواهیم پرداخت.

تابع تجمعی AVG در SQL Server

تابع ()AVG یک تابع تجمعی است که متوسط مقدار یک گروه را بازمی‌گرداند.

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

AVG([ALL | DISTINCT] expression)

در این syntax:

  • ALL به تابع ()AVG دستور می‌دهد تا تمام مقادیر را برای محاسبه لحاظ کند. ALL به صورت پیش‌فرض مورد استفاده قرار می‌گیرد.

  • DISTINCT به تابع ()AVG دستور می‌دهد تا فقط روی مقادیر منحصر به فرد عمل کند.

  • Expression یک عبارت valid یا معتبر است که یک مقدار عددی را بازمی‌گرداند.

تابع ()AVG مقادیر NULL را نادیده می‌گیرد.

تابع ()AVG: ALL در برابر DISTINCT

کد زیر یک جدول جدید ایجاد می‌کند، چند مقدار به جدول وارد می‌کند و داده‌ها را از آن به دست می‌آورد:

CREATE TABLE t(
    val dec(10,2)
);
INSERT INTO t(val) 
VALUES(1),(2),(3),(4),(4),(5),(5),(6);
 
SELECT
    val
FROM
    t;

کد زیر از تابع ()AVG برای محاسبه متوسط تمام مقادیر در جدول t استفاده می‌کند:

SELECT
    AVG(ALL val)
FROM
    t;

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

نتایج

در این مثال، ما از modifier یا تغییر دهنده ALL استفاده کردیم، بنابراین، تابع Average همه ۸ مقدار موجود در ستون val را در محاسبه لحاظ می‌کند:

(1 + 2 + 3 + 4 + 4 + 5 + 5 + 6) /  8 = 3.75

کد زیر از تابع ()AVG همراه با modifier یا تغییر دهنده DISTINCT استفاده می‌کند:

SELECT
    AVG(DISTINCT val)
FROM
    t;

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

نتایج

به خاطر تغییر دهنده DISTINCT، تابع ()AVG محاسبه را روی مقادیر مجزا یا distinct انجام می‌دهد:

(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.5

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

اجازه دهید چند مثال انجام دهیم تا چگونگی عملکرد ()AVG را ببینیم.

الف) مثال ساده ()AVG

مثال زیر متوسط قیمت تمام محصولات را بازمی‌گرداند:

SELECT
    AVG(list_price)
FROM
    production.products;

در این مثال، تابع ()AVG یک مقدار برای کل جدول باز می‌گرداند.

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

نتایج

ب) مثال ()AVG همراه با GROUP BY

اگر از تابع ()AVG همراه با دستور GROUP BY استفاده کنید، تابع ()AVG به جای اینکه یک مقدار برای کل جدول بازگرداند، یک مقدار برای هر گروه بازمی‌گرداند.

مثال زیر متوسط قیمت برای هر دسته محصول را بازمی‌گرداند:

SELECT
    category_name,
    CAST(ROUND(AVG(list_price),2) AS DEC(10,2))
    avg_product_price
FROM
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id
GROUP BY
    category_name
ORDER BY
    category_name;

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

نتایج

پ) مثال ()AVG در داخل دستور HAVING

مثال زیر را ببینید:

SELECT
    brand_name,
    CAST(ROUND(AVG(list_price),2) AS DEC(10,2))
    avg_product_price
FROM
    production.products p
    INNER JOIN production.brands c ON c.brand_id = p.brand_id
GROUP BY
    brand_name
HAVING
    AVG(list_price) > 500
ORDER BY
    avg_product_price;

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

نتایج

در این مثال:

  • اول، دستور GROUP BY محصولات را بر اساس برندها به چند گروه تقسیم می‌کند.

  • دوم،، تابع ()AVG متوسط قیمت برای هر گروه را محاسبه می‌کند.

  • سوم، دستور HAVING برندی که متوسط قیمت آن کمتر از ۵۰۰ هست را حذف می‌کند.

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

تابع CHECKSUM_AGG در SQL Server

تابع ()CHECKSUM_AGG یک تابع تجمعی است که checksum مقادیر در یک مجموعه را بازمی‌گرداند.

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

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression)  

در این syntax:

  • ALL به تابع دستور می‌دهد checksum تمام مقادیر از جمله مقادیر تکراری را بازگرداند.

  • DISTINCT تابع را مجبور می‌کند تا checksum مقادیر distinct را محاسبه کند.

  • Expression یک عبارت عددی یا integer است. تابع subqueryها یا توابع تجمعی را نمی‌پذیرد.

تابع ()CHECKSUM_AGG مقادیر NULL را نادیده می‌گیرد.

به خاطر الگوریتم hashing، ممکن است که تابع ()CHECKSUM_AGG مقادیر مشابهی با داده‌های ورودی مختلف بازگرداند. در نتیجه، باید از این تابع زمانی استفاده کنید که برنامه شما بتواند تحمل گاهی از دست دادن یک تغییر را داشته باشد.

مثال تابع ()CHECKSUM_AGG در SQL Server

کد زیر یک جدول جدید با داده‌های به دست آمده از جدول production.stocks از پایگاه داده نمونه Bikestores بازمی‌گرداند. جدول جدید محصولات و مقادیر آن‌ها را ذخیره می‌کند:

SELECT
    product_id, 
    SUM(quantity) quantity
INTO 
    sales.inventory
FROM
    production.stocks
GROUP BY 
    product_id;

برای به دست آوردن checksum تجمعی از ستون quantity، می‌توانید از کد زیر استفاده کنید:

SELECT 
    CHECKSUM_AGG(quantity) qty_checksum_agg
FROM
    sales.inventory;

کد زیر خروجی را نشان می‌دهد:

qty_checksum_agg
----------------
29
 
(1 row affected)

اجازه دهید داده‌های جدول sales.inventory را تغییر دهیم:

UPDATE 
    sales.inventory
SET
    quantity = 10
WHERE
    product_id = 1;

و تابع ()CHECKSUM_AGG را برای ستون quantity اعمال کنیم:

SELECT 
    CHECKSUM_AGG(quantity) qty_checksum_agg
FROM
    sales.inventory;

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

qty_checksum_agg
----------------
32
 
(1 row affected)

همان‌طور که به وضوح در خروجی می‌بینید، نتیجه ()CHECKSUM_AGG تغییر کرده است. این یعنی اینکه داده‌های quantity از زمان آخرین محاسبه checksum تغییر کرده‌اند.

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

تابع COUNT در SQL Server

()COUNT یک تابع تجمعی است که تعداد آیتم‌های یافت شده در یک مجموعه را بازمی‌گرداند.

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

COUNT([ALL | DISTINCT  ] expression)

در این syntax:

  • ALL به تابع ()COUNT دستور می‌دهد تا برای همه مقادیر اعمال شود. ALL به صورت پیش‌فرض اجرا می‌شود.

  • DISTINCT به تابع ()COUNT دستور می‌دهد تا تعداد مقادیر غیر NULL یکتا را بازگرداند.

  • Expression یک عبارت است که می‌تواند هر نوعی باشد به جز text، image یا ntext. توجه کنید که نمی‌توانید از یک subqery یا یک تابع تجمعی در عبارت استفاده کنید.

تابع ()COUNT دارای یک فرم دیگر به شکل زیر نیز هست:

COUNT(*)

در این فرم، (*)COUNT تعداد ردیف‌ها در یک جدول مشخص شده را بازمی‌گرداند. (*)COUNT از DISTINCT پشتیبانی نمی‌کند و هیچ پارامتری نمی‌گیرد. این فرم از COUNT هر ردیف به صورت مجزا و جداگانه شمارش می‌شود و شامل ردیف‌هایی است که حاوی مقادیر NULL هستند.

به طور خلاصه:

  • (*)COUNT تعداد آیتم‌ها در یک مجموعه را شمارش می‌کند. این تابع می‌تواند حاوی NULL و مقادیر تکراری نیز باشد.

  • (CUONT(ALL EXPRESSION عبارت یا expression در کد را برای هر ردیف در یک مجموعه را ارزیابی می‌کند و تعداد مقادیر غیر NULL را بازمی‌گرداند.

  • (COUNT(DISTINCT expression عبارت یا Expression را برای هر ردیف در یک مجموعه ارزیابی می‌کند و تعداد مقادیر یکتا و غیر NULL را بازمی‌گرداند.

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

کد زیر یک جدول جدید به نام t ایجاد می‌کند، مقداری داده به آن وارد می‌کند و داده‌ها را از آن به دست می‌آورد:

CREATE TABLE t(
    val INT
);
 
INSERT INTO t(val)
VALUES(1),(2),(2),(3),(null),(null),(4),(5);
 
SELECT
    val
FROM
    t;

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

val
-----------
1
2
2
3
NULL
NULL
4
5

مثال (*)COUNT در SQL Server

(*)COUNT تمام ردیف‌ها در یک جدول مشخص شده را بازمی‌گرداند که در کد زیر مشاهده می‌کنید:

SELECT
    COUNT(*) val_count
FROM
    t;

خروجی عبارت است از:

val_count
-----------
8
 
(1 row affected)
مثال (COUNT(DISTINCT exptession در SQL Server

مثال زیر از (COUNT(DISTINCT expression برای بازگرداندن تعداد مقادیر یکتای غیر NULL در جدول t استفاده می‌کند:

SELECT
    COUNT(DISTINCT val) val_count
FROM
    t;

کد زیر خروجی را نشان می‌دهد:

val_count
-----------
5
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(1 row affected)
مثال (COUNT(expression در SQL Server

مثال زیر از (COUNT(exspression برای بازگرداندن تعداد مقادیر غیر NULL در جدول t استفاده می‌کند:

SELECT
    COUNT(val)
FROM
    t;

کد زیر خروجی را نشان می‌دهد:

val_count
-----------
6
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(1 row affected)

تابع ()COUNT: مثال‌های عملی

کد زیر تعداد محصولات در جدول products را بازمی‌گرداند:

SELECT 
    COUNT(*) product_count
FROM
    production.products;

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

product_count
-------------
321
 
(1 row affected)
مثال تابع ()COUNT با دستور GROUP BY

کد زیر  از تابع (*)COUNT برای یافتن تعداد محصولات در هر دسته محصول استفاده می‌کند:

SELECT 
    category_name,
    COUNT(*) product_count
FROM
    production.products p
    INNER JOIN production.categories c 
    ON c.category_id = p.category_id
GROUP BY 
    category_name
ORDER BY
    product_count DESC;

کد زیر خروجی را نشان می‌دهد:

category_name        product_count
-------------------- -------------
Cruisers Bicycles    78
Mountain Bikes       60
Road Bikes           60
Children Bicycles    59
Comfort Bicycles     30
Electric Bikes       24
Cyclocross Bicycles  10
 
(7 rows affected)

در این مثال، ابتدا دستور GROUP BY محصولات را با استفاده از نام دسته به چند گروه تقسیم می‌کند، سپس تابع ()COUNT برای هر گروه اعمال می‌شود.

مثال تابع ()COUNT با دستور HAVING

کد زیر برند و تعداد محصولات برای هر برند را بازمی‌گرداند. علاوه بر این، فقط برندهایی که دارای تعداد محصولات بیشتر از ۲۰ عدد هستند را بازمی‌گرداند:

SELECT 
    brand_name,
    COUNT(*) product_count
FROM
    production.products p
    INNER JOIN production.brands c 
    ON c.brand_id = p.brand_id
GROUP BY 
    brand_name
HAVING
    COUNT(*) > 20
ORDER BY
    product_count DESC;

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

brand_name           product_count
-------------------- -------------
Trek                 135
Electra              118
Surly                25
Sun Bicycles         23
 
(4 rows affected)

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

تابع MAX در SQL Server

تابع ()MAX یک تابع تجمعی است که مقدار ماکسیمم از یک مجموعه را بازمی‌گرداند.

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

MAX(expression)

تابع ()MAX یک عبارت را می‌پذیرد که این عبارت می‌تواند یک ستون یا یک عبارت معتبر باشد.

تابع ()MAX، همانند تابع ()MIN مقادیر NULL را نادیده می‌گیرد و تمام مقادیر را در یک محاسبه لحاظ می‌کند.

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

برای مثال‌ها از جداول products و brands استفاده می‌کنیم:

جداول products و brands

یافتن بیشترین قیمت با استفاده از ()MAX

کد زیر از تابع ()MAX برای یافتن بیشترین قیمت از بین تمام محصولات در جدول products استفاده می‌کند:

SELECT
    MAX(list_price) max_list_price
FROM
    production.products;

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

نتایج

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

SELECT 
    product_id,
    product_name,
    list_price
FROM 
    production.products
WHERE 
    list_price = (
        SELECT 
            MAX(list_price )
        FROM
            production.products);

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

نتایج

در این مثال:

  • ابتدا، subquery از تابع ()MAX برای بازگرداندن بیشترین قیمت از بین تمام محصولات استفاده کرده است.

  • سپس، query بیرونی محصولی را انتخاب کرده است که قیمت آن برابر با بیشترین قیمت بازگشت داده شده از subquery است.
مثال تابع ()MAX همراه با دستور GROUP BY

کد زیر نام برند را می‌گیرد و بیشترین قیمت محصولات را در هر برند بازمی‌گرداند:

SELECT
    brand_name,
    MAX(list_price) max_list_price
FROM
    production.products p
    INNER JOIN production.brands b
        ON b.brand_id = p.brand_id 
GROUP BY
    brand_name
ORDER BY
    brand_name;

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

نتایج

در این مثال:

  • ابتدا، دستور GROUP BY محصولات را بر اساس نام‌های برند به چند گروه تقسیم می‌کند.

  • سپس، تابع ()MAX برای هر گروه اعمال می‌شود تا بیشترین قیمت برای هر برند را بازگرداند.
مثال تابع ()MAX با دستور HAVING

کد زیر نام‌های برند و بیشترین قیمت برای هر کدام از آن‌ها را می‌یابد. علاوه بر این، از دستور HAVING برای فیلتر محصولاتی که بیشترین قیمت آن‌ها کمتر یا برابر با ۱۰۰۰ باشد استفاده می‌کند:

SELECT
    brand_name,
    MAX(list_price) max_list_price
FROM
    production.products p
    INNER JOIN production.brands b
        ON b.brand_id = p.brand_id 
GROUP BY
    brand_name
HAVING 
    MAX(list_price) > 1000
ORDER BY
    max_list_price DESC;

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

نتایج

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

تابع MIN در SQL Server

تابع ()MIN یک تابع تجمعی است که به شما اجازه می‌دهد حداقل مقدار را در یک مجموعه بیابید.

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

MIN(expression)

تابع ()MIN یک عبارت می‌پذیرد که این عبارت می‌تواند یک ستون یا یک عبارت معتبر باشد. تابع ()MIN برای همه مقادیر در یک مجموعه اعمال می‌شود. یعنی اینکه تغییر دهنده DISTINCT هیچ تأثیری روی تابع ()MIN ندارد.

توجه کنید که تابع ()MIN مقادیر NULL را نادیده می‌گیرد.

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

از جداول products و categories از پایگاه داده نمونه Bikestores برای مثال‌ها استفاده می‌کنیم.

جداول products و categories

مثال ساده تابع ()MIN

مثال زیر کمترین قیمت از بین تمام محصولات را می‌یابد:

SELECT
    MIN(list_price) min_list_price
FROM
    production.products;

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

نتایج

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

SELECT 
    product_id,
    product_name,
    list_price
FROM 
    production.products
WHERE 
    list_price = (
        SELECT 
            MIN(list_price )
        FROM
            production.products);

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

نتایج

ابتدا، subquery از تابع ()MIN برای بازگرداندن کمترین قیمت استفاده می‌کند، سپس query بیرونی محصولی که قیمت آن برابر با کمترین قیمت است را می‌یابد.

مثال تابع ()MIN با دستور GROUP BY

کد زیر کمترین قیمت برای هر دسته محصول را می‌یابد:

SELECT
    category_name,
    MIN(list_price) min_list_price
FROM
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id 
GROUP BY
    category_name
ORDER BY
    category_name;

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

نتایج

در این syntax:

  • ابتدا، دستور GROUP BY محصولات را بر اساس نام دسته به چند گروه تقسیم می‌کند.

  • سپس، تابع ()MIN برای هر گروه اعمال می‌شود تا کمترین قیمت را برای هر گروه بیابد.
مثال تابع ()MIN درون دستور HAVING

مثال زیر از تابع ()MIN درون دستور HAVING استفاده می‌کند تا دسته محصول دارای حداقل قیمت کمتر از ۵۰۰ را به دست آورد:

SELECT
    category_name,
    MIN(list_price) min_list_price
FROM
    production.products p
    INNER JOIN production.categories c 
        ON c.category_id = p.category_id 
GROUP BY
    category_name
HAVING
    MIN(list_price) > 500
ORDER BY
    category_name;

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

نتایج

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

تابع SUM در SQL Server

تابع ()SUM یک تابع تجمعی است که مجموعه تمام یا مقادیر مجزا (distinct) در یک عبارت را محاسبه می‌کند.

Syntax تابع ()SUM به این شکل است:

SUM([ALL | DISTINCT ] expression)

در این syntax:

  • ALL به تابع ()SUM دستور می‌دهد تا مجموع، تمام مقادیر (از جمله مقادیر تکراری) را بازگرداند. ALL به صورت پیش‌فرض اجرا می‌شود.

  • DISTINCT به تابع ()SUM دستور می‌دهد تا مجموعه فقط مقادیر مجزا را محاسبه کند.

  • Expression هر عبارت معتبری است که یک مقدار عددی دقیق یا تقریبی را بازگرداند. توجه کنید که توابع تجمعی یا subqueryها در عبارت پذیرفته نمی‌شوند.

تابع ()SUM مقادیر NULL را نادیده می‌گیرد.

ALL در برابر DISTINCT

اجازه دهید یک جدول جدید برای نمایش تفاوت بین ALL و DISTINCT ایجاد کنیم:

CREATE TABLE t(
    val INT
);
 
INSERT INTO t(val)
VALUES(1),(2),(3),(3),(4),(NULL),(5);
 
SELECT
    val
FROM
    t;

نتایج

کد زیر مجموع تمام مقادیر در ستون val را بازمی‌گرداند:

SELECT
    SUM(val) total
FROM
    t;

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

total
-----------
18
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(1 row affected)

به هر حال، وقتی از تغییر دهنده DISTINCT استفاده می‌کنیم، تابع ()SUM مجموع فقط مقادیر یکتا در ستون val را بازمی‌گرداند:

SELECT
    SUM(DISTINCT val) total
FROM
    t;

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

total
-----------
15
Warning: Null value is eliminated by an aggregate or other SET operation.
 
(1 row affected)

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

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

الف) مثال ساده تابع ()SUM

کد زیر کل مقدار موجود از تمام محصولات در تمام فروشگاه‌ها را باز می‌گرداند:

SELECT 
    SUM(quantity) total_stocks
FROM 
    production.stocks;

کد زیر خروجی را نشان می‌دهد:

total_stocks
------------
13511
 
(1 row affected)
ب) مثال تابع ()SUM همراه با GROUP BY

دستور زیر کل مقدار موجود را بر اساس id فروشگاه می‌یابد:

SELECT
    store_id,
    SUM(quantity) store_stocks
FROM
    production.stocks
GROUP BY
    store_id;

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

نتایج

در این مثال:

  • ابتدا، دستور GROUP BY مقادیر موجود را بر اساس id فروشگاه به چند گروه تقسیم می‌کند.

  • سپس، تابع ()SUM برای هر گروه اعمال می‌شود تا کل مقادیر موجود برای هر گروه محاسبه شود.

اگر می‌خواهید نام فروشگاه را به جای id فروشگاه نمایش دهید، می‌توانید از کد زیر استفاده کنید:

SELECT
    store_name,
    SUM(quantity) store_stocks
FROM
    production.stocks w
    INNER JOIN sales.stores s
        ON s.store_id = w.store_id
GROUP BY
    store_name;

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

نتایج

پ) مثال تابع ()SUM درون دستور HAVING

کد زیر مقادیر موجود برای هر محصول را می‌یابد و فقط محصولاتی که مقادیر موجود آن‌ها بیشتر از ۱۰۰ هست را بازمی‌گرداند:

SELECT
    product_name,
    SUM(quantity) total_stocks
FROM
    production.stocks s
    INNER JOIN production.products p
        ON p.product_id = s.product_id
GROUP BY
    product_name
HAVING
    SUM(quantity) > 100
ORDER BY
    total_stocks DESC;

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

نتایج

ت) مثال تابع ()SUM همراه با عبارت

کد زیر از یک عبارت در تابع ()SUM برای محاسبه مقدار خالص برای هر سفارش فروش را محاسبه می‌کند:

SELECT
    order_id,
    SUM(
        quantity * list_price * (1 - discount)
    ) net_value
FROM
    sales.order_items
GROUP BY
    order_id
ORDER BY
    net_value DESC;

خروجی به شرح زیر است:

نتایج

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

خلاصه

در این جلسه به مبحث توابع تجمعی در SQL Server پرداختیم. با تمام این توابع آشنا شدید، نحوه عملکرد آن‌ها توضیح داده شد و برخی از مهم‌ترین و پر کاربرد‌ترین آن‌ها به صورت مفصل و مثال محور توضیح داده شدند که عبارت بودند از: MIN، MAX، COUNT، COUNT_BIG، CHECKSUM_AGG، AVG و SUM.

در قسمت بعدی به توابع تاریخ و تقویمی در SQL Server خواهیم پرداخت.

با MUG همراه باشید!

 منبع: sqlservertutorial

 مطالب مرتبط

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

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

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

آخرین مطالب

آموزش جامع SQL Server (جلسه ۳۱: توابع رشته‌ای – بخش ۳)
آموزش جامع SQL Server (جلسه ۳۱: توابع رشته‌ای – بخش ۳)

در جلسه قبل بخش ۲ مطالب مبحث توابع رشته‌ای یا string را به صورت کامل توضیح دادیم. و ...

آموزش جامع SQL Server (جلسه ۳۰: توابع رشته‌ای – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۰: توابع رشته‌ای – بخش ۲)

در جلسه قبل، مبحث توابع رشته‌ای یا String را آغاز کردیم. به دلیل ازدیاد این توابع و برای ...

با Visual Studio Code’s Live Share گروهی برنامه‌نویسی کنیم!
با Visual Studio Code’s Live Share گروهی برنامه‌نویسی کنیم!

چه در مراحل اولیه یادگیری یک زبان برنامه‌نویسی باشید یا یک کد نویس با تجربه و ماهر باشید، ...

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

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

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