#메인대시보드 - 추후 합계 값 table 생성하여 속도 올릴 예정 set @SGG = 0; #시군구 미선택시 0 (지도클릭) set @HD = 0; #행정동 미선택시 0 (지도클릭) set @M = 0; #전체 선택시 0 현재 시각화 프로젝트를 진행 중에 있습니다. 아래 처럼 작성한 후 더이상은 떠오르지 않는 상황 입니다.. 더 진보된 방법이 있을까요? #1 - 1)성별 세대별 소비매출현황 (필터(4) = YEAR, MONTH, SGG, HD) #5세 단위 → 10세 단위로 수정 SELECT N.SEX_CD, CASE WHEN N.AGE_CD = 0019 THEN '20대 미만' WHEN N.AGE_CD in (2024,2529) THEN '20대' WHEN N.AGE_CD in (3034,3539) THEN '30대' WHEN N.AGE_CD in (4044,4549) THEN '40대' WHEN N.AGE_CD in (5054,5559) THEN '50대' WHEN N.AGE_CD in (6064,6569) THEN '60대' WHEN N.AGE_CD = 7099 THEN '70대' ELSE 'NONE' END, SUM(N.AMT_N) AS 2020매출액, CONCAT(ROUND(((SUM(N.AMT_N) - SUM(N.AMT_O)) / SUM(N.AMT_O) * 100), 1), '%') AS 매출액증감 FROM ( SELECT CASE WHEN @M != 0 AND CONCAT(YEAR(TA_D),MONTH(TA_D)) = '201901' THEN AMT #CONCAT(YEAR,@M) WHEN @M = 0 AND YEAR(TA_D) = '2019' THEN AMT #YEAR ELSE 'NONE' END AS 'AMT_N', CASE WHEN @M != 0 AND CONCAT(YEAR(TA_D),MONTH(TA_D)) = '201801' THEN AMT #CONCAT(YEAR-1,@M) WHEN @M = 0 AND YEAR(TA_D) = '2018' THEN AMT #YEAR-1 ELSE 'NONE' END AS 'AMT_O', SEX_CD, AGE_CD, TA_D FROM GN_SEXAGE WHERE SEX_CD != '법인' AND CASE WHEN @HD != 0 THEN HD_CD = @HD WHEN @SGG != 0 THEN LEFT(HD_CD,5) = @SGG ELSE 1 END)N GROUP BY N.SEX_CD, CASE WHEN N.AGE_CD = 0019 THEN '20대 미만' WHEN N.AGE_CD in (2024,2529) THEN '20대' WHEN N.AGE_CD in (3034,3539) THEN '30대' WHEN N.AGE_CD in (4044,4549) THEN '40대' WHEN N.AGE_CD in (5054,5559) THEN '50대' WHEN N.AGE_CD in (6064,6569) THEN '60대' WHEN N.AGE_CD = 7099 THEN '70대' ELSE 'NONE' END;
SELECT sex_cd , age_nm , SUM(amt_n) 매출액 , CONCAT(ROUND(SUM(amt_n) / SUM(amt_o) * 100 - 100, 1), '%') 매출액증감률 FROM (SELECT sex_cd , CASE WHEN age_cd = 0019 THEN '10대 이하' WHEN age_cd IN (2024, 2529) THEN '20대' WHEN age_cd IN (3034, 3539) THEN '30대' WHEN age_cd IN (4044, 4549) THEN '40대' WHEN age_cd IN (5054, 5559) THEN '50대' WHEN age_cd IN (6064, 6569) THEN '60대' WHEN age_cd = 7099 THEN '70대 이상' END age_nm , CASE LEFT(ta_d, 4) WHEN @YEAR THEN amt END amt_n , CASE LEFT(ta_d, 4) WHEN @YEAR-1 THEN amt END amt_o FROM gn_sexage WHERE sex_cd != '법인' AND ta_d >= CONCAT(@YEAR-1, '0101') AND ta_d <= CONCAT(@YEAR , '1231') AND ( @M = 0 OR MONTH(ta_d) = @M ) AND ( (@HD = 0 AND @SGG = 0) OR (@HD = 0 AND @SGG != 0 AND hd_cd BETWEEN @SGG * 1000 AND @SGG * 1000 + 999) OR (@HD != 0 AND @SGG != 0 AND hd_cd = @HD) ) ) a GROUP BY sex_cd, age_nm ;