데이터 청년 캠퍼스/전처리
[전처리] 고급 SQL 이해와 활용(1)
뚱뚜루뚱
2022. 7. 21. 13:33
사용자 생성: SQL Plus
-- SQLPlus에서 system 계정으로 로그인
alter session set "_ORACLE_SCRIPT" = true ;
-- 사용자 생성
create user (사용자명) identified by (비밀번호)
default tablespace users quota unlimited on users ;
-- 권한 부여
grant connect, resource to (사용자명) ;
Why SQL
SQL은 데이터 전처리를 위한 강력한 수단이며, 데이터 전처리는 데이터 분석에서 아주 중요한 과정이다.
예제 데이터 모델 ERD (Entity Relationship Diagram)
Entity와 Relationship을 표현한 다이어그램
- 구성요소: 엔티티, 속성, 제약조건, 관계
- 관계 표현의 관점: 대응수 Mapping Cardinality, 필수 Mandatory / 선택 Optional, 절대종속 Strong / 상대종속 Weak
ERD 표기법
- DA#: 필수(실선) / 선택(점선). #은 primary key를, 1은 절대종속을 나타낸다.
- ER-win: 1 대 다의 관계를 표현한다. foreign key가 primal key의 일부가 되면 절대종속(실선)이며 그렇지 않으면 상대종속(점선)이다.
실습
기본 테이블 생성
DROP TABLE employees CASCADE CONSTRAINTS ;
CREATE TABLE employees (
emp_no NUMBER(6),
birth_date DATE NOT NULL,
first_name VARCHAR2(30) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
gender VARCHAR2(1) NOT NULL,
hire_date DATE NOT NULL,
CONSTRAINTS EMPLOYEES_PK PRIMARY KEY (emp_no),
CONSTRAINTS GENDER_CK CHECK (gender in ('M', 'F'))
);
DROP TABLE departments CASCADE CONSTRAINTS ;
CREATE TABLE departments (
dept_no VARCHAR2(4),
dept_name VARCHAR2(40) NOT NULL,
CONSTRAINTS DEPARTMENTS_PK PRIMARY KEY (dept_no),
CONSTRAINTS DEPARTMENTS_UK1 UNIQUE (dept_name)
);
DROP TABLE dept_manager CASCADE CONSTRAINTS ;
CREATE TABLE dept_manager (
emp_no NUMBER(6),
dept_no VARCHAR2(4) NOT NULL,
to_date VARCHAR2(10) NOT NULL,
from_date VARCHAR2(10) NOT NULL,
CONSTRAINTS DEPT_MANAGER_PK PRIMARY KEY (dept_no, to_date),
CONSTRAINTS DEPT_MANAGER_FK1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
CONSTRAINTS DEPT_MANAGER_FK2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
);
DROP TABLE dept_emp CASCADE CONSTRAINTS ;
CREATE TABLE dept_emp (
emp_no NUMBER(6),
dept_no VARCHAR2(4) NOT NULL,
to_date VARCHAR2(10) NOT NULL,
from_date VARCHAR2(10) NOT NULL,
-- CONSTRAINTS DEPT_EMP_PK PRIMARY KEY (emp_no, to_date),
CONSTRAINTS DEPT_EMP_FK1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
CONSTRAINTS DEPT_EMP_FK2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
);
DROP TABLE titles CASCADE CONSTRAINTS ;
CREATE TABLE titles (
emp_no NUMBER(6) NOT NULL,
to_date VARCHAR2(10) NOT NULL,
from_date VARCHAR2(10) NOT NULL,
title VARCHAR2(50) NOT NULL
-- CONSTRAINTS TITLES_PK PRIMARY KEY (emp_no, to_date),
-- CONSTRAINTS TITLES_FK1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE
);
DROP TABLE salaries CASCADE CONSTRAINTS ;
CREATE TABLE salaries (
emp_no NUMBER(6) NOT NULL,
to_date VARCHAR2(10) NOT NULL,
from_date VARCHAR2(10) NOT NULL,
salary NUMBER(7) NOT NULL
--CONSTRAINTS SALARIES_PK PRIMARY KEY (emp_no, to_date),
--CONSTRAINTS SALARIES_FK1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE
);
select count(*) from employees ;
select count(*) from departments ;
select count(*) from dept_manager ;
select count(*) from dept_emp ;
select count(*) from titles ;
select count(*) from salaries ;
데이터를 넣을 때 foreign key 참조와 무결성 제약조건 등을 지키기 위해 부모격에 해당하는 데이터를 먼저 넣는다. 이때 파일형식 헤더 포함여부와 열 정의에 주의한다
EMPLOYEES, DEPARTMENTS → DEPT_EMP, DEPT_MANAGER, TITLE, SALARIES
복제테이블 생성
-- 복제테이블 copy_ymd 생성
-- ymd 문자타입, ymd_date 문자타입
create table copy_ymd (
ymd varchar2(8) not null,
ymd_date date not null ) ;
-- 복제테이블 copy_ymd 데이터 생성
-- rownum 일련번호, 날짜타입은 산술연산이 가능하다
insert into copy_ymd
select to_char(to_date('19791231','yyyymmdd') + rownum, 'yyyymmdd')
, to_date('19791231','yyyymmdd') + rownum
from employees
where rownum <= 30000 ; -- rownum : STOP KEY
select max(ymd) from copy_ymd;
-- 모든 작업을 정상처리했다고 확정하며 변경된 내용을 영구저장
commit;
rownum은 원래 테이블에 부여된 값이 아니라 출력될 때 생성되는 값이다
-- rownum이란
select rownum, emp_no
from employees;
select emp_no, last_name
from employees
where rownum<=10;
select emp_no, last_name
from employees
where rownum<=1;
select emp_no, last_name
from employees
where rownum=2; #아무것도 출력되지 않는다
select *
from (
select emp_no, last_name, rownum rnum
from employees
where rownum<=2
)
where rnum=2;
달력 만들기
-- 바인드입력: 202207
select ceil((day+start_yoil-1)/7) 주차
, min(sun) 일
, min(mon) 월
, min(tue) 화
, min(wed) 수
, min(thu) 목
, min(fri) 금
, min(sat) 토
from (
select decode(mod(yoil, 7), 1, day) sun
, decode(mod(yoil, 7), 2, day) mon
, decode(mod(yoil, 7), 3, day) tue
, decode(mod(yoil, 7), 4, day) wed
, decode(mod(yoil, 7), 5, day) thu
, decode(mod(yoil, 7), 6, day) fri
, decode(mod(yoil, 7), 0, day) sat
, day
from (
select trunc(substr(ymd, 7),'0') day
, to_char(ymd_date, 'd') yoil
from copy_ymd
where ymd like :v_month||'%' )) a, ( select to_char(ymd_date, 'd') start_yoil
from copy_ymd
where ymd = :v_month||'01') b
group by ceil((day+start_yoil-1)/7)
order by 1 ;
카이제곱 검정 사례: 범주형 변수 간 연관성 분석
카이제곱 검정을 위한 scipy 패키지 중 stats 모듈 임포트: from scipy import stats
chis = stats.chisquare(obs_array, exp_array, ddof=v_ddof)
create table d_base3_2 (
시군 varchar2(12) not null,
지정구분 varchar2(20) not null ) ;
select * from d_base3_2 ;
select a.시군
, a.지정구분
, nvl(b.관측도수,0) 관측도수 /*관측되지않은 (시군+지정구분)은 0으로 처리*/
, a.기대도수_시군 * a.기대도수_지정구분 / a.기대도수_전체 기대도수
from ( select x.시군, y.지정구분
, x.기대도수_시군
, y.기대도수_지정구분
, x.기대도수_전체
from ( select 시군
, count(*) 기대도수_시군 /* 시군 속성의 cardinality */
, sum(count(*)) over () 기대도수_전체 /* 전체 행 개수 */
from d_base3_2
group by 시군 ) x,
( select 지정구분
, count(*) 기대도수_지정구분 /* 지정구분 속성의 cardinality */
from d_base3_2
group by 지정구분 ) y ) a,
( select 시군
, 지정구분
, count(*) 관측도수 /* 시군, 지정구분 별 실제 행 개수 */
from d_base3_2
group by 시군, 지정구분 ) b
where a.시군 = b.시군(+) /* 특정 (시군+지정구분) 값은 존재하지 않을 수
있어서 외부조인으로 처리 */
and a.지정구분 = b.지정구분(+) ;
제약조건 Intergrity Constraints
데이터베이스 무결성 보장을 위한 장치
제약 조건의 종류
- Not Null: Null을 허용하지 않음
- UNIQUE: 컬럼값의 유일성 보장
- Primary Key: 해당 테이블의 대표 속성으로서 Not Null과 Uniqueness 보장
- Foreign Key: 참조 무결성 보장을 위한 것으로, 참조하는 테이블의 기본키 값만 가질 수 있음
- Check: 입력될 수 있는 데이터의 범위 제한
참조 무결성 제약조건을 보장하기 위한 4가지 옵션
참조관계 테이블에서 부모 테이블 튜플 삭제 시 참조무결성 위배 가능성이 있으므로 이를 참조하는 자식 테이블 튜플의 처리방법이 4가지 존재한다
- RESTRICTED: 자식테이블에서 참조하고 있는 부모 테이블 튜플 삭제(수정) 거부
- CASCADE: 부모테이블 튜플 삭제(수정_ 시, 이를 참조하는 자식테이블 튜플도 같이 삭제(수정)
- DEFAULT: 부모테이블 튜플 삭제(수정_ 시, 이를 참조하는 자식테이블 튜플의 속성값을 미리 지정한 기본값 Default Value로 변경
- NULL: 부모테이블 튜플 삭제(수정_ 시, 이를 참조하는 자식테이블 튜플의 속성값을 NULL로 변경. 단 NULL값이 허용되어야한다.
데이터 정제
from_date보다 to_date를 PK로 하는 이유: PK는 자동으로 인덱스를 생성하며, 대부분의 질의가 현재 상태를 묻기에 to_date가 PK 인덱스를 가질 때 성능이 더 좋다.
-- 사원이 동시에 2개 부서에 소속된 이력 중 하나 제거
-- 동시에 두개 부서에 소속된 사원
select *
from dept_emp
where (emp_no, from_date) in (select emp_no, from_date
from dept_emp
group by emp_no, from_date
having count(*) > 1 ) ;