| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| 8 | 9 | 10 | 11 | 12 | 13 | 14 |
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
| 29 | 30 |
- 인터럽트
- 쉬운코드
- 네트워크
- 백엔드
- 코딩테스트 [ ALL IN ONE ]
- concurrency control
- 시스템프로그래밍
- SQL
- 데이터베이스
- SDK
- vite
- 운영체제와 정보기술의 원리
- 개발남노씨
- 시그널 핸들러
- 반효경
- CPU 스케줄링
- BreadcrumbsComputer-Networking_A-Top-Down-Approach
- Git
- Extendable hashing
- 쉬운 코드
- B tree 데이터삽입
- 온디바이스AI
- 코딩애플
- 갤럭시 S24
- 커널 동기화
- 김영한
- 운영체제
- 트랜잭션
- recoverability
- 프로세스 주소 공간
- Today
- Total
티끌모아 태산
SQL 데이터 조회: group by, aggregate function, order by 본문
이번 시간에도 배운 내용을 정리해 보도록하겠습니다.
ORDER BY
조회 결과를 특정 attribute(s) 기준으로 정렬하여 가져오고 싶을 때 사용합니다. 이때, default 정렬 방식은 오름차순입니다.
- 오름차순 정렬: ASC
- 내림차순 정렬: DESC
- 임직원들의 정보를 연봉 순서대로 정렬해서 알고 싶다.
SELECT * FROM employee ORDER BY salary DESC;우선, * 통해 선택된 튜플의 모든 attribute 정보를 가져온 다음 연봉 기준으로 내림차순 해줍니다.
❗️만약 여기서 추가로 부서별로 연봉을 내림차순으로 정렬하고 싶을 땐 어떻게 해야할까요? ORDER BY는 하나의 attribute뿐만 아니라 여러개의 attribute를 정렬할 수 있습니다. 그리고 앞에 써준 attribute가 우선순위가 가장 높습니다.
SELECT * FROM employee ORDER BY dept_id ASC, salary DESC;위 코드는 dept_id를 오름차순으로 먼저 정렬을 한 후, 연봉을 내림차순으로 정렬을 해 줍니다. dept_id가 integer 이므로 ORDER BY를 통해 '부서별'로 정렬을 진행할 수 있었다.
aggregate function
여러 튜플들의 정보를 요약해서 하나의 값으로 추출하는 함수입니다. 대표적으로 COUNT, SUM, MIN, AVG 함수들이 있습니다. 그리고 주로 관심있는 attribute에 사용됩니다. 예를 들어, AVG(salary), MAX(birth_date) 등이 있습니다. 마지막으로 여러 튜플들의 정보를 요약해서 하나의 값으로 추출할 때, NULL 값들은 제외하고 요약 값을 추출합니다.
- 임직원 수를 알고 싶다.
SELECT COUNT(*) FROM employee;이때, * 는 튜플의 수를 의미하고 결국, COUNT(*)는 튜플의 수를 세라는 의미 입니다.
COUNT()에서 괄호 안에는 카운트 하고싶은 attribute를 넣어줘도 됩니다. 예를 들어, employee table에서 직군의 수를 알고 싶을 때 아래와 같이 코드를 작성하면 됩니다. ❗️그리고 여기서 하나의 특징을 알 수 있는데요, COUNT는 중복을 포함해서 카운트합니다. 다시 말해 결과값으로 16은 튜플의 수와 같은데, 같은 포지션에 속한 사람들도 포함해서 카운트 했다는 의미가 됩니다.
SELECT COUNT(position) FROM employee;
아까 말씀드렸듯이, aggregate function은 NULL 값을 제외하고 결과 값을 하나로 요약해서 표현하기 때문에 아래와 같이 작성하면 결과값이 14가 나옵니다.
SELECT COUNT(dept_id) FROM employee;
- 프로젝트 2002에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다.
SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE W.proj = 2002;우선, 프로젝트 2002에 참여한 임직원들과 관련이 있기 때문에 works_on 테이블을 참조해야합니다. 그리고 연봉 정보는 employee테이블에 있기 때문에 둘의 테이블을 JOIN 해줘야한다. 그리고 WHERE W.proj_id = 2002;를 해주고 구하려고 한 것을 aggregate function으로 구해주면 됩니다. ❗️JOIN은 두 개 이상의 테이블들에 있는 데이터를 한번에 조회하는 것.
GROUP BY
관심있는 attribute(s)기준으로 그룹을 나눠서 그룹별로 aggregate function을 적용하고 싶을 때 사용합니다. grouping attribute(s)는 그룹을 나누는 기준이 되는 attribute(s)이고 grouping attribute(s)에 NULL 값이 있을 때는 NULL 값을 가지는 튜플끼리 묶인다.
앞선 예제를 GROUP화 해서 결과 테이블로 나타내봅시다. ❗️2002가 아니라 각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다.
SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E on empl_id = E.id
GROUP BY W.proj_id;
위 코드에서 추가로 SELECT절에 W.proj_id 를 더 해주는 이유는 proj_id 별로 데이터를 뽑고싶기 때문이다. ⭐️그래서 반드시 GROUP BY에 적어준 attribute는 SELECT 절에 함께 적어줘야합니다.
HAVING
HAVING은 GROUP BY와 함께 사용합니다. aggregate function의 결과값을 바탕으로 그룹을 필터링하고 싶을 때 사용합니다. 즉, HAVING절에 명시된 조건을 만족하는 그룹만 결과에 포함시킵니다.
앞선 예제를 참조해서 - 프로젝트 참여 인원이 7명 이상인 프로젝트들에 대해서 각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고싶다.
SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E on empl_id = E.id
GROUP BY W.proj_id;
HAVING COUNT(*) >= 7;- 각 부서별 인원수를 인원 수가 많은 순서대로 정렬해서 알고 싶다.
SELECT dept_id, COUNT(*) AS empl_count FROM employee
GROUP BY dept_id
ORDER BY empl_count DESC;먼저, 각 부서별 인원수를 알아야하기 때문에 GROUP BY를 해줘야합니다. 그리고 그 결과를 기준으로 인원 수가 많은 순서대로 정렬하면 됩니다. ORDER BY 사용. 그리고 명시적으로 나타내기 위해 COUNT(*)을 empl_count로 별칭을 주었다.
- 각 부서별, 각 성별 인원수를 인원 수가 많은 순서대로 정렬해서 알고 싶다.
SELECT dept_id, sex, COUNT(*) AS empl_count FROM employee
GROUP BY dept_id, sex
ORDER BY empl_count DESC;
위 예제는 부서와 성별로 그룹화 한 것이다.
- 회사 전체 평균 연봉보다 평균 연봉이 적은 부서들의 평균 연봉을 알고 싶다.
- 회사 전체 평균 연봉 구하기 -> SELECT AVG(salary) FROM employee
- 각 부서의 평균 연봉 구하기 -> SELECT dept_id, AVG(salary) FROM employee GROUP BY dept_id
- 그 회사 전체 평균 연봉보다 적은 부서들구하기 -> 비교를 위해 subquery 사용
SELECT dept_id, AVG(salary) FROM employee
GROUP BY dept_id
HAVING AVG(salary) < (
SELECT AVG(salary) FROM employee)
);
- 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고 싶다. 이때, 평균 연봉에서 소수점 이하는 제외한다.
- 프로젝트별로 그룹핑하기
- 그리고 프로젝트별로 참여한 사람들 중에서 90년대생들을 구하기
- 이들의 평균 연봉 구하기
SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id;소수점 이하는 제외하고 싶기때문에, 반올림으로 ROUND 사용!
- 추가로 여기 proj_id 별로 오름차순 정렬을 하고 싶을 경우
SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id;
ORDER BY W.proj_id;- 추가로 프로젝트 참여 인원이 7명 이상인 프로젝트에 한정해서 각 프로제트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고 싶다.
SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id;
HAVING COUNT(*) >= 7;
ORDER BY W.proj_id;프로젝트에 참여한 인원이 7명 이상인 프로젝트로 필터링을 해서 결과를 보고싶다. ❗️그래서 만약 위 코드처럼 HAVING을 넣어준다면 틀렸습니다.
이때, COUNT(*)는 프로젝트에 참여한 90년대생들 수이지 프로젝트에 참여한 모든 수를 나타내는 것이 아닙니다! 그래서 HAVING이 아니라 WHERE절을 수정해야합니다. 따라서 프로젝트 참여 인원이 7명 이상인 프로젝트를 구해야합니다.
SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E 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(*) >= 7)
GROUP BY W.proj_id;
ORDER BY W.proj_id;
⭐️따라서 여기서 중요한 점은 문제의 조건에 따라 WHERE절에 조건을 명시해 줄 것인지 아니면 HAVING절에 조건을 명시해 줄것인지 잘 판단해야 한다는 것입니다.
'CS 지식 > 데이터베이스' 카테고리의 다른 글
| Relational Database Design(2) - functional dependency(함수 종속) (0) | 2023.11.07 |
|---|---|
| SQL 데이터 조회: JOIN (1) | 2023.09.17 |
| SQL로 데이터 조회하기, NULL (0) | 2023.09.16 |
