231129 DB - 단일행함수 3, 그룹 함수와 그룹화 1

단일 행 함수 - 변환 함수, 제어 흐름 함수, 시스템 정보 함수

변환 함수

date_format(날짜, 형식) : 날짜를 형식에 맞게 출력하는 함수

select date_format(now(), '%Y-%M-%d') as "Now";

그림 1. date_format 함수 예시 1

날짜 형식 요소

%Y : 년도를 4자리 숫자로 표현,

%y : 년도를 2자리로 표현

%M : 월을 영문 풀네임으로 표현

%b : 월을 영문 약자로 표현

%m : 월을 2자리로 표현

%c : 월을 2자리 숫자로 표현하되 10보다 작을 경우 한자리로 표현

%d : 일을 2자리 숫자로 표현

%e : 일을 2자리 숫자로 표현하되 10보다 작을 경우 한자리로 표현

%W : 요일을 영문 풀네임으로 표현, %a : 요일을 영문 약자로 표현

%H : 시간을 24시간으로 표현

%h : 시간을 12시간으로 표현

%k : 시간을 24시간으로 표현하되 10보다 작을 경우 한자리로 표현

%l : 시간을 12시간으로 표현하되 10보다 작을 경우 한자리로 표현

%i : 분을 2자리로 숫자로 표현

%s : 초를 2자리 숫자로 표현

등등..

 

select employee_id, date_format(hire_date, '%Y-%M-%d %W') as "입사일" from employees;

그림 2. date_format 함수 예시 2

 

cast(값 as 데이터타입) : 값을 지정된 데이터타입으로 변환하는 함수

데이터타입 binary, char, signed(부호 있는 정수형), unsigned(부호 없는 정수형), decinal(숫자형), double, float, datetime, date, time

select cast('123' as signed), cast('-123.45' as signed);

그림 3. cast 함수 예시 1

select cast('123' as unsigned), cast('123.45' as unsigned);

그림 4. cast 함수 예시 2

select cast('2023/11/29' as date) as '날짜';
select cast('2023@11@29' as date) as '날짜';

그림 5. cast 함수 예시 3

select cast('2022-01-02 21:24:33.123' as date) as "DATE",
cast('2022-01-02 21:24:33.123' as time) as "TIME",
cast('2022-01-02 21:24:33.123' as datetime) as "DATETIME";

그림 6. cast 함수 예시 4

 

제어 흐름 함수

if(논리식, 참, 거짓) : 논리식이 참이면 참값을, 거짓이면 거짓값을 출력하는 함수

select if(100>200, '참','거짓') as "결과";

그림 7. if 함수 예시 1

select employee_id, salary, if(salary>10000, '1등급', '2등급') as "급여 등급" from employees;

그림 8. if 함수 예시 2

 

ifnull(수식1, 수식2) : 수식1이 null이 아니면 수식1이 반환되고, 수식1이 null이면 수식2가 반환되는 함수

select ifnull(null,'널') as "결과1", ifnull(100,'널') as "결과2";

그림 9. ifnull 함수 예시 1

select employee_id, last_name, salary, commission_pct, ifnull(commission_pct, 0) from employees;

그림 10. ifnull 함수 예시 2

select employee_id, last_name, ifnull(department_id, '미정') from employees;

그림 11. ifnull 함수 예시 3

employees 테이블에서 전 직원들의 employee_id, last_name, salary, commission_pct, 연봉 출력
연봉 공식 = (12 * salary) + (salary*commission_pct*12)
select employee_id, last_name, salary, commission_pct, 

ifnull((12 * salary) + (salary*commission_pct*12),12*salary) as "연봉"
from employees;

그림 12. ifnull 함수 예시 4

 

nullif(수식1, 수식2) : 수식1과 수식2가 같으면 null을 반환하고, 다르면 수식1을 반환하는 함수

select nullif(100, 100) as "결과1", nullif(100, 200) as "결과2";

그림 13. nullif 함수 예시 1

select employee_id, first_name, last_name, nullif(length(first_name), length(last_name)) as "결과" from employees;

그림 14. nullif 함수 예시 2

 

cf) case 구문 : SQL 구문에서 if-then-else의 논리를 적용할 수 있는 구문

SQL> case 비교값 when 값 1 then 결과 1
                               when 값2 then 결과 2
                               ...
                               else 기본값 
         end as "별칭"
         from 테이블명; 

 

 

select case 10 when 1 then '일'

                       when 5 then '오'
                       when 10 then '십'
                       else '?'
end as "case예제";

그림 15. case-when-then-else 예제 1

select employee_id, last_name, department_id,
case department_id when 10 then '부서10'
                              when 50 then '부서50'
                             when 100 then '부서100'
                             when 150 then '부서150'
                             when 200 then '부서200'
                             else '기타 부서'
end as "부서정보"

그림 16.  case-when-then-else 예제 2

 

시스템 정보 함수

user() = current_user() = session_user() : 현재 사용자 정보를 반환하는 함수

select user(), current_user(), session_user();

그림 17. user, current_user, session_user 함수

 

database() = schema() : 현재 데이터베이스 또는 스키마 정보를 반환하는 함수

그림 18. database, schema 함수

 

version() : 현재 MySQL 버전을 반환하는 함수

그림 19. version 함수

---

연습문제 1
employees 테이블로부터 사원들의 last_name과 commission_pct를 출력하되 커미션을 받는 사원은 자신의 커미션 비율을 출력하고, 커미션을 받지 않는 사원은 "No Commission"을 출력
select last_name, ifnull(commission_pct,'No Commission') from employees;

그림 20. 연습문제 1

 

연습문제2
employees 테이블로부터 job_id 값을 기반으로 모든 사원의 등급을 표시하는 쿼리를 작성
select job_id, 
case job_id when 'ad_pres' then 'A'
                   when 'st_man' then 'B'
                   when 'it_prog' then 'C'
                   when 'sa_rep' then 'D'
                  when 'st_clerk' then 'E'
                  else '0'
end as "grade"
from employees;

그림 21. 연습문제 2

 

 

그룹 함수와 그룹화

그룹 함수(=다중행함수)

행 그룹을 조작해서 하나의 결과값을 반환하는 함수

그룹함수 종류 : sum, avg, max, min, count,...

그룹함수의 특징 : null값은 제외하고 작업함.

 

min(행그룹) : 행그룹에서 최소값을 구해주는 함수. 모든 데이터타입에 사용 가능
max(행그룹) : 행그룹에서 최대값을 구해주는 함수. 모든 데이터타입에 사용 가능
select min(salary) as "최소 급여", max(salary) as "최대 급여" from employees;

그림 22. min, max 함수 예시 1

select min(hire_date) as "가장 오래된 입사일", max(hire_date) as "가장 최근 입사일" from employees;

날짜 최소값은 과거, 날짜 최대값 최근날짜

그림 23. min, max 함수 예시 2

select min(last_name) as "name1", max(last_name) as "name2" from employees;

문자에서는 abc순, ㄱㄴㄷ순

그림 24. min, max 함수 예시 3

 

sum(행그룹) : 행그룹의 합계를 구해주는 함수. 숫자형 데이터 타입만 가능
avg(행그룹) : 행그룹의 평균을 구해주는 함수. 숫자형 데이터 타입만 가능

select  sum(salary) as "급여 합계", avg(salary) as "급여 평균" from employees;

그림 25. sum, avg 함수 예시 1

 sum(salary) as "급여 합계", avg(salary) as "급여 평균" from employees where job_id like '%REP%';

그림 26. sum, avg 함수 예시 2

 

count(*) : 행그룹에서 행의 개수를 반환해주는 함수(null값, 중복값 포함)
select count(*) from employees;

※ select count(employee_id) from employees;와 같음

그림 27. count(*) 함수 예시 1

select count(*) from employees where department_id <= 80;

그림 28. count(*) 함수 예시 2

 

count(행그룹) : 행그룹에서 행의 개수를 반환해주는 함수(null값, 중복값 포함)
select count(commission_pct) from employees;

그림 29. count 함수 예시 1

select count(commission_pct) from employees where department_id <= 80;

그림 30. count 함수 예시 2

select count(department_id) from employees;

그림 31. count 함수 예시 3

 

count(distinct 행그룹) : 행그룹에서 행의 개수를 반환해주는 함수(null값, 중복값 포함)
select count(distinct department_id) from employees;

그림 32. count(disinct 행그룹) 함수 예시

※count(*), count(행그룹), count(distinct 행그룹)은 인수에 따라 개수가 달라질 수 있음.

count(*) : 모든 컬럼을 기준으로 행의 수를 반환

count(행그룹) : 특정 컬럼을 기준으로 행의 개수를 반환

count(distinct 행그룹) : 특정 컬럼을 기준으로 중복값 제외하고 행의 개수를 반환

 

ex) employees 테이블에서 전체 직원의 커미션 평균을 구하시오 단, avg_comm로 출력
select sum(commission_pct) / count(*) as 'avg_comm' from employees;

== select avg(ifnull(commission_pct, 0)) as 'avg_comm' from employees;

그림 33. ex 결과

 

그룹화(group by절)

테이블 내에서 작은 그룹화를 해서 그룹함수를 적용할 때 사용되는 절

SQL> select 컬럼명, 그룹함수(컬럼)
          from 테이블명
          [where 조건문]
          [group by 컬럼명]
          [order by 컬럼명];

 

부서별 그룹화 부서별 평균 임금

select department_id, avg(salary)
from employees
group by department_id
order by department_id;

그림 34. 부서 그룹화 후 그룹별 평균 임금