Data inquiry and data analysis using SQL

부천대 IoT를 수강하는 학생들을 위해 만든 SQL을 활용한 데이터조회와 데이터분석자료입니다.

집계 함수와 산술 함수

집계함수

  • 개수(COUNT)

  • 최댓값(MAX)

  • 최솟값(MIN)

  • 평균값(AVG)

  • 합계(SUM)

  • 표준편차(STD)

select emp_no ,max(salary) 
from ( 
select a.* from (select emp.emp_no, hire_date, salary, from_date, to_date from employees.employees emp left join employees.salaries sal on emp.emp_no= sal.emp_no where salary between 60000 and 100000
) a 
where hire_date >= '1986-01-01' 
order by from_date asc ) b;

select count(* ) from employees.employees ; 
select count(distinct emp_no ) from employees.employees ;
select emp.emp_no, max(salary),hire_date ,from_date from employees.employees emp left join employees.salaries sal on emp.emp_no= sal.emp_no ; 
select emp.emp_no, min(salary) from employees.employees emp left join employees.salaries sal on emp.emp_no= sal.emp_no ;
select avg(salary) from employees.employees emp left join employees.salaries sal on emp.emp_no= sal.emp_no where to_date >= '19870101' and to_date >= '19871231' ;
select  from employees.employees where gender is null ; 
select  from employees.employees where gender is not null ;

산술함수

  • ABS 함수 - 절대값을 구하는 함수

  • SQRT 함수 - 제곱근을 구하는 함수

  • CEIL 함수 - 올림 함수

  • FLOOR 함수 - 내림 함수

  • ROUND 함수 - 반올림 함수

  • TRUNC 함수 - 버림함수

select abs(-185.7) from dual ; 
select abs(-1); 

SELECT SQRT(4) from dual; 
SELECT SQRT(9) from dual ; 

select ceil(100.7) from dual ; 
select ceil(181.4) from dual ;

SELECT ROUND(3456.1234567) FROM DUAL; 
SELECT ROUND(3456.1234567 ,1) FROM DUAL ; 
SELECT ROUND(3456.1234567 ,4) FROM DUAL ;

SELECT TRUNCATE(3456.1234567 ,1) FROM DUAL; 
SELECT TRUNCATE(3456.1234567 ,4) FROM DUAL; 
SELECT TRUNCATE(3456.1234567 ,-1) FROM DUAL; 
SELECT TRUNCATE(3456.1234567 ,-2) FROM DUAL;

이밖의 다양한 산술함수가 있으니 아래 사이트를 참고하길 바란다.

과제

다음은 어느 마라탕 집의 리뷰 데이터를 발췌하여 가져온 것이다. 이를 테이블로 만들었다. 

create database maratang ; 
CREATE TABLE maratang.review (id INTEGER PRIMARY KEY, star INTEGER, comment TEXT, reg_date DATE); 
INSERT INTO maratang.review VALUES (1, 2, NULL, '2020-01-03'); 
INSERT INTO maratang.review VALUES (2, 5, '마라탕의 고수좀 빼주세요 ', '2020-02-14');
INSERT INTO maratang.review VALUES (3, 3, '정말 맛있었요. 그런데 배달이 너무 느려서 좀만 늦었음 면 뿔뻔...', '2020-03-11'); 
INSERT INTO maratang.review VALUES (4, NULL, NULL, '2020-11-23'); 
INSERT INTO maratang.review VALUES (5, 3, '나쁘지않네', '2020-09-01'); 
INSERT INTO maratang.review VALUES (6, 5, NULL, '2020-05-02'); 
INSERT INTO maratang.review VALUES (7, 2, '단무지 안줘요?', '2019-10-05');
commit; 
select * from maratang.review ;

문제

고객들의 리뷰 수와, 별점 평균값을 구하려고 다.

단,댓글이 존재하는(comment 컬럼이 NULL이 아닌) 리뷰들의 개수와 그 별점들의 평균만 구하고,

별점의 평균값은 반올림을 해주세요.

case when

MySQL에서도 프로그래밍에서의 Switch(스위치) 문 혹은 IF문과 같이 조건문을 사용하여 결과값을 내야 하는 경우가 있다.

MySQL에서는 CASE라는 Function(함수)을 제공하고 있으며, 해당 Function은 "MySQL 4.0 이상"부터 사용이 가능하다.

MySQL의 CASE문을 사용하는 방법은 다음과 같다.

  • WHEN - THEN은 항상 같이 사용 되어야 한다.

  • WHEN - THEN은 여러개 사용이 가능하다.

  • ELSE가 존재하면 모든 WHEN - THEN 조건이 TRUE(참)이 아닌 경우 ELSE의 결과값을 반환한다.

  • ELSE가 없고, 조건이 TRUE(참)이 아니면 NULL을 반환한다.

 CASE WHEN 조건1 THEN 결과값1 
      WHEN 조건2 THEN 결과값2 
      WHEN 조건N THEN 결과값N 
      ELSE 결과값 END as '컬럼'

아래 예제 데이터를 만든다음 case when 구문을 실습해보겠다.

create database test_DB ; 
CREATE TABLE test_DB.member ( id int NOT NULL AUTO_INCREMENT, age int DEFAULT NULL, gender text, height double DEFAULT NULL, weight double DEFAULT NULL, birthday date DEFAULT NULL, PRIMARY KEY (id) ) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO test_DB.member (id, age, gender, height, weight, birthday) VALUES (1001,28,'m',178.2,70,'1992-01-03'), 
(1002,29,'m',165.7,67.3,'1992-08-13'), 
(1003,31,'m',NULL,70.2,'1990-01-03'), 
(1004,-10,'f',183.5,72,'1975-03-12'), 
(1005,27,'m',181.3,70,'1992-09-02'), 
(1006,300,'f',172.2,NULL,'1989-01-03'), 
(1007,28,'m',195.2,66,'1992-03-13'), 
(1008,36,'f',167.7,66.2,'1992-01-03'), 
(1009,30,'m',180.2,70.4,'1991-02-13'), 
(1010,111,'f',NULL,70.5,'1970-08-01');


select * from test_db.member ;

주어진 데이터로 각 고객의 체질량지수 (BMI)를 계산하겠다.

 select weight/ ((height /100) *(height /100))) from test_DB.member ;

BMI를 통해 산출된 값이 18.5이하면 저체중, 18.5~23은 정상, 23~25는 과체중, 25~30은 비만, 30이상은 고도비만으로 나눠보겠다.

select a.id , bmi, 
case when bmi is null then '데이터가 없음' 
          when bmi <= 18.5 then '저체중' 
          when bmi between 18.5 and 23 then '정상체중' 
          when bmi between 23 and 25 then '과체중' 
          when bmi >= 30 then '고도비만' end as bmi_segment 
from ( 
select id , weight/ ((height /100) *(height /100)) as bmi from test_DB.member
 )a ;

이렇게 case when 구문을 이용해 조회한 BMI 비만척도 결과를 'test_DB' 라는DB에 'bmi_segmentation' 이라는 테이블 명을 만들어 별도로 저장해보겠다. 

create table test_db.bmi_segmentation as ( 
select a.id , bmi, case when bmi is null then '데이터가 없음' 
                        when bmi <= 18.5 then '저체중' 
                        when bmi between 18.5 and 23 then '정상체중' 
                        when bmi between 23 and 25 then '과체중' 
                        when bmi >= 30 then '고도비만' 
end as bmi_segment 
from ( 
select id , weight/ ((height /100) *(height /100)) as bmi from test_DB.member
 )a ) ;

고객 테이블의 정보와 함께 보고 싶을 때 left join을 이용하면 된다.

 select * from test_db.member m 
 left join test_db.bmi_segmentation bmi on m.id = bmi.id ;

마케팅에서 자주 사용하는 고객 세분화 기법중 하나인 RFM SCORE를 SQL구문으로 작성한 페이지가 있어 시간날때 참고하길 바란다.

SQL: RFM 분석으로 사용자를 3가지 관점의 그룹으로 나누기

NULL

NULL은 알 수 없거나 값이 존재하지 않는 값으로 NULL + , NULL - , NULL * , NULL / 등 어떠한 연산을 처리해도 결과는 NULL이라고 설명한 바 있다.

select null + 2   from dual;  --null
select null - 2   from dual;  --null
select null * 3   from dual;  --null
select null / 4   from dual;  --null

이번에는 실에서 NULL을 어떻게 다루는지 실습해보겠다.

실습을 위해 데이터를 만들고 진행하겠다.

create database test_employee;
use test_employee; --고정

create table emp_info(
emp_no int not null,
emp_name varchar(8),
title varchar(20),
manager int,
salary int,
dept_no int,
primary key (emp_no)
);

insert into emp_info values (001,'이상윤','CEO',null, 1000000000,1);
insert into emp_info values (002,'강민호','COO',001, 120000000,2);
insert into emp_info values (003,'홍정훈','product manager',002, 200000000,2);
insert into emp_info values (004,'로맥','developer',003, 300000000,3);
insert into emp_info values (005,'김병규','UIdesigner',004, 500000000,3);
insert into emp_info values (006,'박찬호','serving',005, 400000000,3);

select * from emp_info;

만약에 회사에서 나의 관리자가 없는 경우는 어떠한 사람일까?

분명 직급이 높을것이라 생각 할 수 있다.

실제 사원정보나 부서 관련 데이터를 다루다보면 나의 매니저가 없는 경우가 있다.

그래서 값이 NULL로 보이는데, 이런경우 어떻게 처리할까?

오라클의 NVL() 함수와 같은 기능을 하는 MySQL에서는 IFNULL 함수가 있다.

IFNULL('expr1', 'expr2')

IFNULL 기능은 expr1이 null이라면, expr2를 리턴다. 그렇지 않다면, expr1을 리턴한다.

select * from emp_info;

select IFNULL(manager, 'NO.1') from test_employee.emp_info ; 
-- ORACLE NVL()과 같음 .만약 manager 값이 NULL이면 ROOT

그렇다면 실제 계층 구조인 데이터에서는 어떻게 최상위 노드인지, 내 아랫사람은 누구이고 윗사람은 누구인 확인 할 수 있을까? 단순히 where 를 통해 확인이 가능할까? 이럴때 계층쿼리를 사용하면 쉽다.

오라클에서는 Connect by prior , START WITH를 사용하여 찾을 수 있지만 Mysql에서는 지원하지 않는다. 나중에 시간되면 구현해보겠다.

다음은 SQLD 시험에서 가장많이 출제되는 시험문이다. 반드시 알아두고 가길 바란다.

-- 1
select IFNULL(max(emp_name), '존재하지않음') from test_employee.emp_info 
where emp_name ='이상윤'; 

-- 2
select IFNULL(max(emp_name), '존재하지않음') from test_employee.emp_info 
where emp_name ='정창용';

-- 3
select IFNULL(emp_name, '존재하지않음') from test_employee.emp_info 
where emp_name ='정창용';

첫번째 SQL문을 실행하면 '이상윤' 이라는 결과가 나온다 . 이상윤은 실제 이 emp_info 테이블에 존재하기에 결과도 '이상윤' 이름이 나온다.

두번째 SQL문과 세번째 SQL문을 비교해보자.

두번째 SQL문을 실행하면 '존재하지않음'의 결과가 나오고 세번째는 결과가 나오지 않는. 왜그럴까?

max()함수는 공집합의 경우에도 한개의 ROW가 생성된다. 즉 MAX()를 사용하면 NULL이 리턴되고 IFNULL 기능에 의해 NULL값이니 '존재하지 않음'의 결과가 나다. 이 둘의 차이는 SQLD 자격증 시험에 자주 출제되니 꼭 알아두길 바란다.

NULLIF('expr1', 'expr2')

NULLIF는 expr1 과 expr2가 같은 값이면 NULL을 리턴하고, 그렇지 않으면 expr1을 리턴한다.

select nullif('이상윤','이상윤') from dual ; -- NULL
select nullif('이상윤','정창용') from dual ; -- 이상윤

COALESCE

COALESCE() 함수는 처음으로 NULL 이아닌 컬럼 값을 만나면 그 컬럼 값을 리턴한다.

COALESCE('컬럼1','컬럼2','컬럼3',...)

컬럼1이 NULL이 아니면 컬럼1을 리턴한다.

컬럼1이 NULL이면 컬럼2을 리턴한다.

컬럼1이 NULL이고 컬럼2가 NULL이 아니면 컬럼2 리턴한다.

즉 컬럼 1부터 컬럼N-1 까지 데이터가 NULL이면 컬럼N값에서 컬럼N값을 리턴한다.

select coalesce(1,NULL,NULL) from dual; -- 1
select coalesce(NULL,2,NULL) from dual; -- 2
select coalesce(NULL, NULL, 3) from dual; -- 3
select coalesce(NULL, NULL, NULL) from dual; -- NULL

그룹화(Grouping)

row들을 여러개로 그룹으로 나눌때 쓰는 group by는 특정 컬럼 이름을 지정(column-names)해주면 그 컬럼의 UNIQUE한 값에 따라서 데이터를 그룹 짓고, 중복된 열은 제거된다.

보통 현업에서 데이터분석가들이 월별로 신규 회윈 수 , 상품 카테고리 별 클릭 수와 구매 수,월별 매출액 등 을 구할 때 집계함수와 함께 쓰이며 문법은 다음과 같다.

SELECT column-names
  FROM table-name
 WHERE condition
 GROUP BY column-names
HAVING condition

employees 데이터베이스의 사원 테이블 (employes) 로 모든 사원들을 성별로 기준을 잡아 그룹화 시켜 보겠다.

 select * from employees.employees ;
 select gender from employees.employees ;
 select distinct gender from employees.employees ;

겉으로 보기에 distinct와 비슷해 보일 수 있나 group by의 결과는 모든 row들 중에서 gender가 m인 대상과 f의 대상을 따로 보여준 것이다.

다음은 조회되는 row의 개수를 구해주는 count 함수를 사용하여 성별로 그룹핑된 결과를 구하여 각 성별에 따른 사원 수를 구해보겠다. 

select gender, count(*)  from employees.employees group by gender ;

다음은 사원의 나이를 구하고, 성별에 따른 사원 수, 성별로 사원들의 평균 나이, 성별로 가장 낮은 나이와 높 나이가를 구하겠다.


//사원들의 나이를 구한 후 , 성별로 사원 수와 평균 나이 대를 구한다.
select date_format('1990-09-21' ,'%Y-%m-%d')from dual;
select emp_no ,year(date_format('1990-09-21' ,'%Y-%m-%d')) - year(birth_date) age  from employees.employees;

select gender ,count(*)  from employees.employees group by gender ;
select  gender ,  count(*) ,avg(year(date_format('1990-09-21' ,'%Y-%m-%d')) - year(birth_date)) age  from employees.employees group by gender  ;


// 서브쿼리를 이용함 어떻게 했는지 눈크게 뜨고 보세요.
select b.gender , avg(b.age), min(b.age) min_age , max(b.age) max_age from 
(select a.* 
from ( 
select  emp_no , gender ,(year(date_format('1990-09-21' ,'%Y-%m-%d')) - year(birth_date)) age  
from employees.employees ) a ) b
group by b.gender ;

우리는 실습을 통해 Group by를 쓰지 않고 집계함수를 썼을 땐 테이블 전체가 하나의 집합의 대상이었고, 그 집합이 그룹핑 대상이었다. 하지만 Group by를 쓴 후 특정 컬럼의 unique 값을 기준으로 각각의 그룹핑이 집합의 대상이었고, 그룹핑 별 집계함수가 동작한 것을 알 수 있었다.

다음은 사원 테이블과 (employees) , 각 사원이 속한 부서정보가 담긴 테이블(dept_emp), 회사의 부서와 부서명 정보가 있는 테이블(departments)을 대상으로 실습을 진행하겠다.

먼저 subquery를 이용하여 각 테이블을 join 시키겠다.

//3개의 테이블 조
select e.*
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c 
left join employees.departments d on c.dept_no = d.dept_no) e ;

제대로 만들었다면 다음과 같은 테이블이 조회 될 것이다.

이제 부서명과 ,성별을 group by 시켜 각 부서의 성별 분포를 확인해보겠다.

// 먼저 각 부서의 사원 수를 구해 보았다.
select dept_name, count(*) emp_count
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by dept_name order by  emp_count desc ;


//각 부서의 성별 분포를 확인
select dept_name, gender , count(*)
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by dept_name , gender order by dept_name  ;

전체적으로 이 회사는 남자 사원이 많이 채용된 것을 알 수 있다.

이처럼 group by의 대상을 늘릴수록 세분화된 그룹을 볼 수 있다.

HAVING

다음은 grouping 된 모든 부서를 보고싶지 않고, 특정 부서의 남자 회원 수만을 보고 싶다. 이럴 때 having 조건을 사용하면 된다.

예를들어 마케팅팀의 여자 사원 수만 보고 싶을 때 다음과 같이 having 조건을 사용하면 된다.

select dept_name, gender , count(*)
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by dept_name , gender 
having dept_name = 'Marketing' and gender ='F';

그렇다면 where 조건을 사용해도 동일한 결과를 얻을 수 있지 않을까?

select dept_name, gender , count(*)
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by dept_name , gender 
where dept_name = 'Marketing' and gender ='F' ;

실행하면 Error가 생긴다.

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where dept_name = 'Marketing' and gender ='F'' at line 8

왜그럴까?

where는 테이블의 row를 조회할 때 row들을 조건을 설정하기 위해 만들어진 것이고, having은 이미 조회된 row를 다시 grouping 했을 때 생성된 그룹들 중에서 필터링을 할 때 쓰는 구문이다.

따라서 Group by를 사용할 때 지켜야 규칙이 있다.

Group by의 규칙은 다음과 같다.

1. GROUP BY 뒤에 쓰지 않은 컬럼 이름을 SELECT 뒤에 쓸 수는 없다.

왜냐하면 각 그룹의 row들 중에서 해당 컬럼의 값을 어느 row에서 가져와야할지 결정할 수가 없기 때문다.

예를들어 갑자기 마케팅 팀의 여성 사원 중에서 , 가장 입사가 빠른 사람을 조회한다고 가정해보자.

select dept_name, gender , min(hire_date)
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by dept_name , gender 
where dept_name = 'Marketing' and gender ='F' ;

아마 다음과 같은 error가 뜰 것이다.

Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'copang_main.member.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

그루핑 기준으로 사용되지 않은 컬럼(nonaggregated column)이 SELECT 절에 존재하면 안 된다는 뜻이다.

2. SELECT 절 뒤에서 집계 함수에 그 외의 컬럼 이름을 인자로 넣는 것은 허용된다.

COUNT,() MAX(),avg() 등과 같은 집계 함수는 사용할 수 있다.

WITH ROLLUP

Group by와 함께쓰는 WITH Rollup은 세부 그룹들의 부분 총계를 구해주는 기능으로서 그루핑된 row들을 좀더 큰 단위의 그룹으로 중간마다 합쳐준다.

다음은 위의 예제에서 ROLLUP을 함께쓰면 어떤 결과물을 보여주는지 실습해보겠다.

select dept_name, gender , count(*)
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by dept_name , gender with ROLLUP ;

위 코드를 실행 시 다음과 같은 결과물이 나온다.

Customer Service팀의 gender를 보면 NULL이 생긴것을 볼 수 있다.

이전에는 GROUPBY만 사용할 경우Cutomer Service팀의 여성 사원이 9448명, 남성회원은 14132명만 조회되었다면 ROLLUP을 사용했을 시 부서마다 부분적으로 총 인원 수를 구해준 것을 확인할 수 있다.

Group by를 사용할 때 ROLLUP을 사용하면 이와 같은 이점이 있다.

하지만 ROLLUP을 쓸 때 조심할 점이 있다.

WITH ROLLUP이 GROUP BY 뒤에 나오는 그루핑 기준의 등장 순서에 맞춰서 계층적인 부분 총계를 보여주기 때문에 GROUP BY 의 그루핑 기준의 순서에 따라 WITH ROLLUP이 출력하는 결과가 달라진다. 

select  gender ,dept_name, count(*)
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by gender , dept_name with ROLLUP ;

만약에 데이터가 NULL일 경우 어떻게 분할 수 있을까?

이럴때 GROUPING 함수를 함께 사용하면 좋다.

Grouping 함수는 실제로 NULL을 나타내기 위해 쓰인 NULL인 경우에는 0, 부분 총계를 나타내기 위해 표시된 NULL은 1을 리턴해 구분해 준다.

지금 우리가 실습하는 데이터에는 NULL 값이 없지만 NULL이 있을경우 0으로 리턴해 보여준다.

 select dept_name, gender , GROUPING(gender), count(*)
from 
(select c.* ,d.dept_name
from 
(select a.emp_no, first_name, last_name , gender, b.dept_no from employees.employees  a 
left join employees.dept_emp b on a.emp_no = b.emp_no ) c left join employees.departments d on c.dept_no = d.dept_no) e
group by dept_name , gender with ROLLUP ;

JOIN

실무에서 가장 많이 쓰이는 SQL 중 join은 반드시 알아야 한다.

join은 서로 다른 종류의 테이블을 합쳐 하나로 보이게 한다. 물론 셀프조인에서는 사실 하나의 테이블을 다루지만 이에 대해서는 나중에 다시 언급하겠다.

조인의 종류에는 크게 4가지가 있다.

조인의 종류

  1. INNER JOIN

  2. CROSS JOIN

  3. OUTER JOIN

  4. SELF JOIN

곧바로 SQL 실습을 통해 join을 알아보도록 하겠다.

INNER JOIN

INNER JOIN은 키 값이 있는 테이블의 컬럼 값을 비교 후 조건에 맞는 데이터를 가져오는 것이다. 즉 서로 연관된 데이터만 검색하는 조인 방법이다.

SELECT a.emp_no , a.dept_no , b.dept_name FROM employees.dept_manager a 
inner join employees.departments b on a.dept_no = b.dept_no ;

CROSS JOIN

크로스 조인은 특정 기준 없이, 두 테이블간 가능한 모든 경우의 수에 대한 결합을 결과로 보여주는 방법이다.

특정한 기준이 필요 없으므로 on절이 없고 , 두테이블의 row 수를 곱한만큼의 결과가 출력된다.

dept_manager(24) * departments(13) = 312건 

select count() from employees.dept_manager; -- 24 
select count() from employees.departments; -- 13 
select count(*) from employees.dept_manager cross join employees.departments; 
-- 312

OUTER JOIN

아우터 조인은 그림과 같이 두 테이블의 공통영역을 포함해 한쪽 테이블의 다른 데이터를 포함하는 조인방식이다.

select *
from employees.employees a 
left join employees.dept_emp b 
on a.emp_no = b.emp_no ;

select a.* , b.dept_no
from employees.employees a 
left join employees.dept_emp b 
on a.emp_no = b.emp_no ;

from 있는 줄부터 해석을 하면 employees 테이블과 dept_emp 테이블을 합칠 때 employees테이블의 emp_no 컬럼과 dept_emp 테이블의 emp_no 컬럼을 비교해서 서로 값이 같은 row끼리 가로방향으로 결하겠다는 뜻이다.

그런데 실제 데이터는 join을 하면 값이 존재하지 않아 Null로 표기된게 많다.

그럼 left join이 있다면 right join도 존재하지 을까?

select * from employees.employees a 
right join employees.dept_emp b on a.emp_no = b.emp_no ;

leftjoin과 같은 방법이지만 차이가 있다면 right join은 오른쪽 테이블을 기으로 테이블을합친다.

SELF JOIN

self join은 하나의 테이블을 조인하는 방식으로 보통 원하는 데이터들이 하나의 테이블에 모두 존재할 때 사용한다.

예를들어 ㅇㅇㅇ과장이 10001 id를 가진 사원의 생일을 챙겨주고 싶다. 그런데 생일을 기억하지 못해 DBMS에서 알아보려 한다. 

select a.emp_no , b.birth_date from employees.employees a 
join employees.employees b 
on a.emp_no = b.emp_no  
where a.emp_no =10001;

서브쿼리(SubQuery)

SQL 문 안에 부품처럼 들어가는 서브쿼리는 크게 3종류가 있다고 설명한 바 있다.

이번에는 어떻게 서브쿼리를 이용하여 데이터를 조회하고 분석하는지 설명하겠다.

Having 에서 사용한 서브쿼리

먼저 1987년에 입사한 사원들 중 평균연봉보다 많이받는 사원들의 리스트를 알아보겠다.

--접근방법

-- 1. 1987년에 입사한 사원들을 조
select emp_no , salary  from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' group by emp_no; 

-- 2. 1987년의 입사한 사원들의 평균연봉 구하
select  avg(salary)  from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' ;

--3.1 Having을 이용한 평균연봉 이상자를 조
select emp_no , salary  from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' 
group by emp_no having salary >54983.0487 ;

-- 3.2 subquery를 이용한 조
select emp_no , salary  from employees.salaries where from_date > '1987-01-01' and to_date <= '1987-12-31' 
group by emp_no having salary > 
(select  avg(salary)  from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31');

3.1의 SQL문과 3.2 SQL문을 비교하면 동일한 결과를 얻는것을 알 수 있다.

서브쿼리를 사용할 땐 반드시 지켜야할 규칙이 있다. SQL문을 괄호로 감싸주어야 한다. 안그러면 오류가 발생한다.

이처럼 서브쿼리는 전체 SQL문에서 일부를 이루는 또다른 SQL문이라 할 수 있다. 이러한 서브쿼리는 HAVING 절 뿐만 아니라 SELECT 절, WHERE 절, FROM 절 등에서도 사용이 가능하다.

SELECT절에서 사용한 서브쿼리

보통 select절에서 사용하는 서브쿼리는 기존에 사용하지 않은 컬럼을 추가하여 조회할 때 사용한다.

1987년에 입사한 신입사원들 중 가장 높은 연봉을 신입사원과 얼마나 차이나는지 실습을 통해 select 절에서 사용하는 서브쿼리를 알아보겠다.

select emp_no , salary , 
( select max(salary) from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' ) as max_salary
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' 
group by emp_no ;

select emp_no , salary , 
( select max(salary) from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' ) as max_salary
( select avg(salary) from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' ) as avg_salary
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' 
group by emp_no ;

select a.emp_no , salary, max_sal , (max_sal - salary) as sal_diff
from (
select emp_no , salary, ( select max(salary) from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' ) as max_sal 
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' 
group by emp_no ) a;

이처럼 select절의 서브쿼리를 용하면 각 컬럼의 특징도 살펴볼 수 있다. 하지만 주의할 점이 있다.

서브쿼리의 결과는 반드시 단일 행이나 SUM, COUNT 등의 집계 함수를 거친 단일 값으로 리턴되어야 한다.

Where절에서 사용하는 서브쿼리

where절에서 사용하는 서브쿼는 보통 조건값을 비교할 때 많이 사용한다.

다음은 where절에서 사용하는 서브쿼리 예제이다.

  1. 1987년 입사자들 중 평균연봉을 받지 못한 사원들 리스트

  2. 1987년 입사자들 중 최소연봉을 받는 사원들 리스

-- 1987년 입사자들 중 평균연봉을 받지 못한 사원들 리스트
select *
from 
(select *
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31') a 

where a.salary <(select avg(salary)
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31')  ;


-- 1987년 입사자들 중 최소연봉을 받는 사원들 리스트
select *
from 
(select *
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31') a 

where a.salary =(select min(salary)
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31')  ;

지금까지 우리가 배운 서브쿼리는 단일값만 리턴했지만, 서브쿼리는 어떻게 조회하느냐에 따라 값이 다양하게 나올 수 있다.

예를들어 근속년수가 15년 넘은 사원들을 조회고 싶다면 어떻게 야 될까?

-- 접근방법
 -- 1. 연봉을 15번이상 받은 사원을 체
select emp_no, count(*) from employees.salaries 
group by  emp_no  having count(salary) >= 15 ;

  -- 2. 조건절 in을 이용하여 그 해당하는 사원들만 조회 
select * from employees.employees 
where emp_no in ( 
select emp_no from employees.salaries 
group by  emp_no  having count(salary) >= 15 
);

From절에서 사용하는 서브쿼리

From절에서 사용하는 서브쿼리는 테이블의 결과를 리턴할 때 사용한다. 이러한 서브쿼리를 derieved table이라고 부른다. 이때 주의할 점은 From 절에서 서브쿼리를 사용할 시 alias를 지정해줘야한다.

다음은 From절 서브쿼리를 이용하여 회사원들의 부서정보까지 조회할 수 있도록 테이블을 만들겠.

-- 사원테이블과 사원이 소속된 부서코드가 들어간 테이블을 join
select c.* 
from (
select a.emp_no , a.first_name, last_name, b.dept_no, b.from_date dept_fr_dt, b.to_date dept_to_dt
 from employees.employees a left join employees.dept_emp b on a.emp_no = b.emp_no) c ;
 
 
 --위에 조회된 테이블에서 부서명이 들어간 테이블을 조인
 select c.* , d.dept_name
from (
select a.emp_no , a.first_name, last_name, b.dept_no, b.from_date dept_fr_dt, b.to_date dept_to_dt
 from employees.employees a left join employees.dept_emp b on a.emp_no = b.emp_no) c 
 left join employees.departments d on c.dept_no = d.dept_no ;

View

이전시간에 배운 서브쿼리의 예제를 살펴보면 SQL문이 복잡하고 중복되는 부분이 있다보니 가독성이 떨어졌다. 이러한 문제를 해결할 수 있는 방법으로 View(뷰)가 있다.

일종의 가상 테이블이라 불리는 View(뷰)는 복잡한 SQL을 편리하게 재생성 할 수 있는 이점이 있다.

예를들어 이전시간에 배운 쿼리를 보면 1987년 입사자들의 그해의 연봉을 From절에 사용한 후 다시 where절에 중복해서 사용했다.

-- 1987년 입사자들의 입사해 평균연봉을 못받 사원들 리스트
select *
from 
(select *
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31') a 

where a.salary <(select avg(salary)
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31')  ;

하지만 조인등을 해서 만든 결과 테이블을 View(뷰)로 저장하면 중복성을 피하고 가독성을 높일 수 있다.

View 생성방법

뷰의 기본문법은 다음과 같다.

-- view 생성
CREATE VIEW 뷰이름 AS SELECT 구문;

-- view 삭제

DROP VIEW 뷰이름;

위 기본문법을 바탕으로 1987년 입사자들의 입사해의 연봉을 뷰로 만들어 보겠다.

create view employees.sal_1987 as ( 
select *
from employees.salaries 
where from_date > '1987-01-01' and to_date <= '1987-12-31' ) ;

다음 workbench 왼쪽상단의 새로고침버튼을 누르고 Views를 클릭하면 sal_1987 이름의 View가 생성된 것을 확인할 수 있다.

이렇게 가상으로 만들어진 뷰는 마치 원래 테이블이 있는 것처럼 select 구문을 사용해 데이터를 조회하면 된다.

select * from employees.sal_1987;

따라서 1987년 입사자들 중에서 그 당시 평균연봉을 받지 못한 사원들 리스트를 View로 조회하면 다음과 같다.

select avg(salary) from employees.sal_1987;  -- 54983.0487
select * from employees.sal_1987 where salary < 54983.0487 ;

select * from employees.sal_1987 
where salary < (select avg(salary) from employees.sal_1987) ;

이처럼 자주 사용하는 데이터 집합을 View로 저장하면 짧은 SQL문을 사용한 것과 같 가독성을 높일 수 있다.

View와 테이블 차이

그렇다면 뷰와 일 테이블은 어떠 차이가 있는걸까?

가장 큰 차이는 뷰는 일반 테이블과 달리 데이터가 물리적으로 컴퓨터에 저장되어 있지않다.

테이블은 우리가 표 형식으로 보는 데이터들이 실제로 컴퓨터에 저장되어 있지만, 뷰는 SQL 문을 재실행하는 방식으로 가상의 테이블을 만들어준것이다.

또한 뷰에서는 ALTER 명령어를 사용 할 수 없다. 뷰의 내용을 수정 하고 싶으면 drop & create를 반복해야한다.

View의 장점

앞서 설명 뷰는 사용자에게 편의성을 제공해줄 뿐만 아니라 데이터 보안을 제공한다.

예를들어 마케팅 담당자가 DBA에게 스키마내 특정 테이블의 어떤 정보 업무상 꼭 필요하다 요할청할 때 view를 생성하여 꼭 필요한것 정보만 보여주고, 전체 정보는 공개하지 않게만들 수 있다.

WITH

SQL문을 사용하다 보면 매번 자주 사용했던 쿼리를 권한(privilage)이 없어 테이블로 저장하거나 View로 만들 수 없다.

이럴때 어떻게해야될까?

테이블을 만들지 않고 테이블을 만든것 같은 효과를 주는 with가 있다.

with를 사용하면 기존의 테이블이나 뷰를 대신할 수 있고, 더 간결하게 보일 수 있는 장점이 있다.

with를 사용하면 옵티마이저는 SQL을 인라인 뷰나 임시 테이블(temp table)로 판단한다.

with는 보통 통계, 계층 쿼리를 사용할 때 자주 쓰인다.

with를 사용하는 방법은 다음과 같다.

-- with 기본문법
with 'DB명'.'테이블명' AS( 쿼리 )
select * from 'DB명'.'테이블명';

 
--with 여러개  

WITH T1 AS (
SELECT '1' A FROM DUAL UNION ALL
SELECT '2' FROM DUAL
), 
T2 AS (
SELECT '3' A FROM DUAL UNION ALL
SELECT A FROM T1
)

SELECT * FROM T2

다음은 지난시간에 실습했던 서브쿼리 예제를 with구문를 사용하여 서브쿼리를 사용할 때 보다 SQL문을 간결하게 보여주겠다.

예를들어 employees 테이블을 보면 사원이 어디 부서에 속해있는지 알 수 없다.

그래서 우리는 서브쿼리를 사용했는데, 만약 이 SQL문을 계속 사용하거 응용할 경우 SQL문이 잡해지는데,with를 한번 만들어 사용하면 재사용이 편하고 코드의 가독성도 높아진다.

select c.* , d.dept_name
from (
select a.emp_no , a.first_name, last_name, b.dept_no, b.from_date dept_fr_dt, b.to_date dept_to_dt
 from employees.employees a left join employees.dept_emp b on a.emp_no = b.emp_no) c 
 left join employees.departments d on c.dept_no = d.dept_no ;  
 
 
 --with를 사용할 때 
 
 with emp_dept as
 (
 select c.* , d.dept_name
from (
select a.emp_no , a.first_name, last_name, b.dept_no, b.from_date dept_fr_dt, b.to_date dept_to_dt
 from employees.employees a left join employees.dept_emp b on a.emp_no = b.emp_no) c 
 left join employees.departments d on c.dept_no = d.dept_no 
 )
 
 select * from emp_dept;

Window function

분석함수로도 알려져 있 윈도우 함수는 행과 행간의 관계에서 다양한 연산 처리를 할 수 있는 함수이다.

윈도우함수는 일반 함수와 달리 중첩하여 호출 될 수는 없다. 즉 함수안에 함수를 호출하기 어렵다.

윈도우 함수에는 다양한 종류가 있다.

종류

설명

순위관련함수

RANK

DENSE_RANK

ROW_NUMBER

집계관련함수

SUM

MAX

MIN

AVG

COUNT

행순서관련함수

FIRST_VALUE

LAST_VALUE

LAG

LEAD

그룹내 비율관련함수

CUME_DIST

PERCENT_RANK

NTILE

RATIO_TO_REPORT

이중에 우리는 집계함수를 배운바 있었고, 현업에서 분석가들이 순위,행순서, 비율관련 함수를 자주 사용하니 이번 기회를 통해 알아두길 바란다.

윈도우함수의 기본문법은 다음과 같다.

SELECT 
       윈도우함수(인자) OVER (PARTITION BY 칼럼 ORDER BY 칼럼) 
       윈도우절 
  FROM 테이블명 
;

partition by는 전체 집합을 기준에 의해 소그룹으로 나눌 때, order by 어떤 항목에 대해 순위를 지정할 때 쓴. 윈도우 절은 함수의 대상이 되는 행 기준의 범위를 지정할 때 쓴다.

실습을 통해 윈도우 함수에 대해 알아보겠다.

순위함수 

SELECT * FROM ecommerce.employees;

--데이터 셋 변경
update ecommerce.employees set  birthdate = '1952-02-19 00:00:00' where EmployeeID =8;
-- 부서 컬럼 추가 초기 데이터 값 0 셋팅 ; 
alter table ecommerce.employees ADD `dept_no` int not null ;  
SELECT * FROM ecommerce.employees;
update ecommerce.employees set dept_no = 100 where EmployeeID =1 ;
update ecommerce.employees set dept_no = 100 where EmployeeID =2 ;
update ecommerce.employees set dept_no = 100 where EmployeeID =3 ;
update ecommerce.employees set dept_no = 101 where EmployeeID =4 ;
update ecommerce.employees set dept_no = 102 where EmployeeID =5 ;
update ecommerce.employees set dept_no = 102 where EmployeeID =6 ;
update ecommerce.employees set dept_no = 102 where EmployeeID =7 ;
update ecommerce.employees set dept_no = 102 where EmployeeID =8 ;
update ecommerce.employees set dept_no = 103 where EmployeeID =9 ;
update ecommerce.employees set dept_no = 103 where EmployeeID =10 ;
SELECT * FROM ecommerce.employees;

SELECT a.*, rank() over(order by a.birthdate) as 'RANK',  
dense_rank() over(order by a.birthdate) as 'dense_RANK',
ROW_NUMBER() over(order by a.birthdate) as 'ROW_NUMBER',
rank() over(partition by dept_no order by a.birthdate) as 'RANK_DEPT'
FROM ecommerce.employees a order by BirthDate asc  ;

위 코드를 실행하면 다음과 같은 결과를 얻는다.

빠른 생일순으로 순위를 매긴다고 했을 때 RANK, DENSERANK, ROW_NUMBER 가 어떻게 변하는지 보자.

Rank 함수의 경우 생일이 같은 경우 같은 순위를 매기고 (1,2,2) 그 다음 3이 아닌 4 넘어간다.

DENSE_Rank 함수의 경우 생일이 같은 경우 같은 순위를 매기고 (1,2,2) 그 다음 3이 나오고,

ROWNUMBER는 생일이 같음에도 1,2,3,4 순으로 나온다.

또한 partition by 옵션을 추가 시 해당 컬럼 별에 따라 그룹단위로 순위가 달라지게 된다.

집계함수

다음은 사원들의 연봉을 집계함수를 사용해 부서별 연봉합계가 어떻게 되는지 알아보겠다.

select * from ecommerce.employees;
alter table ecommerce.employees ADD `연봉` int ;
update ecommerce.employees set 연봉 = 1000 where EmployeeID =1 ;
update ecommerce.employees set 연봉 = 1100 where EmployeeID =2 ;
update ecommerce.employees set 연봉 = 1000 where EmployeeID =3 ;
update ecommerce.employees set 연봉 = 2000 where EmployeeID =4 ;
update ecommerce.employees set 연봉 = 2000 where EmployeeID =5 ;
update ecommerce.employees set 연봉 = 2000 where EmployeeID =6 ;
update ecommerce.employees set 연봉 = 2200 where EmployeeID =7 ;
update ecommerce.employees set 연봉 = 3000 where EmployeeID =8 ;
update ecommerce.employees set 연봉 = 3000 where EmployeeID =9 ;
update ecommerce.employees set 연봉 = 6000 where EmployeeID =10 ;
select * from ecommerce.employees;

SELECT employeeid, dept_no, 연봉 ,
sum(a.연봉) over(partition by a.dept_no ) as '각 부서의 연봉총합' ,
sum(a.연봉) over(partition by a.dept_no order by a.연봉 range UNBOUNDED preceding	) as '각 부서의 연봉누적총합',
max(a.연봉) over(partition by a.dept_no 	) as '각 부서의 연봉최고',
min(a.연봉) over(partition by a.dept_no 	) as '각 부서의 연봉최저',
avg(a.연봉) over(partition by a.dept_no) as '각 부서의 연봉평균',
TRUNCATE(avg(a.연봉) over(partition by a.dept_no),0) as '각 부서의 연봉평균', -- truncate 까먹을 것 같아
sum(a.연봉) over(partition by a.dept_no order by a.연봉 ROWS between 1 preceding  and 1 following	) as '내 앞뒤 연봉누적총합' 
from ecommerce.employees a ;

행순서 함수

이번에는 행 순서관련 함수에 대해 알아보겠다.

행 순서 관련 함수는 데이터분석가들이 주가지수, 전월매출,실적비교 등 시계열 데이터를 다룰때 사용한다.

그 중 LAG와 LEAD를 필히 기억해두길 바란다. LAG는 바로 이전의 행의 값을 가져오고, LEAD는 그 다음 행의 값을 가져온다. 이 떄 숫자 옵션에 따라 몇 행의 값을 가져올지 결정한다.

다음 아래 예제는 mysql에서 기본적으로 제공하는 employees DB의 salary 테이블을 가지고 실습을 진행해 보았다.

SELECT a.* , first_value(salary) over(partition by emp_no order by from_date rows unbounded preceding) as '입사 해 첫 연동', 
last_value(salary) over(partition by emp_no order by from_date rows between current row and unbounded following) as '퇴사전 그 회사에서의 마지막 연봉',
LAg(salary, 1) over(partition by emp_no order by from_date ) as '직전 연봉',
LEAD(salary, 1) over(partition by emp_no order by from_date ) as '그 다음 해 연봉'
FROM employees.salaries  a;

SQL문을 실행해보면 rows unbounded preceding, rows between current row and unbounded following가 있다.

rows unbounded preceding는 맨 앞에 있는 행을 기준으로 가져오는 것이고, rows between current row and unbounded following은 partition by '컬럼'을 기준에 의한 맨마지막까지 행을 나열한 후 그 마지막 row 값을 가져온다.

그룹내 비율함수

다음은 그룹내 비율을 구할 때 사용하는 함수를 알아보겠다.

ORACLE에서는 해당 소계가 전체대비 몇 %의 비율을 차지하는지 ratio_to_report 함수를 사용하면 쉽게 알 수 있다. 하지만 mysql에서는 제공하지 않아 이를 직접 구현해보려 한다.

실습 데이터는 w3school에서 제공하는 ecommerce DB 데이터를 활용하겠다.

select dept_no , count(EmployeeID) as '부서인원수' , 
sum(`연봉`) as '부서 총 연봉', 
sum(sum(`연봉`)) over() as '이 회사 구성원의 총 연봉',
sum(`연봉`) * 100 / sum(sum(`연봉`))  over() as '비율 = ratio_to_report'  
from ecommerce.employees group by dept_no ;

위 SQL문의 결과를 보면 알 수 있듯이 이 회사의 각 부서가 차지하는 연봉의 비율을 알 수 있다.

여기서 with rollup 함수를 같이 사용한다면 sum(연봉) * 100 * 2 를 하여 마지막행에서 총 인원수, 총 비까지 알아 낼 수 있다.

select * from ecommerce.employees ;
select dept_no , count(EmployeeID) as '부서인원수' , 
sum(`연봉`) as '부서 총 연봉', 
sum(sum(`연봉`)) over() as '이 회사 구성원의 총 연봉',
sum(`연봉`) * 100  * 2 / sum(sum(`연봉`))  over() as '비율 = ratio_to_report'  
from ecommerce.employees group by dept_no with rollup ;

다음은 부서별 총 연봉순서에 대한 백분율을 구하기 위해 percent_to_rank 함수를 사용하겠다. 

select a.*, percent_rank() over(order by `부서 총 연봉` ) * 100 as `부서별연봉비율순서백분율`
from  
(select dept_no , count(EmployeeID) as '부서인원수' , 
sum(`연봉`) as '부서 총 연봉', 
sum(sum(`연봉`)) over() as '이 회사 구성원의 총 연봉',
sum(`연봉`) * 100   / sum(sum(`연봉`))  over() as '비율 = ratio_to_report'  
from ecommerce.employees group by dept_no ) a

다음은 NTILE 함수를 사용하여 부서별 연봉비율에 대한 순서를 4등분하여 보여주겠다.

select a.*, percent_rank() over(order by `부서 총 연봉` ) * 100 as `부서별연봉비율순서백분율`
,NTILE(4) over (order by '부서 총 연봉') as '부서별연봉비율순서4등분결과'
from  
(select dept_no , count(EmployeeID) as '부서인원수' , 
sum(`연봉`) as '부서 총 연봉', 
sum(sum(`연봉`)) over() as '이 회사 구성원의 총 연봉',
sum(`연봉`) * 100   / sum(sum(`연봉`))  over() as '비율 = ratio_to_report'  
from ecommerce.employees group by dept_no ) a

데이터 출처

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

실전 데이터분석

실무에서 데이터분석 과정이 어떻게 이루어지는지 알아보자. 분석절차는 다음과 같다.

  1. 문제정의 (interview)

  2. 분석방법탐색 (research)

  3. 데이터수집 또는 가공 (Data Handling)

  4. 분석(analysis)

  5. 인사이트 도출 (report)

문제정의

데이터분석을 시작하기 전 가장 먼저 해야될 일은 고객사에서 혹은 자신이 속한 조직내에서 해결해야 될 문제가 무엇인지 파악하는 것이다.

하지만 실제 현장에 가보면 대부분 조직에서 이를 파악하지 못하고 있는 경우가 많다.

기업에서 해결해야 될 문제는 다른말로 우리의 사업이 잘 진행되고 있는가?와 유사하다.

잘 진행되고 있는지 파악하려면 비즈니스 현황을 파악해야 한다. 이때 데이터를 통해 파악이 가능하다.

지금부터 실습을 통해 알아보려 한다.

PreviousSQLNext품질 & RiskManagement

Last updated