create table boston
(b_id number(20,2),
CRIM number(20,5),
ZN number(20,5),
INDUS number(20,5),
CHAS number(20,5),
NOX number(20,5),
RM number(20,5),
AGE number(20,5),
DIS number(20,5),
RAD number(20,5),
TAX number(20,5),
PTRATIO number(20,5),
B number(20,5),
LSTAT number(20,5),
price number(20,5));
2) 찰스강 경계에 있는 집값의 평균값과 찰스강 경계에 있지 않는 집값의 평균값을 먼저 구해서 어디가 더 비싼지를 비교
select round(avg(price)) as 강주변o
from boston
where chas = 1;
select round(avg(price)) as 강주변x
from boston
where chas = 0;
3) 집값을 예측하는 머신러닝 모델
select * from boston;
DROP TABLE SETTINGS_REG2;
CREATE TABLE SETTINGS_REG2
AS
SELECT *
FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
WHERE SETTING_NAME LIKE '%GLM%';
select * from settings_reg2;
BEGIN
INSERT INTO SETTINGS_REG2
VALUES (DBMS_DATA_MINING.ALGO_NAME,'ALGO_GENERALIZED_LINEAR_MODEL');
INSERT INTO SETTINGS_REG2
VALUES (DBMS_DATA_MINING.PREP_AUTO, 'ON');
COMMIT;
END;
/
select * from settings_reg2;
-- 4. 머신러닝 모델을 생성합니다.
BEGIN
DBMS_DATA_MINING.DROP_MODEL('MD_REG_MODEL2');
END;
/
-- 모델을 생성하면서 바로 학습 데이터로 학습까지 수행합니다.
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
MODEL_NAME => 'MD_REG_MODEL2', -- 모델이름
MINING_FUNCTION => DBMS_DATA_MINING.REGRESSION, -- 회귀모델 생성
DATA_TABLE_NAME => 'BOSTON', -- 훈련 데이터 테이블명
CASE_ID_COLUMN_NAME => 'B_ID', -- emp 로 치면 empno 같은 컬럼
TARGET_COLUMN_NAME => 'PRICE', -- 정답 데이터 컬럼
SETTINGS_TABLE_NAME => 'SETTINGS_REG2'); -- 환경 설정 테이블명
END;
/
-- 5. 생성된 머신러닝 모델을 확인합니다.
SELECT MODEL_NAME,
ALGORITHM,
MINING_FUNCTION
FROM ALL_MINING_MODELS
WHERE MODEL_NAME = 'MD_REG_MODEL2';
-- 6. 머신러닝 모델 구성 정보를 확인합니다.
SELECT SETTING_NAME, SETTING_VALUE
FROM ALL_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = 'MD_REG_MODEL2';
-- 7. 회귀분석 모델의 회귀계수를 확인합니다.
SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, ROUND(COEFFICIENT), P_VALUE
FROM TABLE (DBMS_DATA_MINING.GET_MODEL_DETAILS_GLM ('MD_REG_MODEL2'));
4) 가설검정만 ( 귀무가설: 찰스강 경계에 집이 위치하는 것은 집값에 영향이 없다. /대립가설: 찰스강 경계에 집이 위치하는것은 집값에 영향이 있다. / 신뢰 수준은 95%)
신뢰수준 95%
p-value 가 0.05보다 작기 때문에 귀무가설은 기각되고 대립가설이 채택된다. 따라서 찰스강 경계에 집이 위치하는 것은 집값에 영향이 있다.
[TIL 21] 240612
※ SQL 포트폴리오시 회귀분석으로 가설검정 할 때 주의사항!
1. 문자 칼럼은 빼고 숫자 데이터만 가지고 회귀분석을 해야 합니다. 2. 훈련 데이터와 테스트 데이터를 나눌지 말지를 결정해줘야 합니다. 데이터의 양이 작으면 그냥 나누지 말고 통으로 다 분석하고 데이터의 양이 많으면 훈련과 테스트를 8:2로 나눠서 머신러닝 시키면 됩니다
* 적합한 머신러닝 모델 선정하기
1. 학습데이터가 숫자로만 구성
회귀 모델, 신경망 모델
2. 학습데이터가 문자로만 구성
나이브 베이즈 모델
3. 학습데이터가 숫자 & 문자로 구성
의사결정트리 모델, 랜덤 포레스트
머신러닝 모델 만들 때 수치 예측을 하는 거면 예측값과 실제값의 상관계수를 구하면 되고 분류 모델을 만드는것면 실제값과 예측값이 서로 같은지를 비교해서 정확도를 구하면 됩니다.
119. 데이터 딕셔너리
예제 119번. 데이터 딕셔너리 데이터 딕셔너리(data dictionary)는 데이터 사전입니다. 데이터 사전은 db에 존재하는 데이터에 대한 자세한 정보들이 들어있습니다.
- 내가 가지고 있는 테이블이 뭐뭐가 있는지? - 이 테이블을 select 할 때 너무 검색이 느린데 인덱스가 있는지 확인하고 싶다?
문제 1. c##scott유저로 접속해서 c##scott유저가 소유하고 있는 테이블들 리스트를 조회하시오.
1) 유저가 소유하고 있는 테이블 확인
select table_name
from user_tables;
2) 유저가 소유하고 있는 객체들 + 다른유저가 권한을 준 객체들 테이블 확인
select table_name
from all_table;
3) database에 있는 모든 객체 테이블 확인
select count(*)
from dba_tables;
--2831개
문제 2. 내가 조회하는 테이블들의 건수가 어떻게 되는지 확인하시오.
select table_name, num_rows, last_analyzed
from user_tables;
문제 3. c##scott 유저가 가지고 있는 모든 테이블들에 대해서 통계정보를 수집하시오.
1)Oracle 데이터베이스에서 통계를 수집하는 프로시저를 실행
exec dbms_stats.gather_schema_stats('C##SCOTT');
-- bms_stats.gather_schema_stats() 함수는 C##SCOTT 스키마에 대한 통계를 수집함.
select table_name, num_rows, last_analyzed
from user_tables
order by num_rows desc nulls last;
-- bms_stats.gather_schema_stats() 함수는 C##SCOTT 스키마에 대한 통계를 수집함. 설명: 내가 데이터 분석을 해야 하는 테이블들이 뭐가 있고 제일 큰 테이블의 건수가 뭐가 있구나 파악할 때 사용
문제 4. 지금 내가 분석하려는 테이블을 고객사에 언제 생성했는지 확인하시오.
select *
from user_objects
where object_name in ('EMP','DEPT');
문제 5. EMP과 DEPT테이블이 어떤 용도의 테이블인지 그 설명을 확인하시오.
select *
from user_tab_comments
where table_name in ('EMP','DEPT');
문제 6.emp 테이블에 대한 주석을 남기시오.
comment on table emp is '사원들에 대한 자세한 정보가 있는 테이블입니다.';
문제 7. emp 테이블의 칼럼들에 대한 설명이 있는 데이터 사전을 조회하고 주석을 추가하시오.
select *
from user_col_comments
where table_name='EMP';
comment on column emp.empno is '사원번호에 대한 데이터 입니다';
comment on column emp.ename is '사원이름에 대한 데이터 입니다';
comment on column emp.sal is '사원 월급에 대한 데이터 입니다';
comment on column emp.job is '직업에 대한 데이터 입니다';
comment on column emp.mgr is '관리자번호에 대한 데이터 입니다';
comment on column emp.hiredate is '입사일에 대한 데이터 입니다';
comment on column emp.deptno is '부서번호에 대한 데이터 입니다';
comment on column emp.comm is '커미션에 대한 데이터 입니다';
문제 8. 데이터의 품질을 확인하기 위해 EMP 테이블에 걸려있는 제약이 무엇이 있는지 조회하시오!
1) 어떠한 테이블에 제약이 있는지 확인
select *
from user_constraints
where table_name='EMP';
2) 어떠한 컬럼에 제약이 있는지 확인
select *
from user_cons_columns
where table_name='EMP';
- not null제약과 unique제약이 있는 테이블을 분석했을 때 분석결과가 대체로 좋음
문제 9. 위와 같이 데이터 사전이 뭐가 있는지 쉽게 알아낼 수 있는 방법(딕셔너리 조회)
select *
from dictionary
where table_name like '%INDEX%';
문제 10. TABLE이라는 단어를 포함하는 딕셔너리가 뭐가 있는지 조회하시오.
select *
from dictionary
where table_name like '%TABLE%';
문제 11. 아래의 SQL이 너무 느려서 INDEX 여부 확인하기.
1) 인덱스 여부 확인
select *
from user_indexes
where table_name ='EMP';
2) 어느 컬럼의 인덱스가 있는지 확인
select *
from user_ind_columns
where table_name ='EMP';
120. 치환변수(&)
예제 120. 치환변수 사용하기 - 치환변수란? 1) 반적으로 물어보는 쿼리문을 쉽게 작성할 수 있음. 2) 간단한 조회 화면을 만들 수 있음
문제 1. 사원번호를 물어보게 하고 사원번호를 입력받아 사원번호와 이름과 월급이 출력되게 하시오!
select empno, ename, sal
from emp
where empno = &empno;
문제 2. 물어보는 메시지를 다르게 변경해서 물어보게 하시오!
accept p_empno prompt '사원번호를 입력해주세요 ~ '
select empno, ename, sal
from emp
where empno = &empno;
문제 3. 사원 이름을 물어보게 하고 사원이름을 입력하면 해당사원의 이름과 월급, 직업이 출력되게 하시오.
accept p_ename prompt '사원이름를 입력해주세요 ~ '
select ename, sal, job
from emp
where ename = '&p_ename';
문제 4. 위의 스크립트를 실행할 때 소문자로 입력해도 결과가 출력되게 수정하시오
accept p_ename prompt '사원이름를 입력해주세요 ~ '
select ename, sal, job
from emp
where upper(ename) = upper('&p_ename');
문제 5. 직업을 물어보게 하고 직업을 입력하면 해당 직업인 사원들의 이름과 직업, 월급이 출력되게 하시오 (소문자로 입력되어도 출력되게!)
accept p_job prompt '직업를 입력해주세요 ~ '
select ename, sal, job
from emp
where upper(job) = upper('&p_job');
121. NAIVEBAYES (학습할 데이터가 전부 문자)
예제 121번. SQL로 머신러닝 구현하기 - NAIVEBAYES 독감 환자의 빅데이터를 기계 학습 시켜서 만든 머신러닝 모델입니다. 이 머신러닝 모델이 사용하는 알고리즘이 나이브베이스 확률입니다.
1) 학습할 데이터가 전부 숫자
회귀분석
2) 학습할 데이터가 전부 문자
나이브 베이즈 확률
오라클의 강력한 머신러닝 패키지를 이용해서 구현을 해보겠습니다.
실습 1.
-- 예제_179 SQL로 머신러닝 구현하기1(NAIVEBAYES)
-- 1. 머신러닝 모델을 학습시킬 학습 데이터를 테이블로 생성합니다.
DROP TABLE NAIVE_FLU_TRAIN;
CREATE TABLE NAIVE_FLU_TRAIN
( PATIENT_ID NUMBER(10),
CHILLS VARCHAR2(2),
RUNNY_NOSE VARCHAR2(2),
HEADACHE VARCHAR2(10),
FEVER VARCHAR2(2),
FLU VARCHAR2(2));
INSERT INTO NAIVE_FLU_TRAIN VALUES(1,'Y','N','MILD','Y','N');
INSERT INTO NAIVE_FLU_TRAIN VALUES(2,'Y','Y','NO' ,'N', 'Y');
INSERT INTO NAIVE_FLU_TRAIN VALUES(3,'Y','N','STRONG','Y','Y');
INSERT INTO NAIVE_FLU_TRAIN VALUES(4,'N','Y','MILD','Y','Y');
INSERT INTO NAIVE_FLU_TRAIN VALUES(5,'N','N','NO','N','N');
INSERT INTO NAIVE_FLU_TRAIN VALUES(6,'N','Y','STRONG','Y','Y');
INSERT INTO NAIVE_FLU_TRAIN VALUES(7,'N','Y','STRONG','N','N');
INSERT INTO NAIVE_FLU_TRAIN VALUES(8,'Y','Y','MILD','Y','Y');
COMMIT;
SELECT * FROM NAIVE_FLU_TRAIN;
-- 2. 학습한 머신러닝 모델을 테스트할 테이블을 생성합니다.
DROP TABLE NAIVE_FLU_TEST;
CREATE TABLE NAIVE_FLU_TEST
( PATIENT_ID NUMBER(10),
CHILLS VARCHAR2(2),
RUNNY_NOSE VARCHAR2(2),
HEADACHE VARCHAR2(10),
FEVER VARCHAR2(2),
FLU VARCHAR2(2));
INSERT INTO NAIVE_FLU_TEST VALUES(9,'Y','N','MILD','N', NULL);
COMMIT;
SELECT * FROM NAIVE_FLU_TEST;
-- 3. 머신러닝 모델 환경설정 테이블을 생성합니다.
DROP TABLE SETTINGS_GLM;
CREATE TABLE SETTINGS_GLM
AS
SELECT *
FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
WHERE SETTING_NAME LIKE '%GLM%';
BEGIN
INSERT INTO SETTINGS_GLM
VALUES (DBMS_DATA_MINING.ALGO_NAME, 'ALGO_NAIVE_BAYES');
INSERT INTO SETTINGS_GLM
VALUES (DBMS_DATA_MINING.PREP_AUTO, 'ON');
COMMIT;
END;
/
-- 4. 머신러닝 모델을 생성합니다.
BEGIN
DBMS_DATA_MINING.DROP_MODEL('MD_CLASSIFICATION_MODEL');
END;
/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
MODEL_NAME => 'MD_CLASSIFICATION_MODEL',
MINING_FUNCTION => DBMS_DATA_MINING.CLASSIFICATION,
DATA_TABLE_NAME => 'NAIVE_FLU_TRAIN',
CASE_ID_COLUMN_NAME => 'PATIENT_ID',
TARGET_COLUMN_NAME => 'FLU',
SETTINGS_TABLE_NAME => 'SETTINGS_GLM');
END;
/
-- 5. 머신러닝 모델이 잘 생성되었는지 확인합니다.
SELECT MODEL_NAME,
ALGORITHM,
MINING_FUNCTION
FROM ALL_MINING_MODELS
WHERE MODEL_NAME = 'MD_CLASSIFICATION_MODEL';
-- 6. 나이브 베이즈 머신러닝 모델이 테스트 데이터에 대해 예측한 값을 확인합니다.
SELECT T.*,
PREDICTION (MD_CLASSIFICATION_MODEL USING *) 예측값
FROM NAIVE_FLU_TEST T;
문제 1 (참고 446pg) 길병원의 왓슨처럼 머신러닝 모델을 활용할 수 있는 화면을 만드시오. (위에서 만든 독감환자 여부를 예측하는 나이브베이즈 모델을 활용하여 치환변수를 사용한 스크립트를 구현하시오.
-- 예제_180 SQL로 머신러닝 구현하기2(NAIVEBAYES)
SET SERVEROUTPUT ON -- 결과를 프린트하는 dbms_output이라는 패키지를 작동시키려면 serveoutput sqlplua 명령어를 on으로 설정합니다.
SET VERIFY OFF -- 치환변수에 입력되기 전값과 입력된 후 값을 화면이 출력되지 않기위한 명령어
ACCEPT P_CHILLS PROMPT '오한이 있습니까(Y/N) ? '
ACCEPT P_RUNNY_NOSE PROMPT '콧물이 있습니까(Y/N) ? '
ACCEPT P_HEAD_ACHE PROMPT '두통이 있습니까(STRONG/MILD/NO) ? '
ACCEPT P_FEVER PROMPT '열이 있습니까(Y/N) ? '
DECLARE
V_PRED VARCHAR2(20); --예측한 정답을 담을 변수
V_PROB NUMBER(10,2); --예측한 확률을 담을 변수
BEGIN
WITH TEST_DATA AS (SELECT UPPER('&P_CHILLS') CHILLS ,
UPPER('&P_RUNNY_NOSE') RUNNY_NOSE,
UPPER('&P_HEAD_ACHE') HEADACHE,
UPPER('&P_FEVER') FEVER
FROM DUAL )
SELECT PREDICTION (MD_CLASSIFICATION_MODEL USING *),
PREDICTION_PROBABILITY(MD_CLASSIFICATION_MODEL USING * ) INTO V_PRED, V_PROB
FROM TEST_DATA;
IF V_PRED ='Y' THEN
DBMS_OUTPUT.PUT_LINE('머신러닝이 예측한 결과: 독감입니다. 독감일 확률은 ' || ROUND(V_PROB,2) * 100 || '%입니다');
ELSE
DBMS_OUTPUT.PUT_LINE('머신러닝이 예측한 결과: 독감이 아닙니다. 독감이 아닐 확률은 ' || ROUND(V_PROB,2) * 100 || '%입니다');
END IF;
END;
/
문제 2. 위 머신러닝 스크립트를 활용해서 지방간 여부를 판정하는 모델을 생성하시오.
-- 3. 머신러닝 모델 환경설정 테이블을 생성합니다.
DROP TABLE SETTINGS_GLM;
CREATE TABLE SETTINGS_GLM
AS
SELECT *
FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
WHERE SETTING_NAME LIKE '%GLM%';
BEGIN
INSERT INTO SETTINGS_GLM
VALUES (DBMS_DATA_MINING.ALGO_NAME, 'ALGO_NAIVE_BAYES');
INSERT INTO SETTINGS_GLM
VALUES (DBMS_DATA_MINING.PREP_AUTO, 'ON');
COMMIT;
END;
/
-- 4. 머신러닝 모델을 생성합니다.
BEGIN
DBMS_DATA_MINING.DROP_MODEL('MD_CLASSIFICATION_MODEL');
END;
/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
MODEL_NAME => 'MD_CLASSIFICATION_MODEL',
MINING_FUNCTION => DBMS_DATA_MINING.CLASSIFICATION,
DATA_TABLE_NAME => 'FATLIVER',
CASE_ID_COLUMN_NAME => 'P_ID',
TARGET_COLUMN_NAME => 'FATLIVER',
SETTINGS_TABLE_NAME => 'SETTINGS_GLM');
END;
/
-- 5. 머신러닝 모델이 잘 생성되었는지 확인합니다.
SELECT MODEL_NAME,
ALGORITHM,
MINING_FUNCTION
FROM ALL_MINING_MODELS
WHERE MODEL_NAME = 'MD_CLASSIFICATION_MODEL';
-- 6. 나이브 베이즈 머신러닝 모델이 테스트 데이터에 대해 예측한 값을 확인합니다.
SELECT F.*,
PREDICTION (MD_CLASSIFICATION_MODEL USING *) 예측값
FROM FATLIVER F;
문제 3. 나이브 베이즈 모델을 활용하는 스크립트를 구현하시오
-- 예제_180 SQL로 머신러닝 구현하기2(NAIVEBAYES)
SET SERVEROUTPUT ON -- 결과를 프린트하는 dbms_output이라는 패키지를 작동시키려면 serveoutput sqlplua 명령어를 on으로 설정합니다.
SET VERIFY OFF -- 치환변수에 입력되기 전값과 입력된 후 값을 화면이 출력되지 않기위한 명령어
ACCEPT P_AGE PROMPT '나이대를 입력하세요 (예:40대) ? '
ACCEPT P_GENDER PROMPT '성별을 입력하세요(예:남자/여자) ? '
ACCEPT P_DRINK PROMPT '주량을 입력하세요 (음주많음/음주적음) ? '
ACCEPT P_SMOKING PROMPT '흡연여부를 입력하세요 (흡연/금연) ? '
DECLARE
V_PRED VARCHAR2(20); --예측한 정답을 담을 변수
V_PROB NUMBER(10,2); --예측한 확률을 담을 변수
BEGIN
WITH TEST_DATA AS (SELECT UPPER('&P_AGE') AGE ,
UPPER('&P_GENDER') GENDER,
UPPER('&P_DRINK') DRINK,
UPPER('&P_SMOKING') SMOKING
FROM DUAL )
SELECT PREDICTION (MD_CLASSIFICATION_MODEL USING *),
PREDICTION_PROBABILITY(MD_CLASSIFICATION_MODEL USING * ) INTO V_PRED, V_PROB
FROM TEST_DATA;
IF lower(V_PRED) ='yes' THEN
DBMS_OUTPUT.PUT_LINE('머신러닝이 예측한 결과: 지방간입니다. 지방간일 확률은 ' || ROUND(V_PROB,2) * 100 || '%입니다');
ELSE
DBMS_OUTPUT.PUT_LINE('머신러닝이 예측한 결과: 지방간이 아닙니다. 지방간이 아닐 확률은 ' || ROUND(V_PROB,2) * 100 || '%입니다');
END IF;
END;
/
122.NAIVEBAYES(2)
독버섯과 식용버섯을 분류하는 나이브 베이즈모델 만들기 (499PG) -> 이데이 터도 전부 문자로 되어있어서 나이브 모델로 학습하기 적합함
실습 1
-- 예제_181 SQL로 머신러닝 구현하기3(NAIVEBAYES)
-- 1. 버섯 데이터를 저장할 테이블을 생성합니다.
DROP TABLE MUSHROOMS;
CREATE TABLE MUSHROOMS
( ID NUMBER(10),
TYPE VARCHAR2(10),
CAP_SHAPE VARCHAR2(10),
CAP_SURFACE VARCHAR2(10),
CAP_COLOR VARCHAR2(10),
BRUISES VARCHAR2(10),
ODOR VARCHAR2(10),
GILL_ATTACHMENT VARCHAR2(10),
GILL_SPACING VARCHAR2(10),
GILL_SIZE VARCHAR2(10),
GILL_COLOR VARCHAR2(10),
STALK_SHAPE VARCHAR2(10),
STALK_ROOT VARCHAR2(10),
STALK_SURFACE_ABOVE_RING VARCHAR2(10),
STALK_SURFACE_BELOW_RING VARCHAR2(10),
STALK_COLOR_ABOVE_RING VARCHAR2(10),
STALK_COLOR_BELOW_RING VARCHAR2(10),
VEIL_TYPE VARCHAR2(10),
VEIL_COLOR VARCHAR2(10),
RING_NUMBER VARCHAR2(10),
RING_TYPE VARCHAR2(10),
SPORE_PRINT_COLOR VARCHAR2(10),
POPULATION VARCHAR2(10),
HABITAT VARCHAR2(10) );
--데이터 입력: SQL Developer를 이용하여 mushroons.csv 데이터를 MUSHROOMS 테이블에 로드 합니다.
SELECT COUNT(*) FROM MUSHROOMS;
-- 8124
-- 2. 버섯 데이터(mushrooms)를 훈련 데이터와 테스트 데이터는 9대1 비율로 나눕니다.
DROP TABLE MUSHROOMS_TRAINING;
CREATE TABLE MUSHROOMS_TRAINING
AS
SELECT *
FROM MUSHROOMS
WHERE ID < 7312;
DROP TABLE MUSHROOMS_TEST;
CREATE TABLE MUSHROOMS_TEST
AS
SELECT *
FROM MUSHROOMS
WHERE ID >= 7312;
-- 3. 머신러닝 모델 환경설정 테이블을 생성합니다.
DROP TABLE SETTINGS_GLM;
CREATE TABLE SETTINGS_GLM
AS
SELECT *
FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
WHERE SETTING_NAME LIKE '%GLM%';
BEGIN
INSERT INTO SETTINGS_GLM
VALUES (DBMS_DATA_MINING.ALGO_NAME, 'ALGO_NAIVE_BAYES');
INSERT INTO SETTINGS_GLM
VALUES (DBMS_DATA_MINING.PREP_AUTO, 'ON');
COMMIT;
END;
/
-- 4. 머신러닝 모델을 생성합니다.
BEGIN
DBMS_DATA_MINING.DROP_MODEL('MD_CLASSIFICATION_MODEL');
END;
/
BEGIN
DBMS_DATA_MINING.CREATE_MODEL(
MODEL_NAME => 'MD_CLASSIFICATION_MODEL',
MINING_FUNCTION => DBMS_DATA_MINING.CLASSIFICATION,
DATA_TABLE_NAME => 'MUSHROOMS',
CASE_ID_COLUMN_NAME => 'ID',
TARGET_COLUMN_NAME => 'TYPE',
SETTINGS_TABLE_NAME => 'SETTINGS_GLM');
END;
/
-- 5. 머신러닝 모델이 잘 생성되었는지 확인합니다.
SELECT MODEL_NAME,
ALGORITHM,
CREATION_DATE,
MINING_FUNCTION
FROM ALL_MINING_MODELS
WHERE MODEL_NAME = 'MD_CLASSIFICATION_MODEL';
-- 6. 머신러닝 모델 구성 정보를 확인합니다.
SELECT SETTING_NAME, SETTING_VALUE
FROM ALL_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = 'MD_CLASSIFICATION_MODEL';
-- 7. 나이브 베이즈 머신러닝 모델이 테스트 데이터에 대해 예측한 값을 확인합니다.
SELECT ID, CAP_SHAPE, CAP_SURFACE, CAP_COLOR, BRUISES, ODOR, TYPE 실제값,
PREDICTION (MD_CLASSIFICATION_MODEL USING *) 예측값
FROM MUSHROOMS_TEST T
WHERE id in (7620, 7621, 7622, 7623);
-- 8. 나이브 베이즈 머신러닝 모델의 정확도를 확인합니다.
SELECT SUM(DECODE(P.MODEL_PREDICT_RESPONSE, I.TYPE, 1,0)) / COUNT(*) 정확도
FROM (
SELECT ID, PREDICTION (MD_CLASSIFICATION_MODEL USING *) MODEL_PREDICT_RESPONSE
FROM MUSHROOMS_TEST T ) P, MUSHROOMS I
WHERE P.ID= I.ID;
1) 머신러닝 모델 확인
2) 머신러닝 모델 구성정보
3) 예측값
4) 정확도
123. 다중테이블 INSERT문 유형 4가지
예제 123. 다중테이블 INSERT문 유형 4가지
서브쿼리를 사용한 INSERT문을 이용해서 여러 개의 테이블에 동시에 데이터를 입력하는 INSERT문
1. unconditional all insert문 (무조건 insert)
무조건 모두 insert 함
insert all into 데이터 넣을 테이블명 values(데이터 넣을 칼럼명) into 데이터 넣을 테이블명 values(데이터 넣을 칼럼명) ... select 참조할 칼럼명 from 참조할 테이블명 where 검색조건;
2. conditional insert all문 (조건부 insert all)
조건부로 insert 함
insert all when sal >=2500 then into emp_hisal values(ename, sal) when sal >= 1000 then into emp_midsal values(ename,sal) else into emp_lowsal values(ename,sal)
select ename,sal from emp;
설명: emp_midsal의 경우에는 월급이 1000 ~ 2500 사이의 데이터가 아니라 월급이 1000 이상이면 다 입력되었습니다.
3. conditional insert first 문
처음에 나온 조건 다음의 조거
insert first when sal >=2500 then into emp_hisal values(ename, sal) when sal >= 1000 then into emp_midsal values(ename,sal) else into emp_lowsal values(ename,sal)
select ename,sal from emp;
설명: emp_midsal의 경우에는 월급이 1000 ~ 2500 사이의 데이터가 입력되었습니다.
4. pivoting insert
잘못된 칼럼 구조를 갖고 있는 테이블로부터 데이터를 옮길 때 사용합니다. pivoting 은 비관계형 데이터 베이스 테이블과 같은 입력 스트림의 각 레코드를 관계형 데이터베이스 테이블 환경의 다중 레코드로 변환하는 작업을 수행하는 insert 문입니다.
INSERT ALL INTO sales_data VALUES (ename, week_id, 'Monday', mon_sales) INTO sales_data VALUES (ename, week_id, 'Tuesday', tue_sales) INTO sales_data VALUES (ename, week_id, 'Wednesday', wed_sales) INTO sales_data VALUES (ename, week_id, 'Thursday', thur_sales) INTO sales_data VALUES (ename, week_id, 'Friday', fri_sales) SELECT * FROM sales;
문제 1. emp89와 emp90 테이블을 생성한 후 emp 테이블에서 월급이 2500 이상인 조건 데이터를 2개의 테이블에 동시에 입력하시오.
1) 테이블 생성
create table emp89
as
select ename, hiredate, sal
from emp
where 1=2 ;
create table emp90
as
select ename, job, sal
from emp
where 1=2 ;
2) 다중 테이블 데이터 삽입
insert all
into emp89 values(ename, hiredate,sal)
into emp90 values(ename, job, sal)
select *
from emp
where sal >= 2500;
문제 2. 아래 2개의 테이블을 생성하고 emp테이블에서 직업이 salesman인 사원들의 데이터를 아래의 개의 테이블에 동시에 입력하시오. (무조건 insert)
1) 테이블 생성
create table s_emp
as
select ename,empno, job
from emp
where 1=2;
create table sa_emp
as
select ename,sal, hiredate
from emp
where 1=2;
2) 다중테이블 데이터 삽입
insert all
into s_emp values(ename,empno, job)
into sa_emp values(ename,sal, hiredate)
select *
from emp
where job = 'SALESMAN';
문제 3. 월급이 2001~3000 사이의 월급을 받는 사원들은 emp_hisal에 입력하고 월급을 0~2000 사이에 받는 사원들은 emp_midsal에 입력하고 나머지는 emp_lowsal에 입력하시오.
insert first
when sal between 2001 and 3000 then
into emp_hisal values(ename,sal)
when sal between 0 and 2000 then
into emp_midsal values(ename,sal)
else
into emp_lowsal values(ename,sal)
select ename,sal
from emp;
문제 4. 아래 sql을 수행하여 테이블을 만드시오 (sales_info)
drop table sales_info;
create table sales_info
as
select empno, ename, sal
from emp
where 1=2;
create table sales_source_data
( empno, ename, sales_mon, sales_tue,sales_wed,sales_thur,sales_fri)
as
select empno, ename, sal, sal, sal, sal, sal
from emp;
insert all
into sales_info values(empno,ename,sales_mon)
into sales_info values(empno,ename,sales_tue)
into sales_info values(empno,ename,sales_wed)
into sales_info values(empno,ename,sales_thur)
into sales_info values(empno,ename,sales_fri)
select empno, ename,sales_mon,sales_tue,sales_wed,sales_thur, sales_fri
from sales_source_data;
select * from sales_info;
select * from sales_source_data;
문제 5. crime_week 테이블 데이터를 pivoting insert문으로 데이터를 입력할 테이블을 crime_week_pivot으로 만드시오.
★ 문제. crime_week 테이블 데이터를 crime_week_pivot 테이블에 pivoting insert문으로 데이터를 입력하고 절도가 가장 많이 일어난 요일과 건수, 순위를 출력하시오.
1)crime_week_pivot 테이블에 데이터 삽입
insert all
into crime_week_pivot values(c_big_type,c_small_type,'일요일',c_sun)
into crime_week_pivot values(c_big_type,c_small_type,'월요일',c_mon)
into crime_week_pivot values(c_big_type,c_small_type,'화요일',c_tue)
into crime_week_pivot values(c_big_type,c_small_type,'수요일',c_wed)
into crime_week_pivot values(c_big_type,c_small_type,'목요일',c_thur)
into crime_week_pivot values(c_big_type,c_small_type,'금요일',c_fri)
into crime_week_pivot values(c_big_type,c_small_type,'토요일',c_sat)
select *
from crime_week;
2) 테이블 확인
select * from crime_week_pivot;
3) 가장많은 요일 건수 순위 구하기
select week_day 요일,c_cnt 건수, dense_rank() over (order by c_cnt desc) 순위
from crime_week_pivot
where c_big_type like '%절도%';
