SQL 5일차

20230320 SQL

SELECT upper(last_name)
FROM employees;

■ 그룹함수

• 여러 행당 하나의 결과를 반환하는 함수
• SUM, AVG, MEDIAN, VARIANCE, STDDEV, MAX, MIN, COUNT
• 그룹함수에 입력하는 값이 숫자항만 입력해야 하는 함수: SUM, AVG, MEDIAN, VARIANCE, STDDEV
• 그룹함수에 입력하는 값이 모든 타입이 가능한 함수: MAX, MIN, COUNT
• 그룹함수는 NULL을 포함하지 않습니다.
• 대표값: 자료의 특징을 대표하는 값(평균 AVG, 중앙값 MEDIAN, 최빈값 COUNT)

◆ COUNT : 행의 수를 구하는 함수

SELECT count(*) - null을 포함함
FROM employees;

SELECT count(employee_id) - null을 포함하지 않음
FROM employees;

SELECT count(commission_pct) - null을 포함하지 않음
FROM emloyees;

SELECT count(department_id) - null을 포함하지 않음
FROM employees;

SELECT count(distinct department_id) - 중복을 제거한 건수
FROM employees;

SELECT count(*) - null을 포함한 행 수
FROM employees
WHERE department_id = 50;

SELECT count(commission_pct) - null을 포함하지 않은 행 수
FROM employees
WHERE department_id = 50;

◆ SUM : 합
SELECT sum(salary)
FROM employees;

SELECT sum(commission_pct)
FROM employees;

SELECT sum(salary)
FROM employees
WHERE department_id = 50;

◆ AVG : 평균
SELECT avg(salary) => 6461.831.....
FROM employees;

SELECT round(avg(salary)) => 6462
FROM employees;

SELECT avg(commission_pct) - null을 포함하지 않음
FROM employees;

<예> ===================================
10, 20, NULL
SUM : 10 + 20
AVG : (10 + 20) / 2

10, 20, 0
SUM : 10 + 20 + 0
AVG : (10 + 20 + 0) / 3
================ =======================

1)
SELECT avg(commission_pct) - null을 포함하지 않음
FROM employees;

SELECT count(commission_pct) - null을 포함하지 않음(결과값 : 35)
FROM employees;

SELECT 7.8 / 35 => avg(commission_pct) 값과 동일함
FROM dual;

2)
SELECT avg(nvl(commission_pct,0)) - null을 포함한 평균
FROM employees;

SELECT 7.8 / 107 => avg(nvl(commission_pct,0)) 값과 동일함
FROM dual;

◆ MEDIAN : 중앙값
20, 30, 10, 50, 60, 90, 70, 40, 80
1) 자료를 크기순으로(오름차순 정렬) 나열한 후 가운데 값이 중앙값
10, 20, 30, 40, 50, 60, 70, 80, 90
2) 자료가 홀수일 경우

• 중앙값이 있는 위치 : (관측값 수 + 1) / 2 = 5
  3) 자료가 짝수일 경우
• 중앙값이 있는 위치
  (관측값 수) / 2 = 4
  (관측값 수) / 2 + 1 = 5 의 중간으로,
  10, 20, 30, 40, 50, 60, 70, 80, 90 의 중앙값은

SELECT (40 + 50) / 2 FROM dual; 의 결과값인 45임

SELECT avg(salary), median(salary) => 6461.831..... , 6200
FROM employees;

◆ VARIANCE : 분산

• 내가 가진 자료(데이터)가 평균값을 중심으로 퍼져있는 평균적인 거리
  1) 평균
  2) 편차 제곱 합의 평균
  (관측값 – 관측값 평균)² + (관측값 – 관측값 평균)² / (관측값의 수 – 1) (자유도)
  => 관측값의 수 : COUNT로 구해야 함

SELECT variance(salary)
FROM employees;

◆ STDDEV : 표준편차

• 분산의 제곱근을 수행한 값(분산에 루트씌움)

SELECT stddev(salary), variance(salary), avg(salary)
=> 3909. 579,,,, 15284813.669,..., 6461.831.....
FROM employees;

◆ MAX : 최대값

SELECT max(salary), max(last_name), max(hire_date) => 24000, Zlotkey, 08/04/21
FROM employees;

SELECT min(salary), min(last_name), min(hire_date) => 2100, Abel, 01/01/13
FROM employees;

◆ MIN : 최소값

SELECT min(salary), min(last_name), min(hire_date)
FROM employees;

◆ 범위 : MAX - MIN

SELECT max(salary) - min(salary) range => 21900
FROM employees;

◆ GROUP BY : 테이블의 행을 작은 그룹(군집)으로 나눌 수 있는 절
(예) 부서별 총액 급여를 구해주세요.

=================== ==============

(잘못된 예시)

SELECT distinct department_id => 10부터 110까지 10단위로 有
FROM employees
ORDER BY 1;

SELECT sum(salary)
FROM employees
WHERE department_id = 10; => 그룹 하나당 salary를 구하긴 번거로움
... ... ...

(옳은 예시)

SELECT department_id, sum(salary)
FROM employees
GROUP BY department_id
ORDER BY 1;
============ ========================

SELECT department_id, sum(salary)
FROM employees
GROUP BY department_id;

• F10 실행 결과 확인시
  ● 오라클 버전 중 9I R1 버전은 GROUP BY가 SORT GROUP BY로 실행되어 GROUP BY 절에서 명시된 컬럼을 기준으로 정렬된 결과로 출력됨
  ● 오라클 버전 중 9I R2 버전은 GROUP BY가 HASH GROUP BY로 실행되어 GROUP BY 절에서 명시된 컬럼을 기준으로 정렬이 수행되지 않음(ORDER BY 절 사용하기)

■ 그룹함수 사용시 주의사항
1) null을 포함하지 않음 - count(*)만 포함함
2) SELECT 절에 그룹함수에 포함되지 않은 개별 컬럼은 하나도 빠짐없이 GROUP BY 절에 명시해야 함

(잘못된 예시)

SELECT department_id, sum(salary) => 오류: not a single-group group function
FROM employees;

(옳은 예시)

SELECT department_id, job_id, manager_id, sum(salary)
FROM employees
GROUP BY department_id, job_id, manager_id
ORDER BY 1;

3) GROUP BY 절에는 열 별칭, 위치표기법 사용할 수 없음 – SELECT 절보다 먼저 돌아감
=> order by 절은 둘 다 가능 – 제일 늦게 돌아감

4) WHERE 절에서는 그룹함수의 결과를 제한할 수 없음
◆ HAVING 절: 그룹함수의 결과를 제한하는 절

=========================================

(잘못된 예시)
SELECT department_id, sum(salary) => 오류: group function is not allowed here
FROM employees where 절이 제일 먼저 돌아감(전체? 그룹? 모름)
WHERE sum(salary) > 10000
GROUP BY department_id;

(옳은 예시)

SELECT department_id, sum(salary)
FROM employees
GROUP BY department_id => GROUP, HAVING, SELECT절 순서로 돌아감
HAVING sum(salary) > 10000; => HAVING 절 제일 밑에 쓰기
============== =======================

SELECT department_id, count(*) => 부서별 전체 인원수
FROM employees
GROUP BY department_id;

SELECT department_id, sum(salary) => 부서별 5인 이상인 부서의 급여 총액
FROM employees
GROUP BY department_id
HAVING count(*) >= 5;

SELECT department_id, sum(salary)
FROM employees
GROUP BY department_id
HAVING avg(salary) >= 10000;

=> SELECT 절에 있는 그룹함수만 HAVING 절에 포함되는 건 아님
모든 그룹함수가 HAVING 절에 포함됨

SELECT department_id, sum(salary)
FROM employees
WHERE last_name like '%i%'
GROUP BY department_id
HAVING sum(salary) >= 10000
ORDER BY 1;

=> group by, having 절끼리를 제외하고는 절 순서 지켜야 함
=> FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY 순으로 돌아감

SELECT department_id, sum(salary), count()
FROM employees
WHERE last_name like '%i%'
GROUP BY department_id
HAVING avg(salary) >= 5000
AND count() >= 5
ORDER BY 1;

(잘못된 예시)
SELECT department_id, sum(salary), count()
FROM employees
WHERE last_name like '%i%'
GROUP BY department_id
HAVING department_id IN (30, 40, 50, 60, 70) => NO!!! 그룹함수만 포함시켜야 함
AND avg(salary) >= 5000
AND count() >= 5
ORDER BY 1;

①
SELECT department_id
FROM employees
GROUP BY department_id;

②
SELECT distinct department_id
FROM employees; => 두 개 동일함

5) 그룹함수는 두 번까지만 중첩할 수 있음

• 그룹함수 두 번 중첩시 개별함수로 사용불가, 오류발생
• 해결방법 : 서브쿼리를 이용해 해결함

SELECT max(avg(salary))
FROM employees
GROUP BY department_id;

SELECT department_id, max(avg(salary)) => 오류: group function is not allowed here
FROM employees
GROUP BY department_id;

[문제29] 2008년도에 입사한 사원들의 job_id별 인원수를 구하고 인원수가 많은 순으로 출력하세요.

SELECT job_id, count(*)
FROM employees
WHERE hire_date BETWEEN '2008/01/01' AND '2009/01/01'
GROUP BY job_id
ORDER BY count(*) desc;

=> BETWEEN 절 쓸 경우 시간정보를 꼭 써야함!
SELECT job_id, count(*)
FROM employees
WHERE hire_date BETWEEN to_date('2008/01/01', ‘yyyy/mm/dd’)
AND to_date(‘2008/12/31 23:59:59’, ‘yyyy/mm/dd hh24:mi:ss’)
GROUP BY job_id
ORDER BY count(*) desc;

SELECT job_id, count(*)
FROM employees

• WHERE to_char(hire_date, 'yyyy') = 2008
• WHERE substr(hire_date, 1, 2) = ‘08’ => 세 개 절대 안 됨
• WHERE hire_date like ‘08%’
  GROUP BY job_id
  ORDER BY count(*) desc;

=> hire_date 컬럼은 날짜타입의 컬럼이기 때문에 위와 같은 쿼리문장 사용시 성능상 문제가 발생할 수 있음

• date type 컬럼이 to_char, substr, like을 사용하면 내부적으로 to_char 형변환 함수가 수행되다 보니 성능상 문제가 발생함

SELECT job_id, count(*)
FROM employees
WHERE hire_date >= to_date('2008/01/01', 'yyyy-mm-dd')
AND hire_date < to_date('2009/01/01', 'yyyy-mm-dd')
GROUP BY job_id
ORDER BY 2 desc;

=> hire_date를 ‘to_date’와 비교하는 이유: 날짜는 날짜 타입과 비교하기!

[문제30] 연도별 입사한 사원수를 출력해주세요.

SELECT to_char(hire_date, 'yyyy') YEARS, count(*)
FROM employees
GROUP BY to_char(hire_date, 'yyyy')
ORDER BY 1;

[문제31] 월별 입사한 사원수를 출력해주세요.

SELECT to_char(hire_date, 'MM') MONTH, count(*)
FROM employees
GROUP BY to_char(hire_date, 'MM')
ORDER BY 1;

SELECT to_char(hire_date, 'FMMM') MONTH, count(8)
FROM employees
GROUP BY to_char(hire_date, 'FMMM') =>선행되는 ‘0’ 제거, 문자 형태임
ORDER BY 1; =>숫자 순서대로 나열되지 않음

▽ 문제 해결을 위해

SELECT to_number(to_char(hire_date, 'FMMM')) MONTH, count(*)
FROM employees
GROUP BY to_number(to_char(hire_date, 'FMMM'))
ORDER BY 1;

[문제32] 연도별 입사 인원수를 아래 화면과 같이 출력해주세요.

TOTAL	2001년	2002년	2003년
107	1	7	6

SELECT count(*) as TOTAL,
COUNT(decode(to_char(hire_date, 'yyyy'), ‘2001’, '01')) as "2001년",
COUNT(decode(to_char(hire_date, 'yyyy'), ‘2002’, '02')) as "2002년",
COUNT(decode(to_char(hire_date, 'yyyy'), ‘2003’, '03')) as "2003년"
FROM employees;

SELECT count(*) as TOTAL,
COUNT(case when to_char(hire_date, 'yyyy') = '2001' THEN '01' END) as "2001년",
COUNT(case when to_char(hire_date, 'yyyy') = '2002' THEN '02' END) as "2002년",
COUNT(case when to_char(hire_date, 'yyyy') = '2003' THEN '03' END) as "2003년"
FROM employees;

=> COUNT 사용 시 참값(‘01’, ‘02’, ‘03’)은 다른 문자나 숫자로 바꾸어도 무방함

SELECT count(*) as TOTAL,
SUM(case when to_char(hire_date, 'yyyy') = '2001' THEN '1' END) as "2001년",
SUM(case when to_char(hire_date, 'yyyy') = '2002' THEN '1' END) as "2002년",
SUM(case when to_char(hire_date, 'yyyy') = '2003' THEN '1' END) as "2003년"
FROM employees;

=> SUM 사용 시 합계이므로 참값은 ‘1’로 표현해야 함
■ JOIN(조인)

• 두 개 이상의 테이블에서 원하는 데이터를 가져오는 방법

SELECT employee_id, last_name, department_id
FROM employees;

SELECT department_id, department_name
FROM departments;

=> 사원번호, 성, 부서아이디, 부서명 다 출력하고 싶음(두 개 붙여!)
low 107건 나와야 함(가장 많은 행 수 기준)

SELECT employee_id, last_name, department_name
FROM employees , departments;
=> 오류 : 결과 나오지만 행의 수가 너무나 많음. 왜?

=> cartesian product 카테시안 곱

• 조인 조건이 생략되었을 경우
• 조인 조건이 잘못 만들어진 경우
• 첫 번째 테이블의 행수와 두 번째 테이블의 행수가 곱해짐

■ equi join, inner join, simple join, 등가조인

• 조인 키값이 일치하는 데이터만 추출하는 조인

SELECT employee_id, last_name, department_name
FROM employees , departments
WHERE department_id = department_id; => 오류: column ambiguously defined

SELECT employees.employee_id, employees.last_name, departments.department_name
FROM employees , departments
WHERE employees.department_id = departments.department_id;
                   M쪽 집합                            1쪽 집합

=> M쪽 집합(many, 107개)행의 수보다 작거나 같은 결과 건수가 나와야 함
=> 접두에 테이블명과 마침표(.)를 적어주기
=> 결과 건수 106개 나옴(department_id가 일치하는 데이터이므로 null일 경우 누락)
=> 테이블 별칭 사용하여 join 수행하면 코드가 심플해지니 메모리 사용 줆(일관적 사용 必)

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e , departments d
WHERE e.department_id = d.department_id;

● 쿼리문장 추가하여 직접 연결할 수 없는 테이블 연결고리 테이블 찾아 연결하기!

SELECT employee_id, last_name, department_id
FROM employees;

SELECT location_id, city
FROM locations;

=> 두 개 직접 연결할 수 있는 고리가 없음, 거쳐가야 함

SELECT department_id, location_id
FROM departments;

=> 징검다리 역할할 고리를 찾는다!!

SELECT e.employee_id, e.last_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id => 조인 조건 술어
AND d.location_id = l.location_id;

=> 이렇게!!!
=> 조인 조건 술어는 연결해야 하는 테이블 개수인 “N – 1”개임

● 오라클 내 테이블 옵션 중 ‘model’ 들어가면 ERD (or table description 달라고 하기!) 통해서 연결고리 확인 가능