在SQL中使用GROUP BY和ORDER BY

教學

SQL

介紹

結構化查詢語言（SQL）數據庫可以跨多個表格存儲和管理大量數據。對於大數據集，重要的是要了解如何對數據進行排序，特別是用於分析結果集或組織數據以供報告或外部通信使用。

SQL中兩個常見的用於排序數據的語句是 GROUP BY 和 ORDER BY。一個 GROUP BY 語句根據您在查詢中指定的列對數據進行分組排序，並與聚合函數一起使用。一個 ORDER BY 允許您按字母順序或數字順序以及升序或降序對結果集進行組織。

在本教程中，您將使用 GROUP BY 和 ORDER BY 語句對SQL中的查詢結果進行排序。您還將練習在查詢中實現聚合函數和 WHERE 子句，以進一步對結果進行排序。

先決條件

要遵循這個指南，您需要運行某種使用SQL的關聯式數據庫管理系統（RDBMS）的計算機。本教程中的說明和示例已使用以下環境驗證：

A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
在服務器上安裝並保護了MySQL。請遵循我們的如何在Ubuntu 20.04上安裝MySQL指南來進行設置。本指南假設您還設置了一個非根MySQL用戶，如本指南的步驟3中所述。

注意：請注意，許多關聯式數據庫管理系統使用自己獨特的SQL實現。儘管本教程中概述的命令將在大多數RDBMS上運行，但如果您在MySQL以外的系統上測試它們，確切的語法或輸出可能會有所不同。

為了在本教程中練習對數據結果進行排序，您需要一個包含樣本數據的數據庫和表。如果您沒有準備好要插入的數據庫，您可以閱讀以下連接到MySQL並設置示例數據庫部分，了解如何創建數據庫和表。本教程將在整個過程中參考此示例數據庫和表。

由於你在幾種不同的類型電影上工作，你想知道它們受到電影觀眾的接受程度。具體而言，你想知道每種電影類型的平均觀眾人數。使用SELECT從movie_genre列檢索各種類型的電影。然後在guest_total列上應用聚合函數AVG，使用AS為名為average的列創建別名，並包含GROUP BY語句以按movie_genre分組結果。以這種方式將它們分組將為你提供每種電影類型的平均結果：

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

此輸出提供了movie_genre組中每種類型的四個平均值。根據這些信息，Action類型的電影吸引了每場放映觀眾人數的最高平均值。

接下來，假設您想要測量電影院在兩個不同日期的收入。以下查詢返回date列的值，以及SUM聚合函數返回的值。具體而言，聚合函數SUM將在括號中包含一個數學方程式，以乘（使用*運算符）總觀眾數與票價的乘積，表示為：SUM(guest_total * ticket_cost)。此查詢包括AS子句，為聚合函數返回的列提供別名total_revenue。然後，使用GROUP BY語句完成查詢，將查詢結果按date列分組：

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

由於您使用GROUP BY來對date列進行分組，您的輸出提供了每天票務銷售的總收入結果，在這個例子中，5月27日星期五的收入為$7,272，5月28日星期六的收入為$9,646。

現在想像你想專注並分析一部電影：《壞人》。在這種情況下，您想弄清時間和價格如何影響家庭觀看動畫電影的選擇。對於此查詢，使用聚合函數MAX來檢索最大的ticket_cost，確保包含AS以創建price_data列的別名。然後，使用WHERE子句將結果按movie_name縮小為僅“壞人”，並使用AND來根據guest_total數字來確定最受歡迎的電影時間，該數字超過100，使用比較運算符>。然後，使用GROUP BY語句完成查詢，並按time分組：

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

根據這個輸出，更多的客人參加了早上9:00的壞人電影，該時段的票價更實惠，每張票價為$8.00。然而，這些結果還表明，影片觀眾在下午5:00時支付了更高的票價，為$13.00，這表明家庭更喜歡不太晚的放映時間，並且會願意多支付一點票價。與壞人電影僅有83位觀眾並且每張票價為$18.00的晚上10:00時相比，這似乎是一個公平的評估。這可以作為提供給電影院經理的有用信息，證明開放更多的早場和早晚場次可以增加那些根據偏好的時間和價格點進行選擇的家庭的出席率。

請注意，即使 GROUP BY 幾乎總是與聚合函數一起使用，也可能存在例外情況，雖然可能性很小。但是，如果您確實想要對結果進行分組而不使用聚合函數，則可以使用 DISTINCT 陳述來實現相同的結果。 DISTINCT 子句通過返回列中的唯一值來刪除結果集中的任何重複項，並且它只能與 SELECT 陳述一起使用。例如，如果您想將所有電影按名稱分組在一起，可以使用以下查詢：

SELECT DISTINCT movie_name FROM movie_theater;

Output+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

如您回憶在表中查看所有數據時看到的，由於有多個場次，電影名稱存在重複。因此，DISTINCT 刪除了這些重複項，並有效地將唯一值分組在單個列 movie_name 下。這與包含 GROUP BY 陳述的以下查詢效果完全相同：

SELECT movie_name FROM movie_theater GROUP BY movie_name;

現在您已經練習了如何使用帶有聚合函數的 GROUP BY，接下來您將學習如何使用 ORDER BY 陳述對查詢結果進行排序。

使用 `ORDER BY`

ORDER BY語句的功能是根據您在查詢中指定的列，以升序或降序的方式對結果進行排序。根據您在其後指定的列存儲的數據類型，ORDER BY將按字母順序或數字順序對其進行組織。默認情況下，ORDER BY將按升序對結果進行排序；但是，如果您希望按降序排序，則必須在查詢中包括關鍵字DESC。您還可以將ORDER BY語句與GROUP BY一起使用，但必須在後面才能正常運行。與GROUP BY類似，ORDER BY還必須在FROM語句和WHERE子句之後。使用ORDER BY的一般語法如下：

ORDER BY syntax

SELECT column_1, column_2 FROM table ORDER BY column_1;

讓我們繼續使用電影院的示例數據並練習使用ORDER BY對結果進行排序。從以下查詢開始，該查詢檢索guest_total列的值，並使用ORDER BY語句對這些數值進行組織：

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

由於您的查詢指定了一個具有數值的列，ORDER BY語句按數字和升序對結果進行排序，從guest_total列下的25開始。

如果您想按降序排列列，您可以在查询末尾添加DESC关键字。此外，如果您想按movie_name下的字符值排序数据，您需要在查询中指定。让我们执行这种类型的查询，使用ORDER BY按movie_name列中的字符值降序排列数据。通过包括WHERE子句進一步排序结果，以检索time列中晚上10:00的电影数据：

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

此结果集按字母顺序降序列出四个晚上10:00播放的不同电影，从《壮志凌云：狼》到《唐顿庄园：新时代》。

对于下一个查询，结合ORDER BY和GROUP BY语句使用聚合函数SUM生成每部电影的总收入结果。不过，假设电影院误算了总来宾人数，忘记包括预购和预留了12张票的特别聚会。

在這個查詢中使用 SUM 並包括每場電影放映的額外 12 位客人，請實現加法運算符 + 並將 12 添加到 guest_total 中。請確保將此數值放在括號中。然後，將這個總數乘以 ticket_cost，使用乘法運算符 *，並在最後將括號閉合。使用 AS 子句為新列創建別名，標題為 total_revenue。然後，使用 GROUP BY 根據從 movie_name 列檢索到的數據將 total_revenue 結果分組為每部電影。最後，使用 ORDER BY 按升序排列 total_revenue 列下的結果：

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

這個結果集告訴我們每部電影的總收入，包括額外的 12 張門票銷售，並按照從最低到最高的順序組織總門票銷售。從中，我們得知《壯志凌雲：獨行俠》獲得了最多的門票銷售，而《Men》則獲得了最少。與此同時，《壞人們》和《唐頓莊園：新時代》電影的總門票銷售非常接近。

在這個部分，您練習了實現 ORDER BY 陳述的各種方法，以及如何指定您偏好的排序方式，例如升序和降序的字符和數值數據值。您還學習了如何使用 WHERE 子句來縮小結果範圍，並執行了一個使用 GROUP BY 和 ORDER BY 陳述的查詢，帶有聚合函數和數學方程式。

結論

了解如何使用GROUP BY和ORDER BY語句對於對結果和數據進行排序至關重要。無論您是想將多個結果組織在一個組下，將您的一列按字母順序和降序排列，還是同時執行這兩種操作；都由您和您所需的結果決定。您還學習了使用WHERE子句進一步對結果進行排序的其他方法。如果您想了解更多，請查看我們的教程如何在SQL中使用通配符，以練習使用LIKE子句過濾結果。

Source:
https://www.digitalocean.com/community/tutorials/how-to-use-groupby-and-orderby-in-sql

介紹

先決條件

連接到MySQL並設置示例數據庫

使用 GROUP BY

使用 ORDER BY

結論

使用 `GROUP BY`

使用 `ORDER BY`