SQL에서 GROUP BY 및 ORDER BY 사용 방법

소개

구조화된 쿼리 언어(SQL) 데이터베이스는 다양한 테이블을 통해 많은 양의 데이터를 저장하고 관리할 수 있습니다. 대규모 데이터 세트에서 데이터를 정렬하는 방법을 이해하는 것은 특히 결과 집합을 분석하거나 보고서나 외부 통신을 위해 데이터를 조직화할 때 중요합니다.

데이터를 정렬하는 데 도움이 되는 SQL의 두 가지 일반적인 문은 GROUP BY와 ORDER BY입니다. GROUP BY 문은 쿼리에서 지정한 열을 기준으로 데이터를 그룹화하여 정렬하며 집계 함수와 함께 사용됩니다. ORDER BY는 결과 집합을 알파벳순 또는 숫자순으로 그리고 오름차순 또는 내림차순으로 구성할 수 있습니다.

이 자습서에서는 SQL을 사용하여 GROUP BY와 ORDER BY 문을 사용하여 쿼리 결과를 정렬합니다. 또한 결과를 더 정렬하기 위해 집계 함수와 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 및 보안 설정이 필요합니다. 이를 설정하려면 당사의 우분투 20.04에 MySQL 설치하는 방법 가이드를 따르십시오. 이 가이드는 또한 이 가이드의 단계 3에서 설명된대로 비루트 MySQL 사용자도 설정했다고 가정합니다.

이 튜토리얼에서 데이터 결과를 정렬하는 연습을 하려면 샘플 데이터가 포함된 데이터베이스와 테이블이 필요합니다. 삽입할 준비가 된 데이터베이스가 없는 경우 다음 MySQL에 연결하고 샘플 데이터베이스 설정하기 섹션을 읽어서 데이터베이스와 테이블을 만드는 방법을 배우십시오. 이 튜토리얼은 이 샘플 데이터베이스와 테이블을 계속 참조할 것입니다.

MySQL에 연결하고 샘플 데이터베이스 설정하기

SQL 데이터베이스가 원격 서버에서 실행되는 경우 로컬 컴퓨터에서 서버에 SSH로 로그인하세요:

ssh sammy@your_server_ip

다음으로 MySQL 프롬프트를 열고 sammy를 MySQL 사용자 계정 정보로 대체합니다:

mysql -u sammy -p

movieDB라는 데이터베이스를 생성하십시오:

CREATE DATABASE movieDB;

데이터베이스가 성공적으로 생성되면 다음 출력이 표시됩니다:

Output
Query OK, 1 row affected (0.01 sec)

movieDB 데이터베이스를 선택하려면 다음 USE 문을 실행하십시오:

USE movieDB;

Output
Database changed

데이터베이스를 선택한 후 해당 데이터베이스 내에 테이블을 생성하십시오. 이 튜토리얼의 예제를 위해 지역 영화관 상영 정보를 저장하는 테이블을 생성하겠습니다. 이 테이블에는 다음 일곱 개의 열이 포함됩니다:

• theater_id: 각 극장의 상영관에 대한 int 데이터 유형의 값이 저장되며, 테이블의 기본 키로 작동하여 이 열의 각 값은 해당 행의 고유 식별자로 기능합니다.
• date: 영화가 상영된 특정 날짜를 연, 월 및 일별로 저장하기 위해 DATE 데이터 유형을 사용합니다. 이 데이터 유형은 다음 매개변수를 준수합니다: 연도는 네 자리로, 월과 일은 최대 두 자리입니다 (YYYY-MM-DD).
• time: TIME 데이터 유형을 사용하여 영화의 예정 상영을 시, 분 및 초로 나타냅니다 (HH:MM:SS).
• movie_name: 최대 40자까지의 길이로 영화 이름을 저장하는 varchar 데이터 유형을 사용합니다.
• movie_genre: 각 영화의 장르에 대한 정보를 보유하기 위해 최대 30자의 문자열을 사용하는 varchar 데이터 유형을 사용합니다.
• guest_total: int 데이터 유형을 사용하여 영화 상영에 참석한 총 손님 수를 표시합니다.
• ticket_cost: decimal 데이터 유형을 사용하며, 이 열의 값은 소수점 오른쪽에 두 자리를 가지므로 정밀도는 네 자리입니다. 이 열은 특정 영화 상영의 티켓 비용을 나타냅니다.

다음 CREATE TABLE 명령문을 실행하여 각 열을 포함하는 movie_theater라는 테이블을 생성하십시오:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

다음으로, 빈 테이블에 일부 샘플 데이터를 삽입하십시오:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

데이터를 삽입한 후에는 SQL에서 쿼리 결과를 정렬하는 작업을 시작할 준비가되었습니다.

GROUP BY 사용

GROUP BY 문의 기능은 공유된 값으로 레코드를 그룹화하는 것입니다. GROUP BY 문은 쿼리에서 항상 집계 함수와 함께 사용됩니다. 집계 함수는 정보를 요약하고 단일 결과를 반환합니다. 예를 들어 열의 총 개수 또는 합계를 쿼리할 수 있으며, 이는 결과로 단일 값을 생성합니다. GROUP BY 절을 사용하면 원하는 각 그룹에 대해 하나의 결과 값을 얻을 수 있습니다.

GROUP BY는 당신이 지정한 그룹별로 여러 원하는 결과를 반환하는 데 유용합니다. 오직 하나의 열이 아닌. 게다가 GROUP BY는 항상 FROM 문과 WHERE 절 뒤에 와야 하며, 사용하는 경우 WHERE 절 이후에 와야 합니다. GROUP BY 및 집계 함수가 있는 쿼리의 구조를 예로 들어 보겠습니다:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

GROUP BY 문을 사용하는 방법을 설명하기 위해 몇 가지 영화 출시를 주도하고 마케팅 노력의 성과를 평가하려고 한다고 가정해 보겠습니다. 금요일과 토요일에 손님들로부터 수집한 데이터를 현지 극장에서 공유하도록 요청합니다. 먼저 SELECT 및 * 기호를 사용하여 movie_theater 테이블에서 “모든 열”을 선택하여 데이터를 검토합니다:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

이 데이터는 유용하지만, 특정 열에 대한 결과를 깊이 평가하고 정렬하려고 합니다.

몇 가지 다른 장르의 영화에 참여했기 때문에 관객들에게 얼마나 잘 받아졌는지 알고 싶습니다. 구체적으로 각 영화 장르별 평균 관객 수를 알고 싶습니다. movie_genre 열에서 다양한 종류의 영화를 검색하려면 SELECT를 사용하십시오. 그런 다음 guest_total 열에 집계 함수 AVG를 적용하고 average라는 열의 별칭을 만들기 위해 AS를 사용하고 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). 이 쿼리에는 집계 함수에 의해 반환된 열에 대한 별칭 total_revenue를 제공하기 위해 AS 절이 포함되어 있습니다. 그런 다음 쿼리를 완료하려면 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)

date 열을 그룹화하기 위해 GROUP BY를 사용했으므로, 결과 출력은 각 날짜별 티켓 매출의 총액을 제공합니다. 이 경우, 5월 27일 금요일에는 $7,272이고, 5월 28일 토요일에는 $9,646입니다.

지금은 한 영화에 초점을 맞추고 분석하려고 합니다: The Bad Guys. 이 시나리오에서는 가족이 애니메이션 영화를 관람하는 데 시간과 가격이 어떻게 영향을 미치는지 파악하려고 합니다. 이 쿼리에는 최대 ticket_cost를 검색하는 집계 함수 MAX를 사용하고, price_data 열의 별칭을 생성하기 위해 AS를 포함해야 합니다. 그 후에 movie_name을 사용하여 결과를 “The Bad Guys”로 좁히고, 비교 연산자 >를 사용하여 guest_total 숫자가 100 이상인 가장 인기 있는 영화 시간을 결정하기 위해 WHERE 절을 사용하세요. 그런 다음 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)

이 출력에 따르면, The Bad Guys 영화에 더 많은 손님이 참석한 시간은 저렴한 $8.00의 티켓 가격을 가진 아침 9시의 조조 상영이었습니다. 그러나 이 결과는 또한 오후 5시에 $13.00의 더 높은 티켓 가격을 지불한 것을 보여주며, 이는 가족들이 너무 늦은 시간의 상영을 선호하지 않고 조금 더 비싼 티켓 값도 감수할 것임을 시사합니다. 이는 The Bad Guys 영화가 손님 수가 100명보다 많았고 티켓 가격이 $18.00이었던 10:00 pm 시간과 비교할 때 공정한 평가로 보입니다. 이는 특정 시간과 가격 지점을 기반으로 선택을 하는 가족들의 출석률을 높일 수 있는 조조 상영과 일찍 시작하는 저녁 상영을 추가로 개설하는 것이 영화관 관리자에게 유용한 정보가 될 수 있습니다.

다음 쿼리를 사용하여 결과를 그룹화하려는 경우를 제외하고 대부분의 경우 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 다음에 와야 합니다. GROUP BY와 마찬가지로 ORDER BY도 FROM 문과 WHERE 절 다음에 와야 합니다. ORDER BY를 사용하는 일반적인 구문은 다음과 같습니다:

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 열을 문자값을 기준으로 내림차순으로 정렬하는 이런 유형의 쿼리를 수행해 봅시다. 결과를 더 정렬하려면 time 열에서 10:00 pm에 상영되는 영화 데이터를 검색하는 WHERE 절을 포함하세요:

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)

이 결과 집합은 Top Gun Maverick에서 Downtown Abbey A New Era까지 내림차순 알파벳 순으로 10:00 pm에 상영되는 네 가지 다른 영화를 나열합니다.

다음 쿼리에서는 ORDER BY와 GROUP BY 문을 결합하여 각 영화별 총 수익을 나타내는 결과를 생성하세요. 그러나, 영화관이 총 손님을 잘못 세어 각 상영에 사전에 구매하고 예약된 12명의 특별 손님을 포함하지 않았다고 가정합시다.

이 쿼리에서는 SUM을 사용하고 각 영화 표시마다 추가 12명의 손님을 포함시키기 위해 덧셈 연산자 +를 구현하고 그 결과에 12를 guest_total에 더합니다. 이를 괄호 안에 넣어야 합니다. 그런 다음, 이 총계를 ticket_cost와 곱하기 연산자 *를 사용하여 곱하고 괄호를 닫아 수학적 방정식을 완료합니다. 새로운 열에 대한 별칭으로 total_revenue라는 제목을 만들기 위해 AS 절을 추가하십시오. 그런 다음 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명의 손님 티켓 판매로 인한 총 수익을 보여주고, 가장 낮은 것부터 가장 높은 것까지 총 티켓 판매를 오름차순으로 정리합니다. 이를 통해 탑 건 매버릭이 가장 많은 티켓 판매를 기록한 반면, 남자는 가장 적게 팔렸음을 알 수 있습니다. 한편, 나쁜 녀석들과 다운턴 애비: 새로운 시대 영화는 총 티켓 판매에서 매우 가깝게 위치했습니다.

이 섹션에서는 ORDER BY 문을 구현하는 다양한 방법과 선호하는 순서를 지정하는 방법을 연습했습니다. 문자 및 숫자 데이터 값에 대한 오름차순 및 내림차순 순서를 모두 포함시키는 방법을 배웠으며 결과를 좁히기 위해 WHERE 절을 포함하고 집계 함수 및 수학적 방정식을 사용하여 GROUP BY 및 ORDER BY 문을 모두 사용하여 쿼리를 수행했습니다.

결론

SQL에서 와일드카드 사용하는 방법튜토리얼을 확인하여 LIKE 절을 사용하여 결과를 필터링하는 연습을 해보세요. GROUP BY 및 ORDER BY 문을 사용하는 방법을 이해하는 것은 결과 및 데이터를 정렬하는 데 중요합니다. 여러 결과를 한 그룹으로 구성하거나 열 중 하나를 알파벳 및 내림차순으로 정렬하거나 둘 다 동시에 수행하려는지 여부는 사용자 및 원하는 결과에 달려 있습니다. WHERE 절로 결과를 더 세분화하는 다른 방법에 대해서도 배웠습니다.