여러 테이블에서 가져온 데이터를 한 ROW로 묶으려면 어떻게 해야 할까
여러 테이블에서 가져온 데이터를 한 ROW로 묶으려면 어떻게 해야 할까?
오늘 분석한 쿼리를 정리해보자. 물론 각색한 버전이라 정확한 예시는 아님.
해당 쿼리 미션 : 센터별 업무 처리 건수를 조회해오기. 업무 현황은 처리 / 미처리로 나뉜다.
문제
1) 처리 / 미처리는 각각 참조하는 테이블이 다르며 조건도 다르다.
2) 특정 날짜에 처리 / 미처리된 건수를 센터별로 조회해와야 한다. 즉 '센터' 컬럼을 기준으로 SUM이 필요함.
3) '센터' 컬럼 기준으로 그룹화해 처리/ 미처리를 한번에 보여줘야 한다. 센터는 총 n00개...
이 쿼리는 이런 식으로 문제를 해결했다.
1) 처리 / 미처리 각각 SELECT문을 만든다. 이때, WHERE 절에 검색 날짜 조건을 넣고, '센터'를 기준으로 GROUP BY해 처리/미처리 건수를 SUM 해온다. 처리 건수를 구하는 SELECT문에서는 미처리 관련 컬럼을 모두 0으로, 미처리 건수를 구하는 SELECT 문은 처리 관련 컬럼을 모두 0으로 함.
처리 SELECT 문
| 센터명 | 쓸기 처리건수 | 닦기 처리건수 | 쓸기 미처리건수 | 닦기 미처리건수 |
| 초록빛센터 | 10 | 5 | 0 | 0 |
| 파란빛센터 | 7 | 8 | 0 | 0 |
미처리 SELECT 결과
| 센터명 | 쓸기 처리건수 | 닦기 처리건수 | 쓸기 미처리건수 | 닦기 미처리건수 |
| 초록빛센터 | 0 | 0 | 3 | 5 |
| 파란빛센터 | 0 | 0 | 10 | 8 |
2) 각 SELECT문을 UNION ALL 한다.
| 센터명 | 쓸기 처리건수 | 닦기 처리건수 | 쓸기 미처리건수 | 닦기 미처리건수 |
| 초록빛센터 | 10 | 5 | 0 | 0 |
| 파란빛센터 | 7 | 8 | 0 | 0 |
| 초록빛센터 | 0 | 0 | 3 | 5 |
| 파란빛센터 | 0 | 0 | 10 | 8 |
3) UNION ALL 한 select문을 inline view로 처리한 뒤, 다시 '센터' 컬럼으로 GROUP BY 한다. == > 여기서 0인 결과가 나오는 컬럼들은 어디로 사라지나? 바로 SUM()으로 처리. 처리 테이블에서 가져온 컬럼은 값을 갖고 있고, 미처리 테이블에서 갖고 오는 해당 컬럼은 0으로 처리하면 됨!
| 센터명 | 쓸기 처리건수 | 닦기 처리건수 | 쓸기 미처리건수 | 닦기 미처리건수 |
| 초록빛센터 | 10 | 5 | 3 | 5 |
| 파란빛센터 | 7 | 8 | 10 | 8 |
4) 필요한 테이블과 join해 마지막 SELECT를 한다.
이 쿼리를 분석하며 GROUP BY에 대해 정확히 공부할 필요성을 느껴서 아래와 같이 정리.
GROUP BY
역할 : 컬럼을 기준으로 데이터를 GROUP으로 묶는다.
예시 테이블 people
| name | gender | age |
| amy | f | 10 |
| tom | m | 15 |
| ann | f | 10 |
| paul | m | 32 |
select gender from people group by gender;
결과
| GENDER | 설명 |
| f | gender가 f인 로우들이 집합. |
| m | gender가 m인 로우들이 집합. |
보이는 것은 f, m 로우 2개이지만, 이 안에 여러 행들이 숨어있는 것을 알고 있어야 함.
여기서 AVG, SUM, COUNT 같은 집계 함수를 사용하면?
select gender, count(*) from people by gender;
| GENDER | COUNT(*) |
| f | 2 |
| m | 2 |
각 그룹에서 count를 해온다.
GROUP BY 컬럼이 있는 이상, 전체 테이블이 아닌 각 그룹에서 집계 함수를 사용한다.
즉 Group by 는 먼저 그룹을 만들고, 그 다음 집계함수를 실행한다는 의미.
참고! SQL 실행 순서
FROM → WHERE → GROUP BY → HAVING → ORDER BY
그렇다면 GROUP BY 컬럼이 여러 개 있는 경우는 어떨까?
select gender, age, count(*) from people group by gender, age;
이 같은 경우,
먼저
- gender 가 f이면서 age가 10인 그룹
- gender가 m이면서 age가 15인 그룹
- gender가 m이면서 age가 32인 그룹
...
이런 그룹들이 만들어지고, count(*) 함수를 실행하는 듯.
어쨌든 결과!
| gender | age | count(*) |
| f | 10 | 2 |
| m | 15 | 1 |
| m | 32 | 1 |
또한 GROUP BY 를 테스트해보며 드는 의문들을 해결했다!
Q. 집계함수 외의 컬럼은 어떤 기준으로 가져오는 건가?
→ 테스트해보니, 해당 그룹의 첫 번째 행을 가져오는 듯하다. 그런데 이건 큰 의미가 없다고 함. 왜냐하면 group by 를 쓰는 쿼리는 보통 SELECT에 집계함수와 GROUP BY 컬럼만 들어가기 때문! 그 외의 컬럼은 의미가 없어서 잘 SELECT하지 않음.
Q. GROUP BY 컬럼값이 NULL인 것도 같이 세나?
-> 그렇다. 테스트해보니 GROUP BY 컬럼이 NULL일 때도 세더라!
Q. GROUP BY 컬럼1, IFNULL(컬럼2, ‘대체값’)과 GROUP BY 컬럼1, 컬럼2의 차이는 뭘까..
-> GROUP BY 컬럼1, 컬럼2 => 컬럼1과 컬럼2가 NULL일 때도 그룹화함
GROUP BY 컬럼1, IFNULL(컬럼2, '대체값') => 컬럼2가 NULL일 때는 고려하지 않음. 컬럼2는 더이상 NULL이 없으니까. 대신에 대체값 그룹으로 들어감.
참고 사이트 : https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns