SQL 통계_ 행열 뒤집기. ( 시계열 표현 )

** 실습 ) 

다음의 결과를 년도를 가로로 구분하고 해당년도에 맞게 데이터가 들어가도록 행열을 뒤집어보자.

SELECT  attrc_name, 2010, 2011, 2011 ... 2017

FROM ( 10번 쿼리 )

* 년도 기준으로 뒤집어짐.

attrac_name, IF(year=2010, figure, 0) as 2010,

                       IF(year=2011, figure, 0) as 2011,

                                 .............

                        IF(year=2017, figure,0) as 2017

1) .IF 조건문과 일치할경우에만 넣고자 하는 값을 입력. 

SELECT attrc_name,

IF(year=2010, figure, 0) as '2010',IF(year=2011, figure, 0) as '2011',

IF(year=2012, figure, 0) as '2012',IF(year=2013, figure, 0) as '2013',

IF(year=2014, figure, 0) as '2014',IF(year=2015, figure, 0) as '2015',

IF(year=2016, figure, 0) as '2016', IF(year=2017, figure, 0) as '2017'
FROM (SELECT CONCAT(p.prvn_name,' ',d.distc_name,' ',att.attrc_name) AS attrc_name, SUBSTR(f.basis_date,1,4) as year, round(AVG(f.native_cnt)) as figure
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd 
JOIN attraction att ON d.prvn_cd = att.prvn_cd AND d.distc_cd = att.distc_cd
JOIN figure f ON att.prvn_cd = f.prvn_cd AND att.distc_cd = f.distc_cd AND att.attrc_cd = f.attrc_cd
WHERE p.prvn_name = '경기도' AND d.distc_name='과천시'
GROUP BY SUBSTR(f.basis_date,1,4),att.prvn_cd, att.distc_cd, att.attrc_cd
ORDER BY year, figure DESC) a ;

2. 여러개의 데이터 합치기: 합치거나(sum) 최대값(max) 을 주면 0이 든 빈값이 하나로 합쳐지므로 원하는 값만 남는다.그런다음,  attrc_name 기준 그룹으로 묶기. 

SELECT attrc_name,

MAX(IF(year=2010, figure, 0)) as '2010',

MAX(IF(year=2011, figure, 0)) as '2011',

MAX(IF(year=2012, figure, 0)) as '2012',
MAX(IF(year=2013, figure, 0)) as '2013',

MAX(IF(year=2014, figure, 0)) as '2014',
MAX(IF(year=2015, figure, 0)) as '2015',

MAX(IF(year=2016, figure, 0)) as '2016',

MAX(IF(year=2017, figure, 0)) as '2017'
FROM (SELECT CONCAT(p.prvn_name,' ',d.distc_name,' ',att.attrc_name) AS attrc_name, SUBSTR(f.basis_date,1,4) as year, round(AVG(f.native_cnt)) as figure
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd 
JOIN attraction att ON d.prvn_cd = att.prvn_cd AND d.distc_cd = att.distc_cd
JOIN figure f ON att.prvn_cd = f.prvn_cd AND att.distc_cd = f.distc_cd AND att.attrc_cd = f.attrc_cd
WHERE p.prvn_name = '경기도' AND d.distc_name='과천시'
GROUP BY SUBSTR(f.basis_date,1,4),att.prvn_cd, att.distc_cd, att.attrc_cd
ORDER BY year, figure DESC) a 
GROUP BY attrc_name;

행열 뒤집기 완료 !