SQL_Practice_기온 데이터 분석

 

◎ 기온 데이터 생성

• https://data.kma.go.kr/stcs/grnd/grndTaList.do?pgmNo=70

기상자료개방포털[기후통계분석:통계분석:기온분석]

• 해당 페이지에서 1904년 1월 1일부터 2021년 8월 4일까지의 기온 데이터 검색
• csv파일로 저장 후 SQL Developer를 사용해 분석

다운 받은 csv파일의 모습이며 불필요한 행들은 제거

 

-- csv파일의 내용을 넣을 테이블을 생성
CREATE TABLE TB_TEMPER_DATA
(
STD_DE CHAR(10)
, AREA_CD CHAR(3)
, AVG_TEMPER NUMBER(3, 1)
, MIN_TEMPER NUMBER(3, 1)
, MAX_TEMPER NUMBER(3, 1)
);

-- 분석을 위한 테이블 추가 생성
CREATE TABLE TB_TEMPER
(
STD_DE CHAR(8)
, AREA_CD CHAR(3)
, AVG_TEMPER NUMBER(3, 1)
, MIN_TEMPER NUMBER(3, 1)
, MAX_TEMPER NUMBER(3, 1)
, CONSTRAINT TB_TEMPER_PK PRIMARY KEY (STD_DE, AREA_CD)
);

-- TB_TEMPER에 데이터 저장
INSERT INTO TB_TEMPER
SELECT
REPLACE(STD_DE, '-', '') AS STD_DE
, AREA_CD
, AVG_TEMPER
, MIN_TEMPER
, MAX_TEMPER
FROM TB_TEMPER_DATA;
COMMIT;

 

 

◎ 기온 데이터 분석

 

-- 서울시의 AREA_CD는 108인 것을 확인
-- 서울시 역사상 최저 및 최고 온도를 구해본다.
SELECT
A.AREA_CD
, MIN(A.MIN_TEMPER) AS MIN_TEMPER
, MAX(A.MAX_TEMPER) AS MAX_TEMPER
FROM TB_TEMPER A
WHERE A.AREA_CD = '108' --지역코드 : 서울특별시
GROUP BY A.AREA_CD 
;

-- 서울시 역사상 최저 및 최고 온도를 구하고 해당 일자 또한 구해본다.
SELECT 
B.AREA_CD
,B.STD_DE
,B.MIN_TEMPER
,B.MAX_TEMPER
FROM
	(
	SELECT 
		AREA_CD
		, MIN(MIN_TEMPER) AS MIN_TEMPER
		, MAX(MAX_TEMPER) AS MAX_TEMPER
		FROM TB_TEMPER A
		WHERE A.AREA_CD='108'
		GROUP BY A.AREA_CD
	) A
	, TB_TEMPER B
WHERE (B.AREA_CD=A.AREA_CD AND B.MIN_TEMPER = A.MIN_TEMPER)
    OR (B.AREA_CD=A.AREA_CD AND B.MAX_TEMPER = A.MAX_TEMPER);
    
-- 생일 기준 최고 및 최저 온도 구하기
SELECT SUBSTR(A.STD_DE,5,4) AS MMDD
	, MIN(MIN_TEMPER) AS MIN_TEMPER
	, MAX(MAX_TEMPER) AS MAX_TEMPER
	FROM TB_TEMPER A
	WHERE A.AREA_CD='108' 
	AND A.STD_DE LIKE '____1020'
	GROUP BY SUBSTR(A.STD_DE,5,4)
;

 

역사상 최저온도는 -23.1도 최고온도는 39.6도인 것을 확인할 수 있다.

날짜까지 나오는 코드는 복잡하다.. 완벽히 숙지할 필요가 있다..

생일날 최저 / 최고 온도를 확인할 수 있다.

 

 

◎기온 데이터 분석(2)

 

-- 서울시를 기준 1년중 평균 일교차가 가장 큰달 구하기
SELECT A.MM
, A.DAILY_CROSS
FROM
	(
	SELECT
		SUBSTR(A.STD_DE, 5, 2) AS MM
		, ROUND(AVG(MAX_TEMPER-MIN_TEMPER), 2) AS DAILY_CROSS
		FROM TB_TEMPER A
		WHERE A.AREA_CD = '108' -- : 
		GROUP BY SUBSTR(A.STD_DE, 5, 2)
		ORDER BY DAILY_CROSS DESC
		) A
WHERE ROWNUM <= 1;

-- 서울시를 기준 일교차가 가장 큰 날과 해당 일의 일교차, 최저, 최고 온도를 구하기
SELECT A.STD_DE 
, A.DAILY_CROSS 
, A.MAX_TEMPER 
, A.MIN_TEMPER
FROM
	(
		SELECT A.STD_DE 
		, A.MAX_TEMPER-A.MIN_TEMPER AS DAILY_CROSS
		, A.MAX_TEMPER
		, A.MIN_TEMPER
		FROM TB_TEMPER A 
		WHERE A.AREA_CD = '108' -- : 
		AND MAX_TEMPER IS NOT NULL
		AND MIN_TEMPER IS NOT NULL
		ORDER BY DAILY_CROSS DESC
		) A
WHERE ROWNUM <= 1;

 

 

◎기온 데이터 분석(3)

 

-- 연도별 평균기온의 상승 확인하기
SELECT SUBSTR(A.STD_DE, 1, 4) AS YYYY
, ROUND(AVG(AVG_TEMPER), 2) AS AVG_TEMPER
FROM TB_TEMPER A 
WHERE A.AREA_CD = '108' 
GROUP BY SUBSTR(A.STD_DE, 1, 4)
ORDER BY SUBSTR(A.STD_DE, 1, 4)

-- 10년 단위로 평균기온의 상승 확인하기
SELECT
CASE
WHEN YYYY BETWEEN '1900' AND '1909' THEN '1900년대' WHEN YYYY BETWEEN '1910' AND '1919' THEN '1910년대'
WHEN YYYY BETWEEN '1920' AND '1929' THEN '1920년대' WHEN YYYY BETWEEN '1930' AND '1939' THEN '1930년대'
WHEN YYYY BETWEEN '1940' AND '1949' THEN '1940년대' WHEN YYYY BETWEEN '1950' AND '1959' THEN '1950년대'
WHEN YYYY BETWEEN '1960' AND '1969' THEN '1960년대' WHEN YYYY BETWEEN '1970' AND '1979' THEN '1970년대'
WHEN YYYY BETWEEN '1980' AND '1989' THEN '1980년대' WHEN YYYY BETWEEN '1990' AND '1999' THEN '1990년대'
WHEN YYYY BETWEEN '2000' AND '2009' THEN '2000년대' WHEN YYYY BETWEEN '2010' AND '2019' THEN '2010년대'
WHEN YYYY BETWEEN '2020' AND '2029' THEN '2020년대' END AS "연대"
, ROUND(AVG(AVG_TEMPER), 2) AS "평균기온"
FROM
(
SELECT SUBSTR(A.STD_DE, 1, 4) AS YYYY
, ROUND(AVG(AVG_TEMPER), 2) AS AVG_TEMPER
FROM TB_TEMPER A 
GROUP BY SUBSTR(A.STD_DE, 1, 4)
ORDER BY SUBSTR(A.STD_DE, 1, 4) 
) 
GROUP BY CASE
WHEN YYYY BETWEEN '1900' AND '1909' THEN '1900년대' WHEN YYYY BETWEEN '1910' AND '1919' THEN '1910년대'
WHEN YYYY BETWEEN '1920' AND '1929' THEN '1920년대' WHEN YYYY BETWEEN '1930' AND '1939' THEN '1930년대'
WHEN YYYY BETWEEN '1940' AND '1949' THEN '1940년대' WHEN YYYY BETWEEN '1950' AND '1959' THEN '1950년대'
WHEN YYYY BETWEEN '1960' AND '1969' THEN '1960년대' WHEN YYYY BETWEEN '1970' AND '1979' THEN '1970년대'
WHEN YYYY BETWEEN '1980' AND '1989' THEN '1980년대' WHEN YYYY BETWEEN '1990' AND '1999' THEN '1990년대'
WHEN YYYY BETWEEN '2000' AND '2009' THEN '2000년대' WHEN YYYY BETWEEN '2010' AND '2019' THEN '2010년대'
WHEN YYYY BETWEEN '2020' AND '2029' THEN '2020년대' END
ORDER BY "연대"
;

 

평균기온은 상승되고 있음을 알 수 있다.