Mọi thứ bạn cần biết về câu lệnh SQL GROUP BY

Mọi thứ bạn cần biết về câu lệnh SQL GROUP BY

Phần lớn sức mạnh của cơ sở dữ liệu quan hệ đến từ việc lọc dữ liệu và nối các bảng lại với nhau. Đây là lý do tại sao chúng tôi đại diện cho những mối quan hệ đó ngay từ đầu. Nhưng các hệ thống cơ sở dữ liệu hiện đại cung cấp một kỹ thuật có giá trị khác: phân nhóm.





Phân nhóm cho phép bạn trích xuất thông tin tóm tắt từ cơ sở dữ liệu. Nó cho phép bạn kết hợp các kết quả để tạo ra dữ liệu thống kê hữu ích. Việc phân nhóm giúp bạn không phải viết mã cho các trường hợp phổ biến như tính trung bình danh sách các số liệu. Và nó có thể tạo ra các hệ thống hiệu quả hơn.



Mệnh đề GROUP BY làm gì?

GROUP BY, như tên cho thấy, nhóm các kết quả thành một tập hợp nhỏ hơn. Kết quả bao gồm một hàng cho mỗi giá trị riêng biệt của cột được nhóm. Chúng tôi có thể hiển thị cách sử dụng của nó bằng cách xem một số dữ liệu mẫu với các hàng có chung một số giá trị chung.





nút cửa sổ không hoạt động trên bàn phím

Sau đây là một cơ sở dữ liệu rất đơn giản với hai bảng đại diện cho các album ghi. Bạn có thể thiết lập một cơ sở dữ liệu như vậy bằng cách viết một lược đồ cơ bản cho hệ thống cơ sở dữ liệu đã chọn của bạn. Các tập ảnh bảng có chín hàng với một khóa chính Tôi cột và các cột cho tên, nghệ sĩ, năm phát hành và doanh số bán hàng:

+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+

Các nghệ sĩ bảng thậm chí còn đơn giản hơn. Nó có bảy hàng với các cột id và tên:



+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+

Bạn có thể hiểu các khía cạnh khác nhau của GROUP BY chỉ với một tập dữ liệu đơn giản như sau. Tất nhiên, một tập dữ liệu trong cuộc sống thực sẽ có nhiều hàng hơn, nhưng các nguyên tắc vẫn như cũ.

Nhóm theo một cột duy nhất

Giả sử chúng tôi muốn tìm hiểu xem chúng tôi có bao nhiêu album cho mỗi nghệ sĩ. Bắt đầu với một điển hình LỰA CHỌN truy vấn để tìm nạp cột artist_id:



SELECT artist_id FROM albums

Điều này trả về tất cả chín hàng, như mong đợi:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+

Để nhóm các kết quả này theo nghệ sĩ, hãy thêm cụm từ GROUP BY artist_id :



SELECT artist_id FROM albums GROUP BY artist_id

Điều nào cho kết quả sau:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+

Có bảy hàng trong tập hợp kết quả, giảm từ tổng số chín trong tập ảnh bàn. Mỗi duy nhất artist_id có một hàng duy nhất. Cuối cùng, để có được số lượng thực tế, hãy thêm ĐẾM(*) vào các cột đã chọn:

SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+

Kết quả nhóm hai cặp hàng cho các nghệ sĩ có id 2 và 6 . Mỗi người có hai album trong cơ sở dữ liệu của chúng tôi.

Có liên quan: Bảng gian lận lệnh SQL cơ bản cho người mới bắt đầu

Cách truy cập dữ liệu được nhóm bằng hàm tổng hợp

Bạn có thể đã sử dụng ĐẾM hoạt động trước đây, đặc biệt là trong ĐẾM(*) hình thức như đã thấy ở trên. Nó tìm nạp số lượng kết quả trong một tập hợp. Bạn có thể sử dụng nó để lấy tổng số bản ghi trong một bảng:

SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

COUNT là một hàm tổng hợp. Thuật ngữ này đề cập đến các hàm dịch các giá trị từ nhiều hàng thành một giá trị duy nhất. Chúng thường được sử dụng cùng với câu lệnh GROUP BY.

Thay vì chỉ đếm số hàng, chúng ta có thể áp dụng hàm tổng hợp cho các giá trị được nhóm:

SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+

Tổng doanh số được hiển thị ở trên cho nghệ sĩ 2 và 6 là doanh số bán nhiều album của họ cộng lại:

SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+

Nhóm theo nhiều cột

Bạn có thể nhóm nhiều hơn một cột. Chỉ cần bao gồm nhiều cột hoặc biểu thức, được phân tách bằng dấu phẩy. Kết quả sẽ nhóm theo sự kết hợp của các cột này.

SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales

Điều này thường sẽ tạo ra nhiều kết quả hơn là nhóm theo một cột duy nhất:

+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+

Lưu ý rằng, trong ví dụ nhỏ của chúng tôi, chỉ có hai album có cùng năm phát hành và số lượng bán ra (28 năm 1977).

Các hàm tổng hợp hữu ích

Ngoài COUNT, một số chức năng hoạt động tốt với GROUP. Mỗi hàm trả về một giá trị dựa trên các bản ghi thuộc mỗi nhóm kết quả.

  • COUNT () trả về tổng số bản ghi phù hợp.
  • SUM () trả về tổng tất cả các giá trị trong cột đã cho được cộng lại.
  • MIN () trả về giá trị nhỏ nhất trong một cột nhất định.
  • MAX () trả về giá trị lớn nhất trong một cột nhất định.
  • AVG () trả về giá trị trung bình. Nó tương đương với SUM () / COUNT ().

Bạn cũng có thể sử dụng các hàm này mà không có mệnh đề GROUP:

cách sửa bàn di chuột trên macbook pro
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

Sử dụng GROUP BY với mệnh đề WHERE

Cũng giống như với SELECT thông thường, bạn vẫn có thể sử dụng WHERE để lọc tập kết quả:

SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+

Bây giờ bạn chỉ có những album được phát hành sau năm 1990, được nhóm theo nghệ sĩ. Bạn cũng có thể sử dụng một phép nối với mệnh đề WHERE, độc lập với GROUP BY:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+

Tuy nhiên, hãy lưu ý rằng nếu bạn cố gắng lọc dựa trên một cột tổng hợp:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;

Bạn sẽ gặp lỗi:

ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Các cột dựa trên dữ liệu tổng hợp không có sẵn cho mệnh đề WHERE.

Sử dụng mệnh đề HAVING

Vì vậy, làm thế nào để bạn lọc tập hợp kết quả sau khi một nhóm đã diễn ra? Các ĐANG CÓ điều khoản giải quyết nhu cầu này:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;

Lưu ý rằng mệnh đề HAVING đứng sau GROUP BY. Nếu không, về cơ bản, nó là một sự thay thế đơn giản của WHERE bằng HAVING. Kết quả là:

+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+

Bạn vẫn có thể sử dụng điều kiện WHERE để lọc kết quả trước khi nhóm. Nó sẽ hoạt động cùng với mệnh đề HAVING để lọc sau khi nhóm:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;

Chỉ có một nghệ sĩ trong cơ sở dữ liệu của chúng tôi phát hành nhiều hơn một album sau năm 1990:

+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+

Kết hợp kết quả với GROUP BY

Câu lệnh GROUP BY là một phần cực kỳ hữu ích của ngôn ngữ SQL. Nó có thể cung cấp thông tin tóm tắt về dữ liệu, cho một trang nội dung, chẳng hạn. Nó là một giải pháp thay thế tuyệt vời để tìm nạp số lượng lớn dữ liệu. Cơ sở dữ liệu xử lý tốt khối lượng công việc bổ sung này vì chính thiết kế của nó làm cho nó tối ưu cho công việc.

Khi bạn hiểu cách nhóm và cách nối nhiều bảng, bạn sẽ có thể sử dụng hầu hết sức mạnh của cơ sở dữ liệu quan hệ.

Đăng lại Đăng lại tiếng riu ríu E-mail Cách truy vấn nhiều bảng cơ sở dữ liệu cùng lúc với SQL Joins

Tìm hiểu cách sử dụng các phép nối SQL để sắp xếp hợp lý các truy vấn, tiết kiệm thời gian và khiến bạn cảm thấy mình giống như một người dùng SQL thành thạo.

cửa hàng sắp ngừng kinh doanh gần tôi
Đọc tiếp Chủ đề liên quan
  • Lập trình
  • SQL
Giới thiệu về tác giả Bobby Jack(58 bài báo đã xuất bản)

Bobby là một người đam mê công nghệ, người đã làm việc như một nhà phát triển phần mềm trong gần hai thập kỷ. Anh ấy đam mê chơi game, làm Biên tập viên đánh giá tại Tạp chí Switch Player và đắm mình trong tất cả các khía cạnh của xuất bản trực tuyến và phát triển web.

Xem thêm từ Bobby Jack

Theo dõi bản tin của chúng tôi

Tham gia bản tin của chúng tôi để biết các mẹo công nghệ, đánh giá, sách điện tử miễn phí và các ưu đãi độc quyền!

Bấm vào đây để đăng ký