오라클 Oracle GROUP BY절과 HAVING절

오라클 Oracle GROUP BY절과 HAVING절

 

 

SELECT column(s)

 FROM table(s) | view(s)

 WHERE expression

 GROUP BY column(s)

 HAVING expression

 ORDER BY column(s);

 

부서별 (group by) 급여 (salary) 합(sum) 조회

SELECT e.DEPARTMENT_ID, e.JOB_ID,SUM(e.SALARY)
 FROM EMPLOYEES e
 -- where x
 group BY e.DEPARTMENT_ID, e.JOB_ID
 ORDER BY e.DEPARTMENT_ID ASC

부서별 급여합 => 부서번호, 합, 부서명(departments table)

SELECT a.DEPARTMENT_ID, a.dsum, b.DEPARTMENT_NAME
  FROM (
  	SELECT e.DEPARTMENT_ID, SUM(e.SALARY) AS dsum
		 FROM EMPLOYEES e
		 -- where x
		 group BY e.DEPARTMENT_ID
  )a , DEPARTMENTS b
 WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID
;

 새로 보는 테이블 용도 확인
1. 테이블 용도 파악 => 테이블 이름으로 기본 파악 가능
2. 컬럼 용도 파악 => 컬럼 이름 기본 파악, 어떤 데이터가 저장되는 가?
3. 데이터 파악 => distinct 사용

1. kor_loan_status : 한국대출상태
2. desc

DESC kor_loan_status;

3. select

SELECT COUNT(*)
 FROM KOR_LOAN_STATUS b;
--238 row

SELECT DISTINCT a.PERIOD
 FROM KOR_LOAN_STATUS a;
 ORDER BY a.PERIOD ASC
;

REGION : 지역
카운트 :17개

SELECT COUNT(DISTINCT a.REgion)
 FROM KOR_LOAN_STATUS a
 ORDER BY a.REGION
 ;

 

REGION : 지역
 값 확인 : 시, 도 구분

 SELECT DISTINCT a.REgion
 FROM KOR_LOAN_STATUS a
 ORDER BY a.REGION
 ;

 

 GUBUN : 대출상품구분
 카운트 : 2

 SELECT COUNT(DISTINCT a.GUBUN)
 FROM KOR_LOAN_STATUS a
 ORDER BY a.GUBUN
 ;

GUBUN : 대출상품구분
값 확인 : 기타대출 주택담보대출

 SELECT DISTINCT a.GUBUN
 FROM KOR_LOAN_STATUS a
 ORDER BY a.GUBUN
 ;

 

주어지는 요건 : 월별, 지역별 가계 대출 잔액 조회
월별(period),지역별(region),가계대출 잔액 (loan_jan_amt)
월별(7개),지역별(17개) => 119개

SELECT a.PERIOD, a.REGION, sum(a.LOAN_JAN_AMT) AS tot_jan
 FROM KOR_LOAN_STATUS a
 -- WHERE X
 GROUP BY a.PERIOD, a.REGION
 ORDER BY a.REGION, a.PERIOD
 ;

---

2013년도만 가지고 올려면 where에다 period like '2013%'

코드 두 개 비교 , 결과값은 같으나 실행계획 (DBMS _XPLAN)이 다름 

1) 

SELECT a.PERIOD, a.REGION, sum(a.LOAN_JAN_AMT) AS tot_jan
 FROM KOR_LOAN_STATUS a   -- 1번
 WHERE a.PERIOD LIKE '2013%' -- 2번
 GROUP BY a.PERIOD, a.REGION  -- 3번
 ORDER BY a.REGION, a.PERIOD
 ;

실행계획 (DBMS _XPLAN)

Plan hash value: 2025996661
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    12 |   240 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |                 |    12 |   240 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| KOR_LOAN_STATUS |    39 |   780 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

 

2) 

SELECT a.PERIOD, a.REGION, sum(a.LOAN_JAN_AMT) AS tot_jan
 FROM KOR_LOAN_STATUS a
 GROUP BY a.PERIOD, a.REGION
 HAVING a.PERIOD LIKE '2013%'
 ORDER BY a.REGION, a.PERIOD
 ;

 

실행계획 (DBMS _XPLAN)

Plan hash value: 2025996661
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |    12 |   240 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |                 |    12 |   240 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| KOR_LOAN_STATUS |    39 |   780 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

---

 

요건 : 지역(17개)별  잔액 합 =>2013년 11월 : 201311

SELECT a.REGION, SUM(a.LOAN_JAN_AMT) AS tot_jan
 FROM KOR_LOAN_STATUS a
 WHERE a.PERIOD = '201311'
 GROUP BY  a.REGION
-- having a.period = '201311'
-- having a.region = '서울''
 ;

2013년 11월 지역별 잔약 합 조회
잔액합이 100조 이상 지역 조회 : 1000000(백조)
출력 : 년월 (period), 지역(region),잔액합(sum)

SELECT a.PERIOD,a.REGION, SUM(a.LOAN_JAN_AMT) AS tot_jan
 FROM KOR_LOAN_STATUS a
 WHERE a.PERIOD = '201311'
 GROUP BY a.REGION,a.PERIOD
 ORDER BY a.REGION ASC
;

SELECT a.PERIOD,a.REGION, SUM(a.LOAN_JAN_AMT) AS tot_jan
 FROM KOR_LOAN_STATUS a
 WHERE a.PERIOD = '201311'
 GROUP BY a.REGION,a.PERIOD
 HAVING SUM(a.LOAN_JAN_AMT)
-- HAVING tot_jan > 1000000 =>> 이건 error다 alias는 group by에선 쓸 수 없다
 ORDER BY tot_jan DESC
;

 

연습문제 부서별 사원수 구하고  출력 : 부서번호, 사원수

SELECT e.DEPARTMENT_ID, COUNT(e.EMPLOYEE_ID) AS ecnt
 FROM EMPLOYEES e
 GROUP BY e.DEPARTMENT_ID
 ORDER BY ecnt desc
;

연습문제 부서별 사원수 구하고  사원수가 30명 이상  출력 : 부서번호, 사원수

SELECT e.DEPARTMENT_ID, COUNT(e.EMPLOYEE_ID) AS ecnt
 FROM EMPLOYEES e
 GROUP BY e.DEPARTMENT_ID
 HAVING COUNT(e.EMPLOYEE_ID) > 30
 ORDER BY ecnt desc
;

연습문제 부서별 평균급여 구하고, 평균급여가 10000달러 미만인 부선번호, 평균 급여 출력

SELECT e.DEPARTMENT_ID,  AVG(e.SALARY) AS salavg
FROM employees e
 GROUP BY e.DEPARTMENT_ID
 HAVING ROUND(AVG(e.SALARY)) > 10000
 ORDER BY e.DEPARTMENT_ID ASC
 ;

연습문제 급여별 사원수 조회, 급여별 2000달러대  => 2000~2999 출력

SELECT 2999, TRUNC(2999,-3) FROM dual;

SELECT TRUNC(a.salary, -3)
FROM EMPLOYEES a
;

SELECT TRUNC(e.salary,-3), COUNT(e.EMPLOYEE_ID) AS ecnt
FROM EMPLOYEES e
GROUP BY TRUNC(e.salary,-3)
ORDER BY ecnt DESC
;

연습문제 부서별 최소 급여를 조회, 출력: 부서번호, 최고 급여

SELECT e.DEPARTMENT_ID,   MAX(e.SALARY) AS maxsal
FROM employees e
GROUP BY e.DEPARTMENT_ID, e.JOB_ID
ORDER BY maxsal DESC
;