آموزش جامع SQL Server (جلسه هشت: گروه‌بندی داده‌ها)
1398/02/24 11:16 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه هشت: گروه‌بندی داده‌ها)

گروه‌بندی داده‌ها شامل دستور‌ها و روش‌های زیر است:

  • GROUP BY – نتایج query را بر اساس مقادیر موجود در یک لیست مشخص شده از ستون‌ها را گروه‌بندی می‌کند.
  • HAVING – یک شرط جستجو برای یک گروه یا یک مجموعه مشخص می‌کند.
  • GROUPING SETS – چندین grouping set ایجاد می‌کند.
  • CUBE– چندین Grouping set ایجاد می‌کند که شامل تمام ترکیبات ستون‌های dimention باشند.
  • ROLLUP – چندین grouping set با فرض وجود سلسله مراتب بین ستون‌های ورودی ایجاد می‌کند.

در این جلسه به صورت مفصل و کامل تمام موارد بالا را بررسی خواهیم کرد. با ما همراه باشید!

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

 

دستور GROUP BY در SQL Server

دستور GROUP BY به شما اجازه می‌دهد ردیف‌های یک query را به صورت چند گروه مرتب کنید. گروه‌ها با ستون‌هایی مشخص می‌شوند که شما در دستور GROUP BY معرفی می‌کنید.

کد زیر syntax دستور GROUP  BY را نشان می‌دهد:

SELECT
    select_list
FROM
    table_name
GROUP BY
    column_name1,
    column_name2 ,...;

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

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

SELECT
    customer_id,
    YEAR (order_date) order_year
FROM
    sales.orders
WHERE
    customer_id IN (1, 2)
ORDER BY
    customer_id;

نتایج

در این مثال، ما customer id و order year مشتریانی که customer id آن‌ها ۱ یا ۲ بود را بازگرداندیم.

همان‌طور که به وضوح در نتایج مشخص است، مشتری دارای id شماره ۱، یک سفارش در سال ۲۰۱۶ و ۲ سفارش در سال ۲۰۱۸ داشته است. مشتری با id شماره ۲، دو سفارش در سال ۲۰۱۷ و یک سفارش در سال ۲۰۱۸ داشته است.

حالا اجازه دهید یک دستور GROUP BY به query اضافه کنیم تا تأثیر آن را مشاهده کنیم:

SELECT
    customer_id,
    YEAR (order_date) order_year
FROM
    sales.orders
WHERE
    customer_id IN (1, 2)
GROUP BY
    customer_id,
    YEAR (order_date)
ORDER BY
    customer_id;

نتایج

دستور GROUP BY سه ردیف اول را در دو گروه مرتب کرد و سپس سه ردیف بعدی را در دو گروه دیگر مرتب کرد که دارای ترکیبات منحصر به فردی از customer id و order year هستند.

از لحاظ کاربرد، دستور GROUP BY در query بالا نتایج مشابهی با query پایین که از دستور DISTINCT استفاده می‌کند، ارائه می‌دهد:

SELECT DISTINCT
    customer_id,
    YEAR (order_date) order_year
FROM
    sales.orders
WHERE
    customer_id IN (1, 2)
ORDER BY
    customer_id;

نتایج

دستور GROUP BY و توابع تجمعی (aggregate) در SQL Server

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

برای مثال، تابع ()COUNT تعداد ردیف‌ها در هر گروه را بازمی‌گرداند. دیگر توابع تجمعی مرسوم عبارت‌اند از()AVG() ،SUM(میانگین)، ()MIN(مینیمم) و ()MAX(ماکسیمم).

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

برای مثال، query زیر تعداد سفارشات ارائه شده توسط مشتری را در هر سال باز می‌گرداند:

SELECT
    customer_id,
    YEAR (order_date) order_year,
    COUNT (order_id) order_placed
FROM
    sales.orders
WHERE
    customer_id IN (1, 2)
GROUP BY
    customer_id,
    YEAR (order_date)
ORDER BY
    customer_id; 

نتایج

اگر می‌خواهید به هر ستون یا عبارتی که در دستور GROUP BY لیست نشده است اشاره کنید، باید از آن ستون به عنوان ورودی یک تابع تجمعی استفاده کنید. در غیر این صورت، برنامه به شما ارور می‌دهد، چون هیچ تضمینی وجود ندارد که ستون یا عبارت در هر گروه مقداری بازگرداند. برای مثال، query زیر خطا می‌دهد:

SELECT
    customer_id,
    YEAR (order_date) order_year,
    order_status
FROM
    sales.orders
WHERE
    customer_id IN (1, 2)
GROUP BY
    customer_id,
    YEAR (order_date)
ORDER BY
    customer_id;

مثال‌های بیشتر از دستور GROUP BY

اجازه دهید چند مثال دیگر حل کنیم تا به خوبی عملکرد دستور GROUP BY را درک کنید.

مثال استفاده از دستور GROUP BY با تابع ()COUNT

Query زیر تعداد مشتریان در هر شهر را بازمی‌گرداند:

SELECT
    city,
    COUNT (customer_id) customer_count
FROM
    sales.customers
GROUP BY
    city
ORDER BY
    city;

نتایج

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

به طور مشابه، query زیر تعداد مشتریان بر اساس ایالت و شهر را بازمی‌گرداند.

SELECT
    city,
    state,
    COUNT (customer_id) customer_count
FROM
    sales.customers
GROUP BY
    state,
    city
ORDER BY
    city,
    state;

نتایج

مثال استفاده از دستور GROUP BY با توابع MIN و MAX

کد زیر قسمت‌های حداقل و حداکثر تمام محصولات مدل ۲۰۱۸ را بر اساس برند آن‌ها بازمی‌گرداند:

SELECT
    brand_name,
    MIN (list_price) min_price,
    MAX (list_price) max_price
FROM
    production.products p
INNER JOIN production.brands b ON b.brand_id = p.brand_id
WHERE
    model_year = 2018
GROUP BY
    brand_name
ORDER BY
    brand_name;

نتایج

در این مثال، مثل همیشه دستور WHERE قبل از دستور GROUP BY پردازش می‌شود.

مثال استفاده از دستور GROUP BY با تابع ()AVG

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

SELECT
    brand_name,
    AVG (list_price) avg_price
FROM
    production.products p
INNER JOIN production.brands b ON b.brand_id = p.brand_id
WHERE
    model_year = 2018
GROUP BY
    brand_name
ORDER BY
    brand_name;

نتایج

مثال استفاده از دستور GROUP BY با تابع SUM

جدول order_items را در نظر بگیرید:

جدول order_items

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

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

نتایج

به این صورت، چگونگی استفاده از دستور GROUP BY برای مرتب‌سازی ردیف‌ها در چند گروه بر اساس لیست خاصی از ستون‌ها را به صورت کامل و شامل آموختید.

دستور HAVING در SQL Server

دستور HAVING گاهی برای فیلتر کردن گروه‌ها بر اساس لیست مشخص شده‌ای از شروط، همراه با دستور GROUP BY به کار می‌رود. کد زیر suntax دستور HAVING را نشان می‌دهد:

SELECT
    select_list
FROM
    table_name
GROUP BY
    group_list
HAVING
    conditions;

در این syntax، دستور GROUP BY ردیف‌ها را به چند گروه خلاصه کرده و دستور HAVING یک یا چند شرط به این گروه‌ها اضافه می‌کند. فقط گروه‌هایی که باعث شوند مقدار شرط‌ها TRUE شد در خروجی نمایش داده می‌شوند. به عبارت دیگر، گروه‌هایی که نتیجه شرط یا شروط آن‌ها FLASE یا UNKNOWN است فیلتر می‌شوند.

چون SQL Server دستور HAVING را بعد از دستور GROUP BY پردازش می‌کند، نمی‌توانید با استفاده از alias ستون، به تابع تجمعی مشخص شده در لیست انتخابی اشاره کنید. query زیر دچار ارور می‌شود:

SELECT
    column_name1,
    column_name2,
    aggregate_function (column_name3) column_alias
FROM
    table_name
GROUP BY
    column_name1,
    column_name2
HAVING
    column_alias > value;

در عوض، باید به وضوح از عبارت تابع تجمعی در دستور HAVING استفاده کنید، که در کد زیر مشاهده می‌کنید:

SELECT
    column_name1,
    column_name2,
    aggregate_function (column_name3) alias
FROM
    table_name
GROUP BY
    column_name1,
    column_name2
HAVING
    aggregate_function (column_name3) > value;

مثال‌های دستور HAVING در SQL Server

اجازه دهید چند مثال حل کنیم تا نحوه عملکرد دستور HAVING را به خوبی درک کنید.

مثال استفاده از دستور HAVING همراه با تابع COUNT

جدول orders از پایگاه داده نمونه Bikestores را مشاهده کنید:

جدول orders

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

SELECT
    customer_id,
    YEAR (order_date),
    COUNT (order_id) order_count
FROM
    sales.orders
GROUP BY
    customer_id,
    YEAR (order_date)
HAVING
    COUNT (order_id) >= 2
ORDER BY
    customer_id;

نتایج

در این مثال:

  • ابتدا، دستور GROUP BY سفارشات و فروش را بر اساس مشتری و سال سفارش گروه‌بندی می‌کند. تابع ()COUNT تعداد سفارشاتی که هر مشتری در هر سال ثبت کرده است را بازمی‌گرداند.
  • سپس، دستور HAVING تمام مشتریانی که تعداد سفارشی‌هایشان کمتر از ۲ هست را فیلتر می‌کند.
مثال استفاده از دستور HAVING همراه با تابع ()SUM در SQL Server

جدول order_items را در نظر بگیرید:

جدول order_items

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

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

نتایج

در این مثال:

  • ابتدا، تابع ()SUM ارزش‌های خالص سفارشات فروش را بازمی‌گرداند.
  • سپس، دستور HAVING سفارشات فروشی که ارزش خالص آن‌ها کمتر یا برابر با ۲۰۰۰۰ است را فیلتر می‌کند.
مثال استفاده از دستور HAVING همراه با توابع MAX و MIN در SQL Server

جدول products را مشاهده کنید:

جدول products

کد زیر ابتدا قیمت‌های ماکسیمم و مینیمم را در هر دسته محصول می‌یابد. سپس، دسته‌ای که دارای قیمت حداکثر بیشتر از ۴۰۰۰ یا قیمت حداقل کمتر از ۵۰۰ است را فیلتر می‌کند:

SELECT
    category_id,
    MAX (list_price) max_list_price,
    MIN (list_price) min_list_price
FROM
    production.products
GROUP BY
    category_id
HAVING
    MAX (list_price) > 4000 OR MIN (list_price) < 500;

نتایج

مثال استفاده از دستور HAVING همراه با تابع ()AVG در SQL Server

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

SELECT
    category_id,
    AVG (list_price) avg_list_price
FROM
    production.products
GROUP BY
    category_id
HAVING
    AVG (list_price) BETWEEN 500 AND 1000;

نتایج

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

GROUPING SETها در SQL Server

GROUPING SETS برای ایجاد چندین مجموعه گروه‌بندی به کار می‌روند. قبل از اینکه به بررسی آن‌ها بپردازیم باید مقدماتی را فراهم کنیم.

ایجاد یک جدول خلاصه فروش

ابتدا یک جدول به نام sales.sales_sumary برای مثال‌هایمان ایجاد می‌کنیم.

SELECT
    b.brand_name AS brand,
    c.category_name AS category,
    p.model_year,
    round(
        SUM (
            quantity * i.list_price * (1 - discount)
        ),
        0
    ) sales INTO sales.sales_summary
FROM
    sales.order_items i
INNER JOIN production.products p ON p.product_id = i.product_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
    b.brand_name,
    c.category_name,
    p.model_year
ORDER BY
    b.brand_name,
    c.category_name,
    p.model_year;

 در این query، ما داده‌های میزان فروش را بر اساس برند و دسته بازمی‌گردانیم و آن‌ها را در جدول Sales.sales_summary قرار می‌دهیم.

Query زیر داده‌ها را از جدول sales.sales_summary را بازمی‌گرداند:

SELECT
 *
FROM
 sales.sales_summary
ORDER BY
 brand,
 category,
 model_year;

نتایج

آغاز کار با GROUPING SETS در SQL Server

از لحاظ تعریف یک grouping set، در واقع گروهی از ستون‌ها است که بر اساس آن‌ها گروه‌بندی انجام می‌شود. معمولاً، یک QUERY با یک تابع تجمعی، یک GROUPING SET را تعریف می‌کنند.

برای مثال، query زیر یک grouping set تعریف می‌کند که شامل برند و دسته‌ای است که به شکل (brand, category) نمایش داده شده است. Query میزان فروش گروه‌بندی شده با برند و دسته را بازمی‌گرداند:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    category
ORDER BY
    brand,
    category;

نتایج

Query زیر میزان فروش را بر اساس برند بازمی‌گرداند. در واقع یک grouping set (برند) تعریف می‌کند:

SELECT
    brand,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand
ORDER BY
    brand;

نتایج

Query زیر میزان فروش را بر اساس دسته بازمی‌گرداند. یک grouping set تعریف می‌کند (دسته):

SELECT
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    category
ORDER BY
    category;

نتایج

Query زیر یک ()grouping set خالی تعریف می‌کند. این query مقدار فروش را برای تمام برندها و دسته‌ها بازمی‌گرداند.

SELECT
    SUM (sales) sales
FROM
    sales.sales_summary;

نتایج

چهار query بالا چه مجموعه نتیجه با چه grouping set بازمی‌گردانند:

  1. (brand, category)
  2. (brand)
  3. (category)
  4. ()

برای به دست آوردن یک مجموعه نتیجه متحد و یکپارچه همراه با داده‌های تجمعی برای تمام grouping setها، می‌توانید از عملگر UNION ALL استفاده کنید.

چون برای استفاده از عملگر UNION ALL لازم است که تمام مجموعه نتایج دارای تعداد ستون یکسان باشند، پس همان‌طور که در کد‌های زیر مشاهده می‌کنید، باید به لیست انتخابی NULL اضافه کنید:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    category
UNION ALL
SELECT
    brand,
    NULL,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand
UNION ALL
SELECT
    NULL,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    category
UNION ALL
SELECT
    NULL,
    NULL,
    SUM (sales)
FROM
    sales.sales_summary
ORDER BY brand, category;

نتایج

Query بالا همان‌طور که انتظار داشتیم یک نتیجه یکپارچه همراه با داده‌های تجمعی برای تمام grouping setها تولید کرد.

به هر حال، دو مشکل عمده در آن وجود دارد:

  1. Query خیلی طولانی است
  2. Query کند عمل می‌کند چون SQL Server باید چهار query را اجرا کرده و مجموعه نتایج را در یک مجموعه نتایج یکپارچه نمایش دهد.

برای برطرف کردن این مشکلات، SQL Server یک دستور فرعی برای GROUP BY فراهم کرده است که GROUPINS SETS نام دارد.

GROUPING SETS چندین grouping set را در یک query تعریف می‌کند. کد زیر syntax کلی GROUPING SETS را نشان می‌دهد:

SELECT
    column1,
    column2,
    aggregate_function (column3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column1, column2),
        (column1),
        (column2),
        ()
);

این query چهار grouping set ایجاد می‌کند:

  1. (column1,column2)
  2. (column1)
  3. (column2)
  4. ()

می‌توانید از دستور GROUPING SETS برای بازنویسی query که داده‌های فروش را دریافت می‌کند استفاده کنید، مانند کد زیر:

SELECT
 brand,
 category,
 SUM (sales) sales
FROM
 sales.sales_summary
GROUP BY
 GROUPING SETS (
 (brand, category),
 (brand),
 (category),
 ()
 )
ORDER BY
 brand,
 category;

همان‌طور که مشاهده می‌کنید، query همان نتایجی را تولید کرد که دفعه قبل با استفاده از عملگر UNION ALL به دست آمدند. به هر حال، این query خیلی خواناتر و مسلماً خیلی کارآمدتر است.

تابع GROUPING

تابع GROUPING نشان می‌دهد که آیا یک ستون مشخص شده در یک دستور GROUP BY تجمعی  (aggregate) هست یا نه. این تابع در مجموعه نتایج برای موارد aggregate مقدار ۱ و برای موارد غیر aggregate مقدار  ۰ بازمی‌گرداند.

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

SELECT
    GROUPING(brand) grouping_brand,
    GROUPING(category) grouping_category,
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    GROUPING SETS (
        (brand, category),
        (brand),
        (category),
        ()
    )
ORDER BY
    brand,
    category;

نتایج

مقدار در ستون grouping_brand نشان می‌دهد که ردیف aggregate هست یا نه، مقدار ۱ یعنی میزان فروش توسط برند aggregate شده است، مقدار ۰ یعنی میزان فروش بر اساس برند Aggregate نشده است. همین صحبت‌ها برای ستون grouping_category نیز صدق می‌کند.

پس با این اوصاف، به صورت کامل و همراه با مثال با grouping setها و دستور فرعی GROUPING SETS آشنا شدید.

دستور فرعی CUBE در SQL Server

همان‌طور که در بخش قبل گفته شد، grouping setها گروه‌های داده در یک query را مشخص می‌کنند. برای مثال، query زیر یک grouping set را تعریف می‌کند که با نام brand بیان شده است:

SELECT 
    brand, 
    SUM(sales)
FROM 
    sales.sales_summary
GROUP BY 
    brand;

اگر بخواهیم از دستور فرعی GROUPING SETS استفاده نکنیم، می‌توانیم جدول sales.sales_summary را با استفاده از query زیر ایجاد کنیم:

SELECT
    b.brand_name AS brand,
    c.category_name AS category,
    p.model_year,
    round(
        SUM (
            quantity * i.list_price * (1 - discount)
        ),
        0
    ) sales INTO sales.sales_summary
FROM
    sales.order_items i
INNER JOIN production.products p ON p.product_id = i.product_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
    b.brand_name,
    c.category_name,
    p.model_year
ORDER BY
    b.brand_name,
    c.category_name,
    p.model_year;

اگرچه query بالا از دستور GROUP BY استفاده نکرد، اما یک grouping set خالی ایجاد کرد که به شکل () نمایش داده می‌شود.

SELECT 
    SUM(sales)
FROM 
    sales.sales_summary
GROUP BY 
    brand;

CUBE یک دستور فرعی از دستور GROUP BY است که به شما امکان می‌دهد چندین grouping set ایجاد کنید. مثل زیر syntax کلی دستور فرعی CUBE را نمایش می‌دهد:

SELECT
    d1,
    d2,
    d3,
    aggregate_function (c4)
FROM
    table_name
GROUP BY
    CUBE (d1, d2, d3);       

در این syntax، دستور CUBE تمام grouping setهای ممکن را بر اساس ستون‌های dimention با نام‌های d2، d1 و D3 که در دستور CUBE مشخص شده‌اند را ایجاد می‌کند.

Query بالا همان نتیجه‌ای را بازمی‌گرداند که query زیر تولید می‌کند، کد زیر از GROUPING SETS استفاده می‌کند:

SELECT
    d1,
    d2,
    d3,
    aggregate_function (c4)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (d1,d2,d3), 
        (d1,d2),
        (d1,d3),
        (d2,d3),
        (d1),
        (d2),
        (d3), 
        ()
     );

اگر N ستون dimention در CUBE داشته باشید، آنگاه 2N تعداد grouping set خواهید داشت.

کاهش تعداد grouping setها با استفاده نسبی از CUBE امکان پذیر است که در کد زیر مشاهده می‌کنید:

SELECT
    d1,
    d2,
    d3,
    aggregate_function (c4)
FROM
    table_name
GROUP BY
    d1,
    CUBE (d2, d3);

در این مورد، query چهار grouping set ایجاد می‌کند، چون فقط دو ستون dimention در CUBE مشخص شده‌اند.

مثال‌های CUBE در SQL Server

کد زیر از CUBE برای ایجاد چهار grouping set زیر استفاده می‌کند:

  1. (brand, category)
  2. (brand)
  3. (category)
  4. ()
SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    CUBE(brand, category);

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

نتایج

در این مثال، ما دو ستون dimention مشخص شده در دستور CUBE داریم، در نتیجه، در کل چهار grouping set داریم.

مثال زیر نشان می‌دهد که چگونه باید دستور CUBE را به صورت نسبی به کار ببرید تا تعداد grouping setهای تولید شده توسط query کاهش یابند:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    CUBE(category);

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

نتایج

به این شکل، با دستور فرعی CUBE برای بهینه‌سازی grouping setها آشنا شدید.

دستور فرعی ROLLUP در SQL Server

ROLLUP در SQL Server یکی از دستورهای فرعی دستور GROUP BY است که یک راه میانبر برای تعریف چندین grouping set فراهم می‌کند. بر خلاف دستور فرعی CUBE، دستور ROLLUP تمام grouping setها را بر اساس ستون‌های dimention ایجاد نمی‌کند.

دستور CUBE یک زیرمجموعه از آن‌ها ایجاد می‌کند.

وقتی grouping set ایجاد می‌کنید، دستور ROLLUP یک سلسله مراتب در بین ستون‌های dimention فرض می‌کند و grouping setها را فقط بر اساس آن سلسله مراتب تولید می‌کند.

دستور ROLLUP گاهی برای ایجاد زیرمجموعه و مجموعه برای اهداف گزارش دهی استفاده می‌شود.

اجازه دهید یک مثال بزنیم. دستور (CUBE (d1, d2, d3 در زیر ۸ عدد grouping set را تعریف می‌کند:

  1. (d1, d2, d3)
  2. (d1, d2)
  3. (d2, d3)
  4. (d1, d3)
  5. (d1)
  6. (d2)
  7. (d3)
  8. ()

و دستور (ROLLUP (d1, d2, d3 فقط چهار grouping set ایجاد می‌کند، با فرض سلسله مراتب d1 > d2 > d3، که در زیر آن‌ها را مشاهده می‌کنید:

  1. (d1, d2, d3)
  2. (d1, d2)
  3. (d1)
  4. ()

دستور ROLLUP معمولاً برای محاسبه Aggregateهای داده‌های سلسله مراتبی مانند فروش بر اساس ماه < چهار ماه<سال استفاده می‌شود.

Syntax دستور ROLLUP در SQL Server

Syntax کلی دستور ROLLUP در SQL Server به شکل زیر است:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    ROLLUP (d1, d2, d3);

در این syntax، کدهای d2، d1 و d3 ستون‌های Dimention هستند. کد بالا تجمع یا aggregation مقادیر موجود در ستون c4 را بر اساس سلسله مراتب d1 > d2 > d3 نشان می‌دهد.

همچنین می‌توانید برای کاهش زیرمجموعه‌های تولید شده، به شکل زیر از دستور ROLLUP به صورت نسبی استفاده کنید:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    d1, 
    ROLLUP (d2, d3);

مثال‌های ROLLUP در SQL Server

دوباره از جدول sales.sales_summary که در بخش مربوط به دستور فرعی GROUPING SETS ایجاد شد برای این مثال‌ها استفاده می‌کنیم. اگر هنوز جدول را در رایانه خود ایجاد نکرده‌اید، می‌توانید با استفاده از کد زیر این کار را انجام دهید:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    d1, 
    ROLLUP (d2, d3);

Query زیر از دستور ROLLUP برای محاسبه میزان فروش بر اساس برند (زیرمجموعه) و همچنین با استفاده از برند و دسته (مجموعه) استفاده می‌کند.

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    ROLLUP(brand, category);

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

نتایج

 در این مثال، query فرض می‌کند که یک سلسله مراتب بین برند و دسته وجود دارد، که دسته < برند است.

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

SELECT
    category,
    brand,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    ROLLUP (category, brand);

در این مثال، سلسله مراتب به شکل بخش < برند است:

نتایج

کد زیر چگونگی اجرای نسبی ROLLUP را نشان می‌دهد:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    ROLLUP (category);

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

نتایج

به این صورت، در این قسمت با دستور ROLLUP در SQL Server آشنا شدید و متوجه شدید که این دستور با فرض وجود یک سلسله مراتب در ستون‌های ورودی، چندین grouping set ایجاد می‌کند.

خلاصه

در این جلسه به صورت کامل به مبحث گروه‌بندی نتایج آشنا شدید. دستورات و دستورات فرعی مرتبط با این موضوع به شرح زیر بودند:

به صورت مفصل و مثال محور با تمام این موارد آشنا شدید. در جلسه آینده در مورد subquery یا queryهای فرعی صحبت خواهید کرد.

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

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

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

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