Group by시 그룹화 대상이 아닌 컬럼 가지고 오기

관련 SQL 프로그래머스 문제 : 물고기 종류 별 대어 찾기

문제점

fish_type 컬럼으로 Group by 시 fish_type 컬럼 외에는 집계함수 외에는 사용하지 못함

-> ID컬럼으로 인하여 this is incompatible with sql_mode=only_full_group_by 오류 발생

 

§ 해당 오류 쿼리 예시 

select ID, FISH_NAME, MAX(LENGTH)
  from fish_info f
  join fish_name_info fn on f.fish_type = fn.fish_type
  group by f.fish_type

---

원인 : only_full_group_by 설정이 ON되어있기 때문

 

□ only_full_group_by를 ON을 설정하는 이유

  : 그룹화되지 않은 컬럼을 선택할 수 없으므로, 데이터의 일관성과 정확성을 보장

 

□ only_full_group_by가 OFF이면 생기는 문제  - ( 참고1 ) 

그룹화 되지 않은 컬럼은 그룹화된 튜플 중 임의의 값을 추출한다. 
order by로 원하는 컬럼값을 추출하려고 하더라도, Result가 임의의 값으로 설정된 뒤에 order by되므로 order by로도 원하는 값을 추출할 수 없다.

 

---

해결 

(1) 매칭되는 컬럼이 하나일 경우 : 서브쿼리 활용 where 절 이하에 그룹핑된 쿼리를 조회

(2) 매칭되는 컬럼이 두개 이상일 경우 : JOIN하여 원하는 값만 출력 (where 절에 단일 컬럼만 사용할 경우)

        - 아래 코드는 매칭시킬 컬럼이 2개 이상이므로 where절로는 원하는 값을 출력할 수 없다.

select f.id id, fi.fish_name fish_name, f.length length
from fish_info f
join  (select fish_type, MAX(LENGTH) as length
                              from fish_info
                          group by fish_type) tmp
on f.fish_type = tmp.fish_type and f.length = tmp.length
join fish_name_info fi on f.fish_type = fi.fish_type

 

(2) window function 이용

SELECT f.id, fi.fish_name, f.length
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY fish_type ORDER BY length DESC) as rn
    FROM fish_info
) f
JOIN fish_name_info fi ON f.fish_type = fi.fish_type
WHERE f.rn = 1
order by ID;

 

---

§ ( 참고1 )Mysql workbench 8.0 reference 발췌 (https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html)

If ONLY_FULL_GROUP_BY is disabled,~중략~. 
In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.
Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. 

 

 

§(참고) anyValue('Group by 와 무관한 컬럼') : only_full_group_by 설정이 ON일시, 사용할 수 있다. 하지만 그룹화된 값 내에서 임의의 값을 선택하는 방식이므로 데이터 일관성을 해친다. 해당 컬럼에 그룹별 값이 동일하다고 보장이 될 경우에만 사용할 것