[SQL] HackerRank - Aggregation

[ MySQL 구문 ]

오늘은 집계(Aggregation)와 관련된 함수를 알아보자!

집계 함수는 이름 그대로 데이터를 집계하는 함수를 의미한다. 달리 명시하지 않는 한 집계 함수는 NULL 값을 무시한다.

✅ AVG()

행의 평균값을 반환한다.

SELECT AVG(필드명)
FROM 테이블명

• AVG()는 NULL 값을 제외하고 평균을 구하는데 만약 NULL 값을 0으로 치환하고 평균값을 내고 싶은 경우 다음과 같이 코드를 작성하면 된다.

SELECT AVG(IFNULL(필드명, 0))
FROM 테이블명

✅ COUNT()

행의 개수를 반환한다.

SELECT COUNT(필드명)
FROM 테이블명

• 중복값을 제외한 행의 개수를 카운트하고 싶을 경우, 다음과 같이 DISTINCT를 이용하면 된다.

SELECT COUNT(DISTINCT 필드명)
FROM 테이블명

✅ MAX(), MIN()

행의 최댓값/최솟값을 반환한다.

SELECT MAX(필드명)
FROM 테이블명

SELECT MIN(필드명)
FROM 테이블명

• MAX(), MIN()는 다른 집계 함수와는 달리 문자열이나 날짜에도 사용 가능하다.

✅ SUM()

행의 합계를 반환한다.

SELECT SUM(필드명)
FROM 테이블명

💡 문제마다 추가적으로 필요한 개념들은 따로 정리해보도록 하겠다!

---

[ Aggregation ]

🔗 Aggregation 문제 리스트

Revising Aggregations - The Count Function

Query a count of the number of cities in CITY having a Population larger than 100,000.

• CITY 테이블의 Population이 100,000보다 큰 레코드의 개수를 출력하라.

💡 Solve

SELECT COUNT(NAME)
FROM CITY
WHERE POPULATION > 100000;

Revising Aggregations - The Sum Function

Query the total population of all cities in CITY where District is California.

• CITY 테이블의 District가 California인 레코드의 population의 합을 출력하라.

💡 Solve

SELECT AVG(POPULATION)
FROM CITY
WHERE DISTRICT = 'California';

Revising Aggregations - Averages

Query the average population of all cities in CITY where District is California.

• CITY 테이블의 District가 California인 레코드의 population의 평균을 출력하라.

💡 Solve

SELECT AVG(POPULATION)
FROM CITY
WHERE DISTRICT = 'California';

Average Population

Query the average population for all cities in CITY, rounded down to the nearest integer.

• CITY 테이블의 모든 population의 평균을 가장 가까운 정수로 출력하라.

💡 Solve

SELECT ROUND(AVG(POPULATION), 0)
FROM CITY;

Japan Population

Query the sum of the populations for all Japanese cities in CITY. The COUNTRYCODE for Japan is JPN.

• CITY 테이블의 COUNTRYCODE가 'JPN'인 레코드의 population의 총합을 출력하라.

💡 Solve

SELECT SUM(POPULATION)
FROM CITY
WHERE COUNTRYCODE = 'JPN';

Population Density Difference

Query the difference between the maximum and minimum populations in CITY.

• CITY 테이블의 Population의 최댓값과 최솟값의 차이를 출력하라.

💡 Solve

SELECT MAX(POPULATION) - MIN(POPULATION)
FROM CITY;

---

The Blunder

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard's 0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.
Write a query calculating the amount of error (i.e.: average monthly salaries), and round it up to the next integer.

• 실제 월평균 급여과 0을 제외한 월평균 급여의 차이를 출력하라.
• 가장 가까운 다음 정수로 출력하라 → 올림하라.

💡 Solve

SELECT CEIL(AVG(SALARY) - AVG(REPLACE(SALARY, 0, '')))
FROM EMPLOYEES

✅ CEIL

✅ REPLACE

---

Top Earners

We define an employee's total earnings to be their monthly salary X months worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as 2 space-separated integers.

• total earnings(총 수입)는 salary * months이다.
• 가장 큰 값을 갖는 total earnings를 max total earnings로 정의한다.
• (max total earnings, max total earnings 값을 갖는 직원 수) 출력하기

💡 Solve

SELECT SALARY * MONTHS AS earnings, COUNT(*)
FROM EMPLOYEE
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1

✅ GROUP BY

---

Weather Observation Station 14

Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than 137.2345. Truncate your answer to 4 decimal places.

• STATION 테이블의 LAT_N 필드값이 137.2345보다 작은 레코드 중 가장 큰 LAT_N 필드값을 출력하라.
• 소수점 아래 4자리로 반올림하여 출력하라.

💡 Solve

SELECT ROUND(MAX(LAT_N), 4)
FROM STATION
WHERE LAT_N < 137.2345;

Weather Observation Station 15

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345. Round your answer to 4 decimal places.

• STATION 테이블의 LAT_N 필드값이 137.2345보다 작은 레코드 중 가장 큰 LAT_N 값을 갖는 레코드의 LONG_W 필드값을 출력하라.
• 소수점 아래 4자리로 반올림하여 출력하라.

💡 Solve

SELECT ROUND(LONG_W, 4)
FROM STATION
WHERE LAT_N < 137.2345
ORDER BY LAT_N DESC
LIMIT 1;

Weather Observation Station 16

Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780. Round your answer to 4 decimal places.

• STATION 테이블의 LAT_N 필드값이 38.7780보다 큰 레코드 중 가장 작은 LAT_N 필드값을 출력하라.
• 소수점 아래 4자리로 반올림하여 출력하라.
  
  

💡 Solve

SELECT ROUND(MIN(LAT_N), 4)
FROM STATION
WHERE LAT_N > 38.7780;

Weather Observation Station 17

Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 decimal places.

• STATION 테이블의 LAT_N 필드값이 38.7780보다 큰 레코드 중 가장 작은 LAT_N 값을 갖는 레코드의 LONG_W 필드값을 출력하라.
• 소수점 아래 4자리로 반올림하여 출력하라.

💡 Solve

SELECT ROUND(LONG_W, 4)
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N
LIMIT 1;

---

[ 참고자료 ]

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
https://ansohxxn.github.io/db/ch6/