MySQL 예제를 만들어 보았습니다. 이 예제에서는 시간 계산 및 그룹을 다룹니다.
문제
테이블 `girlgroup`의 데이터가 다음과 같이 있다고 가정해 봅시다.
| groupNm | memberNm | birthday |
|---|---|---|
| BLACKPINK | 지수 | 1995-01-03 |
| BLACKPINK | 제니 | 1996-01-16 |
| BLACKPINK | 로제 | 1997-02-11 |
| BLACKPINK | 리사 | 1997-03-27 |
| LE SSERAFIM | 사쿠라 | 1998-03-19 |
| aespa | 카리나 | 2000-04-11 |
| ITZY | 예지 | 2000-05-26 |
| ITZY | 리아 | 2000-07-21 |
| LE SSERAFIM | 김채원 | 2000-08-01 |
| aespa | 지젤 | 2000-10-30 |
| aespa | 윈터 | 2001-01-01 |
| ITZY | 류진 | 2001-04-27 |
| ITZY | 채령 | 2001-06-05 |
| LE SSERAFIM | 허윤진 | 2001-10-08 |
| IVE | 가을 | 2002-09-24 |
| aespa | 닝닝 | 2002-10-23 |
| LE SSERAFIM | 카즈하 | 2003-08-09 |
| IVE | 안유진 | 2003-09-01 |
| ITZY | 유나 | 2003-12-09 |
| IVE | 레이 | 2004-02-03 |
| NewJeans | 민지 | 2004-05-07 |
| IVE | 장원영 | 2004-08-31 |
| NewJeans | 하니 | 2004-10-06 |
| IVE | 리즈 | 2004-11-21 |
| NewJeans | 다니엘 | 2005-04-11 |
| NewJeans | 해린 | 2006-05-15 |
| LE SSERAFIM | 홍은채 | 2006-11-10 |
| IVE | 이서 | 2007-02-21 |
| NewJeans | 혜인 | 2008-04-21 |
[문제 1] 이 테이블에서 각 멤버의 나이를 구하여 다음과 같이 표출되도록 하시오. 단, 만 나이로 표시되도록 하며 기준 날짜는 2023년 12월 31일입니다.
| groupNm | memberNm | age |
|---|---|---|
| BLACKPINK | 지수 | 28 |
| BLACKPINK | 제니 | 27 |
| BLACKPINK | 로제 | 26 |
| BLACKPINK | 리사 | 26 |
| LE SSERAFIM | 사쿠라 | 25 |
| aespa | 카리나 | 23 |
| ITZY | 예지 | 23 |
| ITZY | 리아 | 23 |
| LE SSERAFIM | 김채원 | 23 |
| aespa | 지젤 | 23 |
| aespa | 윈터 | 22 |
| ITZY | 류진 | 22 |
| ITZY | 채령 | 22 |
| LE SSERAFIM | 허윤진 | 22 |
| IVE | 가을 | 21 |
| aespa | 닝닝 | 21 |
| LE SSERAFIM | 카즈하 | 20 |
| IVE | 안유진 | 20 |
| ITZY | 유나 | 20 |
| IVE | 레이 | 19 |
| NewJeans | 민지 | 19 |
| IVE | 장원영 | 19 |
| NewJeans | 하니 | 19 |
| IVE | 리즈 | 19 |
| NewJeans | 다니엘 | 18 |
| NewJeans | 해린 | 17 |
| LE SSERAFIM | 홍은채 | 17 |
| IVE | 이서 | 16 |
| NewJeans | 혜인 | 15 |
정답
SELECT groupNm, memberNm, TIMESTAMPDIFF(YEAR, birthday, '2023-12-31') AS age FROM `girlgroup`;
[문제 2] 이 테이블에서 각 그룹별 멤버 수를 구하여 다음과 같이 표출되도록 하시오.
| groupNm | members |
|---|---|
| aespa | 4 |
| BLACKPINK | 4 |
| ITZY | 5 |
| IVE | 6 |
| LE SSERAFIM | 5 |
| NewJeans | 5 |
정답
SELECT groupNm, COUNT(*) AS members FROM `girlgroup` GROUP BY groupNm;
[문제 3] 이 테이블에서 각 그룹별로 1999년 12월 31일 이전 출생한 멤버 수를 구하여 다음과 같이 표출되도록 하시오.
| groupNm | members |
|---|---|
| BLACKPINK | 4 |
| LE SSERAFIM | 1 |
정답
SELECT groupNm, COUNT(*) AS members FROM `girlgroup` WHERE birthday <= '1999-12-31' GROUP BY groupNm;
[문제 4] 앞의 '문제 2'의 결과를 다음과 같이 멤버 수가 5명인 그룹만 표출되도록 하시오.
| groupNm | members |
|---|---|
| ITZY | 5 |
| LE SSERAFIM | 5 |
| NewJeans | 5 |
정답
SELECT groupNm, COUNT(*) AS members FROM `girlgroup` GROUP BY groupNm HAVING members = 5;
[문제 5] 이 테이블에서 각 그룹별 멤버들의 평균 나이를 소수점 이하 둘째 자리에서 반올림한 값으로 산출하여 다음과 같이 평균 나이가 많은 순서로 표출되도록 하시오. 단, 만 나이로 표시되도록 하며 기준 날짜는 2023년 12월 31일입니다.
| groupNm | ageav |
|---|---|
| BLACKPINK | 26.8 |
| aespa | 22.3 |
| ITZY | 22.0 |
| LE SSERAFIM | 21.4 |
| IVE | 19.0 |
| NewJeans | 17.6 |
정답
SELECT groupNm, ROUND(AVG(TIMESTAMPDIFF(YEAR, birthday, '2023-12-31')),1) AS ageav
FROM `girlgroup` GROUP BY groupNm ORDER BY ageav DESC;
설명
1번 문제의 TIMESTAMPDIFF 함수는 시간 차이를 산출하기 위한 함수입니다. TIMESTAMPDIFF(단위, 시간A, 시간B) 형식으로 씁니다. 시간B에서 시간A를 뺀 후 그 값을 단위에 따라 구하게 됩니다. 단위는 SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR로 지정할 수 있습니다. 시간B가 시간A보다 나중이면 양수가 나오고 반대로 시간A가 시간B보다 나중이면 음수가 나옵니다.
TIMESTAMPDIFF(DAY, 시간A, 시간B)의 경우 DATEDIFF(시간B, 시간A)로 대신 쓸 수 있습니다. 단, DATEDIFF의 경우는 TIMESTAMPDIFF와는 반대로 앞에 오는 날짜가 나중이어야 양수를 출력한다는 점을 주의해 주십시오.
2번-3번 문제의 COUNT 함수는 WHERE 조건에 맞는 전체 행의 수를 구하기 위한 함수입니다. GROUP BY 절과 함께 사용시 그룹별로 묶어서 COUNT 함수의 값을 산출하게 됩니다.
4번 문제는 HAVING 절을 사용했는데, 이는 GROUP BY 절에 의해 산출된 결과에서 다시 조건을 지정해 해당 조건에 맞는 산출치만 표출하는 절입니다. 이 예제에서는 행의 수를 groupNm으로 묶어서 산출한 값을 members로 두고, members의 값이 5인 결과만 표출하도록 하는 의미입니다.
5번 문제의 AVG 함수는 선택된 열의 수에 대한 평균을 구하는 함수입니다. 2번-3번 문제와 마찬가지로 GROUP BY 절과 함께 사용시 그룹별로 묶어서 평균을 산출합니다.