--SQL로 숫자 1~ 10을 출력하시오
select level as num
from dual
connect by level <= 10;예제. 위의 결과를 with절로 임시테이블을 생성하고 수행하시오
with gugu as (select level as num
from dual
connect by level <= 10 )
select *
from gugu;예제. 위의 SQL을 이용해서 구구단 2단을 출력하시오
with gugu as (select level as num
from dual
connect by level <= 10 )
select '2 x '|| num || ' = ' || 2*num as 구구단2단
from gugu;문제614. 구구단 2단을 출력하고 그 밑에 3단도 출력하시오
with gugu as (select level as num
from dual
connect by level <= 10 )
select '2 x '|| num || ' = ' || 2*num
from gugu
union all
select '3 x '|| num || ' = ' || 3*num
from gugu ;ㅁunion all에서는 컬럼별칭은 맨 위의 쿼리문에 사용해야 결과에 적용되고 order by 절은 맨 아래의 쿼리문에 사용해야 결과에 적용됨.
문제615. 구구단 전체를 SQL로 출력하시오
-- with절 2개
with gugu1 as (select level as num1
from dual
connect by level <= 9 ),
gugu2 as (select level as num2
from dual
connect by level <= 9 )
select num1 || ' x '|| num2 || ' = ' || num1*num2
from gugu1, gugu2
order by num1,num2 asc ;
-- self join
with gugu as (select level as num1
from dual
connect by level <= 9 )
select a.num1 || ' x '|| b.num1 || ' = ' || a.num1*b.num1
from gugu a, gugu b
order by a.num1,b.num1 ;문제616. 위의 결과에서 짝수단만 출력하시오
with gugu1 as (select level as num1
from dual
connect by level <= 9 ),
gugu2 as (select level as num2
from dual
connect by level <= 9 )
select num1 || ' x '|| num2 || ' = ' || num1*num2
from gugu1, gugu2
where mod(num1,2) = 0;
ㅁ데이터 분석 SQL문제
SQL로 회귀분석 (수치 예측)
예를 들어, 지방간의 가장 영향을 주는 요소는 무엇인가? 음주여부? 흡연여부? 나이? 성별? BMI지수? 체중
위의 데이터가 수천개가 있으면 위의 데이터를 회귀분석해서 다음과 같은 결과를 도출할 수 있음
1. 지방간에 영향을 주는 것 중 가장 중요한 요소는?
2. 새로운 환자가 왔을 때 당신이 지방간에 걸릴 확률이 몇 퍼센트 입니다.
ㅁ예제_193 sql로 머신러닝 구현하기15 (regression)
미국 대학교 입학점수(acceptance)에 영향을 미치는 컬럼이 학과점수(academic)인지 체육점수(sports)인지 아니면 음악점수(music)인지 알아보는 문제. 3개 중 가장 영향력이 큰 과목은?
200건의 데이터 중 180건은 회귀분석 모델을 만들기 위한 훈련데이터로 사용하고, 나머지 20건은 훈련된 회귀모델이 좋은 모델인지 확인하기 위해서 테스트용으로 별도의 테이블을 구성하겠음.
20건의 데이터로 기계학습한 회귀모델을 테스트해볼건데, 테스트를 하려면 정답(acceptance)과 예측을 서로 비교해서 얼마나 잘 맞췄는지 확인을 해보겠음.
기계를 학습 시키기위한 기계를 구성하는 데이터가 저장된 테이블 생성.
> 이 데이터는 학습할 데이터는 아니고 기계를 어떻게 셋팅하겠다는 테이블
지금부터 회귀모델을 만들거니가 회귀모델을 생성하도록 셋팅해라
| create table settings_reg1 as select * from table (dbms_data_mining.get_default_settings) where setting_name like '%glm%'; |
dbms_data_mining이란 오라클에서 만들어 놓은 머신러닝 패키지. R과 파이썬을 이용하지 않더라도 머신러닝 할 수 있음.
machine learning : 기계가 데이터를 보고 직접 학습해서 사람의 인지기능을 구사하는 것
위의 코드를 통해 컬럼 2개짜리 테이블만 생성했고 데이터는 이제 입력해줘야함.
입력할 데이터는 회귀모델(수치예측모델)을 구성해달라는 데이터
begin
insert into settings_reg1
values (dbms_data_mining.algo_name, 'algo_generalized_linear_model');
insert into settings_reg1
values (dbms_data_mining.prep_scale_2dnum, 'prep_scale_range');
commit;
end;
/| insert into settings_reg1 values (dbms_data_mining.algo_name, 'algo_generalized_linear_model'); |
> 수치를 예측하는 회귀모델을 만들겠다는 데이터
| insert into settings_reg1 values (dbms_data_mining.prep_scale_2dnum, 'prep_scale_range'); |
> 학습할 데이터를 표준화하겠다는 데이터
(예를들어, 지방간 수치 예측을 위해 체중(80)과 키(178) 정보를 사용한다면 체중과 키는 서로 단위가 다름. 둘 다 0~1 사이의 데이터가 되게끔 데이터 표준화시키는 것 = 데이터 표준화 또는 정규화 / 기계가 데이터를 보고 잘못 판단하지 않도록 단위를 동일하게 맞춰줌 )
-- 회귀모델 생성
| begin dbms_data_mining.drop_model('md_reg_model1'); end; / |
기존에 md_reg_model1이 있을지 모르니까 삭제하는 명령어 한번 입력
begin
dbms_data_mining.create_model(
model_name => 'md_reg_model1',
mining_function => dbms_data_mining.regression,
data_table_name => 'student_score_training',
case_id_column_name => 'st_id',
target_column_name => 'acceptance',
settings_table_name => 'settings_reg1');
end;
/model_name => 'md_reg_model1' 이라고 회귀모델명 지정. 모델명은 임의로 지정 가능
mining_function => dbms_data_mining.regression 은 회귀모델(수치예측)을 생성하겠다라고 지정
data_table_name => 'student_score_training'은 학습할 데이터가 있는 데이블명을 지정
case_id_column_name => 'st_id'에는 primary key에 해당될만한 컬럼을 기술하면 됨
target_column_name => 'acceptance'에는 정답 컬럼(acceptance)을 기술(기계가 학습데이터를 가지고 공부할 때는 정답이 존재해야함)
settings_table_name => 'settings_reg1'에는 위에서 만든 기계를 어떻게 학습시킬지에 대한 환경설정 테이블을 지정
-- 모델 생성여부 확인
SELECT MODEL_NAME,
ALGORITHM,
MINING_FUNCTION
FROM ALL_MINING_MODELS
WHERE MODEL_NAME = 'MD_REG_MODEL1';all_mining_models 안에 우리가 만든 모델(MD_REG_MODEL1)에 대한 정보가 들어가 있음.
-- 모델 구성 정보 확인
SELECT SETTING_NAME, SETTING_VALUE
FROM ALL_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = 'MD_REG_MODEL1';
위의 셋팅값들은 대부분 기본값으로 셋팅되어져 있음. 기본값 셋팅중에 PREP_AUTO가 ON으로 되어져 있는데, 이건 수치예측을 잘하는 모델로 탄생하게끔 오라클이 알아서 최적화 시켜주겠다는 것.
GLMS_CONF_LEVEL 신뢰수준이 .95 즉, 95% 신뢰구간이라는 뜻
신뢰수준이란 통계에서 어떠한 값이 알맞은 추정값이라고 믿을 수 있는 정도 > 유의수준이 0.05 라는 뜻.(유의수준은 얼마나 p-value가 유의하다고 결정할지 연구자가 미리 정하는 수준)
학과점수, 체육점수, 음악점수가 유의수준 안에서 즉 0.05보다 작은 값으로 p-value가 나온다면 입학점수에 영향력을 미치는 컬럼으로 볼 수 있는 것.
-- 테스트 데이터에 대해 회귀분석 모델이 예측한 예측점수를 확인
SELECT ST_ID 학생번호, ACADEMIC 학과점수, ROUND(MUSIC,2) 음악점수 ,
SPORTS 체육점수, ROUND(ACCEPTANCE,2) AS 실제점수, ROUND(MODEL_PREDICT_RESPONSE,2) AS 예측점수
FROM (
SELECT T.*, PREDICTION (MD_REG_MODEL1 USING *) MODEL_PREDICT_RESPONSE
FROM STUDENT_SCORE_TEST T
);-- 실제점수와 예측점수간의 상관계수값을 구해서 얼마나 점수가 비슷한지를 확인
상관계수 함수 = corr()
select corr( 실제점수, 예측점수 )
from (
SELECT ST_ID 학생번호, ACADEMIC 학과점수, ROUND(MUSIC,2) 음악점수 ,
SPORTS 체육점수, ROUND(ACCEPTANCE,2) AS 실제점수, ROUND(MODEL_PREDICT_RESPONSE,2) AS 예측점수
FROM (
SELECT T.*, PREDICTION (MD_REG_MODEL1 USING *) MODEL_PREDICT_RESPONSE
FROM STUDENT_SCORE_TEST T
)
);결과값 = 0.9771860426962691475641713976994838921189
1에 가까울 수록 상관관계가 높은 것.
0.97이라는 결과가 출력되었으므로, 상관관계가 높다고 볼 수 있음.
-- 회귀 모델의 결정계수 R 스퀘어 값을 확인
결정계수란 회귀분석에서 회귀모델이 데이터를 얼마나 잘 설명하고 있는지를 보여주는 지표.이 값도 1에 가까울 수록 좋은 모델임.
SELECT *
FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_GLOBAL(MODEL_NAME => 'MD_REG_MODEL1'))
WHERE GLOBAL_DETAIL_NAME IN ('R_SQ','ADJUSTED_R_SQUARE');
0.9의 결정계수가 출력되고 있음.
기계를 학습 시키는 사람이 예측을 잘하는 좋은 기계가 되도록 여러가지 조치를 해주고 결정계수가 올라가면 잘 조치한 것.
-- 입학점수에 영향력 있는 변수가 무엇인지 확인
SELECT ATTRIBUTE_NAME, COEFFICIENT
FROM TABLE (DBMS_DATA_MINING.GET_MODEL_DETAILS_GLM ('MD_REG_MODEL1'));
coefficient가 높을수록 영향력이 큰 변수.
체육점수가 입학점수에 가장 큰 영향을 주고 그 다음 학과점수, 음악점수 순이다.
p-value가 0.05보다 작아서 유의미한 변수들이라고 말할 수 있음.
문제617. (SQL로 회귀분석하기) 미국인의 의료비를 예측하는 회귀모델을 생성하기 위해서 insurance 테이블을 생성하고 데이터를 입력하세요.
ㅇinsurance 테이블 소개
| BMI | 비만지수 |
| CHILDREN | 자녀수 |
| SMOKER | 흡연여부 |
| REGION | 거주지(northeast, southeast, northwest, southwest) |
| EXPENSES | 보험료 |
-- 훈련데이터와 테스트 데이터로 분리
전체 1338건의 데이터 중 1114건의 데이터를 훈련데이터(83%)로, 나머지(17%)를 테스트 데이터로 구성.
CREATE TABLE INSURANCE_TRAINING
AS
SELECT *
FROM INSURANCE
WHERE ID < 1114;
CREATE TABLE INSURANCE_TEST
AS
SELECT *
FROM INSURANCE
WHERE ID >= 1114;
-- 머신러닝 모델의 환경 구성 테이블을 생성
기계세팅을 하는 데이터를 구성하는데 회귀모델을 생성할 것이고 기계가 알아서 모델을 최적화하라고 설정할 것.
CREATE TABLE SETTINGS_REG2
AS
SELECT *
FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
WHERE SETTING_NAME LIKE '%GLM%';
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;
/GLM = Generalised linear models (일반적인 선형 모델)의 약자
-- 머신러닝 모델 생성
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 => 'INSURANCE_TRAINING',
CASE_ID_COLUMN_NAME => 'ID',
TARGET_COLUMN_NAME => 'EXPENSES',
SETTINGS_TABLE_NAME => 'SETTINGS_REG2');
END;
/ MODEL_NAME => 'MD_REG_MODEL2', # 모델명
MINING_FUNCTION => DBMS_DATA_MINING.REGRESSION, # 수치예측 회귀 모델을 사용하겠다
DATA_TABLE_NAME => 'INSURANCE_TRAINING', #기계가 훈련할 훈련 데이터 테이블
CASE_ID_COLUMN_NAME => 'ID', #primary key에 해당하는 컬럼 / INSURANCE_TRAINING을 대표하는 컬럼
TARGET_COLUMN_NAME => 'EXPENSES', #정답 데이터 컬럼
SETTINGS_TABLE_NAME => 'SETTINGS_REG2' #기계를 어떻게 세팅하겠다는 데이터가 있는 테이블명
-- 생성된 머신러닝 모델 확인
SELECT MODEL_NAME,
ALGORITHM,
MINING_FUNCTION
FROM ALL_MINING_MODELS
WHERE MODEL_NAME = 'MD_REG_MODEL2';
-- 머신러닝 모델 구성 정보 확인
SELECT SETTING_NAME, SETTING_VALUE
FROM ALL_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = 'MD_REG_MODEL2';신뢰수준이 0.95로 기본값 설정되어 있음을 확인. 학습하는 테이블에 컬럼인 나이, 성별, BMI, 자녀수, 거주지, 흡연여부 중 어떤 컬럼이 영향력 있는 컬럼인지 설명할 때 p-value값을 봐야하는데 p-value값이 유의수준 0.05보다 작으면 유의미한 컬럼으로 설명할 수 있음.
-- 회귀분석 모델의 회귀계수를 확인
SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, ROUND(COEFFICIENT), round(p_value,2)
FROM TABLE (DBMS_DATA_MINING.GET_MODEL_DETAILS_GLM ('MD_REG_MODEL2'));coefficient와 p-value를 통해 영향력을 확인
> 설명
1. age는 나이가 1살 더해질때마다 평균적으로 연간의료비가 259달러 더 듦.
2. BMI(비만지수)는 1씩 증가할 때마다 연간의료비가 341달러가 더 듦.
3. children(자녀수)가 1명 더 늘 때마다 연간의료비가 389달러 더 듦.
4. region은 southeast를 기준으로 의료비가 얼마나 더 드는지 나온 것.
= northeast에 사는 사람들은 southeast에 사는 사람들에 비해 연간의료비가 1108달러 더 듦.
= northwest에 사는 사람들은 southeast에 사는 사람들에 비해 연간의료비가 631달러 더 듦.
= southwest에 사는 사람들은 southeast에 사는 사람들에 비해 연간의료비가 156달러 덜 듦.
5. 성별은 여자가 남자에 비해서 연간의료비가 173달러 더 듦.
6. 흡연자는 비흡연자에 비해 연간의료비가 23689달러 더 듦.
단, p-value값에 따라 컬럼을 신뢰할지 말지 결정됨.
-- 예측 값 확인
select corr( EXPENSES,ROUND(PREDICTION (MD_REG_MODEL2 USING *),2))
from (SELECT ID, AGE, SEX, EXPENSES,
ROUND(PREDICTION (MD_REG_MODEL2 USING *),2) MODEL_PREDICT_RESPONSE
FROM INSURANCE_TEST T );문제618. 테스트 데이터에 대해서 실제 의료비와 예측 의료비간의 상관계수를 구하시오
select corr( 실제의료비, 예측의료비)
from (
SELECT ID, AGE, SEX, EXPENSES 실제의료비,
ROUND(PREDICTION (MD_REG_MODEL2 USING *),2) 예측의료비
FROM INSURANCE_TEST T
);
컬럼별칭 반드시 써서 corr() 구해야함.
-- 결정계수 R 스퀘어 값 확인
SELECT GLOBAL_DETAIL_NAME, ROUND(GLOBAL_DETAIL_VALUE,3)
FROM
TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_GLOBAL(MODEL_NAME =>'MD_REG_MODEL2'))
WHERE GLOBAL_DETAIL_NAME IN ('R_SQ','ADJUSTED_R_SQUARE');
0.75에 해당하는 결정계수
출력. 보통 좋은 인공지능이라 한다면 0.90은 넘어줘야 함.
> 그러면 0.90이 넘는 인공지능을 만드려면 어떻게 해야하느냐?
학습하기에 좋은 데이터를 만들어주면 됨.
문제619. 흡연을 하면서 비만이면 의료비가 더 높을것으로 예상되므로, 흡연을 하면서 비만이면 1이라고 하고 아니면 0이라고 하는 데이터를 추가할 것. 아래의 컬럼을 생성하고 이 컬럼에 흡연을 하면서 BMI가 30이상이면 1, 아니면 0이라고 데이터를 갱신하세요.
alter table insurance
add smoker_bmi number(10) ;
-- smoker_bmi컬럼에 데이터 입력
update insurance
set smoker_bmi = 1
where bmi >= 30 and smoker = 'yes' ;
update insurance
set smoker_bmi = 0
where smoker_bmi is null;
--회귀분석 모델의 회귀계수 확인
smoker_bmi가 1일 경우 연간의료비가 19525달러 더 든다는 결과 확인
-- 예측값 확인
-- 상관계수 확인
smoker_bmi컬럼을 추가하여 다시 머신러닝을 실행하였더니 상관계수가 0.93이 출력됨. 상관관계가 높다고 볼 수 있음.
-- 결정계수 R 스퀘어 값 확인
smoker_bmi 컬럼을 추가하기 전에는 결정계수가 0.75였으나
smoker_bmi컬럼을 추가하여 실행하니 결정계수가 0.86으로 높아졌음.
'Study > class note' 카테고리의 다른 글
| 문제8. SQL / 마름모 출력하기 (0) | 2021.12.03 |
|---|---|
| sql / 알고리즘문제2 (0) | 2021.12.02 |
| sql중급 / with절(subauery factoring) (0) | 2021.12.01 |
| sql중급 / with절 (0) | 2021.11.30 |
| sql중급 / 데이터분석 복습 (0) | 2021.11.30 |
