GROUP BY 쿼리 개선하기

집계쿼리에 조인할 테이블이 많은 경우

먼저 집계한 다음에 조인하자!

 

 

기존

SELECT A.prodType, A.ctgryCd1, A.ctgryCd2, A.ctgryCd3
	 , B.code_nm AS prodTypeNm
	 , (SELECT ctgryNm FROM t_info WHERE ctgryId = A.ctgryCd1 AND useYn = 'Y') AS ctgryNm1
	 , (SELECT ctgryNm FROM t_info WHERE ctgryId = A.ctgryCd2 AND useYn = 'Y') AS ctgryNm2
	 , (SELECT ctgryNm FROM t_info WHERE ctgryId = A.ctgryCd3 AND useYn = 'Y') AS ctgryNm3
	 , COUNT(A.seqNo) AS csSum
FROM t_list A INNER JOIN t_code B ON A.prodType = B.`code`
	      INNER JOIN t_info C ON A.cUserId = C.userId
	      LEFT OUTER JOIN t_hp D ON A.hp_Code = D.hp_Code
WHERE 1=1
  AND A.delYn = 'N'
GROUP BY A.prodType, A.ctgryCd1, A.ctgryCd2, A.ctgryCd3
;

 

 

수정

SELECT A.*
	 , B.code_nm AS prodTypeNm
	 , C.ctgryNm AS ctgryNm1
	 , D.ctgryNm AS ctgryNm2
	 , E.ctgryNm AS ctgryNm3
FROM (	
	SELECT prodType, ctgryCd1, ctgryCd2, ctgryCd3, COUNT(seqNo) AS csAggr
	FROM t_list 
	WHERE delYn = 'N'
	GROUP BY prodType, ctgryCd1, ctgryCd2, ctgryCd3
     ) A 
     	 INNER JOIN t_code B ON A.prodType = B.`code`
	 INNER JOIN t_info C ON A.ctgryCd1 = C.ctgryId AND C.useYn = 'Y'
	 INNER JOIN t_info D ON A.ctgryCd2 = D.ctgryId AND D.useYn = 'Y'
	 LEFT  JOIN t_info E ON A.ctgryCd3 = E.ctgryId AND E.useYn = 'Y'
;

 

 

배운점

 

GROUP BY

• GROUP BY에 정의한 내용(컬럼 또는 변형된 컬럼)만 SELECT절에 그대로 사용할 수 있다.
• GROUP BY에 정의하지 않은 컬럼을 SELECT절에서 사용하려면 반드시 집계함수 처리를 해야 한다.
  
  
• GROUP BY에 없는 컬럼을 집계함수 없이 사용하면, MySQL이 아닌 다른 DBMS에서는 실행 자체가 되지 않는다.
  
• MySQL은 결과값을 임의 처리한 SQL이 실행되므로 데이터에 오류가 있어도 감지하지 못하는 경우가 생길 수 있다.
  
  
• MySQL에서도 실행되지 않게 하려면 SQL_MODE를 변경하면 된다.
  

  SELECT @@SQL_MODE;

  • SQL_MODE 값에 'ONLY_FULL_GROUP_BY'를 추가해서 SQL_MODE를 다시 설정하면 GROUP BY에 없는 항목이 집계함수 없이 SELECT절에서 사용되는 것을 막을 수 있다.

    # 현재 설정된 SQL_MODE에 ONLY_FULL_GROUP_BY를 추가해서 설정
    SET SESSION SQL_MODE = CONCAT('ONLY_FULL_GROUP_BY,',@@SQL_MODE);

  • SQL_MODE를 이전으로 되돌리고 싶다면 'SELECT @@SQL_MODE;'를 다시 실행해 얻은 결과에서 'ONLY_FULL_GROUP_BY'를 제거한 값을 SQL_MODE로 재설정하면 된다.

    SET SESSION SQL_MODE;

 

---

ref)

4.09.4 GROUP BY 사용 규칙