9. SQL로 데이터 조회하기 5

by 본투비곰손 2023. 8. 23. 22:33

728x90

그룹짓기, 집계함수, 정렬하기

ORDER BY

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

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

mysql> SELECT *FROM employee ORDER BY salary;
+----+---------+------------+------+-----------+-----------+---------+
| id | name    | birth_date | sex  | position  | salary    | dept_id |
+----+---------+------------+------+-----------+-----------+---------+
|  3 | JENNY   | 2000-10-12 | F    | DEV_BACK  |  50000000 |    1003 |
| 14 | SAM     | NULL       | M    | DEV_INFRA |  70000000 |    NULL |
| 11 | SUZANNE | 1993-03-23 | F    | PO        |  75000000 |    1005 |
|  9 | HENRY   | 1982-05-20 | M    | HR        |  82000000 |    NULL |
| 13 | JISUNG  | NULL       | M    | PO        |  90000000 |    1005 |
| 15 | SIMON   | 1982-08-04 | M    | PL        |  90000000 |    NULL |
|  1 | MESSI   | 1987-02-01 | M    | DEV_BACK  | 100000000 |    1003 |
|  5 | DINGYO  | 1990-11-05 | M    | CTO       | 120000000 |    NULL |
|  7 | MINA    | 1993-06-17 | F    | DSGN      | 160000000 |    1004 |
| 12 | CURRY   | 1998-01-15 | M    | PLN       | 170000000 |    1004 |
|  2 | JANE    | 1996-05-05 | F    | DSGN      | 180000000 |    1004 |
| 10 | NICOLE  | 1991-03-26 | F    | DEV_FRONT | 180000000 |    1003 |
|  4 | BROWN   | 1996-03-13 | M    | CEO       | 240000000 |    NULL |
|  6 | JULIA   | 1986-12-11 | F    | CFO       | 240000000 |    NULL |
+----+---------+------------+------+-----------+-----------+---------+
14 rows in set (0.02 sec)

내림차순 정렬

mysql> SELECT *FROM employee ORDER BY salary DESC;
+----+---------+------------+------+-----------+-----------+---------+
| id | name    | birth_date | sex  | position  | salary    | dept_id |
+----+---------+------------+------+-----------+-----------+---------+
|  4 | BROWN   | 1996-03-13 | M    | CEO       | 240000000 |    NULL |
|  6 | JULIA   | 1986-12-11 | F    | CFO       | 240000000 |    NULL |
|  2 | JANE    | 1996-05-05 | F    | DSGN      | 180000000 |    1004 |
| 10 | NICOLE  | 1991-03-26 | F    | DEV_FRONT | 180000000 |    1003 |
| 12 | CURRY   | 1998-01-15 | M    | PLN       | 170000000 |    1004 |
|  7 | MINA    | 1993-06-17 | F    | DSGN      | 160000000 |    1004 |
|  5 | DINGYO  | 1990-11-05 | M    | CTO       | 120000000 |    NULL |
|  1 | MESSI   | 1987-02-01 | M    | DEV_BACK  | 100000000 |    1003 |
| 13 | JISUNG  | NULL       | M    | PO        |  90000000 |    1005 |
| 15 | SIMON   | 1982-08-04 | M    | PL        |  90000000 |    NULL |
|  9 | HENRY   | 1982-05-20 | M    | HR        |  82000000 |    NULL |
| 11 | SUZANNE | 1993-03-23 | F    | PO        |  75000000 |    1005 |
| 14 | SAM     | NULL       | M    | DEV_INFRA |  70000000 |    NULL |
|  3 | JENNY   | 2000-10-12 | F    | DEV_BACK  |  50000000 |    1003 |
+----+---------+------------+------+-----------+-----------+---------+
14 rows in set (0.00 sec)

같은 부서 별로 정렬 후 연봉기준 내림차순으로 정렬

mysql> SELECT *FROM employee ORDER BY dept_id ASC, salary DESC;
+----+---------+------------+------+-----------+-----------+---------+
| id | name    | birth_date | sex  | position  | salary    | dept_id |
+----+---------+------------+------+-----------+-----------+---------+
|  4 | BROWN   | 1996-03-13 | M    | CEO       | 240000000 |    NULL |
|  6 | JULIA   | 1986-12-11 | F    | CFO       | 240000000 |    NULL |
|  5 | DINGYO  | 1990-11-05 | M    | CTO       | 120000000 |    NULL |
| 15 | SIMON   | 1982-08-04 | M    | PL        |  90000000 |    NULL |
|  9 | HENRY   | 1982-05-20 | M    | HR        |  82000000 |    NULL |
| 14 | SAM     | NULL       | M    | DEV_INFRA |  70000000 |    NULL |
| 10 | NICOLE  | 1991-03-26 | F    | DEV_FRONT | 180000000 |    1003 |
|  1 | MESSI   | 1987-02-01 | M    | DEV_BACK  | 100000000 |    1003 |
|  3 | JENNY   | 2000-10-12 | F    | DEV_BACK  |  50000000 |    1003 |
|  2 | JANE    | 1996-05-05 | F    | DSGN      | 180000000 |    1004 |
| 12 | CURRY   | 1998-01-15 | M    | PLN       | 170000000 |    1004 |
|  7 | MINA    | 1993-06-17 | F    | DSGN      | 160000000 |    1004 |
| 13 | JISUNG  | NULL       | M    | PO        |  90000000 |    1005 |
| 11 | SUZANNE | 1993-03-23 | F    | PO        |  75000000 |    1005 |
+----+---------+------------+------+-----------+-----------+---------+
14 rows in set (0.00 sec)

aggregate function

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

임직원의 수를 알고 싶다.

mysql> SELECT COUNT(*) FROM employee;
+----------+
| COUNT(*) |
+----------+
|       14 |
+----------+
1 row in set (0.00 sec)

프로젝트 2002에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다.

mysql> SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
    -> FROM works_on w JOIN employee E ON W.empl_id= E.id
    -> WHERE W.proj_id= 2002;
+----------+-------------+-------------+----------------+
| COUNT(*) | MAX(salary) | MIN(salary) | AVG(salary)    |
+----------+-------------+-------------+----------------+
|        4 |   180000000 |    70000000 | 111250000.0000 |
+----------+-------------+-------------+----------------+
1 row in set (0.00 sec)

GROUP BY

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

각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다.

mysql> SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
    -> FROM works_on w JOIN employee E ON W.empl_id= E.id
    -> GROUP BY W.proj_id;
+---------+----------+-------------+-------------+----------------+
| proj_id | COUNT(*) | MAX(salary) | MIN(salary) | AVG(salary)    |
+---------+----------+-------------+-------------+----------------+
|    2001 |        4 |   120000000 |    82000000 |  98000000.0000 |
|    2002 |        4 |   180000000 |    70000000 | 111250000.0000 |
|    2003 |        6 |   240000000 |    50000000 | 173333333.3333 |
+---------+----------+-------------+-------------+----------------+
3 rows in set (0.00 sec)

HAVING

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

프로젝트 참여 인원이 6명 이상인 프로젝트들에 대해서 각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다.

mysql> SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
    -> FROM works_on w JOIN employee E ON W.empl_id= E.id
    -> GROUP BY W.proj_id
    -> HAVING COUNT(*) >=6;
+---------+----------+-------------+-------------+----------------+
| proj_id | COUNT(*) | MAX(salary) | MIN(salary) | AVG(salary)    |
+---------+----------+-------------+-------------+----------------+
|    2003 |        6 |   240000000 |    50000000 | 173333333.3333 |
+---------+----------+-------------+-------------+----------------+
1 row in set (0.00 sec)

예제

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

mysql> SELECT COUNT(*), dept_id
    -> FROM employee
    -> GROUP BY dept_id
    -> ORDER BY COUNT(*) DESC;
+----------+---------+
| COUNT(*) | dept_id |
+----------+---------+
|        6 |    NULL |
|        3 |    1003 |
|        3 |    1004 |
|        2 |    1005 |
+----------+---------+
4 rows in set (0.00 sec)
// 아래와 같이 명칭을 줘서 직관적으로 표현 할 수 있다.
mysql> SELECT dept_id, COUNT(*) AS empl_count
    -> FROM employee
    -> GROUP BY dept_id
    -> ORDER BY empl_count DESC;
+---------+------------+
| dept_id | empl_count |
+---------+------------+
|    NULL |          6 |
|    1003 |          3 |
|    1004 |          3 |
|    1005 |          2 |
+---------+------------+
4 rows in set (0.00 sec)

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

mysql> SELECT dept_id, sex, COUNT(*) AS empl_count
    -> FROM employee
    -> GROUP BY dept_id, sex
    -> ORDER BY empl_count DESC;
+---------+------+------------+
| dept_id | sex  | empl_count |
+---------+------+------------+
|    NULL | M    |          5 |
|    1004 | F    |          2 |
|    1003 | F    |          2 |
|    1003 | M    |          1 |
|    NULL | F    |          1 |
|    1005 | F    |          1 |
|    1004 | M    |          1 |
|    1005 | M    |          1 |
+---------+------+------------+
8 rows in set (0.00 sec)

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

mysql> SELECT dept_id, AVG(salary) FROM employee
    -> GROUP BY dept_id
    -> HAVING AVG(salary)<(
    ->          SELECT AVG(salary) FROM employee // subquery를 사용하여 비교하여 준다.
    -> );
+---------+----------------+
| dept_id | AVG(salary)    |
+---------+----------------+
|    1003 | 110000000.0000 |
|    1005 |  82500000.0000 |
+---------+----------------+
2 rows in set (0.00 sec)

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

mysql> SELECT W.proj_id, COUNT(*), ROUND(AVG(salary),0)
    -> FROM works_on W JOIN employee E ON W.empl_id=E.id
    -> WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
    -> GROUP BY W.proj_id;
+---------+----------+----------------------+
| proj_id | COUNT(*) | ROUND(AVG(salary),0) |
+---------+----------+----------------------+
|    2003 |        4 |            187500000 |
|    2001 |        1 |            120000000 |
|    2002 |        3 |            125000000 |
+---------+----------+----------------------+
3 rows in set (0.01 sec)

mysql> SELECT W.proj_id, COUNT(*), ROUND(AVG(salary),0)
    -> FROM works_on W JOIN employee E ON 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;
+---------+----------+----------------------+
| proj_id | COUNT(*) | ROUND(AVG(salary),0) |
+---------+----------+----------------------+
|    2001 |        1 |            120000000 |
|    2002 |        3 |            125000000 |
|    2003 |        4 |            187500000 |
+---------+----------+----------------------+
3 rows in set (0.00 sec)

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

mysql> SELECT W.proj_id, COUNT(), ROUND(AVG(salary),0) -> FROM works_on W JOIN employee E 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()>=6)
//프로젝트 참여 인원이 6명 이상인 프로젝트를 구하는 subquery
-> GROUP BY W.proj_id
-> ORDER BY W.proj_id;
+---------+----------+----------------------+
| proj_id | COUNT(*) | ROUND(AVG(salary),0) |
+---------+----------+----------------------+
| 2003 | 4 | 187500000 |
+---------+----------+----------------------+
1 row in set (0.00 sec)

728x90

'CS전공 지식 > 4. 데이터베이스 개론 & SQL' 카테고리의 다른 글

11. DBMS에 저장해서 쓰는 함수 2 (0)	2023.08.25
10. DBMS에 저장해서 쓰는 함수 1 (0)	2023.08.24
7. SQL로 데이터 조회하기 (0)	2023.08.21
6. SQL로 데이터 조회하기 2 (0)	2023.08.18
5. SQL로 데이터 조회하기(1) (0)	2023.08.17

본투비곰손

고정 헤더 영역

메뉴 레이어

메뉴 리스트

검색 레이어

검색 영역

상세 컨텐츠

본문 제목

본문