연도 별 평균 미세먼지 농도 조회하기

문제 설명

AIR_POLLUTION 테이블은 전국의 월별 미세먼지 정보를 담은 테이블입니다.

AIR_POLLUTION 테이블

Column name	Type	Nullable	Description
LOCATION1	VARCHAR	FALSE	지역구분1
LOCATION2	VARCHAR	FALSE	지역구분2
YM	DATE	FALSE	측정일
PM_VAL1	NUMBER	FALSE	미세먼지 오염도
PM_VAL2	NUMBER	FALSE	초미세먼지 오염도

예를 들어, AIR_POLLUTION 테이블이 다음과 같을 때

LOCATION1	LOCATION2	YM	PM_VAL1	PM_VAL2
경기도	수원	2018-01-01	48	27
경기도	수원	2018-02-01	51	30
경기도	수원	2018-03-01	52	21
경기도	수원	2018-04-01	52	20
경기도	수원	2018-05-01	45	19
경기도	수원	2018-06-01	39	17
경기도	수원	2018-07-01	27	15
경기도	수원	2018-08-01	26	16
경기도	수원	2018-09-01	21	12
경기도	수원	2018-10-01	31	18
경기도	수원	2018-11-01	56	21
경기도	수원	2018-12-01	44	27
서울시	노원	2018-11-01	25	45
경기도	용인	2018-02-01	14	21

SQL을 실행하면 다음과 같이 출력되어야 합니다.

YEAR	PM10	PM2.5
2018	41	20.25

---

문제

AIR_POLLUTION 테이블에서 수원지역의 연도별 평균 미세먼지 오염도와 평균 초미세먼지 오염도를 조회하는 SQL문을 작성해주세요.
이때, 평균 미세먼지 오염도와 평균 초미세먼지 오염도의 컬럼명은 각각 PM10, PM2.5로 해 주시고, 값은 소수 셋째 자리에서 반올림해주세요.
결과는 연도를 기준으로 오름차순 정렬해주세요.

---

풀이 과정

1. 수원 지역 필터링
   • WHERE 절을 사용하여 LOCATION2 컬럼의 값이 ‘수원’인 데이터만 선택
2. 연도 추출
   • EXTRCT(YEAR FROM YM) : YEAR() 함수를 사용하여 YM 컬럼에서 연도를 추출
   • 이후 연도별로 데이터를 그룹화
3. 평균 오염도 계산 및 반올림
   • AVG ()함수를 사용하여 각각의 평균 미세먼지 오염도와 평균 초미세먼지 오염도 계산하고
   • ROUND() 함수를 사용하여 값을 소수 셋째 자리에서 반올림
     • 미세먼지 오염도는 소수점 없이 반올림 시키고
     • 초미세먼지 오염도는 소수 둘째 자리까지 반올림
4. 그룹화 및 정렬
   • GROUP BY YEAR(YM) 사용하여 연도별로 데이터를 그룹화
   • ORDER BY YEAR(YM) 연도 기준 오름차순으로 정렬

• 교훈
  • 미세먼지 오염도는 정수형인데, 반올림해서 정수화 해야하는건지…. TRUNC 해서 소숫점 뒤 날리는건지…… 문제에 제대로 나와있지 않았다.
    • 또 찾았다 Oracle은 TRUNC(값) 인데 MySQL은 TRUNCATE(값, 소숫점자리) 이렇게 쓰는 구나… 수확이 쏠쏠하군..
  • 초미세먼지 오염도의 별칭을 PM2.5로 지정해야 하는데, MySQL에서는 별칭에 특수문자(여기서는 점 .)가 포함되면 에러가 발생할 수 있으므로 백틱(`)을 사용하여 감싸야 한다.. 아님 에러나니까 주의라반… 식별자까지도 끝까지! 꼼꼼하게! 띵킹하자… 띵! 킹!

---

정답

SELECT 
    YEAR(YM) AS YEAR,
    ROUND(AVG(PM_VAL1), 0) AS PM10,
    ROUND(AVG(PM_VAL2), 2) AS 'PM2.5' --쩜! 주의하자 (Oracle에서는 식별자의 경우 쌍따옴표로 해야한다.)
FROM 
    AIR_POLLUTION
WHERE 
    LOCATION2 = '수원'
GROUP BY 
    YEAR(YM)
ORDER BY 
    YEAR(YM);

---

Tip

연도 추출

• YEAR(YM) 함수 사용하여 날짜에서 연도 추출

평균 오염도 계산 및 반올림

• AVG() 함수 사용 해서 평균 오염도를 계산하고, ROUND() 함수 써서 소수 셋째 자리에서 반올림

그룹화와 정렬

• GROUP BY YEAR(YM) : YM 연도별로 데이터를 그룹화
• ORDER BY YEAR(YM) : 결과를 연도 기준 오름차순 정렬

컬럼 별칭 시에 점 . 이 있을 시 주의!

• MySQL에서는 별칭에 특수문자(여기서는 점 .) 이 포함되면 에러가 발생할 수 있으므로 백틱(`)을 사용하여 감싸야 함

---

개념 확인

• 소수점 이하 버림 함수 (MySQL vs Oracle)
  • MySQL에서는 소수점 이하를 버림(제거)할 때 TRUNCATE() 함수를 사용함.
    TRUNCATE(값, 소수점자리수)
  • Oracle에서는 같은 기능을 하는 함수로 TRUNC() 함수를 사용함
    TRUNC(값)
• 문자열 리터럴과 식별자 표기 (MySQL vs Oracle)
  • 문자열 리터럴은 항상 작은따옴표 (‘’)를 사용하여 표기함
    예: 'Hello World'
  • MySQL
    • 기본적으로 식별자는 따옴표 없이 사용하며, 필요시 백틱(‘’) 사용
      ex) SELECT column_name FROM table_name;
    • 큰따옴표(“)는 기본적으로 문자열 리터럴로 인식 (ANSI_QUOTES 모드 활성화 시 식별자 표기에 사용 가능)
  • Oracle
    • 식별자를 사용할 때, 대소문자 및 특수문자를 유지하려면 큰따옴표 (“)로 감싸야 함
      ex) SELECT "Column_Name" FROM "Table_Name";
• YEAR 함수 (MySQL)
  • YEAR(date) 함수는 주어진 날짜에서 연도를 숫자로 반환
    • EXTRACT() 함수는 ANSI SQL 표준 함수로, Oracle, MySQL 등 다양한 DBMS에서 지원됨
      EXTRACT(YEAR FROM 컬럼명)
    • YEAR() 함수는 MySQL에서 제공하는 함수
      YEAR(컬럼명)와 같이 간단하게 사용
• GROUP 함수
  • 특정 컬럼 값을 기준으로 데이터를 그룹화하여 집계 계산 수행
  • AVG 함수 : 주어진 컬럼의 모든 숫자형 데이터의 평균값을 계산하는 집계 함수.
    컬럼의 값들을 모두 더한 후, NULL 값을 제외한 행의 개수로 나누어 평균을 산출함
    • 연산을 포함한 계산식의 평균을 구할 수도 있음.
      ex) SELECT AVG((컬럼명1 * 2 + 컬럼명2) / 3)
• ROUND() 함수
  • 소수점 이하의 값을 반올림하는 데 사용
  • 기본 구문은 ROUND(컬럼명 or 숫자, 자릿수)이며, 반올림하고자 하는 소수 자릿수를 명시함

---

Useful link

연도 별 평균 미세먼지 농도 조회하기