9. SQL로 데이터 조회하기 5부 : grouping, ordering

youbing 2024. 12. 5. 14:23

본 내용은 유튜버 쉬운코드의 강의 "데이터베이스"를 참고하여 작성하였습니다.

order by

조회 결과를 특정 attribute(s) 기준으로 정렬하여 가져오고 싶을 때 사용한다.
default 정렬 방식은 오름차순이다.
오름차순 정렬은 ASC, 내림차순 정렬은 DESC로 표기한다.

예제 : 임직원들의 정보를 연봉 순서대로 정렬해서 알고 싶다.

SELECT * FROM employee ORDER BY salary [DESC];

-- 부서별로 묶어서 연봉순으로 보고 싶으면
SELECT * FROM employee ORDER BY dept_id ASC, salary DESC;

ASC로 정렬하면 NULL이 가장 먼저 뜸.

aggregate function

여러 tuple들의 정보를 요약해서 하나의 값으로 추출하는 함수
대표적으로 COUNT, SUM, MAX, MIN, AVG 함수가 있다.
(주로) 관심있는 attribute에 사용된다. e.g) AVG(salary), MAX(birth_date)
NULL 값들은 제외하고 요약 값을 추출한다.

예제 1 : 임직원 수를 알고 싶다.

SELECT COUNT(*) FROM employee;

COUNT는 중복도 세기 때문에 COUNT(position)을 하더라도 동일한 결과가 나옴.
그러나 NULL은 세지 않음.
그래서 테이블의 튜플 수를 세고 싶은 것이라면 COUNT(*)를 추천

예제 2 : 프로젝트 2002에 참여한 임직원 수/최대 연봉/최소 연봉/평균 연봉을 알고 싶다.

SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
  FROM employee E JOIN works_on W ON E.id = W.empl_id
    WHERE W.proj_id = 2002;

group by

예제 2-1 : 각 프로젝트에 참여한 임직원 수/최대 연봉/최소 연봉/평균 연봉을 알고 싶다.

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
  FROM employee E JOIN works_on W ON E.id = W.empl_id
    GROUP BY W.proj_id;

어떤 프로젝트에 따른 결과인지 알기 위해 W.proj_id도 같이 출력 -> GROUP BY에 쓴 속성을 꼭 같이 출력해줘야 함.
WHERE W.proj_id = 2002 대신 GROUP BY W.proj_id로 변경

GROUP BY

관심있는 attribute(s) 기준으로 그룹을 나눠서 그룹별로 aggregate fct을 적용하고 싶을 때 사용
grouping attribute(s) : 그룹을 나누는 기준이 되는 attribute(s)
grouping attribute(s)에 NULL 값이 있을 때는 NULL 값을 가지는 tuple끼리 묶인다.

having

예제 2-2 : 프로젝트 참여 인원이 7명 이상인 프로젝트들에 대해서 각 프로젝트에 참여한 임직원 수/최대 연봉/최소 연봉/평균 연봉을 알고 싶다.

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
  FROM employee E JOIN works_on W ON E.id = W.empl_id
    GROUP BY W.proj_id
    HAVING COUNT(*) >= 7;

GROUP BY와 함께 사용한다.
aggregate fct의 결과값을 바탕으로 그룹을 필터링하고 싶을 때 사용한다.
HAVING절에 명시된 조건을 만족하는 그룹만 결과에 포함된다.

예1 (group by + order by)

예제 1 : 각 부서별 인원수를 인원 수가 많은 순서대로 정렬해서 알고 싶다.

SELECT dept_id, COUNT(*) AS empl_count FROM employee
  GROUP BY dept_id
  ORDER BY empl_count DESC;

예제 1-1 : 각 부서별-성별 인원수를 인원 수가 많은 순서대로 정렬해서 알고 싶다.

SELECT dept_id, sex, COUNT(*) AS empl_count FROM employee
  GROUP BY dept_id, sex
  ORDER BY empl_count DESC;

예2 (group by + having + subquery)

예제 : 회사 전체 평균 연봉보다 평균 연봉이 적은 부서들의 평균 연봉을 알고 싶다.

SELECT dept_id, AVG(salary) AS dept_salary FROM employee
  GROUP BY dept_id
  HAVING dept_salary < (SELECT AVG(salary) FROM employee);

예3 (group by + order by + having + subquery)

예제 1 : 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고 싶다.

SELECT W.proj_id, COUNT(*), ROUND(AVG(E.salary), 0)
  FROM employee E JOIN works_on W ON E.id = W.empl_id
  WHERE E.birth_date between'1990-01-01' AND '1999-12-31'
  GROUP BY W.proj_id;

이 상태에서 proj_id 순으로 정렬하고 싶으면 ORDER BY W.proj_id 추가

예제 1-1 : 프로젝트 참여 인원이 7명 이상인 프로젝트에 한정해서 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고 싶다.

위의 코드에서 HAVING COUNT(*) >= 7 추가하면 90년대생 참여 인원이 7명 이상인 프로젝트 한정이 되기 때문에 안됨. -> WHERE 절에 조건을 추가해야 함.

SELECT W.proj_id, COUNT(*), ROUND(AVG(E.salary), 0)
  FROM employee E JOIN works_on W ON E.id = W.empl_id
  WHERE E.birth_date between'1990-01-01' AND '1999-12-31'
    AND W.proj_id IN (SELECT proj_id FROM works_on GROUP BY proj_id HAVING COUNT(*) >= 7)
  GROUP BY W.proj_id;

select 요약 & 실행 순서

SELECT attribute(s) or aggregate fct(s)  -- 6
  FROM table(s)                          -- 1
  [WHERE condition(s)]                   -- 2
  [GROUP BY group attribute(s)]          -- 3
  [HAVING group condition(s)]            -- 4
  [ORDER BY attribute(s)]                -- 5

select 쿼리에서 각 절(phrase)의 실행 순서는 개념적인 순서이다
select 쿼리의 실제 실행 순서는 각 RDBMS에서 어떻게 구현했는지에 따라 다르다.

참고 사항

1. 오늘 내용은 MySQL 기준입니다. 다른 RDBMS의 SQL 문법은 조금씩 다를 수 있습니다.

2. 오늘 영상은 ordering, grouping 관련하여 대표적으로 중요한 기본기들을 살펴봤습니다. 디테일한 내용들은 추가적으로 공부하시는 것이 필요합니다.