데이터베이스 질문과 답변
/* 연령별 쿼리 */ select k.admi_cd, k.adm_nm, substring(etl_ymd,1,6) as months , sum(k.m_under_10+k.f_under_10) as under_10 , sum(k.m_under_20+k.f_under_20) as under_20 , sum(k.m_under_30+k.f_under_30) as under_30 , sum(k.m_under_40+k.f_under_40) as under_40 , sum(k.m_under_50+k.f_under_50) as under_50 , sum(k.m_under_60+k.f_under_60) as under_60 , sum(k.m_under_70+k.f_under_70) as under_70 , sum(k.m_over_70+k.f_over_70) as under_70 from (select a.admi_cd, b.sido_nm, sgg_nm, adm_nm, etl_ymd, timezn_cd, M00 as m_under_10, M10 + M15 as m_under_20, M20 + M25 as m_under_30, M30 + M35 as m_under_40, M40 + M45 as m_under_50, M50 + M55 as m_under_60, M60 + M65 as m_under_70, M70 as m_over_70 , F00 as f_under_10, F10 + F15 as f_under_20, F20 + F25 as f_under_30, F30 + F35 as f_under_40, F40 + F45 as f_under_50, F50 + F55 as f_under_60, F60 + F65 as f_under_70, F70 as f_over_70 , tot_cnt from kn.ky_2019_0112 a, kn.admi_mst b where a.admi_cd = b.admi_cd and a.etl_ymd between 20191201 and 20191231 ) k group by k.adm_nm order by adm_nm, timezn_cd;
해당 부분에 대하여 admi_mst 테이블은 약 400건, ky_2019_0112 테이블은 약 4000만건 입니다.
쿼리가 나올 생각을 하지 않는데... 방법이 있을지요?
by 마농
[2020.11.20 14:49:10]
etl_ymd 컬럼으로 파티션되거나 인덱싱 되어 있는지?
etl_ymd 컬럼의 자료형이 뭔지? 문자인데 숫자로 조건 잘 못 준 건 아닌지? (조건에 따옴표 추가)
조인 후 집계하지 말고 집계 후 조인하세요.
그룹바이 구문은 오류날 것 같은데? 이 쿼리 맞는건지?
by 와니와플
[2020.11.20 18:14:13]
쿼리는 잘 돌아가긴 했습니다. 날짜가 일별로 되어 있어서 etl_ymd = 20191201 로 하루만 집계했을 때 (약 100만건) 나왔습니다.
by DarkBee
[2020.11.20 17:32:09]
etl_ymd 컬럼이 숫자컬럼이 맞나요?
by 와니와플
[2020.11.20 17:43:14]
/*---------------------------------------------
-- 오브젝트명: kn.ky_2019_0112
-- 생성일자 : 2020-11-20 13:04:13.0
-- 상태: VALID
---------------------------------------------*/
CREATE TABLE `kn`.`ky_2019_0112`
(
`id_no` bigint(20) DEFAULT NULL,
`x_coord` int(11) DEFAULT NULL,
`y_coord` int(11) DEFAULT NULL,
`timezn_cd` int(11) DEFAULT NULL,
`M00` decimal(6,2) DEFAULT NULL,
`M10` decimal(6,2) DEFAULT NULL,
`M15` decimal(6,2) DEFAULT NULL,
`M20` decimal(6,2) DEFAULT NULL,
`M25` decimal(6,2) DEFAULT NULL,
`M30` decimal(6,2) DEFAULT NULL,
`M35` decimal(6,2) DEFAULT NULL,
`M40` decimal(6,2) DEFAULT NULL,
`M45` decimal(6,2) DEFAULT NULL,
`M50` decimal(6,2) DEFAULT NULL,
`M55` decimal(6,2) DEFAULT NULL,
`M60` decimal(6,2) DEFAULT NULL,
`M65` decimal(6,2) DEFAULT NULL,
`M70` decimal(6,2) DEFAULT NULL,
`F00` decimal(6,2) DEFAULT NULL,
`F10` decimal(6,2) DEFAULT NULL,
`F15` decimal(6,2) DEFAULT NULL,
`F20` decimal(6,2) DEFAULT NULL,
`F25` decimal(6,2) DEFAULT NULL,
`F30` decimal(6,2) DEFAULT NULL,
`F35` decimal(6,2) DEFAULT NULL,
`F40` decimal(6,2) DEFAULT NULL,
`F45` decimal(6,2) DEFAULT NULL,
`F50` decimal(6,2) DEFAULT NULL,
`F55` decimal(6,2) DEFAULT NULL,
`F60` decimal(6,2) DEFAULT NULL,
`F65` decimal(6,2) DEFAULT NULL,
`F70` decimal(6,2) DEFAULT NULL,
`tot_cnt` decimal(6,2) DEFAULT NULL,
`admi_cd` bigint(20) DEFAULT NULL,
`etl_ymd` int(11) DEFAULT NULL
)
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic
PARTITION BY RANGE (`etl_ymd`)
(
PARTITION `c0` VALUES LESS THAN (20190105) ENGINE=InnoDB,
PARTITION `c1` VALUES LESS THAN (20190110) ENGINE=InnoDB,
PARTITION `c2` VALUES LESS THAN (20190115) ENGINE=InnoDB,
PARTITION `c3` VALUES LESS THAN (20190120) ENGINE=InnoDB,
PARTITION `c4` VALUES LESS THAN (20190125) ENGINE=InnoDB,
PARTITION `c5` VALUES LESS THAN (20190205) ENGINE=InnoDB,
PARTITION `c6` VALUES LESS THAN (20190210) ENGINE=InnoDB,
PARTITION `c7` VALUES LESS THAN (20190215) ENGINE=InnoDB,
PARTITION `c8` VALUES LESS THAN (20190220) ENGINE=InnoDB,
PARTITION `c9` VALUES LESS THAN (20190225) ENGINE=InnoDB,
PARTITION `c10` VALUES LESS THAN (20190305) ENGINE=InnoDB,
PARTITION `c11` VALUES LESS THAN (20190310) ENGINE=InnoDB,
PARTITION `c12` VALUES LESS THAN (20190315) ENGINE=InnoDB,
PARTITION `c13` VALUES LESS THAN (20190320) ENGINE=InnoDB,
PARTITION `c14` VALUES LESS THAN (20190325) ENGINE=InnoDB,
PARTITION `c15` VALUES LESS THAN (20190405) ENGINE=InnoDB,
PARTITION `c16` VALUES LESS THAN (20190410) ENGINE=InnoDB,
PARTITION `c17` VALUES LESS THAN (20190415) ENGINE=InnoDB,
PARTITION `c18` VALUES LESS THAN (20190420) ENGINE=InnoDB,
PARTITION `c19` VALUES LESS THAN (20190425) ENGINE=InnoDB,
PARTITION `c20` VALUES LESS THAN (20190505) ENGINE=InnoDB,
PARTITION `c21` VALUES LESS THAN (20190510) ENGINE=InnoDB,
PARTITION `c22` VALUES LESS THAN (20190515) ENGINE=InnoDB,
PARTITION `c23` VALUES LESS THAN (20190520) ENGINE=InnoDB,
PARTITION `c24` VALUES LESS THAN (20190525) ENGINE=InnoDB,
PARTITION `c25` VALUES LESS THAN (20190605) ENGINE=InnoDB,
PARTITION `c26` VALUES LESS THAN (20190610) ENGINE=InnoDB,
PARTITION `c27` VALUES LESS THAN (20190615) ENGINE=InnoDB,
PARTITION `c28` VALUES LESS THAN (20190620) ENGINE=InnoDB,
PARTITION `c29` VALUES LESS THAN (20190625) ENGINE=InnoDB,
PARTITION `c30` VALUES LESS THAN (20190705) ENGINE=InnoDB,
PARTITION `c31` VALUES LESS THAN (20190710) ENGINE=InnoDB,
PARTITION `c32` VALUES LESS THAN (20190715) ENGINE=InnoDB,
PARTITION `c33` VALUES LESS THAN (20190720) ENGINE=InnoDB,
PARTITION `c34` VALUES LESS THAN (20190725) ENGINE=InnoDB,
PARTITION `c35` VALUES LESS THAN (20190805) ENGINE=InnoDB,
PARTITION `c36` VALUES LESS THAN (20190810) ENGINE=InnoDB,
PARTITION `c37` VALUES LESS THAN (20190815) ENGINE=InnoDB,
PARTITION `c38` VALUES LESS THAN (20190820) ENGINE=InnoDB,
PARTITION `c39` VALUES LESS THAN (20190825) ENGINE=InnoDB,
PARTITION `c40` VALUES LESS THAN (20190905) ENGINE=InnoDB,
PARTITION `c41` VALUES LESS THAN (20190910) ENGINE=InnoDB,
PARTITION `c42` VALUES LESS THAN (20190915) ENGINE=InnoDB,
PARTITION `c43` VALUES LESS THAN (20190920) ENGINE=InnoDB,
PARTITION `c44` VALUES LESS THAN (20190925) ENGINE=InnoDB,
PARTITION `c45` VALUES LESS THAN (20191005) ENGINE=InnoDB,
PARTITION `c46` VALUES LESS THAN (20191010) ENGINE=InnoDB,
PARTITION `c47` VALUES LESS THAN (20191015) ENGINE=InnoDB,
PARTITION `c48` VALUES LESS THAN (20191020) ENGINE=InnoDB,
PARTITION `c49` VALUES LESS THAN (20191025) ENGINE=InnoDB,
PARTITION `c50` VALUES LESS THAN (20191105) ENGINE=InnoDB,
PARTITION `c51` VALUES LESS THAN (20191110) ENGINE=InnoDB,
PARTITION `c52` VALUES LESS THAN (20191115) ENGINE=InnoDB,
PARTITION `c53` VALUES LESS THAN (20191120) ENGINE=InnoDB,
PARTITION `c54` VALUES LESS THAN (20191125) ENGINE=InnoDB,
PARTITION `c55` VALUES LESS THAN (20191205) ENGINE=InnoDB,
PARTITION `c56` VALUES LESS THAN (20191210) ENGINE=InnoDB,
PARTITION `c57` VALUES LESS THAN (20191215) ENGINE=InnoDB,
PARTITION `c58` VALUES LESS THAN (20191220) ENGINE=InnoDB,
PARTITION `c59` VALUES LESS THAN (20191225) ENGINE=InnoDB,
PARTITION `c60` VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB
);
현재 이렇게 설정 했습니다.
by 와니와플
[2020.11.20 17:56:18]
인덱스는 etl_ymd, id_no, timezn_cd 이렇게 걸려 있습니다.
by 마농
[2020.11.23 14:46:00]
SELECT a.admi_cd
, b.adm_nm
, a.under_10
, a.under_20
, a.under_30
, a.under_40
, a.under_50
, a.under_60
, a.under_70
, a.over_70
FROM (SELECT admi_cd
, SUM(M00 + F00 ) under_10
, SUM(M10 + M15 + F10 + F15) under_20
, SUM(M20 + M25 + F20 + F25) under_30
, SUM(M30 + M35 + F30 + F35) under_40
, SUM(M40 + M45 + F40 + F45) under_50
, SUM(M50 + M55 + F50 + F55) under_60
, SUM(M60 + M65 + F60 + F65) under_70
, SUM(M70 + F70 ) over_70
FROM ky_2019_0112
WHERE etl_ymd BETWEEN 20191201 AND 20191231
GROUP BY admi_cd
) a
INNER JOIN admi_mst b
ON a.admi_cd = b.admi_cd
;
) 버튼을 클릭하여 작성 하시면 됩니다.