SQL Sever

SQL Server: Các lệnh Group By trong SQL Server có thể bạn chưa biết

Posted on by ComdyVN

SQL Server: Các lệnh Group By trong SQL Server có thể bạn chưa biết

Trong hướng dẫn này, bạn sẽ tìm hiểu các mệnh đề GROUP BY, GROUPING SET, CUBE, ROLLUP và HAVING trong SQL Server để sắp xếp các bản ghi theo nhóm.

Mệnh đề GROUP BY trong SQL Server

Giới thiệu về mệnh đề GROUP BY trong SQL Server

Mệnh đề GROUP BY trong SQL Server cho phép bạn sắp xếp các bản ghi của một truy vấn theo nhóm. Các nhóm được xác định bởi các cột mà bạn chỉ định trong mệnh đề GROUP BY.

Sau đây minh họa cú pháp mệnh đề GROUP BY trong SQL Server:

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

Trong truy vấn này, mệnh đề GROUP BY đã tạo ra một nhóm cho mỗi kết hợp các giá trị trong các cột được liệt kê trong mệnh đề GROUP BY.

Hãy xem ví dụ sau:

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

Đây là kết quả:

GROUP BY trong SQL Server

Trong ví dụ này, chúng tôi đã truy xuất thông tin id khách hàng và năm đặt hàng của những khách hàng có id 1 và 2.

Như bạn có thể thấy ở kết quả đầu ra, khách hàng có id 1 đã đặt một đơn hàng vào năm 2016 và hai đơn hàng vào năm 2018. Khách hàng có id hai đã đặt hai đơn hàng vào năm 2017 và một đơn hàng vào năm 2018.

Hãy thêm mệnh đề GROUP BY vào truy vấn trên và xem kết quả:

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;

Đây là kết quả:

GROUP BY trong SQL Server

Mệnh đề GROUP BY đã sắp xếp ba bản ghi đầu tiên của khách hàng có id là 1 thành hai nhóm và ba bản ghi tiếp theo của khách hàng có id là 2 vào hai nhóm khác với sự kết hợp duy nhất của id khách hàng và năm đặt hàng.

Về mặt chức năng, mệnh đề GROUP BY trong truy vấn trên tạo ra kết quả tương tự như truy vấn sau sử dụng mệnh đề DISTINCT:

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

Đây là kết quả:

GROUP BY trong SQL Server

Mệnh đề GROUP BY và hàm tập hợp của SQL Server

Trong thực tế, mệnh đề GROUP BY thường được sử dụng với các hàm tập hợp để tạo báo cáo tóm tắt.

Một hàm tập hợp thực hiện một phép tính vào một nhóm và trả về một giá trị duy nhất cho mỗi nhóm. Ví dụ, hàm COUNT() trả về số lượng bản ghi trong mỗi nhóm. Các hàm tập hợp thường được sử dụng khác là SUM() (tính tổng), AVG() (tính trung bình), MIN()(nhỏ nhất), MAX()(lớn nhất).

Mệnh đề GROUP BY sắp xếp các bản ghi thành các nhóm và thực hiện các hàm tập hợp (count, min, max, avg, sum, vv) cho mỗi nhóm.

Ví dụ: truy vấn sau đây trả về số lượng đơn đặt hàng của khách hàng theo năm:

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;

Đây là kết quả:

Mệnh đề GROUP BY và hàm tập hợp của SQL Server

Nếu bạn muốn thêm bất kỳ cột hoặc biểu thức nào không được liệt kê trong mệnh đề GROUP BY, bạn phải sử dụng cột đó làm đầu vào của hàm tập hợp.

Nếu không, bạn sẽ gặp lỗi vì không có gì đảm bảo rằng cột hoặc biểu thức đó sẽ trả về một giá trị duy nhất cho mỗi nhóm.

Ví dụ: truy vấn sau sẽ thất bại:

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;

Ví dụ về mệnh đề GROUP BY trong SQL Server

Chúng ta hãy lấy thêm một số ví dụ để hiểu cách hoạt động của mệnh đề GROUP BY.

Sử dụng mệnh đề GROUP BY với hàm COUNT() trong SQL Server

Truy vấn sau đây trả về số lượng khách hàng ở mỗi thành phố:

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

Đây là kết quả:

Sử dụng mệnh đề GROUP BY với hàm COUNT() trong SQL Server

Trong ví dụ này, mệnh đề GROUP BY nhóm các khách hàng lại với nhau theo từng thành phố và hàm COUNT() trả về số lượng khách hàng ở mỗi thành phố.

Tương tự, truy vấn sau đây trả về số lượng khách hàng theo tiểu bang và thành phố.

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

Đây là kết quả:

Sử dụng mệnh đề GROUP BY với hàm COUNT() trong SQL Server

Xem thêm các ví dụ nâng cao về mệnh đề GROUP BY trong SQL Server ở bài viết sau:

GROUP BY trong SQL Server | Comdy
Cách sử dụng mệnh đề GROUP BY trong SQL Server để sắp xếp các bản ghi theo nhóm.
Trung NguyenComdy

GROUPING SET trong SQL Server

Giới thiệu về GROUPING SETS trong SQL Server

Theo định nghĩa GROUPING SETS là một nhóm các cột mà bạn nhóm. Thông thường, một truy vấn với một tập hợp xác định một tập hợp nhóm duy nhất.

Ví dụ: truy vấn sau xác định một tập hợp nhóm bao gồm thương hiệu và danh mục được biểu thị là (brand, category). Truy vấn trả về số tiền bán hàng được nhóm theo thương hiệu và danh mục:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    category
ORDER BY
    brand,
    category;
GROUPING SET trong SQL Server

Truy vấn sau đây trả về số lượng bán hàng theo thương hiệu. Nó xác định một tập hợp nhóm (brand):

SELECT
    brand,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand
ORDER BY
    brand;
GROUPING SET trong SQL Server

Truy vấn sau đây trả về số lượng bán hàng theo danh mục. Nó định nghĩa một tập hợp nhóm (category):

SELECT
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    category
ORDER BY
    category;
GROUPING SET trong SQL Server

Truy vấn sau xác định một tập hợp nhóm trống (). Nó trả về số tiền bán hàng cho tất cả các thương hiệu và danh mục.

SELECT
    SUM (sales) sales
FROM
    sales.sales_summary;
GROUPING SET trong SQL Server

Bốn truy vấn trên trả về bốn tập kết quả với bốn nhóm:

(brand, category)
(brand)
(category)
()

Để có được tập hợp kết quả thống nhất với dữ liệu tổng hợp cho tất cả các tập hợp nhóm, bạn có thể sử dụng toán tử UNION ALL.

Vì toán tử UNION ALL yêu cầu tất cả tập hợp kết quả có cùng số cột, bạn cần thêm cột NULL vào danh sách các truy vấn như sau:

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;
GROUPING SET trong SQL Server

Truy vấn tạo ra một kết quả duy nhất với các tổng hợp cho tất cả các nhóm nhóm như chúng tôi mong đợi.

Tuy nhiên, nó có hai vấn đề lớn:

  1. Truy vấn khá dài.
  2. Truy vấn chậm vì SQL Server cần thực hiện bốn truy vấn con và kết hợp các tập hợp kết quả thành một.

Để khắc phục những sự cố này, SQL Server cung cấp một mệnh đề con của mệnh đề GROUP BY được gọi là GROUPING SETS.

GROUPING SETS định nghĩa nhiều nhóm trong cùng một truy vấn. Sau đây là cú pháp chung của GROUPING SETS:

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

Truy vấn này tạo ra bốn nhóm như sau:

(column1, column2)
(column1)
(column2)
()

Bạn có thể sử dụng GROUPING SETS để viết lại truy vấn lấy dữ liệu bán hàng như sau:

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

Như bạn có thể thấy, truy vấn tạo ra kết quả giống như truy vấn sử dụng toán tử UNION ALL. Tuy nhiên, truy vấn này dễ đọc hơn nhiều và tất nhiên là hiệu quả hơn.

Tìm hiểu thêm về GROUPING SET trong SQL Server ở bài viết sau:

GROUPING SET trong SQL Server
Trong hướng dẫn này, bạn sẽ học cách sử dụng GROUPING SETS trong SQL Server để tạo nhiều tập hợp nhóm.
Trung NguyenComdy

Mệnh đề CUBE trong SQL Server

Giới thiệu về mệnh đề CUBE trong SQL Server

GROUPING SETS định nghĩa các nhóm dữ liệu trong một truy vấn. Ví dụ: truy vấn sau định nghĩa một tập nhóm duy nhất được ký hiệu là (brand):

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

Nếu bạn chưa làm theo hướng dẫn GROUPING SETS, bạn có thể tạo bảng sales.sales_summary bằng cách sử dụng truy vấn sau:

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;

Ngay cả khi truy vấn sau không sử dụng mệnh đề GROUP BY, nó tạo ra một tập hợp nhóm trống được ký hiệu là ().

SELECT 
    SUM(sales)
FROM 
    sales.sales_summary;

CUBE là một mệnh đề con của mệnh đề GROUP BY cho phép bạn tạo nhiều nhóm. Sau đây minh họa cú pháp chung của CUBE:

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

Trong cú pháp này, hàm CUBE tạo tất cả các nhóm có thể dựa trên các cột d1, d2 và d3 mà bạn chỉ định trong mệnh đề CUBE.

Truy vấn trên trả về cùng một tập hợp kết quả như truy vấn sau, sử dụng hàm   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ếu bạn có N cột được chỉ định trong CUBE, bạn sẽ có 2 N tập nhóm kết quả.

Có thể giảm số lượng  tập nhóm kết quả bằng cách sử dụng CUBE một phần như được hiển thị trong truy vấn sau:

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

Trong trường hợp này, truy vấn tạo ra bốn tập hợp nhóm vì chỉ có hai cột được chỉ định trong CUBE.

Ví dụ về mệnh đề CUBE trong SQL Server

Câu lệnh sau sử dụng CUBE để tạo bốn tập nhóm:

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

Đây là kết quả đầu ra:

CUBE trong SQL Server

Xem thêm các ví dụ nâng cao về mệnh đề CUBE trong SQL Server ở bài viết sau:

CUBE trong SQL Server
Trong hướng dẫn này, bạn sẽ học cách sử dụng CUBE trong SQL Server để tạo nhiều tập nhóm.
Trung NguyenComdy

Mệnh đề ROLLUP trong SQL Server

Giới thiệu về mệnh đề ROLLUP trong SQL Server

ROLLUP trong SQL Server là một mệnh đề con của mệnh đề GROUP BY cung cấp cách viết tắt để xác định nhiều GROUPING SETS. Không giống như CUBE – tạo các tập nhóm với tất cả các kết hợp của các cột, ROLLUP không tạo tất cả các tập hợp nhóm có thể dựa trên các cột thứ nguyên.

Khi tạo các tập hợp nhóm, ROLLUP giả định một hệ thống phân cấp giữa các cột thứ nguyên và chỉ tạo các tập hợp nhóm dựa trên hệ thống phân cấp này.

ROLLUP thường được sử dụng để tạo ra tổng số phụ (subtotals) và tổng số (totals) cho mục đích báo cáo.

Hãy xem xét ví dụ sau đây: mệnh đề CUBE (d1,d2,d3) sẽ xác định tám tập hợp nhóm như sau:

(d1, d2, d3)
(d1, d2)
(d2, d3)
(d1, d3)
(d1)
(d2)
(d3)
()

Nhưng mệnh đề ROLLUP(d1,d2,d3) chỉ tạo ra bốn tập hợp nhóm (giả sử phân cấp d1 > d2 > d3) như sau:

(d1, d2, d3)
(d1, d2)
(d1)
()

Mệnh đề ROLLUP thường được sử dụng để tính toán tổng hợp của dữ liệu phân cấp như bán hàng theo năm > quý > tháng.

Cú pháp của mệnh đề ROLLUP trong SQL Server

Cú pháp chung của ROLLUP trong SQL Server như sau:

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

Trong cú pháp này, d1, d2 và d3 là các cột thứ nguyên. Câu lệnh sẽ tính tổng các giá trị trong cột c4 dựa trên thứ bậc d1> d2> d3.

Bạn cũng có thể ROLLUP một phần để giảm tổng số phụ (subtotals) được tạo bằng cách sử dụng cú pháp sau:

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

Ví dụ về mệnh đề ROLLUP trong SQL Server

Chúng ta sẽ sử dụng lại bảng sales.sales_summary trong cơ sở dữ liệu mẫu BikeStores đã tạo trong phần hướng dẫn về GROUPING SETS để minh họa. Nếu bạn chưa tạo bảng sales.sales_summary, bạn có thể sử dụng câu lệnh sau để tạo bảng.

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;

Truy vấn sau đây sử dụng ROLLUP để tính toán số tiền bán hàng theo thương hiệu (tổng số phụ – subtotals) và cả thương hiệu và danh mục (tổng số – totals).

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

Đây là đầu ra:

ROLLUP trong SQL Server

Trong ví dụ này, truy vấn giả định rằng có một hệ thống phân cấp giữa thương hiệu và danh mục, đó là thương hiệu > danh mục.

Xem thêm các ví dụ nâng cao về mệnh đề ROLLUP trong SQL Server ở bài viết sau:

ROLLUP trong SQL Server
ROLLUP trong SQL Server là một mệnh đề con của mệnh đề GROUP BY cung cấp cách viết tắt để xác định nhiều tập hợp nhóm.
Trung NguyenComdy

Mệnh đề HAVING trong SQL Server

Giới thiệu về mệnh đề HAVING trong SQL Server

Mệnh đề HAVING thường được sử dụng với mệnh đề GROUP BY để lọc các nhóm dựa trên một danh sách các điều kiện. Sau đây minh họa cú pháp mệnh đề HAVING:

SELECT
    select_list
FROM
    table_name
GROUP BY
    group_list
HAVING
    conditions;

Trong cú pháp này, mệnh đề GROUP BY sẽ tổng hợp các bản ghi thành các nhóm và mệnh đề HAVING sẽ áp dụng một hoặc nhiều điều kiện cho các nhóm này.

Chỉ các nhóm thỏa mãn điều kiện trong mệnh đề HAVING mới được bao gồm trong tập kết quả.

Vì SQL Server xử lý mệnh đề HAVING sau mệnh đề GROUP BY, bạn không thể sử dụng bí danh cột để tham chiếu đến các hàm tập hợp được chỉ định trong câu lệnh SELECT. Truy vấn sau đây sẽ thất bại:

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

Thay vào đó, bạn phải sử dụng các hàm tập hợp trực tiếp trong mệnh đề HAVING một cách rõ ràng như sau:

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;

Ví dụ về mệnh đề HAVING trong SQL Server

Hãy lấy một số ví dụ để hiểu cách HAVINGhoạt động của mệnh đề.

Sử dụng HAVING với hàm COUNT trong SQL Server

Chúng ta sẽ sử dụng bảng orders trong cơ sở dữ liệu mẫu BikeStores để minh họa:

Bảng orders trong cơ sở dữ liệu mẫu BikeStores

Câu lệnh sau đây sử dụng mệnh đề HAVING để tìm các khách hàng đã đặt ít nhất hai đơn hàng mỗi năm:

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;
Sử dụng HAVING với hàm COUNT trong SQL Server

Trong ví dụ này:

  • Đầu tiên, mệnh đề GROUP BY nhóm các đơn đặt hàng theo khách hàng và năm đặt hàng. Hàm COUNT() trả về số lượng đơn đặt hàng mỗi khách hàng được đặt trong mỗi năm.
  • Thứ hai, mệnh đề HAVING sẽ lọc bỏ tất cả các khách hàng có số lượng đơn đặt hàng ít hơn hai.

Xem thêm các ví dụ nâng cao về mệnh đề HAVING trong SQL Server ở bài viết sau:

HAVING trong SQL Server | Comdy
Cách sử dụng mệnh đề HAVING trong SQL Server để lọc các nhóm dựa trên các điều kiện được chỉ định.
Trung NguyenComdy

Trong hướng dẫn này, bạn đã tìm hiểu các mệnh đề GROUP BY, GROUPING SET, CUBE, ROLLUP và HAVING trong SQL Server để sắp xếp các bản ghi theo nhóm.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *