결측값(결측치) in R

• NA(Not Available), 결측치라고 하며 값이 표기되지 않은 값.
• R에서 [행,열] 중 행 인덱싱 자리에 is.na(dataframe) + sum(결측치 개수), mean(결측률) 을 얻을 수 있다. * pandas : df.isnull().sum() , mean()

결측값(결측치)의 종류

EDA를 하면서 결측치가 있는 칼럼을 표 + 시각화하여 Random(관깊칼럼X) / No random(관깊칼럼O-> 범주별 대치, 예측)을 판단해야한다.

1. Random : 무작위로 정보가 없는 결측치, 다른변수별로 보면 결측비율도 달라진다. -> 삭제(전체or부분) or 조건부 대치 or 예측모형으로 해당변수 예측 모두가능
   ex> 몸무게 결측치, 남자<여자의 결측 가능성이 높다. -> 범주별 대치
   
   
2. No random : 결측값 자체가 (민감한 정보라서) 패턴을 가짐 -> 관깊칼럼범주별 수치(평균/중앙값/최빈값)으로 대치
   ex> 고소득자와 저소득자의 연봉은 결측치가 될 확률이 높다 -> 관계 깊은 칼럼인 사는곳별 평균/중앙값/최빈값 or예측값으로 대치

결측치를 탐색하는 방법 2가지 (표+시각화)

1. 표 : mice패키지 -> 결측치 1(아닌 것을 0)으로 표기한 뒤 x 갯수로 나타내어 조합별로도 확인가능
2. 시각화
   1) cor(y) : 결측치 1(아닌 것을 0)에 대해 이변수간의 상관관계로서 결측치를 나타낸다.
   2) Vim패키지 : 결측치(빨간색)의 빈도 및 패턴을 시각화한다. 결측된 양을 scalebar로 나타내준다.
   
   

결측치 처리방법 3가지

1. 삭제
   1) List Wise Deletion : row가 어느 한 칼럼(변수)라도 missing이면 삭제 -> 간단하지만, sample size가 떨어져 power가 낮아진다.
   2) Pair Wise Deletion : 관심있는 칼럼(변수)에 값이 있다면, 포함시킨다.
   
   

2. 대치(Imputation)
   1) 최빈값(mode, qualitative) : 범주형에서 결측값이 발생시, 범주별 빈도가 가장 높은 값으로 대치한다. -> but bias가 많이 발생할 수 있다. ex> 남자, 여자 중에 남자의 빈도가 높다고 남자도 대치한다면, bias가 높아진다.
   2) 중앙값(median) : 숫자형(연속형)에서 결측값제외한 중앙값으로 대치방법
   3) 평균(mean) : 숫자형(연속형)에서 결측값제외한 평균으로 대치방법
   4) Similar case imputation : 조건부 대치
   5) Generalized Imputation : 회귀분석을 이용한 대치
   
   

3. 예측모델(Prediction model)
   1) 결측값이 없는 칼럼(변수)들로 구성된 dataset으로 -> 결측값이 있는 칼럼을 예측하게 한다.
   2) 예측값으로 해당칼럼을 채워넣는다.

   
   

4. 가장 흔히 쓰는 방법
   1) 하나라도 결측이 있는 변수 제외한 dataset생성
   2) imputation한 dataset 2개 정도 생성 (mean or median / regression / KNN 등으로)
   3) 1)과 2)의 dataset 3개에 대해서 결과값이 서로 일관성 있음을 보여준다. -> 결측대치방법이 senvitivity하지 않다는 것을 제시하는 것

결측값(결측치) 처리 가이드라인

• 10% 미만: 삭제 or 대치
• 10 ~ 20% : Hot deck(매년자료->해당년자료 추정) or regression or model based imputation
• 20 ~ 50% 이상 : regression or model based imputation
• 50% 이상 : 해당 칼럼(변수)자체 제거

결측값 삭제 or 대치의 한계점

1. 삭제를 통해 n수가 달라진다 -> 집단의 특성이 달라질 수 있다. -> missing rate(10%미만)을 확인하고 삭제한다

2. 대치는 틀린값으로 대체될 수 있다는 가능성이 항상 존재한다

이상치(Outlier)에 대해

• 이상치 처리는 데이터 분석 중 가장 많은 시간이 소요된다.
• EDA & data cleaning, data preprocessing, data manupulation, data massage, data munging, data wrangling

이상치

1. 정의 : 데이터 전체적인 패턴에서 동떨어져있는 관측 값

   • 영향점이라고도 불림.
   • 독립변수(x축)에 있는 이상치(영향점) : 지대점
   • 종속변수(y축)에 있는 이상치(영향점) : 아웃라이어
   • boxplot에서 가운데 box의 높이 : 3Q - 1Q = IQR, 가운데 50%를 의미
   • boxplot에서 box양쪽 막대 : 3Q + 1.5*IQR = Upper fence, 1Q-1.5*IQR = Lower fence
   • boxplot에서 Upper, Lower fence 보다 바깥에 있는 값 : 아웃라이어

2. 단변수(boxplot)에서 안보이던 이상치(Outlier)가 이변수(산점도)에서 보일 수 있다.

   • 반드시 그림으로 그려봐야한다.
   • EDA를 통해 새로운 이상치를 발견할 수 있다.

3. 종류 :
   1) 실수에 의한 아웃라이어 : 수집시 오류 + 측정단위 오류 + 실험시 에러
   2) 자연(real연구대상)적 발생 아웃라이어 : 직원들월급 중 임원들은 따로 빼서 분석해야함. 자연적으로는 너무 차이날 수 밖에 없음.
   3) 역코딩에 의한 아웃라이어 : 설문지에 의도적으로 1번과 5번 성향을 바꿔놨는데, 데이터 분석시 고려안함
   4) 의도적 아웃라이어 : 설문조사시 10대들은 알콜섭취량 일부러 낮게 적음
   5) sampling 아웃라이어 : 표본 중에 우연히 이상치요소들이 포함 ex> 사람들 키 조사하는데 농구선수도 포함됨

4. 이상치(아웃라이어)의 영향력

   • 평균뿐만 아니라 분산(표준편차)가 엄청 커져 -> 엄청 불안정한 자료가 된다.
   • 중앙값(median)은 그나마 영향을 덜 받는다.

이상치 발견의 방법

1. 시각화 : 단변수 boxplot -> 이변수 scatter plot
2. Z(표준정규)분포상 3sd이상 떨어진 값들 : 3sd는 왼쪽 2.5%이하, 오른쪽 97.5%이상
   • 1sd사이 : 68.27%
   • 2sd사이 : 95.45%
   • 3sd사이 : 99.73%
3. 도메인 knowledge를 특정기준으로 두고 파악 : ex 수축기 혈압 120 ~ 160 벗어나는 값들

이상치 제거 방법

1. 삭제 : 입력시 error거나 outlier수가 굉장히 적을 경우는 그냥 삭제
2. 대치(Imputation) : 다른 값으로 변경
   • 범주별 mean/median/mode 등으로 대치
   • 통계적모델링(종속변수로 취급하여, 다른변수들로 예측 후 대치)
   • Binning(숫자의 분포를 범주로 매핑)
3. 스케일링(scaling) : 보통 right skewed(캐글에서 0.75이상)인 경우 log 나 square root(제곱근)을 취한다
4. 정규화(normalization) : MinMax, Z-score 등으로 정규화시킨다.

sql고수 효* 선생님께 sql을 다룰 수 있는 간단한 설명과 자료를 건네주셨다.
의료데이터이므로 민감한 자료라 보관을 잘하고 연습후 폐기를 잘해야할 것이다.
나는 이것을 sql로 EDA를 마치고 python으로 불러와서 EDA 및 feature engineering을 거친 뒤, 머신러닝 알고리즘으로 예측해보려는 시도를 해보겠다

보고서 작성이 금요일까진데,, 월요일부터 본격 시도를 하려고한다..
ㅠㅠ 시간이 많이 촉박하다..

통계적 추론 2가지

어떤 이론이 과학적이려면 반증 가능성을 가져야한다.
반증가능성이란? 가설이 실험or관찰에 의해서 반증될 가능성
쉽게 말하자면, H1을 증명하기 어려우니 H0가 아니다로 증명

통계적 추론
1.추정 : 표본으로 모수 등 모집단에 대한 어떤 값을 추즉하는 과정으로 추정값 + 오차범위(표본오차, 신뢰구간) 같이 제시
- 추정 : 모집단의 평균이 30이다.

2.가설검정 : 표본으로 모집단에 대한 주장or가설의 옳고 그름을 판정 하거나 대립가설(H1)을 채택 or 귀무가설(H0)을 기각
- 검정 : 모집단의 평균이 30보다 클 것이다.

가설검정의 4가지 절차

1.알고있던 귀무가설(H0)과 입증하고자하는 대립가설(H1) 세우기 - 보수적, 잘 알려진 것을 H0로 잡고, H0를 실험한다.
- 입증하고자하는 대립가설을 증명하기는 너무 어려우니 알고 있던 귀무가설이 참이라고 가정하고 실험해서 반증하자
- 반증의 기준은 알고 있던 H0의 결과(검정통계량)가 연구자가 정한 유의수준(H0실험 분포상 x축에 찍히는 임계치결정)보다 바깥쪽에 찍혀 검정통계량(H0의 결과)에 의한 유의 확률이 기각역에 포함되어 **우연에 의한것으로 판단** -> 입증하고자**하는 H1가 유의미하다는 것으로 반증**.

2.검정통계량산출(H0가 참이라고 가정하고 한 실험에서 나온 결과 - 해당 분포 그래프 상 x축에 찍히는 점)

3.유의수준 결정 by 연구자 ( 분포상 임계치(-x축에 찍히는 점)와 기각역(-임계치보다 바깥쪽)을 결정)
- 유의수준은 연구자가 결정하는 것으로 H0로 실험한 결과를 우연이 아니라고 해줄 수 있는 한계치. 보통 p-value : 0.05로 한다.
- 유의확률은 H0로 실험한 결과 나오는 확률이다.

4.검정결과 해석

가설검정에 대하여

가설검정 자체는 보수적인 실험으로서 반증가능성을 실험한다.
먼저, 알고 있던 귀무가설(H0)는 실험전까지 맞고 판단하면서 살아간다.
다음, 입증하고자 하는 대립가설(H1)은 실험 후, 알고있던 귀무가설이 아니라는 충분한 근거(유의확률이 유의수준보다 적게나왔을 때)가 있을 때 채택한다.

통계적 추론(값 추정과 가설 검정)의 목적은 data로 의사결정을 내리는 것이다.
통계적 추론(값 추정과 가설 검정)에서의 통계적(statistical)이라는 말은 표본을 이용해서 추론한다는 말이다.

통계라는 말에는 항상 에러가 우연히 발생(error)가 있으니, 우연이 아니라 정말 의미가 있다(effect)를 근거로 통계 분석(통계적 추론 중 가설검정)을 통해 시작한다.

즉, 실험 결과가 우연(error)냐 아니냐(effect)를 결정하는 것이 가설검정이다.

가설검정의 용어

1. H0 : 알고 있던, 기존에 알려진 가설 = 귀무가설 => 유의확률(p-value) - (x축상 검정통계량, H0의 결과확률)가 유의수준(0.05)(x축상 임계치)보다 작을 때 기각
   -이것을 바탕으로 표본을 이용해 실험이 이루어진다. 분포, 그래프 전부다H0가 참이다라는 가정하에 그려진다.
2. H1 : 입증하고자 하는, 알고자하는 가설 = 대립가설 => 유의확률(p-value)가 유의수준(0.05)보다 작을 때 채택
   연구자는 귀무가설을 기각하고 싶어하고, 대립가설을 채택하고 싶어 할 것이다.
3. 제 1종 오류(a) : 귀무가설이 참인데 불구하고, 귀무가설을 기각 = 대립가설을 채택한 오류 => 제 2종 오류보다 심각 => 허용한계를 정해야함(보통 0.05)
   -효과/차이가 없다(H0)가 참인데 불구하고 기각 + 효과/차이가 있다고 판단. => 엄청난 부작용 발생가능성
4. 유의 확률(p값,p-value, significance probability) : 제 1종 오류가 발생할 확률 = 귀무가설이 참인데 불구하고 알고자하는 대립가설을 채택할 확률 -> 이것의 허용치가 유의수준 -> 유의수준 허용치 안에 들어온다면, H0(귀무가설)기각 H1(대립가설 채택).
5. 유의수준(Significant Level) : <귀무가설이 참이다 가정 하에> 데이터를 기반으로한(표본을 이용한) 실험결과(검정통계량)가 항상 옳은 것이 아니므로 연구가가 정한 유의확률(제1종오류가 발생할 확률 = 귀무가설이 참인데, 기각 + 대립가설을 채택하는 확률)의 최대허용한계, 최대값, 마지노선. 보통은 0.05로 잡는게 관례 => 실험의 분포 x축에 임계치(Critical value)가 결정되며, 그 이하가 H0의 기각역(Critical Region or reject region)이 됨
   -쉽게 말하면, 유의수준 0.05 = 100번 중 최대 5번까지는 실수(제1종오류=귀무가설 참인데 기각)가 발생하더라도 허용한다.
   -유의확률 0.03 = 100번 중 3번 실수(H1를 채택) -> 100번 중 5번(유의수준 0.05) 안에 들었다. -> 귀무가설이 참이더라도 기각하고 대립가설 채택
6. 제 2종 오류(b) : 귀무가설이 거짓인데 불구하고, 귀무가설을 기각하지 않은 오류 = 제 2종 오류(b)
7. 검정통계량(Test Statistic, M-) : 귀무가설이 참이다는 가정 하에, 표본을 이용하여 계산된 통계량, 실제 실험의 결과 => 검정 통계량 이하의 확률이 유의확률이 됨
   

가설검정의 예시

1. H0 : A반 키의 평균은 160이다.에 대해 H1 : A반 키의 평균은 160이 아니다를 입증하고 싶다.
   1) 가설설정은 되었다. 2번째 검정통계량(H0 참이라는 가정하 실험해서 결과)을 구하기 위해, A반 학생 10명을 뽑아서 평균 키를 젠다
   2) 유의수준(0.05)하에서 검정통계량(10명의 평균 키)을 바탕으로 유의확률을 계산한다
   3) 기각역에 들어오면 H0 기각 = H1 채택한다.

2. 휴지가 300m인지 확인해보고싶다. (보수적, 잘알려진 H0 : 휴지의 평균길이는 300m임)
   1) 가설설정 : H0 : 휴지의 평균길이 300m이다. H1 : 휴지의 평균길이가 300m이 아니다.
   2) H0하에 검정통계량을 계산하기 위해서 휴지 10개의 길이를 제고 유의수준하에 유의확률을 확인한다
   3) H0기각 = H1채택 하거나 H0를 그대로 기각하지않는다.

3. 숟가락이 잘 구부려진다 -> 나는 초능력자다( 보수적, 잘알려진 H0 : 나는 초능력자x : 내 능력 = 다른사람의 능력)
   1) 가설설정 : H0 : 나의 능력 = 다른사람들의 능력(보통 숟가락30개 구부림) / H1 : 내 능력 != 다른사람들의 능력(30개보다 더 많이 구부릴 것이다)
   2) H0하에서 10명과 함께 숟가락 구부린 횟수의 평균과 내 구부린 횟수의 평균을 비교하여, 검정통계량을 계산한다
   3) 유의수준하에서 유의확률을 확인하여, H0 기각 ? 기각x ? 를 결정한다.

4. 듀라셀 vs 에너자이져의 수명 차이
   1) 가설설정 : H0 : 듀라셀 수명 = 에너자이저 수명
   2) H0하에서 10개의 평균 수명을 비교 실험하여 검정통계량 계산
   3) 유의수준 하(H0실험의 t분포에서 0.05)에서 검정통계량에 의한 유의확률을 확인하여 H0 기각x or H1 채택 을 선택한다.

가설검정의 종류

1. 양측검정 : H1 : Ma != Mb 으로서 실험결과분포의 양쪽(Ma > Mb & Ma < Mb)을 봐야한다.
2. 단측검정 : H1 : Ma > Mb or Ma < Mb으로서 실험결과분포의 한쪽만 본다.

오늘날의 p-value

1. 빅데이터 시대로서 n수가 늘어나서 -> 유의확률0.05 들어가는 검정통계량도 늘었다 -> 다 유의하다고 나옴
2. p-value만 보기에는 문제가 생길 수 있다. -> 평균차이와 비율차이를 같이 제시 등
3. 유의함을 넘어서 더 좁아진 유용함(MCID, The Minimal Clinically Important Change Score)구간도 생김
4. JAMA에서 발표한 임상의 확증적 연구 : 0.005쓰자
5. 미국통계학회(ASA) : 종합해서 판단하자.

문자열로 입력된 날짜를 임시로 만들고 년/월/일을 짤라놓은 뒤 || 을 이용해 년 월 일을 '-'로 연결한 뒤,다시 DATE타입으로 바꾸어 연산해보는 공부

• 칼럼을 생성하고 싶다면 : SELECT절에서  값 AS 칼럼명
• timestamp 1개로 년/월/일 3개로 나누기 : TOCHAR_( date or timestamp, ' date 기호 ')
• || : SELECT절에서 문자열칼럼 or 생성한문자열 을 concat해준다.
• 둘다 DATE타입일 경우, 마이너스 연산이 가능하다. 연산결과는 n days + 시간 -> int로 캐스팅해야 숫자로 사용( 나누기 365 등)할 수 있다.
• PosgreSQL에서 birthday를 만나이로 계산하는 순서(년/월/일)로 나뉘어있다고 가정 : 
  (1) || 을 통해 YYYY-MM-DD형태로 변환
  (2) DATE타입으로 캐스팅
  (3) NOW()현재날짜와 (-) 연산
  (4) int타입으로 캐스팅
  (5) /365해주기
  
  
• MSSQL에서 birthday를 만나이로 계산하는 순서( YYYY-MM-DD)로 나뉘었다고 가정 :
  (1) DATEDIFF( day, 시작날짜, GETDATE()) / 365 로 자동연산

전처리

1. 기존 테이블
   

2. SELECT에 < 한개의자료 AS 칼럼명 > 으로 새 칼럼을 생성한 뒤, INTO로 테이블 생성

   • '문자열' AS 칼럼명 (text형으로 칼럼생성)
   • NOW() AS 칼럼명 (timestamp형로 칼럼생성) 등등.. 다된다.

     SELECT *, NOW() AS time INTO students_with_birthday FROM students;
     

     

3. time칼럼에 날짜 입력해주기

   UPDATE students_with_birthday SET time = '1987-01-30' WHERE name ='Moe';
   UPDATE students_with_birthday SET time = '1988-03-29' WHERE name ='Larry';
   UPDATE students_with_birthday SET time = '1980-02-28' WHERE name ='Rolf';
   UPDATE students_with_birthday SET time = '1987-06-27' WHERE name ='Anne';
   UPDATE students_with_birthday SET time = '1991-02-26' WHERE name ='Robert';
   UPDATE students_with_birthday SET time = '1958-01-26' WHERE name ='Jose';
   

   

4. 날짜(YYYY-MM-DD)를 문자열 & 년/월/일 로 분리하여 칼럼 만들고, 확인되면 INTO 넣어서 필요한 칼럼들만으로 테이블로 만들기

   SELECT *,
    TO_CHAR(time, 'YYYY') as year_of_birth, 
    TO_CHAR(time, 'MM') as month_of_birth, 
    TO_CHAR(time, 'DD') as day_of_birth 
   FROM students_with_birthday
   

   

   SELECT name, current_mood,
    TO_CHAR(time, 'YYYY') as year_of_birth, 
    TO_CHAR(time, 'MM') as month_of_birth, 
    TO_CHAR(time, 'DD') as day_of_birth 
   INTO students_birthday
   FROM students_with_birthday
   SELECT * FROM students_birthday
   

   

문자열인 년/월/일 칼럼을 || 과 sep '-'로 붙히는 연습해보기

1. 문자열칼럼 || '문자열' or 문자열 칼럼

   SELECT year_of_birth  FROM students_birthday                         -- 2019
   SELECT year_of_birth||'-'  FROM students_birthday                     -- 2019-
   SELECT year_of_birth||'-'||month_of_birth  FROM students_birthday            -- 2019-02 
   SELECT year_of_birth||'-'||month_of_birth||'-'||day_of_birth FROM students_birthday     -- 2019-02-01
   

2. 문자열 YYYY || MM || DD 를 concat(||)으로 붙힌 것을 CAST( AS DATE)으로 타입 변환하기 ( YYYY-MM-DD 형태 아니면 안됨 )
   

   SELECT CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE),* FROM students_birthday 
   

3. date타입으로 바꿨으면, 특정시간칼럼과 연산이 가능하다.

   SELECT NOW() - CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE) FROM students_birthday 
   

   

4. 날짜(둘다 DATE여야함.) 차이결과는 days +시간으로 나온다. 이것을 int로 다시 형변환하면, day숫자만 나온다. * NOW()는 date가 아니라 timestamp로서 다른 타입이니 또 캐스팅
   

   SELECT CAST( CAST(NOW() AS DATE) - CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE)  AS INT) FROM students_birthday 
   

5. day를 /365 로 나누면, 만나이(년수)가 나온다.

   SELECT CAST( CAST(NOW() AS DATE) - CAST( year_of_birth||'-'||month_of_birth||'-'||day_of_birth AS DATE)  AS INT) / 365 FROM students_birthday 
   

   

최* 선생님께서 postgesql 서버에 접속한 노트북으로 쿼리를 돌리는 경험을 하게 해주셨다.

다만 민감한 서버라 내가 직접 가지고 연습할수는 없는가보다.

SQL의 귀재 김**선생님 의 세미나를 3시간가량 들었는데 그것을 매일 1회씩 5번 정도 반복했다

처음에는 4시간 넘게 걸리던 것이 1시간 가량으로 줄어들어 익숙해졌다.

쿼리보는 눈이 생긴것 같다.

앞으로 남은 기간동안 잘 할 수 있으려나

쿼리결과만 받아서 어떻게 분석할지 고민해보기로 했다.

설날동안 그동안 pandas분석한 것을 복습해서 최대한 결과물을 만들어봐야겠다.

총 정리

1. SEQUENCE와 달리, CREATE할 필요없이 그냥 테이블 생성시 칼럼 TYPE에 SERIAL 지정해주면 된다.
2. 단, INSERT INTO 시, 테이블명옆에 table( serial칼럼을 제외한 칼럼명들)을 명시해주고 입력해야한다.
   만약, SERIAL 이외 칼럼 명시없이, 제외하고 입력하면 오류난다.

SERIAL

-- SERIAL type 
-- : postgreSQL에 없는 autoincrement를 SEQUENCE로 했었지만, 그럴 필요없이 쉽게할 수있는 type제공
-- (SEQUENCE START n -> SET -> OWNED BY 의 과정이 필요없어진다!)


-- (1) TABLE 생성시 type으로 SERIAL만 주면 된다. 1부터 시작한다.
CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    name character varying(255)
)


-- (2) 입력시 id와 name 중 name만 주면 된다.
INSERT INTO test(name)
VALUES ('chojaeseong');


SELECT * FROM test

총 정리

ENUM

1. ENUM 생성 : CREATE TYPE 이넘명 AS ENUM( '순서범주1', '순서범주2', '순서범주3' ) 형식으로 생성
2. 테이블 생성시, datatype으로 지정해주기
3. INSERT INTO 시 ENUM에 속한 범주만 입력가능
4. WHERE 절에 특정범주에 부등호(< or >)를 통해 여러범주를 필터링할 수 있다.

NESTED SELECT

1. 집계하는 테이블에서는, 집계결과를 따로 칼럼을 보기 위해서는, GROUP BY 에 넣어주고 그 칼럼별 집계밖에 못보지만,
   집계결과를 따로 NESTED SELECT(SubQuery)로 취급하면 -> SELECT문에서 연산에 사용 or WHERE의 조건으로 걸 수 있다.
   
   cf) 서브쿼리는 줄을 바꿔주고 tab하나 넣는게 보통양식
   cf) SELECT 집계연산 FROM 테이블 WHERE 조건 ---> 조건까지 만족하는 테이블 이후 집계가됨
   cf) alias 칼럼명은 "" 쌍따옴표로 만들어주자.

ENUM

-- 1) ENUM 

--     (1) 새로운 dataTYPE을 만드는데 ENUM으로서 만들자. ENUM()안에 limited된 String value들을 정의해주면,
--        ***넣은 순서대로 순서를 가지는*** 순서형 범주(Ordinal <-> Nomial명목형) 데이터 타입이 된다.
 CREATE TYPE mood AS ENUM('extremely unhappy', 'unhappy', 'ok', 'happy', 'extremely happy');

--    (2) 새로 생성한 ENUM type의 mood를 칼럼의 type으로 지정해서 테이블을 생성하자.
 CREATE TABLE students (
name character varying(255),
current_mood mood 
 );

 SELECT * FROM students --mood type의 칼럼이 생김

INSERT INTO students
VALUES ('Moe', 'hapy') -- ENUM 범주에 없는 값을 입력시키면 에러가 난다.

INSERT INTO students
VALUES ('Moe', 'happy')

INSERT INTO students
VALUES ('Larry', 'happy')

INSERT INTO students
VALUES ('Rolf', 'extremely unhappy')

INSERT INTO students
VALUES ('Anne', 'extremely happy')

INSERT INTO students
VALUES ('Robert', 'unhappy')

INSERT INTO students
VALUES ('Jose', 'happy')



SELECT * FROM students



 -- (3) ENUM은 순서형 범주(Ordinal)로서, WHERE 절에서 value로 필터링 할 수 있다.
 -- enum을 이용하면, string형으로 순서를 줄 수 있어서, 회원등급을 메길 수 있고
 -- 설문score도 적용시킬 수 있다. 등등 
 SELECT * FROM students WHERE current_mood > 'ok';

NESTED SELECTD

 -- NESTED SELECT : 집계결과를, 집게테이블에서 WHERE문 or SELECT에 다  쓸 수 있다.
 -- 매우 유용함. 쿼리 안에 쿼리가 들어가는 구조

 -- 예제1) 보다 높은 가격의 item들 목록

 -- 내가 한 것)
 SELECT AVG(price) FROM items        -- 73.16125
 SELECT * FROM items WHERE price > 73.16125

 -- 좋은 것으나 안되는 것)
 SELECT * FROM items WHERE price > AVG(price)

 -- 또다른 조건을 줄때는 HAVING을 쓰나, Group by 된 상태만 된다.

 -- 정답1) NESETED SELECT를 이용해야만, 집계테이블에서, 집계결과를, WHERE문에 사용할 수 있다.
--    (1) 평균(집계)결과값을 사용할 부분을 제외한 WHERE문 작성
SELECT * FROM items WHERE price > 
();
--    (2) 집계결과를 bracket()에 작성
SELECT * FROM items WHERE price > 
(SELECT AVG(items.price) FROM items);


--    my) 이전에 했었던 아이템 목록중 가장 비싼 아이템 골라오기
SELECT * FROM items WHERE price = 
(SELECT MAX(price) FROM items);





-- 예제2 ) 아이템들의 가격이 <아이템 평균가격>보다 얼마나 비싸거나 쌀까?

-- 내가 한 것 )
SELECT * FROM items;
SELECT AVG(price) FROM items;
SELECT items.name, items.price, () FROM itesm;
SELECT items.name, items.price, (SELECT AVG(price) FROM items) FROM items;
SELECT items.name, items.price - (SELECT AVG(price) FROM items) FROM items;


 -- 정답 1)
--    (1) 평균집계값을 () 으로 비워두고, 집계테이블에서 필요한 칼럼들을 SELECT
--        가격의 비교를 위해서는 빼야한다. numeric은 SELECT 란에서 바로 (-)된다.
SELECT items.name, items.price - () FROM items;

--    (2) 서브쿼리의 기본 형식은 라인을 바꿔주고, tab을 넣는 것이다.
SELECT items.name, items.price - (
    SELECT AVG(price) FROM items
) FROM items;





-- 예제3 )  expensive_items아이템들의 가격이
--       과 얼마나 차이나는가?
--        를 VIEW로 만들기

-- 내가 한 것)
SELECT * FROM items;
SELECT * FROM items WHERE price > 80;

SELECT AVG(price) FROM items WHERE price > 80; -- 155.163 : FROM WHERE이후 --> WHERE절로 필터링
SELECT AVG(price) FROM items;    --73.16

SELECT items.name, items.price - () FROM items;

SELECT items.name, items.price - (
    SELECT AVG(price) FROM items WHERE price > 80
) FROM items;

-- 내가 catch못한 것 : 80달러보다 비싼 아이템의 평균값만 구하고, 
-- 그 이후 바깥의테이블에서는 80달러보다 비싸다는 WHERE를 안달아줘서, 모든 items들이 다 나왔음.
-- 문제를 잘못받아적음. 기본적으로 expensive 아이템가격들의 expensive아이템의 평균과의 차이 
-- ***FROM WHERE 필터링 이후--> SELECT의 집계연산이 이루어진다.


-- 정답3 )
--    (1) 80달러보다 비싼 아이템의 평균  = WHERE 절까지 거친 테이블에 집계함수가 일어남
SELECT AVG(items.price) FROM items WHERE price > 80;

--    (2) 다른칼럼들과 함께 + price - (1) 의 차이 SELECT하기 + WHERE 절 안빠트리기
SELECT *, items.price - (

) 
FROM items WHERE price > 80;

--    (3) NESTED SELECT 넣어주기
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) 
FROM items WHERE price > 80;

--    (4) VIEW로 만들기
CREATE VIEW expensive_items_diff AS 
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) 
FROM items WHERE price > 80;

--    (5) VIEW SELECT해보기
SELECT * FROM expensive_items_diff

--    (6) 칼럼이름이 없어서, 다시 DROP후 서브쿼리 ()끝나고 AS로 칼럼명 " "로 alias 정의해주기
DROP VIEW expensive_items_diff;

CREATE VIEW expensive_items_diff AS 
SELECT *, items.price - (
SELECT AVG(items.price) FROM items WHERE price > 80
) AS "average_diff"
FROM items WHERE price > 80;

SELECT * FROM expensive_items_diff;

총 정리

# TO_CHAR() : timestamp -> 문자열 or 영어로표현된 문자열로

1. 현재시간  : MSSQL : GETDATE() / PostgreSQL : NOW()
2. 날짜-> 문자열로 변환 : MSSQL : CONVERT( datatype, 시간(시간칼럼), date type number) / PostgreSQL : TO_CHAR( 시간(시간칼럼) , 'date type' )
3. TO_CHAR(  ,  )에 들어가는 datetype에서,  YYYY=yyyy, MM=mm, DD=dd, HH=hh 대소문자 구분안하고 년/월/일/시이다.
4. 분(minute)는 MI = mi
   
   --위는 TIMESTAMP 형식 : [ YYYY-MM-DD HH:MI:SS ]   
   
   --아래에서는 MM과 DD 대신 --> [  요일(FMDAy) - 일의 th형식   + 월의 영어표현(FMMonth) ] -----
      [ FMDay FMDDth FMMonth, YYYY HH:MI:SS]
   
   
5. 요일은 Day = day = DAY 가 있다. 그러나 공백을 제거하기 위해서는 FM을 붙힌 FMDay = FMDAY = FMday 
6. 일의 th 표현은 DDth = ddth이다.
7. 월의 영어표현은 FMMonth = FMMONTH = FMmonth 이다.

# TO_TIMESTAMP() : 문자열 -> timestamp 로 

1. TO_TIMESTAMP( '문자열로 표현된 시간' , ' 그 문자열의 DATE TYPE 형식' )을 넣어주면, timestamp로 변환

TIMESTAMP

-- DATE
-- date는 많은 문제가 있다. 
-- 1) datetime의 형태가 너무 많다. ex> 2019-01-30  or  01-30-2019 or 01. 30. 2019
-- 2) timezone개념이 붙어있다. ex> GMT, RUSSIAN, UTC 등등 장소에 따라 current time이 달라진다.

-- >> 추천하는 방법 :  SQL에는 TIMEZONE을 입력하지 않고, python 등 어플리케이션 단에서 TIMEZONE을 적용한다.

-- PostgreSQL의 메인 date 타입 4가지 : timestamp, date, time, interval

-- 1) timestamp
--    ISO 8601 standard date 타입 in DB.  YYYY-MM-DD HH:mm:ss 
--    pgSQL의 default 형식으로 많은 문제를 예방할 수 있음.
SELECT timestamp '2019-01-30 13:30:45';



-- 2) timestamp to string
--    (1) NOW()로 현재시간을 가져온다 <--> MSSQL의 GETDATE() or DATEDIFF( 간격, datetime, GETDATE())
--    (2) TO_CHAR( timestamp, 'DD' 등 날짜포맷 ); 으로 문자열로 전환 <--> MSSQL의 CONVERT( type, datetime, style)

SELECT NOW(); --MSSQL에서는 GETDATE() 였다! 

SELECT TO_CHAR( NOW(),'DD' ); -- MSSQL의 CONVERT( type, datetime, style) or DATEDIFF( 간격, datetime, GETDATE())
SELECT TO_CHAR( NOW(),'dd' );
-- text 30

SELECT TO_CHAR( NOW(),'MM' );
SELECT TO_CHAR( NOW(),'mm' );
-- text 01 

SELECT TO_CHAR( NOW(),'YYYY' );
SELECT TO_CHAR( NOW(),'yyyy' );
-- text 2019 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY')
-- text 30-01-2019  
SELECT TO_CHAR( NOW(), 'DD-MM-YY')
-- text 30-01-19   ==> confusing

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH')
-- text 30-01-2019  02 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH:MI'); -- pgSQL에서는 MM,mm month / MI :minute
-- text 30-01-2019  02:28 

SELECT TO_CHAR( NOW(), 'DD-MM-YYYY HH:MI:SS');
-- text 30-01-2019  02:28:08

SELECT TO_CHAR( NOW(), 'Day, DD-MM-YYYY HH:MI:SS');
-- text Wednesday, 30-01-2019  02:28:08
-- 만약 토요일이면, Saturday , 형식으로 빈칸이 하나 발생한다 그걸 제고 하고 싶다면 Day앞에 FM을 쓴다.

SELECT TO_CHAR( NOW(), 'FMDay, DD-MM-YYYY HH:MI:SS');
-- text Wednesday, 30-01-2019  02:28:08


SELECT TO_CHAR( NOW(), 'FMDay Month, DD-MM-YYYY HH:MI:SS');
-- text Wednesday January  , 30-01-2019  02:28:08

SELECT TO_CHAR( NOW(), 'FMDay FMMonth, DD-MM-YYYY HH:MI:SS');
-- text Wednesday January, 30-01-2019  02:28:08


--날짜를 3th 4th 형식으로도 줄 수 있다.
SELECT TO_CHAR( NOW(), 'FMDay DDth FMMonth, DD-MM-YYYY HH:MI:SS');
-- text Wednesday 30th January  , 30-01-2019  02:28:08


-- 이제 DD-MM 을 제거해서 보자.
SELECT TO_CHAR( NOW(), 'FMDay DDth FMMonth, YYYY HH:MI:SS');
-- text Wednesday 30th January, 2019 02:28:08



-- 3) 이제 TO_TIMESTAMP( '마지막에 작성한 양식의 string' , 'string과 똑같은 양식의 날짜포맷' );
--    를 통해 sting을 timestamp로 바꿀 수 있다.

SELECT TO_TIMESTAMP('Wednesday 30th January, 2019 02:28:08', 'FMDay DDth FMMonth, YYYY HH:MI:SS');

-- 4) string양식이 ISO8601 이었으면, 그 string에 맞게 양식을 적어주면, timestamp로 변한다
SELECT TO_TIMESTAMP('2019-01-30 02:28:08', 'YYYY-DD-MM HH:MI:SS');

리스트 속 요소들을 변환한 리스트를 반환받는 법

1. 변환될 빈 리스트 생성 -> for문 돌면서 처리 -> 각각 append
2. list comprehension
3. list ( map ( lambda x: , list ) )

리스트 속 요소들을 필터링한 리스트 반환받는 법

1. list comprehenshion + if
   1) [ x if 조건1 else 조건 2 for in ]
   2) [ x for in if 조건1 ]
2. list ( filter(lambda x: , list ) )

리스트 vs set

1. list() :  [1, 2, 3] or []  로 생성하면 append한다
2. set() :  {1, 2, 3} 로 생성하면 add한다.
3. { } 로 생성은 빈 딕셔너리다 ! set이 아니다! set의 양쪽 구성만 { }

리스트 속 요소들을 변환한 set을 반환받는 방법

1. { set comprehension for in 리스트 }

총정리

1. HAVING은 GROUP BY 끝난 쿼리문에서 조건을 추가적으로 달 수 있다.
2. HAVING은 1차 처리된 칼럼(COUNT()등)을 조건을 걸고 싶을 땐, alias사용이 안된다.

HAVING

-- 내장함수 & HAVING 

-- 내장함수의 종류 : 
-- COUNT(), SUM()
-- 예제 ) 고객별 구매횟수 ( 횟수, 건수 : 해당 테이블의 id를 카운트 해버리면 된다)
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id

-- AVG()
-- 예제 ) 아이템의 가격 평균
SELECT AVG(items.price) FROM items;

-- 예제 ) 구매된(join으로 교집합) 아이템  가격의 평균
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT AVG(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id;



-- 1) 구매된 아이템 중 가격 TOP 1 을 구하는 첫번째 : JOIN 구매된 아이템  -> ORDER BY -> LIMIT
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id

SELECT items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id
ORDER BY items.price DESC
LIMIT 1;


-- 2) 구매된 아이템 가격 TOP 1을 구하는 두번째 : JOIN 구매된 아이템 -> MAX() ? 
--    : 집계테이블이 되어, 같은 테이블 내 칼럼선택(items.name)을 못함

--    만약, 다른컬럼(items.name)도 같이보려고, 집계함수 앞에 SELECT 시
--        ~별 기준이 되어버려서, 각 items.name 별 최대값들이 여러 row로 나옴.

SELECT MAX(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id;    -- 집계테이블로서 1개 칼럼밖에 못봄

SELECT items.name, MAX(items.price) FROM items
INNER JOIN purchases ON items.id = purchases.item_id    
GROUP BY items.name;-- 하나더보려고 select + groupby에 넣으면 기준테이블이 되어 각 아이템별로 최대값이 되어버림


-- 3) HAVING = GROUP BY + 집계함수 결과에 filter링 (집계함수의 WHERE절)
--    (1) 고객별 구매횟수를 위에서 작성했떤 것 복사해오자.
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id

--    (2) GROUPBY  + 집계함수의 결과에서 HAVING을 이용하여 다시 3개이상의 구매자만 걸러내보자.
--        *** 이때, 집계한칼럼의  alias로는 HAVING에서 조건을 걸 수 없다.
--        *** 1차 작업물이 SELECT 문에 있을시 -> 이후 사용은 그 문장 그대로!
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
HAVING purchase_count > 2;     -- alias 사용시 오류

SELECT customers.first_name, customers.last_name, COUNT(purchases.id) AS purchase_count FROM customers
JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id
HAVING COUNT(purchases.id) > 2;

-- 4) 다음에 같은작업(고객별 구매횟수가 가장 많은 사람 뽑기)할일이 있다면, VIEW로 만들어보자.

총 정리

1. DROP TABLE IF EXIST 테이블명
2. 종속관계가 있는 테이블은, 참조하고 있는 테이블부터 지워야 삭제 가능하나, 종속관계를 끊고 삭제하려면 DROP TABLE 테이블명 CASCADE;
   

VIEW정리

1. VIEW 생성 : CREATE VIEW 뷰명 AS  groupby복잡한 쿼리문     을 통해 updatable한 테이블을 생성할 수 있음.
2. VIEW 조회 : SELECT * FROM 뷰명  으로 테이블조회하듯이 조회한다.
3. VIEW 삭제 : DROP VIEW 뷰명
4. VIEW를 조회할 때 WHERE / ORDER BY달고 조회가능 / 
5. VIEW + WHERE 절 달아서 새로운 VIEW 생성가능
   
   
6. VIEW생성시 AS쿼리문에 1) * 모든칼럼 선택한상태 + 2) * group by 등 집계안한 상태 + 3)WHERE문 정도 라면 : INSERT INTO VIEW 로 값 대입 가능, 특별한 옵션이 없다면 WHERE조건 만족못해도 원본테이블에 들어감
7. WITH LOCAL CHECK OPTION으로 VIEW생성시, 해당WHERE절 조건만족못하는 것은 INSERT INTO VIEW로 대입 불가능
8. view1를 이용해 view2를 만든 상태에서
   (1) view1에 local옵션이 없고 & view2 local옵션만 있다면 ==> view2옵션만 체크한다.
   (2) view1에 local옵션이 있고 & view2 local옵션있다면     ==> view1, view2 둘다 체크한다. ( 각 view가 local있다면, 다 체크 )
   (3) view1에 local옵션이 없고 & view2 CASCADED 옵션이 있다면 ==> view1, view2 둘다체크한다. (마지막 view가 cascaded옵션있다면, 다 체크)

DROP TABLE 옵션 :  CASCADE vs CASCADED : VIEW를 포함한 VIEW에서 2개 모두 WHERE절 조건 체크 옵션

DROP

-- DROP TABLE

-- 1) videos테이블에서 users를 참조하고 있는 상황이므로 바로 삭제가 안된다.
DROP TABLE public.users;

-- 2) 강제로 삭제하는 방법은 뒤에 CASCADE를 붙혀 -> 다른테이블과의 관계를 끊는 것이다.
DROP TABLE public.users CASCADE;

-- 3) 관계는 끊어져도 기존에 연결된 값들은 유지되어있다.
--    videos 테이블의 정보를 보면, CONSTRAINT fkey정보가 업어져있다.
SELECT * FROM videos;


-- 4) 만약, videos테이블을 먼저 DROP한다면, uses테이블도 바로 DROP 될 것이다.
DROP TABLE public.videos;

-- 5) 없는 테이블을 삭제하는 오류를 줄이고 싶다면 DROP TABLE <--> 테이블명 사이에 IF EXISTS 를 준다.
--     테이블을 생성하기 전에 적어줘도 좋은 팁이 된다.
DROP TABLE IF EXISTS public.videos;

-- 6) DROP TABLE/DATABASE/SEQUENCE/VIEW 등이 다 DROP이 가능하다.

VIEW

-- VIEW & INSERT VIEW without groupby & WITH LOCAL/CASCADE CHECK OPTION

-- 1) view없이 고객들이 소비한 전체 돈 보기 by GROUP BY 
--     customers별 목록이 1 row로 나타나도록 id로 groupby + 기준테이블 칼럼들은 막 select +  집계테이블은 칼럼없이 sum()으로 1 row로 집계했다.
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

-- 2) 매번 groupby를 할게 아니라, VIEW를 이용해 해당 결과를 저장하자.
--    CREATE VIEW + view이름 + AS + 저장할 쿼리문 형태이다.
CREATE VIEW total_revenue_per_customer AS
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

-- 3) 마치 테이블처럼 SELECT할 수 있다.
SELECT * FROM total_revenue_per_customer

-- 4) DROP VIEW view이름으로 삭제할 수 있다.
DROP VIEW total_revenue_per_customer

-- 5) 다시 view를 만드는데, groupby 의 기준이된 customers.id도 같이 SELECT한 view를 만들자
--    새로운 테이블로 보는데, id칼럼이 없기 때문...?
--    이 아니라,, 새로운 구매정보를 늘릴 때 view는 어떻게 변하는지 보기 위해서다.
CREATE VIEW total_revenue_per_customer AS
SELECT customers.id, customers.first_name, customers.last_name, SUM(items.price) FROM customers
INNER JOIN purchases ON customers.id = purchases.customer_id
INNER JOIN items ON purchases.item_id = items.id
GROUP BY customers.id

SELECT * FROM total_revenue_per_customer;

-- 6) VIEW의 결과에 없는 customers 중에 한명이 item을 구매하도록 해보자.
--    구매정보를 늘이려면 purchases테이블에 추가해야한다.
SELECT * FROM total_revenue_per_customer;    --1,3,4,5는 구매했었음.
SELECT * FROM customers;            --남은건 2번 고객
SELECT * FROM items;                --아이템은 아무거나.. 여기서 6번 아이템


SELECT * FROM purchases;             -- 구매번호는 10번까지 차있구나.

INSERT INTO purchases                -- 구매번호 11, item_id 6번, customer_id 2번
VALUES (11, 6, 2);


-- 7) 기존에 저장한 VIEW를 다시 보자.
--    customers.id 2번이 추가되었다! / 만약 기존고객이 구매정보를 늘렸으면, sum이 증가할 것이다!
--    VIEW는 결과가 아니라 쿼리문 자체를 저장해서 updatable한 테이블을 생성해주는 것
SELECT * FROM total_revenue_per_customer;


-- 8) VIEW로 저장한 쿼리문에는 WHERE문도 달 수 있다.
SELECT * FROM total_revenue_per_customer WHERE sum > 150;

-- 9) VIEW에 WHERE문을 단 것 역시, VIEW로 만들 수 있다.
CREATE VIEW awesome_customer AS
SELECT * FROM total_revenue_per_customer WHERE sum > 150;

SELECT * FROM awesome_customer;

-- 10) VIEW를 table로 취급하여, ORDER BY 한 것도 가능하다.
SELECT * FROM total_revenue_per_customer WHERE sum > 100
ORDER BY sum DESC;



-- INSERT INTO VIEW를 통해 VIEW에 값 대입하기는 group by 절이 포함된 VIEW는 사용하지 못한다.
-- 위에서 만든  total_revenue_per_customer 같은 것들은 INSERT INTO에 view를 사용하지 못한다.

-- 1) group by가 없는 VIEW를 만들자.
CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100;

SELECT * FROM expensive_items

-- 2) 여기에 row를 대입해보자. ( 만약, SEQUENCE를 사용한 테이블이라면 해당값 입력x)
--    VIEW를 만든 테이블(items)에 값이 자동으로 삽입된다!
--     만약 일부칼럼만 가져온 VIEW라면?;; 
INSERT INTO expensive_items(id, name, price)
VALUES (9, 'DSLR',400.00);

SELECT * FROM expensive_items
SELECT * FROM items;            -- items테이블에도 추가되어있다.

-- 3) 만약, VIEW에 포함된 WHERE 조건절을 만족하지 않는 값을 추가한다면?
--     VIEW의 WHERE에는 100달러 이상 아이템만 가져오도록했는데, 5달러짜리를 추가해보자.
INSERT INTO expensive_items(id, name, price)
VALUES (10, 'DSLR', 5.00);

SELECT * FROM expensive_items;    --결과적으로, items테이블에는 추가되지만, VIEW에서는 안보이게 된다. 왜냐 살아있는 쿼리문이 저장되어있는 테이블이므로
SELECT * FROM items;    

-- 4) 그렇다면, INSERT INTO VIEW 를 이용해서, VIEW에 달린 WHERE 조건을 만족하는 값만 입력되도록 하려면?
--    PostgreSQL에만 있는 코드인 WITH LOCAL CHECK OPTION을 VIEW에 추가해줘야한다.
--    WITH LOCAL CHECK OPTION은 데이터를 insert나 update시 where조건절을 항상 체크한다.
DROP VIEW IF EXISTS expensive_items;

CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price > 100
WITH LOCAL CHECK OPTION ;

INSERT INTO expensive_items(id, name, price)
VALUES (11, 'low camera', 5.00);        -- view의 조건을 위반했다고 뜬다.

INSERT INTO expensive_items(id, name, price)
VALUES (11, 'low camera', 101.00);

-- 5) 이번에는 WITH LOCAL CHECK OPTION 없이 만든 expensive_view(100달러이상)에 
--    FROM expensive_view  + WITH LOCAL CHECK OPTION 으로
--    10000달러는 이하인 non_luxury_items VIEW를 
DROP VIEW IF EXISTS expensive_items;

CREATE VIEW expensive_items AS
SELECT * FROM items WHERE price >= 100;

CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH LOCAL CHECK OPTION;

-- 6) non_luxury_items VIEW 에다가 2달러짜리를 INSERT INTO 를 해보자.
--    LOCAL CHECK OPTION은 현재하고 있는 WHERE에 대해서만 체크를 한다고한다.

INSERT INTO non_luxury_items(id, name, price)
VALUES (12, 'Pencil', 2.00);
--    결과적으로 2달러도 입력이 된다.
--    직접적으로 달리지 않은 expensive_items에 대한 체크를 하지 않는다.
--    즉, non_luxury_items에 대한 check만 시행한다.


-- 7) LOCAL 이 아니라 종속관계까지 다 체크해주는 CASCADED CHECK 를 맨 마지막 VIEW에 달면,
--    내부의 VIEW(테이블)의 WHERE절까지 체크해준다.
DROP VIEW IF EXISTS non_luxury_items;

CREATE VIEW non_luxury_items AS
SELECT * FROM expensive_items WHERE price < 10000
WITH CASCADED CHECK OPTION;

INSERT INTO non_luxury_items(id, name, price)
VALUES (13, 'PencilTwo', 90.00); -- 내부에 있는 VIEW인 expensive_item에 대한 WHERE절에 위반되고 있다고 경고창이 뜬다.