MySQL 해커랭크 | Average Population of Each Continent ( ROUND, CEIL, FLOOR 함수 )

 

문제

https://www.hackerrank.com/challenges/average-population-of-each-continent/problem?isFullScreen=true

Average Population of Each Continent | HackerRank

 

• Query the names of all the continents (COUNTRY.Continent)
• Query their respective average city populations (CITY.Population)
  • rounded down to the nearest integer. 
• CITY.CountryCode = COUNTRY.Code 

 

테이블

CITY	COUNTRY

 

풀이과정

# 오답1

CITY 테이블에 LEFT JOIN을 했더니 COTINENT값에 NULL 값이 생김

→ CITY테이블의 NAME은 있는데 CONTINENT 정보는 NULL인 경우가 있음

SELECT C.NAME, CT.CONTINENT, ROUND(AVG(C.POPULATION),0)
FROM CITY C
LEFT JOIN COUNTRY CT ON C.COUNTRYCODE = CT.CODE
GROUP BY CT.CONTINENT, C.NAME

 

# 오답2

문제에서 rounded down 하라고 해서 ROUND(숫자, 0)을 해줬는데 틀림

SELECT CT.CONTINENT, ROUND(AVG(C.POPULATION),0)
FROM CITY C
LEFT JOIN COUNTRY CT ON C.COUNTRYCODE = CT.CODE
GROUP BY 1

 

# 정답

(1) INNER JOIN 사용

(2) rounded down → FLOOR 함수 사용

SELECT CT.CONTINENT, FLOOR(AVG(C.POPULATION))
FROM CITY C
INNER JOIN COUNTRY CT ON C.COUNTRYCODE = CT.CODE
GROUP BY 1

 

배운점

▶ ROUND, CEIL, FLOOR 차이

• ROUND(숫자, 자릿수) : 반올림
• CEIL(숫자) : 올림
• FLOOR(숫자) : 버림

https://velog.io/@donghoim/MySQL-SQL-ROUND-FLOOR-CEIL-%ED%95%A8%EC%88%98

[MySQL] SQL ROUND, FLOOR, CEIL 함수