251022 TIL

Null 관련 함수

nullif(exp1, exp2)

• 두 표현식(인수)을 비교해서 같으면 null을 리턴하고 같지 않으면 exp1을 리턴하는 함수

PL/SQL에서 IF문

IF exp1 = exp2 THEN
        return null;
ELSE
        return exp1;
END IF;

SELECT 
    employee_id,
    length(last_name),
    length(first_name),
    nullif(length(last_name), length(first_name))
FROM hr.employees;

lnnvl

• Logical Negation of NVL(NVL의 논리적 부정)
• 조건절 FALSE 또는 NULL을 조회하는 함수
• 아래의 경우 department_id is null 조건이 없으면 부서가 null인 employee에 대해 누락 발생

SELECT *
FROM hr.employees
WHERE department_id <> 50
OR department_id is null;

• lnnvl 함수를 사용하면 위 쿼리와 동일한 결과

SELECT *
FROM hr.employees
WHERE LNNVL(department_id = 50);

조건 제어문

• SQL에서는 IF문을 사용해서 조건 제어문을 수행할 수 없음
• decode 함수, case 표현식

PL/SQL에서 IF문

IF 기준값 = 비교값1 THEN
        참값1
ELSE IF 기준값 = 비교값2 THEN
        참값2
ELSE IF 기준값 = 비교값3 THEN
        참값3
ELSE
        기본값
END IF;

decode 함수

• decode(기준값, 비교값1, 참값1, 비교값2, 참값2, 비교값3, 참값3, 기본값)
• decode 함수는 기준값과 비교값은 내부적으로 같다 비교연산자만 사용
• decode 함수 안에 decode 함수 중첩 가능

SELECT
    employee_id,
    salary,
    job_id,
    hire_date,
    decode(job_id, 'IT_PROG', salary * 1.1,
                    'ST_CLERK', salary * 1.2,
                    'SA_REP', salary * 1.3,
                    salary) revised_salary,
    decode(job_id, 'IT_PROG', decode(to_char(hire_date, 'yyyy'), '2005', salary * 1.15, salary * 1.1), salary) revised_salary2
FROM hr.employees;

case 표현식

• 기준값과 비교값에 대해서 모든 비교 연산자를 사용 가능(9i)

case 기준값
        when 비교값1 then 참값1
        when 비교값2 then 참값2
        when 비교값3 then 참값3
        else 기본값
end

• 기준값 (=, >, >=, <, <=, <>, !=, ^=, in, between, and, like) 비교값, 논리적(not, and, or)

SELECT
    employee_id,
    salary,
    job_id,
    hire_date,
    case
        when job_id = 'IT_PROG' then salary * 1.1
        when job_id = 'ST_CLERK' then salary * 1.2
        when job_id = 'SA_REP' then salary * 1.3
        else salary 
    end revised_salary
FROM hr.employees;

• 같다 비교 연산은 아래처럼도 가능

SELECT
    employee_id,
    salary,
    job_id,
    hire_date,
    case job_id
        when 'IT_PROG' then salary * 1.1
        when 'ST_CLERK' then salary * 1.2
        when 'SA_REP' then salary * 1.3
        else salary 
    end revised_salary
FROM hr.employees;

• 중첩 가능

SELECT
    employee_id,
    salary,
    job_id,
    hire_date,
    case
        when job_id = 'IT_PROG' then case when to_char(hire_date, 'yyyy') = '2005' then salary * 1.15 else salary * 1.1 end
        when job_id = 'ST_CLERK' then salary * 1.2
        when job_id = 'SA_REP' then salary * 1.3
        else salary 
    end revised_salary
FROM hr.employees;

문제

-- [문제22] 사원들의 급여를 표기준 이용해서 출력해주세요.
/*
         ~4999      : low
     5000~9999      : medium
    10000~19999     : good
    20000~          : excellent
*/

SELECT
    employee_id,
    salary,
    case
        when salary < 5000 then 'low'
        when salary < 10000 then 'medium'
        when salary < 20000 then 'good'
        else 'excellent'
    end grade
FROM hr.employees;

decode 함수, case 표현식에서 null check 방법

• decode 함수는 null keyword 이용해서 null check
• case 표현식은 is null 연산자를 이용해서 null check

SELECT
        employee_id,
    salary,
    commission_pct,
    salary * 12 + salary * 12 * nvl(commission_pct, 0) ann_sal_1,
    nvl2(commission_pct, salary * 12 + salary * 12 * commission_pct, salary * 12) ann_sal2,
    decode(commission_pct, null, salary * 12, salary * 12 + salary * 12 * commission_pct) ann_sal3,
    case
        when commission_pct is null then salary * 12
        else salary * 12 + salary * 12 * commission_pct
    end ann_sal4
FROM hr.employees;

그룹 함수

• 여러 행 당 하나의 결과를 반환하는 함수
• sum, avg, median, variance, staddev, max, min, count
• 그룹 함수에 입력값으로 수치형만 입력해야하는 함수 : sum, avg, median, variance, stddev
• 그룹 함수에 입력값으로 모든 데이터 유형이 가능한 함수 : max, min, count
• 그룹 함수는 null을 포함하지 않음
  • 단 count(*) 는 null 포함한 행수를 구한다

count

• 행의 수를 구하는 함수

SELECT count(*) FROM hr.employees; -- 107, null 포함

SELECT count(department_id) FROM hr.employees; -- 106, null 포함 X

SELECT count(commission_pct) FROM hr.employees; -- 35, null 포함 X

• 중복을 제거해서 행수 구하기

SELECT count(distinct department_id) FROM hr.employees;

SELECT count(unique department_id) FROM hr.employees;

sum

• 합

SELECT sum(salary) FROM hr.employees;

avg

• 평균

SELECT avg(salary) FROM hr.employees;

SELECT avg(commission_pct) FROM hr.employees;

SELECT avg(nvl(commission_pct, 0)) FROM hr.employees;

• 두 쿼리 결과 다름 주의 필요
  • 데이터가 1, null, 3 인 경우
    • (1 + 3) / 2
    • (1 + 0 + 3) / 3

median

• 중앙값

SELECT median(salary) FROM hr.employees;

variance

• 분산

SELECT variance(salary) FROM hr.employees;

stddev

• 표준편차

SELECT stddev(salary) FROM hr.employees;

max

• 최대값

SELECT max(salary) FROM hr.employees;

min

• 최소값

SELECT min(salary) FROM hr.employees;

주의

SELECT
    count(last_name),
    count(hire_date),
    max(last_name),
    max(hire_date),
    min(last_name),
    min(hire_date)
FROM hr.employees;

• count, max, min만 문자형, 날짜형도 가능
• 나머지 함수는 숫자형만 가능

Group by절

• 테이블의 행을 작은 그룹으로 나눌 수 있는 절

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

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     12 |00:00:00.01 |       6 |       |       |          |
|   1 |  HASH GROUP BY     |           |      1 |     11 |     12 |00:00:00.01 |       6 |   941K|   941K| 1736K (0)|
|   2 |   TABLE ACCESS FULL| EMPLOYEES |      1 |    107 |    107 |00:00:00.01 |       6 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

• 해시 group by를 사용하기 때문에 정렬되지 않음

그룹 함수 사용 시 주의점

• null 포함하지 않음(count(*) 제외)
• SELECT 절의 개별 컬럼(개별 포현식)은 모두 GROUP BY 절에 명시해야 함

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

• 그룹 함수를 사용할 경우 group by 없이 개별 컬럼을 함께 사용하면 오류 발생

SELECT department_id, sum(salary)
FROM hr.employees;

• GROUP BY 절에는 컬럼의 별칭, 위치표기법 사용 불가(오류발생)

SELECT department_id, job_id job, sum(salary)
FROM hr.employees
GROUP BY department_id, job;

• WHERE 절에서는 그룹 함수의 결과를 제한 불가
  • WHERE은 행을 제한하는 절, GROUP BY보다 먼저 처리됨

SELECT department_id, sum(salary)
FROM hr.employees
WHERE sum(salary) >= 15000 
GROUP BY department_id;

• 그룹 함수 중첩 시 개별 컬럼 사용 불가

-- 오류 -> 서브 쿼리 활용해야 함
SELECT department_id, max(sum(salary))
FROM hr.employees
GROUP BY department_id;

SELECT max(sum(salary))
FROM hr.employees
GROUP BY department_id;

HAVING 절

• 그룹 함수의 결과를 제한하는 절

SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id
HAVING sum(salary) >= 15000;

주의

SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id
HAVING department_id in (10, 20);

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      2 |00:00:00.01 |       6 |       |       |          |
|*  1 |  FILTER             |           |      1 |        |      2 |00:00:00.01 |       6 |       |       |          |
|   2 |   HASH GROUP BY     |           |      1 |      1 |     12 |00:00:00.01 |       6 |   941K|   941K| 1739K (0)|
|   3 |    TABLE ACCESS FULL| EMPLOYEES |      1 |    107 |    107 |00:00:00.01 |       6 |       |       |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("DEPARTMENT_ID"=10 OR "DEPARTMENT_ID"=20))


SELECT department_id, sum(salary)
FROM hr.employees
WHERE department_id in (10, 20)
GROUP BY department_id;

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |      2 |00:00:00.01 |       3 |
|   1 |  SORT GROUP BY NOSORT         |                   |      1 |      2 |      2 |00:00:00.01 |       3 |
|   2 |   INLIST ITERATOR             |                   |      1 |        |      3 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |      2 |      3 |      3 |00:00:00.01 |       3 |
|*  4 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |      2 |      3 |      3 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access(("DEPARTMENT_ID"=10 OR "DEPARTMENT_ID"=20))

• 전체 데이터를 읽어 그룹핑 후 having 조건에 해당하는 데이터를 필터링하고 있음 → 비효율, 인덱스 사용 불가
• where절에서 먼저 행을 제한 후 그룹핑하면 더 적은 데이터를 읽고(인덱스 사용), 그룹핑 연산도 줄일 수 있음

문제

-- [문제23] 2008년도에 입사한 사원들의 job_id별 인원수를 구하고 인원수가 많은 순으로 출력하세요.
SELECT job_id, count(*) cnt
FROM hr.employees
WHERE hire_date between to_date('20080101', 'yyyymmdd') and to_date('20090101', 'yyyymmdd') - 1/24/60/60
GROUP BY job_id
ORDER BY cnt desc;

-- [문제24] 년도별 입사 인원수를 출력해주세요.
SELECT to_char(hire_date, 'yyyy') year, count(*) cnt
FROM hr.employees
GROUP BY to_char(hire_date, 'yyyy')
ORDER BY year;

-- [문제25] 년도별 입사 인원수를 아래 화면과 같이 출력해주세요.
SELECT 
    count(*) TOTAL,
    count(case when to_char(hire_date, 'yyyy') = '2001' then 1 end) "2001년",
    count(case when to_char(hire_date, 'yyyy') = '2002' then 1 end) "2002년",
    count(case when to_char(hire_date, 'yyyy') = '2003' then 1 end) "2003년"
FROM hr.employees;

/*
     TOTAL     2001년     2002년     2003년
---------- ---------- ---------- ----------
       107          1          7          6
*/

• 25번 쿼리의 경우 데이터 개수만큼 count 함수를 3번씩 호출 → 각각의 경우를 먼저 집계한 후 합치는 방법이 더 효율적일 수 있음

JOIN(조인)

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

1. Cartesian Product(카티션 곱)

• 조인조건이 생략된 경우
• 조인조건이 잘못 생성된 경우
• 첫 번째 테이블 행의 수와 두 번째 테이블의 행 수가 곱해짐

SELECT *
FROM hr.employees, hr.departments;

• employees: 107건, departments: 27건 → 107 * 27 = 2889건 조회

2. equi join, inner join, simple join, 등가 조인

• 조인 키 값이 일치하는 데이터만 추출하는 조인
• 조인 컬럼에 테이블명을 지정하지 않으면, 어떤 테이블의 컬럼인지 알 수 없기 때문에 오류 발생(semantic check 시 컬럼 정의의 모호성 발생)

SELECT e.department_id, department_name
FROM hr.employees, hr.departments
WHERE department_id = department_id;

SELECT e.department_id, department_name
FROM hr.employees, hr.departments
WHERE hr.employees.department_id = hr.departments.department_id;

• 테이블명을 붙이면 너무 길고, 이로 인해 메모리 사용량이 많아질 수 있기 때문에 별칭 사용

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

• 조인 컬럼(department_id) 일치 조건으로 매칭되지 않는 컬럼은 결과에서 제외됨
  • department_id가 null인 경우 빠짐
• n개 테이블을 조인하려면 조인조건술어는 n-1개의 조인조건술어를 작성해야 함

현재 사원들이 근무하는 도시 조회

SELECT e.employee_id, l.city
FROM hr.employees e, hr.departments d, hr.locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

• employees 테이블과 departments 테이블을 먼저 조인 후, 조인한 결과 집합과 locations 테이블을 조인

현재 사원들이 근무하는 국가 조회

SELECT e.employee_id, c.country_name
FROM hr.employees e, hr.departments d, hr.locations l, hr.countries c
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id;