✅ Order by
: 조회 결과를 특정 attribute 기준으로 정렬하여 가져오고 싶을 때 사용한다.
: default 정렬 방식은 오름차순이다.
: 오름차순 정렬은 ASC 로 표기하고, 내림차순 정렬은 DESC 로 표기한다.
바로 예를 살펴보자.
statement 는 아래와 같다.
- 임직원들의 정보를 연봉 순서대로 정렬해서 알고 싶다.
임직원의 정보를 "연봉" 순서대로 알고 싶으므로, salary 속성을 오름차순으로 정렬해주면 된다.
SELECT *
FROM EMPLOYEE
ORDER BY salary;
기본 정렬 방식은 ASC 으로 오름차순을 따른다.
그럼, 내림차순으로 정렬하고 싶을 떄는 어떻게 해야 할까? DESC 키워드를 써주면 된다.
SELECT *
FROM EMPLOYEE
ORDER BY salary DESC;
지금은 EMPLOYEE 전체 테이블에서 연봉만을 기준으로 정렬을 했다.
이제는 부서별로 묶은다음에 부서별로 연봉을 정렬하고 싶다면 어떻게 해야할까?
SELECT *
FROM EMPLOYEE
ORDER BY dept_id ASC, salary DESC;
먼저, dept_id 를 기준으로 정렬을 한 뒤에, 같은 dept_id 내에서는 salary 를 기준으로 내림차순 정렬을 하면 된다.
즉, ORDER BY 뒤에는 먼저 오는 속성을 기준으로 정렬을 하는 셈이다.
만약, salary 를 기준으로 먼저 내림차순 정렬을 하고, 부서를 정렬하면 아래와 같은 결과가 나온다.
SELECT *
FROM EMPLOYEE
ORDER BY salary DESC, dept_id;
✅ Aggregate function - COUNT(), SUM(), MAX(), MIN(), AVG()
: 여러 tuple들의 정보를 요약해서 하나의 값으로 추출하는 함수이다.(=집계함수)
: 대표적으로 COUNT, SUM, MAX, MIN, AVG 함수가 있다.
: (주로) 관심있는 attribute 에 사용된다. (ex) AVG(salary), MAX(birth_date)
: NULL 값들을 제외하고 요약 값을 추출한다.
이런 statement가 있다고 해보자.
- 임직원의 수를 알고 싶다.
SELECT COUNT(*) AS '임직원 수' FROM EMPLOYEE;
여기서 *(asterisk)는 튜플의 개수를 의미한다. 그래서 튜플들의 전체 개수를 세라는 뜻이 된다.
즉, 여기서 임직원의 수를 알고 싶으니까, *(asterisk) 말고도 'name'이 와도 되고 'id' 가 와도 되는 셈이다.
어차피 행의 수는 어떤 열이와도 똑같기 때문이다.
즉, COUNT() 안의 attribute 이름이 전달됐을 때, 그 attribute의 값들에 중복이 있다고 할지라도, 중복을 포함해서 count 해준다.
그럼, COUNT()에 이번에는 dept_id 를 넣어보자.
SELECT COUNT(dept_id) FROM EMPLOYEE;
그럼, 15가 아니라 14가 나온다. 이유가 뭘까?
EMPLOYEE 테이블을 보면 id가 16인 'JULIA'의 부서가 NULL 값인 것을 확인할 수 있다.
즉, COUNT 함수는 NULL 값이 있는 행은 제외하고 count 하는 것을 알 수 있다.
따라서, COUNT() 를 쓸 때 튜플 수를 확인하고 싶은 의도라면, *(asterisk)를 써주는 것이 좋다.
다른 statement는 아래와 같다.
- 프로젝트 2002에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다.
우선 우리가 관심있는 테이블은 어떤 직원이 어떤 프로젝트에 참여했는지 알 수 있는 WORKS_ON 테이블과 연봉 정보가 있는 EMPLOYEE 테이블 이다.
그럼, 먼저 프로젝트 2002 에 참여한 임직원들이 누구인지 한 번 뽑아보자.
SELECT *
FROM WORKS_ON W JOIN EMPLOYEE E ON W.empl_id = E.id
WHERE W.proj_id = 2002;
총 5명의 직원이 프로젝트 2002에 참여한 것을 확인할 수 있다. 이는 COUNT() 집계함수를 통해 추출할 수 있을 것이다.
그럼, 이 테이블을 기준으로 직원들의 최대연봉, 최소연봉, 평균연봉을 뽑으려면 어떻게 해야할까?
직관적으로 MAX() , MIN() , AVG() 함수를 사용하면 될 것이다.
SELECT COUNT(*) AS '프로젝트 2002에 참여한 임직원 수',
MAX(salary) AS '최대연봉', MIN(salary) AS '최소연봉', AVG(salary) AS '평균연봉'
FROM WORKS_ON W JOIN EMPLOYEE E ON W.empl_id = E.id
WHERE W.proj_id = 2002;
✅ GROUP BY
: 관심있는 attribute 기준으로 그룹을 나눠서 그룹별로 aggregate function을 적용하고 싶을 때 사용한다.
: grouping attribute → 그룹을 나누는 기준이 되는 attribute
: grouping attribute에 NULL 값이 있을 때는 NULL 값을 가지는 tuple 들끼리 묶인다.
statement는 아래와 같다.
- 각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균연봉을 알고 싶다.
아까와는 다르게 각각의 특정 프로젝트에 한정해서 통계치를 뽑고 싶은 statement이다.
이럴 때, 우리는 Group By 를 사용할 수 있다.
SELECT W.proj_id,COUNT(*) AS '해당 프로젝트에 참여한 임직원 수',
MAX(salary) AS '최대연봉', MIN(salary) AS '최소연봉', AVG(salary) AS '평균연봉'
FROM WORKS_ON W JOIN EMPLOYEE E ON W.empl_id = E.id
GROUP BY proj_id;
현재 WORKS_ON 테이블과 EMPLOYEE 테이블이 조인을 했고, JOIN된 결과를 기준으로 proj_id 를 가지고, GROUPING 을 하는 것이다. 그 후 그룹핑을 한 그룹별로, 각각의 통계를 뽑아내면 된다.
→ GROUP BY 에 적은 attribute는 반드시 SELECT 절에 적어줘야 그 의도가 분명해진다.
✅ HAVING
: GROUP BY 와 함께 사용하는 키워드이다.
: aggregation function의 결과값을 바탕으로 그룹을 필터링하고 싶을 때 사용한다.
: HAVING 절에 명시된 조건을 만족하는 그룹만 결과에 포함된다.
statement는 아래와 같다.
- 프로젝트 참여 인원이 6명 이상인 프로젝트들에 대해서 각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균연봉을 알고 싶다.
statement를 보면, 아까의 GROUPING 한 결과테이블을 기준으로 조건을 걸어서 그 조건에 해당하는 것만 추출하고 싶다는 의미이다.
즉, 그룹핑한 결과를 기준으로 또 조건을 걸고 싶을 때, 바로 HAVING 을 사용하면 된다.
SELECT W.proj_id,COUNT(*) AS count,
MAX(salary) AS '최대연봉', MIN(salary) AS '최소연봉', AVG(salary) AS '평균연봉'
FROM WORKS_ON W JOIN EMPLOYEE E ON W.empl_id = E.id
GROUP BY proj_id
HAVING count >= 6;
✅ 여러가지 예제
ex1)
- 각 부서별 인원수를 인원 수가 많은 순서대로 정렬해서 알고 싶다.
EMPLOYEE 테이블에는 dept_id 가 있으니까 dept_id 별로 그룹핑을 해주고, 각 부서별로 인원수가 많은 순서대로 정렬해야 하니까 그 수를 기준으로 내림차순을 하면 될 것 같다.
SELECT dept_id, COUNT(dept_id) AS count
FROM EMPLOYEE
GROUP BY dept_id
ORDER BY count DESC;
아직 부서에 배정받지 않은 직원은 제외하고, 즉 부서가 NULL 값인 직원은 세고 싶지 않다면 아래와 같은 쿼리를 작성할 수 있다.
SELECT dept_id, COUNT(dept_id) as count
FROM EMPLOYEE
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY count DESC;
지금은 EMPLOYEE 테이블을 기준으로 부서별로 나눴는데, 이번에는 DEPARTMENT 테이블을 기준으로 부서별로 직원이 얼마나 속해있는지 보려면 LEFT JOIN을 사용해 표현할 수 있다.
SELECT D.dept_id,D.name,COUNT(E.id) as employee_count
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.dept_id = E.dept_id
GROUP BY D.dept_id
ORDER BY employee_count DESC;
ex2)
- 각 부서별 - 성별 인원수를 인원 수가 많은 순서대로 정렬해서 알고 싶다.
EMPLOYEE 테이블을 기준으로 쿼리를 짜면 아래와 같다.
SELECT dept_id,sex,COUNT(dept_id) AS count
FROM EMPLOYEE
GROUP BY dept_id, sex
ORDER BY dept_id,count DESC;
부서를 그룹핑하고 또 성별을 기준으로('F', 'M') 그룹화해서 세분화한 테이블이다.
이번에도 DEPARTMENT 테이블을 기준으로 표현하면 아래와 같다.
SELECT D.dept_id,D.name,E.sex,COUNT(E.id) as employee_count
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.dept_id = E.dept_id
GROUP BY D.dept_id,E.sex
ORDER BY D.dept_id,employee_count DESC;
현재 legal 부서에는 어떠한 직원도 속해있지 않기 때문에, 성별에도 NULL 이 나온 것을 확인할 수 있다.
ex3)
- 회사 전체 평균 연봉과 그 연봉보다 평균 연봉이 적은 부서들의 평균 연봉을 알고 싶다.
일단, 회사 전체의 평균 연봉을 알아야 하기 때문에 아래처럼, 회사 전체의 평균 연봉을 구해준다.
(SELECT AVG(salary) FROM EMPLOYEE)
그리고 이 전체직원의 평균연봉보다 적은 평균연봉을 가져야 하는데, 부서별로의 평균연봉이므로 아래와 같이 쿼리를 작성해준다.
GROUP BY d.dept_id
HAVING AVG(e.salary) < (SELECT AVG(salary) FROM EMPLOYEE)
EMPLOYEE 테이블과 DEPARTMENT 테이블을 같이 결합시켜주되(JOIN), 전체회사의 평균연봉도 보여줘야 하므로, SELECT 절에 별도로 또 다른 SELECT 구문을 추가해 전체 직원의 평균연봉을 보여준다.
SELECT
d.dept_id,d.name,
AVG(e.salary) as department_average_salary,
(SELECT AVG(salary) FROM EMPLOYEE) as company_average_salary
FROM DEPARTMENT d
JOIN EMPLOYEE e ON d.dept_id = e.dept_id
GROUP BY d.dept_id
HAVING AVG(e.salary) < (SELECT AVG(salary) FROM EMPLOYEE)
ex4)
- 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고 싶다.
우선 프로젝트별로 참여한 직원이 누구인지 알 수 있는 WORKS_ON 테이블과 EMPLOYEE 테이블을 직원 id 를 기준으로 JOIN 시켜준다.
조인시켜준 테이블에서 직원의 생년월일이 90년대인 조건을 WHERE 절에 걸어준다.
그럼 이제 90년대생인 직원들이 쭈루룩 나올 것이다.
그리고 GROUP BY를 통해서 프로젝트별로 직원을 나누고, 각 부서마다 직원이 몇명인지 세주고 이들의 평균연봉도 계산해 준다.
SELECT w.proj_id,COUNT(*) AS count, AVG(e.salary)
FROM EMPLOYEE e JOIN WORKS_ON w on w.empl_id = e.id
GROUP BY w.proj_id
HAVING e.birth_date BETWEEN '1990-01-01' and '1999-12-31';(X) → 여기서 HAVING 절에서 e.birth_date를 못 읽는다. proj_id 별로 그룹핑했고 각 프로젝트별로 몇명이 속하는지는 알 수 있어도 각 직원들의 birth_date 는 HAVING을 통해 찾을 수 없다. 잘못된 쿼리이다.
SELECT w.proj_id, COUNT(*) AS count,ROUND(AVG(e.salary),0)
FROM EMPLOYEE e JOIN WORKS_ON w on w.empl_id = e.id
WHERE e.birth_date BETWEEN '1990-01-01' and '1999-12-31'
GROUP BY w.proj_id;(O) → GROUP BY 로 테이블을 만들기 전에 WHERE 조건으로 필터링을 해줘야 한다.
여기서 배울 점은 쿼리의 순서를 명심해야 한다는 점이다.
FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
→ WHERE 절에서는 FROM절에서 읽어온 테이블에서 조건에 맞는 결과만 갖도록 데이터를 간추린다.
→ GROUP BY 절에서는 WHERE 조건으로 간추린 데이터를 선택한 칼럼으로 GROUPING 작업을 한 결과를 갖는다. GROUP BY 절을 사용하게 되면 해당 칼럼으로 그룹(집계)함수를 사용할 수 있다.
→ HAVING 절은 GROUP BY된 이 후 사용되는 조건 절이다. 똑같이 조건을 걸 수 있는 WHERE절과는 조금 다르게 써야한다.
WHERE 절에 있는 내용을 HAVING절에서 사용할 수 있다. 하지만...상황에 따라 WHERE 절에서 먼저 조건을 걸어줘야 할 때가 있고, HAVING절에서 일반 조건들을 다루게 쿼리 실행 순서에 의해 퍼포먼스가 많이 떨어지게 된다.
(※ 데이터 양이 늘어날수록 퍼포먼스에 대한 차이가 커질 수 있다.)
→ SELECT 절은 여러 조건들을 처리한 후 남은 데이터에서 어떤 열을 출력해줄지 선택하는 단계이다.
→ ORDER BY 절은 마지막으로 어떤 열까지 출력할지 정했다면 행의 순서를 어떻게 보여줄지 정렬해주는 절
이런 순서에 고려하여 쿼리를 작성해야 한다. 별칭 AS 나 여러가지 함수를 쓸 때도 순서를 고려해야 잘 동작한다.
.
..
...
그럼 다음 예시에서는 어떻게 해야 할까?
- 프로젝트에 참여한 인원이 5명 이하인 프로젝트에 한정해서 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고 싶다.
이전 statement와 달라진 점은 단지 프로젝트에 참여한 인원이 5명 이하인 조건이라고 해석하고 저 조건에서 추가된 조건을 HAVING 절에 작성하면 되는 셈일까?
SELECT w.proj_id, COUNT(*) AS count,ROUND(AVG(e.salary),0)
FROM EMPLOYEE e JOIN WORKS_ON w on w.empl_id = e.id
WHERE e.birth_date BETWEEN '1990-01-01' and '1999-12-31'
GROUP BY w.proj_id
HAVING count <= 5;(X) → 많은 사람들이 실수하는 중요한 부분이다.
여기서 HAVING 절에 단순히 count가 5명 이하로 조건을 걸어버리면 된다고 생각할 수 있다.
하지만, 지금 HAVING 절에 있는 count 는 그룹핑을 한 뒤에, 각 그룹에 대해서 카운트를 한 값인 것이다. 즉, 여기서 count 의 의미는 각 특정 프로젝트에 대해 90년대생의 인원수를 뜻한다.
statement 에서는 "각 프로젝트 참여인원이 5명이하인 프로젝트에 한정해서" 인데, 지금 HAVING 절을 쓰게 되면, 전체에서 각 프로젝트 별 참여 인원이 5명이하인 프로젝트가 아니라 각 프로젝트 별로 90년대생 직원의 참여인원이 5명이하인 조건을 필터링 하게 되는 것이다.
SELECT w.proj_id, COUNT(*) AS count,ROUND(AVG(e.salary),0)
FROM EMPLOYEE e JOIN WORKS_ON w on w.empl_id = e.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(*) <= 5
)
GROUP BY w.proj_id;(O) → IN을 사용해서 더 엄격하게 쿼리를 걸어줬다.
EMPLOYEE 테이블에서 1990년대생 직원들이 참여하는 프로젝트를 선택하되, IN 절을 살펴보자.
IN 절에서 WORK_ON 테이블을 조회하여 프로젝트에 참여한 총 직원 수가 5명 이하인 프로젝트를 필터링하는 로직을 거친다.
그리고 그 쿼리의 결과에 있는 프로젝트에 1990년대생
결과적으로 이 쿼리는 1990년이 참여하고 있으면서 전체 참여 직원 수가 5명 이하인 프로젝트를 반환한다.
즉, WORK_ON 테이블을 보면, 5명이하의 직원이 참여한 프로젝트는 애초에 2002번 프로젝트 밖에 없고, 이 프로젝트에 참여한 1990년대생의 수가 3명이라는 소리이며, 이들의 평균 연봉이 306666667 이라는 뜻이다.
지금까지 이상으로 5장부터 9장까지 SELECT 로 조회하기에 대해 알아봤다!
SELECT attribute(s) or aggregate function(s)
FROM table(s)
WHERE condition(s)
GROUP BY group attribute(s)
HAVING group condition(s)
ORDER BY attribute(s);참고자료
- 유튜브 쉬운코드
'컴퓨터 공부 > 📗 CS' 카테고리의 다른 글
| [데이터베이스] 11장 - stored procedure + 프로시저의 동작 방식과 특징, stored function과의 차이 (0) | 2024.01.15 |
|---|---|
| [데이터베이스] 10장 - SQL 에서 stored function 이란? + 언제 사용하면 좋을지에 대해 (2) | 2024.01.12 |
| [데이터베이스] 8장 - SQL 로 데이터 조회하기! JOIN (4) | 2024.01.09 |
| [데이터베이스] 7장 - SQL 로 데이터 조회하기! NULL 의 의미와 three-valued logic (0) | 2024.01.09 |
| [데이터베이스] 6장 - SQL 로 데이터 조회하기! SubQuery, IN, EXISTS, ANY, ALL (4) | 2024.01.05 |