데이터가 많은 쿼리 추출 포인트 문의 0 6 182

by 와니와플 [SQL Query] #대용량데이터 #쿼리튜닝 [2020.11.20 13:24:07]


/* 연령별 쿼리  */
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입