- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
G-SQL 실습 2 - GROUP
2020.09.30 21:12
실습 2 - GROUP
기초 문제에서는 고려하지 않고 넘어갔지만
모든 직원이 현재 근무 중인 것은 아니다.
"2. 샘플 데이터 베이스 개요"를 보면 퇴사 관련 필드가 없는 것으로 나타나 있다.
따라서 추측으로 퇴사 여부를 파악했고
종료일자가 있는 테이블의 데이터를 살펴보면 '9999-01-01'인 데이터가 있다.
인터넷으로 본 샘플 데이터베이스에 대한 설명을 못 찾았는데
의미상 현재 근무 중인 직원은 급여나 현재 근무 부서, 업무의 종료일자가 없을 것이다.
종료일자가 없다는 의미를 '9999-01-01'로 사용한 것 같다.
다음 SQL문을 실행해 보면 데이터 개수가 일치한다.
SELECT COUNT(*) FROM SALARIES WHERE TO_DATE='9999-01-01';
SELECT COUNT(*) FROM TITLES WHERE TO_DATE='9999-01-01';
SELECT COUNT(*) FROM DEPT_EMP WHERE TO_DATE='9999-01-01';
이 개념을 가지고 다음 문제들을 풀어갈 것이다.
지금까지의 예는 하나의 테이블을 그룹으로 집계 함수(COUNT, AVG, SUM, MIN, MAX)가 사용되었다.
이제 부터는 특정한 그룹을 형성하고 사용하는 방법에 대하여 연습한다.
1. 업무별 직원수를 구하시오
SELECT TITLE, COUNT(*)
FROM TITLES
WHERE TO_DATE='9999-01-01'
GROUP BY TITLE
ORDER BY TITLE;
현재 근무하는 직원(TO_DATE='9999-01-01')에 대하여 업무별(TITLE)별로 그룹화하여 개수를 계산하였다.
연습: 직원들의 평균 급여를 구하시오.
SELECT EMP_NO, AVG(SALARY)
FROM SALARIES
WHERE TO_DATE='9999-01-01'
GROUP BY EMP_NO
2. 남여 직원수를 구하시오
FROM EMPLOYEES
GROUP BY GENDER;
성별(GENDER)이 있는 직원 테이블(EMPLOYEES)에 종료일자가 없다.
따라서 다른 테이블과 조인을 필요로 하는데 아직 연습하지 않아서 위와 같이 SQL문을 만들어야 한다.
연습: 남여 직원수를 구하되 M은 '남', F는 '여'로 표시하여 출력하시오.
FROM EMPLOYEES
GROUP BY GENDER;
다음과 같이 IF 문을 성별(GENDER) 필드에 적용하면 된다.
IF (조건,참일때,거짓일때)
IF문은 DBMS에 따라 다르게 사용되기 때문에 표준으로 사용되는 CASE문을 사용하는 것이 좋을 수도 있다.
3. 부서별 현재 인원수를 구하시오
SELECT DEPT_NO, COUNT(*)
FROM DEPT_EMP
WHERE TO_DATE = '9999-01-01'
GROUP BY DEPT_NO;
직원들의 부서를 나타내는 테이블(DEPT_EMP)을 대상으로
부서 필드(DEPT_NO)를 그룹으로 지정하여 개수를 세면 된다.
다만, 부서명은 부서 테이블(DEPARTMENTS)에 있고 조인을 아직 연습하지 않았다.
연습: 부서별 현재 인원수를 인원수가 많은 부서부터 출력하시오
SELECT DEPT_NO, COUNT(*) CNT
FROM DEPT_EMP
WHERE TO_DATE = '9999-01-01'
GROUP BY DEPT_NO
ORDER BY CNT DESC;
COUNT(*)를 CNT로 명명하고 ORDER BY 에서 CNT를 내림차순(DESC)으로 지정해 준다.
연습: 부서 이동이 많은 직원순으로 리스트를 출력하시오 (퇴직자 포함)
SELECT EMP_NO, COUNT(*) CNT
FROM DEPT_EMP
GROUP BY EMP_NO
HAVING COUNT(*)>1
ORDER BY CNT DESC
4. 부서별 현재 인원수가 15,000명 이상인 부서를 구하시오.
SELECT DEPT_NO, COUNT(*) CNT
FROM DEPT_EMP
WHERE TO_DATE = '9999-01-01'
GROUP BY DEPT_NO
HAVING COUNT(*) >= 15000
ORDER BY CNT DESC;
그룹에 대한 조건은 WHERE이 아닌 HAVING 이라는 것이 문제의 핵심이다.
그리고, ORDER는 SELECT의 필드로 지정했지만
HAVING 은 조건으로 SELECT의 필드가 아닌 COUNT(*)를 사용한다.
연습: 급여가 70,000이상인 직원 리스트를 구하시오.
5. 부서별 현재 인원수가 가장 많은 상위 5개 부서를 구하시오
SELECT DEPT_NO, COUNT(*) CNT
FROM DEPT_EMP
WHERE TO_DATE = '9999-01-01'
GROUP BY DEPT_NO
ORDER BY CNT DESC
LIMIT 5;
선택된 데이터의 개수를 제한하는 LIMIT문을 사용하는 예제이다.
선택된 데이터를 ORDER BY로 정렬을 하고 LIMIT로 원하는 개수만큼 추출한다.
6. 다음 그림과 같이 직원들의 부서 이동 과정을 출력하시오
SELECT EMP_NO, GROUP_CONCAT(DEPT_NO SEPARATOR ' > ' ) PATH
FROM DEPT_EMP
GROUP BY EMP_NO
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC
먼저, 직원들의 부서 이동을 파악하기 위해 개인(EMP_NO) 별로 그룹화 한다.
그리고 부서 배정 회수가 1회 이상이 되어야 부서 이동이 발생하는 것이기 때문에 HAVING절이 필요하다.
마지막으로 그룹화된 내용들을 하나의 문자열로 묶어준다.
GROUP_CONCAT은 그룹으로 지정된 데이터(레코드)들을 지정한 구분자를 이용하여 하나의 문자열로 만든다.
연습: 업무 변경이 많은 직원 명단을 변경 회수가 많은 사람순으로 출력하시오 (퇴직자 포함)
SELECT EMP_NO, GROUP_CONCAT(TITLE SEPARATOR ' > ' ) PATH
FROM TITLES
GROUP BY EMP_NO
HAVING COUNT(*)>1
ORDER BY COUNT(*) DESC;
정렬에 COUNT 함수를 직접 사용해야 한다.
출처: file:///G:/My Web Sites/https___forest71.tistory.com_/forest71.tistory.com/313101.html?category=554995 [SW 개발이 좋은 사람]
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
