R markdown

setwd("C:/Users/is2js/python_da/deep analysis(논문용 설문지 분석)")
table = read.csv('sens_spec_for_r_table.csv', header = T)
head(table)

##   Kind.of.medicinal.herbs Sensitivity Specitivity              Group
## 1                      SA        0.95       1.000 Ph.D. of Herbology
## 2                      SA        0.95       1.000 Ph.D. of Herbology
## 3                      SA        1.00       0.875 Ph.D. of Herbology
## 4                      SA        0.95       0.900 Ph.D. of Herbology
## 5                      SA        0.90       1.000 Ph.D. of Herbology
## 6                      SA        0.95       0.800 Ph.D. of Herbology

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

herb <- function(table, herbname){
  a = filter(table, Kind.of.medicinal.herbs == herbname)
  return(a)
  
}

ac = herb(table,"AC")
amc = herb(table, "AMC")
sa = herb(table, "SA")

# n수가 적어 정규분포 안따르는 비모수검정 -> 3 그룹간의 차이를 검정
# (정규분포를 따르지 않는)AC에 대한 3그룹 민감도 차이가 없다(H0)를 검정
kruskal.test(ac$Sensitivity ~ ac$Group)  #p-value = 0.02285

## 
##  Kruskal-Wallis rank sum test
## 
## data:  ac$Sensitivity by ac$Group
## Kruskal-Wallis chi-squared = 7.5576, df = 2, p-value = 0.02285

kruskal.test(amc$Sensitivity ~ amc$Group)  #p-value = 0.3188

## 
##  Kruskal-Wallis rank sum test
## 
## data:  amc$Sensitivity by amc$Group
## Kruskal-Wallis chi-squared = 2.2864, df = 2, p-value = 0.3188

kruskal.test(sa$Sensitivity ~ sa$Group)  #p-value = 0.3099

## 
##  Kruskal-Wallis rank sum test
## 
## data:  sa$Sensitivity by sa$Group
## Kruskal-Wallis chi-squared = 2.3433, df = 2, p-value = 0.3099

#install.packages("pgirmess")
library(pgirmess)

kruskalmc(ac$Sensitivity,ac$Group)

## Multiple comparison test after Kruskal-Wallis 
## p.value: 0.05 
## Comparisons
##                                    obs.dif critical.dif difference
## KMD-Ph.D. of Herbology            44.75000     44.74725       TRUE
## KMD-Undergraduates                 2.22973     27.19549      FALSE
## Ph.D. of Herbology-Undergraduates 42.52027     37.51044       TRUE

kruskalmc(amc$Sensitivity,amc$Group)

## Multiple comparison test after Kruskal-Wallis 
## p.value: 0.05 
## Comparisons
##                                      obs.dif critical.dif difference
## KMD-Ph.D. of Herbology            23.7916667     44.74725      FALSE
## KMD-Undergraduates                 0.4087838     27.19549      FALSE
## Ph.D. of Herbology-Undergraduates 23.3828829     37.51044      FALSE

kruskalmc(sa$Sensitivity,sa$Group)

## Multiple comparison test after Kruskal-Wallis 
## p.value: 0.05 
## Comparisons
##                                     obs.dif critical.dif difference
## KMD-Ph.D. of Herbology            26.875000     44.74725      FALSE
## KMD-Undergraduates                 4.600225     27.19549      FALSE
## Ph.D. of Herbology-Undergraduates 22.274775     37.51044      FALSE

정리해보기

• pgSQL에서는 테이블생성시 public. 등의 스키마를 테이블명 앞에 붙힌다( 생략시 default - public. 자동)
  
  
• 2번째 테이블 생성부터는, 자신이 필요한 id(PRIMARY KEY)와 칼럼을 입력후 -> 참조 테이블을 고려한다.
  
  1) 참조테이블_id(칼럼명)  +  참조테이블의 PRIMARY KEY와 같은 datatype  +  REFERENCES  +  스키마.참조테이블명 
  2) 이렇게 지정해주면 참조테이블의 PRIMARY KEY는 2번째 테이블의 FOREIGN KEY가 되어버린다
  
  
• 1번째 테이블을 먼저 입력한 뒤, 참조테이블인 2번째 테이블에 데이터 입력한다.
  
  1) 이 때, 1번째 테이블의 PRIMARY KEY = 2번째 테이블의 FOREIGN KEY에 없는 값은 입력되지 않는다.
  
  
• 
  

CREATE TABLE

-- CREATE TABLE --
-- 만약 default schema인 public을 사용할 것이라면, public.은 생략해도 된다.

-- users테이블을 맨 처음 만들기.
CREATE TABLE public.users (
id integer PRIMARY KEY,  --사용자가 많다면 *bigint를 사용하면 된다. datatype다음에 PRIMARY KEY를 지정해주면 된다.
name character varying(100) NOT NULL, --*pgSQL에서는 varchar(255)가 아니라 character varying(255)로 사용한다.
)


-- PRIMARY KEY 지정은, 다 칼럼들 작성하고 난 다음에, CONSTRAINT로 지정해줄 수 있다.
-- CONSTRAINT + constraint's name + PRIMARY KEY + (칼럼명) 
-- ()안에는 여러개의 primary key를 지정해 줄 수 도 있다.
CREATE TABLE public.users (
id integer,
name character varying(100) NOT NULL, 
CONSTRAINT users_id_pkey PRIMARY KEY (id)
)


-- 그러나 *** 추천 ***은 칼럼 만들때 지정해주는 것이다.
CREATE TABLE public.users (
id integer PRIMARY KEY, 
name character varying(100) NOT NULL
)

SELECT * FROM users





-- videos 테이블 만들기
-- 1) videos에 필요한 칼럼들 먼저 넣기
CREATE TABLE public.videos (
id integer PRIMARY KEY,
name character varying(255) NOT NULL
)

-- 2) *** users 테이블과 연관관계를 고려하여, 어떤사람이 업로드 했는지 id가 필요함.
--    *** id와 users_id가 상관관계가 있으니 라인을 비슷한데 두자.
--     (1) user_id는 users 테이블과 같은 datatype을 가져야할 것이다.
CREATE TABLE public.videos (
id integer PRIMARY KEY,
user_id integer,
name character varying(255) NOT NULL
)
--     (2) user_id는 users 테이블의 id만 들어갈 수 있어야한다.******
--          users 테이블을 참조값만 들어가는 테이블임을 < REFRENCES 스키마.테이블 >로 명시해준다.
--        REFERENCES만 정해주면, ***참조테이블의 primay key가 자동적으로 체크된다.
CREATE TABLE public.videos (
id integer PRIMARY KEY,
user_id integer REFERENCES public.users,
name character varying(255) NOT NULL
)

SELECT * FROM videos

INSERT INTO

-- INSERT INTO --
-- 1) user table에 넣기 *** video 테이블은 users 테이블을 참조하고 있기 때문에 값을 바로 넣을 수 없다 ****
--    테이블명다음에 블라켓()에 칼럼리스트를 적어줘도 되지만, 생략해도 된다.
INSERT INTO public.users(id,name)
VALUES (1, 'marysmith');

SELECT * FROM public.users


INSERT INTO public.users
VALUES (2, 'rolfsmith');

SELECT * FROM public.users

INSERT INTO public.users
VALUES (3, 'josesalvatierra');


-- 2) videos 테이블은, user_id칼럼이 users테이블의 primaykey를 참조하고 있기 때문에,
--     uses테이블을 먼저 채워넣고 ----> 거기에 있는 값만 INSERT INTO 할 수 있다.
--    참조하는 순간 uses테이블의 primary key는 **자동으로 videos테이블의 foreign key가 된다.***
SELECT * FROM public.videos;

--    (1) 만약 users테이블에 없는 값을 입력시킨다면, 에러가 난다.
INSERT INTO public.videos
VALUES (1, 10, 'Test Video');

--     (2) users 테이블의 primary key = videos테이블이 참조해서  foreign key가 된다.
INSERT INTO public.videos
VALUES (1, 2, 'Test Video');

SELECT * FROM public.videos;




-- 3) 이제 2개 테이블을 조인시켜보자.
SELECT * FROM public.videos
INNER JOIN public.users ON public.users.id = public.videos.user_id;




-- 4) users테이블에서 primary key(***자동적으로 NOT NULL적용***)을 
--     autoincrementing 로 적용시켜서 -> name만 입력하면 id가 자동으로 올라가도록 해보자.

* 정리해보기

• JOIN한 상태에서 ~별  ~집계를 할때, 기준테이블의 id로 GROUP BY 하되, 기준테이블의 칼럼들은 SELECT할 수 있으나, 집계시에도 COUNT()는 집계테이블의 id로 COUNT()하되, 집계되는 테이블은 1 row로 접혀서 숫자계산되기 때문에 SELECT문에 칼럼명을 못넣는다.
• 만약, 집계테이블의 칼럼을 groupby의 기준에 넣는다면, 집계테이블 칼럼을 SELECT할 수 있다. 그러나 기준테이블은 안해두 가능!!

JOIN 3개 테이블 해보기

SELECT * FROM purchases;

SELECT * FROM items;

SELECT * FROM customers;


-- 1) item들 중에  구매한 items 목록을 원하는 경우
SELECT * FROM items
JOIN purchases ON items.id = purchases.id;


-- left조인을 쓴다면, 구매하지 않은 아이템도 블랭크로 붙는다.
SELECT * FROM items
LEFT JOIN purchases ON items.id = purchases.id;


-- 2) 이제 어느 고객이 어느 아이템을 샀는지 join해보자.
SELECT * FROM customers
JOIN purchases ON customers.id = purchases.customer_id;

--left join
SELECT * FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;

-- 고객 중에 구매를 한 고객 join데이터 중 이름만 select하기
SELECT customers.first_name, customers.last_name FROM customers
JOIN purchases ON customers.id = purchases.customer_id;



-- 3) ***3개 테이블 한꺼번에 join해보기***
---- (1) items중에 구매한 것들만 item만 나오도록 inner join한다.
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id

---- (2) 여기서 보이는 customer_id로 고객들을 연결할 수 있을 것이다. *** JOIN한것에 바로 JOIN을 해버린다.
SELECT * FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id


---- (3) 필요한 칼럼만 챙겨보자.
SELECT customers.first_name, customers.last_name, items.name, items.price FROM items
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id

GROUP BY

-- GROUP BY --
-- JOIN의 결과는 duplicated된 row들로 나열되서 나타나니 GROUP BY <사람테이블의 id>를 활용해야한다.

SELECT * FROM customers;

-- ***left join***의 결과 ***모든*** 고객들 중에, 구매한 고객들 + 구매안한 고객들의 정보들이 다 나온다
-- left join을 하는 이유는 group by 로 count할 때, *** 구매안한사람도 0으로 count하기위해 ***
-- 하지만, 나는 구매info(item_id) + 연결키(customer_id)가 중요한게 아니라, 얼마나 많이 구매했는지 알고 싶은 상황이다.
-- 각 구매info가 따로따로 row로 나뉘어서 나오므로 이 때 필요한 것이 groupby이다.
SELECT * FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;


-- 1) 이름정보와, purchases테이블에서는 ***primary key인 id만 가져온다. for COUNT ***
--    하지만, 여기까지도 row가 나뉘어져있다.
SELECT customers.first_name, customers.last_name, purchases.id FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id;

-- 2) 이제 count하고 싶은 칼럼인 puchases의 primary key id칼럼에 count함수를 넣어준다.
--    이대로는 에러가 난다. 그래서 필요한 것이 GROUP BY 이다.
--    *** 이때, 보이지 않더라도 LEFT테이블인 customers의 identity & primary key인 id칼럼으로 groupby 한다***
SELECT customers.first_name, customers.last_name, COUNT(purchases.id) FROM customers
LEFT JOIN purchases ON customers.id = purchases.customer_id
GROUP BY customers.id;




-- 3) ***SUM() - GROUP BY를 이용하여, 
--    아이템들 중 구매된 아이템만* 골라내는 INNER JOIN을 한 뒤, 
--     구매된 아이템 중 고객들customers들을 다시 INNER JOIN시켜붙혀보자.
SELECT * FROM items;

SELECT * FROM items 
INNER JOIN purchases ON items.id = purchases.item_id;

SELECT * FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id;

-- 4) 여기에 *** 고객별 의 id로 groupby -> 집계함수 SUM()*** 시켜보자.
--    만약 GROUP BY 만 쓸때, *로 모든 칼럼들에 접근한 상태라면, 데이터 접근 중이므로 GROUP BY로 합칠 수가 없다.
--      필요칼럼선택(GROUP BY 기준 테이블안의) + (GROUP BY기준아닌 테이블의 칼럼을 합칠)집계함수를 사용해야한다.
--    my) customers의 id로 GROUP BY를 한다면, customers테이블은 칼럼 정보 살아 있어서 가져올 수 있다.
-- *********GROUP BY기준이 아닌 테이블의 칼럼들은 개별접근x COUNT(), SUM() 등으로 접혀들어가야한다.
SELECT * FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;

SELECT customers.first_name, customers.last_name, SUM(items.price) FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;

-- 5) 숙제 = 모든 고객이 구매한 가격의 합 ( customers 테이블 연결없이 해봤다 )
--    GROUP BY 의 기준을 items.id로 잡았다면 items테이블 칼럼은 접근가능 -> purchases테이블은 접히니까, 집계함수
SELECT items.name, SUM(items.price) FROM items 
INNER JOIN purchases ON items.id = purchases.item_id
GROUP BY items.id;

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

-- 5-정답)
-- 1) 전체 고객들이 얼마나 많은 돈을 소비했는지 알아보기

--     (1) 구매 정보 테이블 가져오기
SELECT * FROM purchases;

--    (2) 구매정보에 items정보 붙히기 innerjoin    
SELECT * FROM purchases
INNER JOIN items ON purchases.item_id = items.id;

--     (3) SUM()만 해주기
SELECT SUM(items.price) FROM purchases
INNER JOIN items ON purchases.item_id = items.id;

ORDER BY

-- Order by --

-- 이전에 했던대로, *각각의 고객들이 얼마나 많은 *돈을 소비했는지 다시 찾아보자
-- 1) 3개 테이블 다 조인하기 
SELECT * FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id;

-- 2) 필요칼럼 셀렉트하기
SELECT customers.first_name, customers.last_name, items.price FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id;

-- 3) 보이진 않지만, 고객의 primary key인 id칼럼으로 groupby -> SUM()하기
SELECT customers.first_name, customers.last_name, SUM(items.price) FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id;

-- 4) price순으로 정렬하기 ( alis " 띄워쓰기alias시 따옴표 필수 "  -> ORDER BY에 alias적어주기)
SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent";

SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent" DESC;

-- 5) TOP 1만 표시해주기
SELECT customers.first_name, customers.last_name, SUM(items.price) AS "total_spent" FROM items
INNER JOIN purchases ON purchases.item_id = items.id
INNER JOIN customers ON purchases.customer_id = customers.id
GROUP BY customers.id
ORDER BY "total_spent" DESC
LIMIT 1;

알아두어야 할 개념

1. SELECT '문자열' AS 칼럼명으로 새로운 칼럼을 생성할 수 있다.
2. UNION은 2개의 쿼리문의 칼럼의 갯수/이름이 모두 같을 때 합칠 수 있다.
3. 전체범주명을 깔고, 특정 범주명을 UNION하는 것은 안되는 것 같다. CASE WHEN 처럼 범주를 다 나눈 다음 UNIO해주는 방법밖에 없나??
4. UNION은 합집합, UNION ALL은 합집합+교집합 포함

코드

SELECT '5~10만' AS TYPE, * FROM person WHERE personid BETWEEN 50000 AND 100000
UNION 
SELECT '기본 범주' AS TYPE, * FROM person WHERE personid < 50000 OR personid > 100000
ORDER BY personid

csv파일로 보내기

mssql 쿼리문 결과에서 우클릭하기

전처리시 체크사항

1. 중복확인 : duplicated(df)로 T/F 마스크를 만들어서, 인덱싱자리에 넣는다. => dpylr 라이브러리 사용
2. 이상치 확인 : 이상치로 평균값이 튄다.
   ex> 오타, 응급상황 후 어설프게 입력

EDA의 방법 2가지

1. 데이터 형태에 따른 2가지 EDA

   • 범주형(문자형) : 기초통계시 frequency(빈도)가 나옴

     1) 2개 범주 : 남/녀, 성공/실패와 같은 binary / binomial / dichotomous
     2) 3개이상 범주 : 명목형(Nomial)=순서없는 범주 / 순서형(Ordinal) = 순서있는 범주

   • 숫자형 : 기초 통계시 계산에 의해 평균,최소,최대,4분위수
     cf) %/% : 정수나누기 , %% : 나머지, ^ ** : 승수
     1) 연속형 : 키, 온도 등 소수점 표현이 되는 수로서 통계기법 多
     2) 이산형 : 건수, 개수, 등 측정이 아닌 count수

2. 변수 개수에 따른 3가지 EDA
   1) 단 변수분석 :

   • 숫자형 단변수 EDA시 Missing rate, variation 등 확인 => Box plot(분포-통계확인) or hist(정규분포확인)하는 용도
     ex> is.na(age), sum(is.na(age)), mean(is.na(age)), new_age <- c(na.omit(age)) or na.rm = T
     ex> summary(height), quantile(height, na.rm=T), IQR(height, na.rm=T), var(height, na.rm=T), sd()
     cf) 2Q = median = 50% , 3Q - 1Q = IQR -> 가운데 50%가 퍼진 정도
     cf) Boxplot해석 순서 : median(50%)를 기준으로 3Q, 1Q -> 3Q + 1.5IQR = Upper fence / 1Q-1.5IQR = Lower fence -> 이상치들 보기
     cf) histgram : 구간을 쪼갠뒤 -> 카테고리를만들고 -> 그안에 몇개가 있는지 빈도확인 ( no space bar plot )
     cf) 아웃라이어는 평균을 튀게 만드나, 새로운 발견이 필요한 경우 포함시킨다.

   • 범주형 단변수 EDA시 빈도 or 새로운카테고리 확인->상대빈도 파악후 => 범주별 빈도를 기본으로 그리는 Bar chart => Bar chart에 누적된 line까지 그려주는 파레토 차트
     cf) 범주가 지나치게 많은 경우 => 기타 카테고리로 묶는다.

   2) 이 변수분석 :

   • 범주형 이변수 : 교차표를 위함 => 카이제곱검정으로 2개 이상의 범주형 변수 연관관계 파악 => bar chart with 범주
   • 숫자형 이변수 : 산점도(scatter plot) => 상관관계correlation확인
     cf) 상관관계는 선형성에 의한 척도이므로 0.7은 없다. 선의 기울기가 30도라도 +1이다. -30도라도 -1이다. 혹은 0으로 선형성이 없다
     cf) 상관계수는 분포에 대한 것으로 심리분야 0.3이상, 보통 0.7이상이 strong으로 판단, but 그래프 + 수치를 같이 제시해야한다.

   3). 다 변수분석 : 키 + 몸무게 + 혈액형

sql파일

엑셀파일

CREATE DATABASE practice_medi2

USE practice_medi2

-- CodeMaster 칼럼2개 제외 DB우클릭 > T, I > END + W + TAb 아래2칸 + ShiftTaB3칸 파일선택  > N > N > END 위로3칸  > N N F 
-- 가져오기마법사로 실패하는 경우
-- 1. Table 생성 > 2. BELK INSERT 로 csv 파일 집어넣기

create table Person (
personid int,
sex int,
birthday datetime,
ethnicity int
)

--
bulk insert Person from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_person.csv'
with (firstrow=2, format='CSV')



create table Electrocardiogram (
personid int,
ecgdate datetime null,
RR int null,
QT int null,
QTc int null,
ACCI int null,
ecgdept varchar(2) null,
ecgsource varchar(2) null,
)
bulk insert Electrocardiogram from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Electrocardiogram.csv'
with (firstrow=2, KEEPNULLS, format='CSV')


create table Laboratory (
personid int,
labdate datetime null,
labname varchar(2) null,
labvalue float null
)

bulk insert Laboratory from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Laboratory.csv'
with (firstrow=2, KEEPNULLS, format='CSV')




create table Diagnosis (
personid int,
diagdate datetime,
diagcode varchar(20),
diaglocalcode varchar(20),
diagdept varchar(2)
)

bulk insert Diagnosis from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Diagnosis.csv'
with (firstrow=2, KEEPNULLS, format='CSV')




drop table Drug
create table Drug (
personid int,
drugdate datetime null,
druglocalcode varchar(20),
atccode varchar(20),
drugdept varchar(2),
[route] varchar(2),
duration int
)

bulk insert Drug from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\sample_Drug.csv'
with (firstrow=2, KEEPNULLS, format='CSV')



create table DiagnosisCodeMaster (
diaglocalcode varchar(20),
diagnosis varchar(max)
)


bulk insert DiagnosisCodeMaster from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\DiagnosisCodeMaster.csv'
with (firstrow=2, KEEPNULLS, format='CSV')



create table DrugCodeMaster (
druglocalcode varchar(20),
drugigrdname varchar(100)
)
bulk insert DrugCodeMaster from 'C:\Users\is2js\Desktop\인턴활동\0111 DB세미나\sql_loading_sample\DrugCodeMaster.csv'
with (firstrow=2, KEEPNULLS, format='CSV')


-- 실전 분석해보기 --
-- alt+f1, 엑셀--
[dbo].[person]
[dbo].[drug]
[dbo].[DrugCodeMaster]
[dbo].[Diagnosis]
[dbo].[DiagnosisCodeMaster]
[dbo].[Electrocardiogram]
[dbo].[laboratory]

-- 개수 한꺼번에 세기
SELECT COUNT(1) FROM [person]                --18570
SELECT COUNT(1) FROM [drug]                    --1890626
SELECT COUNT(1) FROM [DrugCodeMaster]        --2627
SELECT COUNT(1) FROM [Diagnosis]            --296690
SELECT COUNT(1) FROM [DiagnosisCodeMaster]    --7553
SELECT COUNT(1) FROM [Electrocardiogram]    --35932
SELECT COUNT(1) FROM [laboratory]            --147716


---person table 분석
Person
--칼럼명
personid
sex
birthday
ethnicity

-- 데이터 전체 head보기
SELECT TOP 100 * FROM person
-- key 칼럼의 distinct한 개수 보기
-- person
SELECT COUNT(DISTINCT personid) FROM person        --18570

-- sex    
SELECT TOP 100 sex FROM person                    --head

SELECT DISTINCT sex FROM person                    --sex의 종류 : 1, 0

SELECT COUNT(DISTINCT sex) FROM person            --sex 종류개수 : 2가지 - 종류가 너무 많을 때

SELECT sex, COUNT(DISTINCTpersonid) cnt FROM person        --1    9307 / 0    9263
GROUP BY sex


SELECT 9307 + 9263                                -- 전체(18570)과 sex의 범주별 합(1+0)이 같은지 보고, sex에서 null없나 확인

SELECT * FROM person                            -- sex에 null값 확인
WHERE sex IS NULL

SELECT sex, COUNT(personid) FROM person
GROUP BY sex
ORDER BY 1

--birthday
SELECT TOP 100 birthday FROM person                    --head

SELECT birthday, COUNT(personid) cnt FROM person    --시계열별 groupby count(사람수)
GROUP BY birthday

SELECT birthday, COUNT(personid) cnt FROM person    --시계열별 groupby count(사람수) -> Order by 후 엑셀 시각화
GROUP BY birthday
ORDER BY 1


SELECT GETDATE()

SELECT DATEDIFF(year, birthday, GETDATE()) FROM person        -- 해나이 : interval을 year로
SELECT DATEDIFF(day, birthday, GETDATE()) / 365 FROM person -- 만나이 : interval을 day로 계산한 뒤, 365일을 나눠주기(몫만 남음)

SELECT  DATEDIFF(year, birthday, GETDATE()) 해나이, DATEDIFF(day, birthday, GETDATE()) / 365 만나이 FROM person


SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * FROM person

-- 변형칼럼을 groupby 하기 위해, SELECT *INTO* FROM으로 새 테이블 만들기
SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * 
INTO person_with_age
FROM person

SELECT TOP 100 * FROM person_with_age

-- 새 테이블로 연령대별 groupby 사람수 카운트하기
SELECT 연령대, COUNT(personid) cnt FROM person_with_age
GROUP BY 연령대
ORDER BY 1


--subquery로 테이블 생성없이 하기
SELECT 연령대, COUNT(personid) cnt FROM (
SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * FROM person
)Z
GROUP BY 연령대
ORDER BY 1

-- sex > 연령대 순으로 해야지 엑셀로 시각화 하기 쉬움(나중에 앎)
SELECT 연령대, sex,  COUNT(personid) cnt FROM (
SELECT
CASE WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 0 and 9
    THEN '10세 미만'
     WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 10 and 19
    THEN '10대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 20 and 29
    THEN '20대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 30 and 39
    THEN '30대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 40 and 49
    THEN '40대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 50 and 59
    THEN '50대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 60 and 69
    THEN '60대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 70 and 79
    THEN '70대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 BETWEEN 80 and 89
    THEN '80대'
    WHEN  DATEDIFF(day, birthday, GETDATE()) / 365 >= 90
    THEN '90세 이상'
END AS 연령대, * FROM person
)Z
GROUP BY 연령대, sex
ORDER BY 1, 2

-- 엑셀에서 연령대별 성별별 시각화해보기 : shift+핸들바로 밀어올리기 활용 / 홈>스타일> 조건부서식 > 데이터 막대



--ethnicity 칼럼
SELECT TOP 100 ethnicity FROM person

SELECT DISTINCT ethnicity FROM person

SELECT COUNT(DISTINCT ethnicity) FROM person

SELECT ethnicity, COUNT(personid) cnt FROM person
GROUP BY ethnicity
ORDER BY 1
--0    172
--1    18398




--drug 테이블--
drug
personid
drugdate
druglocalcode
atccode
drugdept
route
duration

SELECT COUNT(1) FROM drug                                --1890626

SELECT TOP 10 * FROM drug

--key칼럼인 personid
SELECT COUNT(DISTINCT personid) FROM person                --18570
SELECT COUNT(DISTINCT personid) FROM drug                --15430  약 안받은 환자 3100명

SELECT cast(15430 as float)/ cast(18570 as float)        --0.830910070005385 = 83%
SELECT 15430 * 1.0 / 18570


--범주형 drugdept
SELECT TOP 100 drugdept FROM drug

SELECT DISTINCT drugdept FROM drug                        -- I E H O

SELECT COUNT(DISTINCT drugdept) FROM drug                -- 범주 4가지

SELECT drugdept, COUNT(personid) cnt FROM drug
GROUP BY drugdept
ORDER BY 1
--E    82257
--H    1770
--I    1431086
--O    375513

SELECT drugdept, COUNT(DISTINCT personid) cnt FROM drug
GROUP BY drugdept
ORDER BY 1
--E    5298
--H    868
--I    8747
--O    11294

SELECT 82257+1770+1431086+375513                        --1890626  == drug전체데이터 1890626 => null값 없음
SELECT * FROM drug WHERE drugdept IS NULL




-- 범주형 route칼럼
SELECT TOP 100 route FROM drug

SELECT DISTINCT route FROM drug                            --E P 
SELECT COUNT(DISTINCT route) FROM drug                    --2종류

SELECT route, COUNT(personid) cnt FROM drug
GROUP BY route
ORDER BY 1
--E    1176263
--P    714363

SELECT * FROM drug WHERE route IS NULL






-- 범주형 같아보이지만 연속형 duration
SELECT TOP 100 duration FROM drug


SELECT DISTINCT duration FROM drug    
SELECT DISTINCT CONVERT(smallint, duration) "duration(int)" FROM drug        --응용                    -- 
ORDER BY 1

SELECT COUNT(DISTINCT duration) FROM drug                    --126종류


ALTER TABLE drug ALTER COLUMN duration int                    --칼럼 타입 변경

SELECT MIN(duration) min, MAX(duration) max, AVG(duration) avg FROM drug -- 0    390    7





--날짜(생년월일x) 칼럼 drugdate
SELECT TOP 100 drugdate FROM drug

ALTER TABLE drug ALTER COLUMN drugdate DATETIME                    --칼럼 타입 변경

SELECT MIN(drugdate) min, MAX(drugdate) max FROM drug        --1994-06-24 00:00:00    2011-08-23 00:00:00

SELECT TOP 100 CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug -- CONVERT 112 : YYYYmmdd

SELECT TOP 100 SUBSTRING(CONVERT(char(20), drugdate, 112), 1, 4) as year FROM drug -- 필요시 SUBSTRING( CONVERT, 시작,이후갯수) ->: YYYY

SELECT YYYYmmdd
FROM (
SELECT  CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug 
)Z

SELECT MIN(YYYYmmdd) min, MAX(YYYYmmdd) max                                                                
FROM (
SELECT CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug 
)Z


SELECT YYYYmmdd year, COUNT(personid)                                                             
FROM (
SELECT CONVERT(char(20), drugdate, 112) as YYYYmmdd, * FROM drug 
)Z
GROUP BY YYYYmmdd
ORDER BY 1





--연속형처럼보이는 범주형 code칼럼 : druglocalcode

SELECT DISTINCT druglocalcode FROM drug                    -- 종류 너무 많음

SELECT COUNT(DISTINCT druglocalcode) FROM drug            --2276

SELECT druglocalcode, COUNT(personid) cnt            -- 범주의 종류가 많으면, 카운트 칼럼으로 머를 많이 썼는지 DESC ORDER BY
FROM drug
GROUP BY druglocalcode
ORDER BY 2 DESC
--Drug2069    27997
--Drug1472    23168
--Drug452    22800
--Drug2163    21542
--Drug2501    20501


SELECT * FROM DrugCodeMaster WHERE druglocalcode = 'Drug2069';    --Drug2069    Tramadol.HCl
--1)
SELECT drugigrname FROM DrugCodeMaster WHERE druglocalcode = 'Drug2069';
--2)
SELECT drugigrname FROM DrugCodeMaster WHERE druglocalcode = druglocalcode;
--3)
SELECT 
(SELECT drugigrname FROM DrugCodeMaster dcm WHERE dcm.druglocalcode = d.druglocalcode) drugigrname, *
FROM drug d
--4)
SELECT 
(SELECT drugigrname FROM DrugCodeMaster dcm WHERE dcm.druglocalcode = d.druglocalcode) drugigrname, d.druglocalcode, COUNT(personid) cnt
FROM drug d
GROUP BY d.druglocalcode
ORDER BY 3 DESC

--join
SELECT * FROM Drug a
JOIN DrugCodeMaster b ON a.druglocalcode = b.druglocalcode

SELECT drugigrname, a.druglocalcode, COUNT(personid) cnt FROM Drug a
JOIN DrugCodeMaster b ON a.druglocalcode = b.druglocalcode
GROUP BY drugigrname, a.druglocalcode
ORDER BY 3 DESC


--연도별 처방빈도
SELECT SUBSTRING( CONVERT(varchar(8), drugdate,112), 1, 4) year, * FROM drug

SELECT year, COUNT(personid) cnt
FROM(
SELECT SUBSTRING( CONVERT(varchar(8), drugdate,112), 1, 4) year, * FROM drug
)Z
GROUP BY year
ORDER BY year DESC

--월별별 처방빈도
SELECT months, COUNT(personid) cnt
FROM(
SELECT SUBSTRING( CONVERT(varchar(8), drugdate,112), 5, 2) months, * FROM drug
)Z
GROUP BY months
ORDER BY months ASC



-- 범주형칼럼 route
SELECT TOP 100 route FROM drug

SELECT DISTINCT route FROM drug

SELECT COUNT(DISTINCT route) FROM drug

SELECT route, COUNT(DISTINCT personid) cnt FROM drug 
GROUP BY route
--E    14321
--P    12077

SELECT COUNT(DISTINCT personid) FROM drug            -- 15430 사람수보다 E + P이 크다 = 교집합이 있다.

SELECT * FROM drug WHERE route IS NULL



--의미내포 범주형 code칼럼 : atccode
SELECT TOP 100 atccode FROM drug

SELECT COUNT(DISTINCT atccode) FROM drug            --984

SELECT LEFT(atccode, 1) FROM drug
SELECT LEFT(atccode, 1), * FROM drug

SELECT                                                -- 내포코드별 빈도확인
atc_1, COUNT(personid) cnt
FROM(
SELECT LEFT(atccode, 1) atc_1, * FROM drug
)Z
GROUP BY atc_1
ORDER BY 2 DESC


-- 연도별 내포코드별 빈도확인
SELECT SUBSTRING(CONVERT(varchar(8), drugdate, 112), 1, 4) FROM drug

SELECT                                                
year, atc_1, COUNT(personid) cnt
FROM(
SELECT SUBSTRING(CONVERT(varchar(8), drugdate, 112), 1, 4) year, LEFT(atccode, 1) atc_1, * FROM drug
)Z
GROUP BY year, atc_1
ORDER BY 1 ASC

서울대 연구공원 단지 내에 있는 SK TACACDEMY의 세미나를 다녀왔다.

시계열 데이터를 다루는 방법이 무척 궁금했는데

엄청 유용하게 쓰일 것 같다.

연습해야하는데 시간이 없다. ㅠ

낙성대역에서 관악2번타고 10분 정도 가면, 서울대 연구공원단지가 있음
거기에 삼성전자, LG, SK, COWAY 연구실이 다모여있었다. ㄷㄷ

중간에는 카페테리아 건물이 있어서 밥도 먹었다.

우아~~

MSSQL 의 sp_help(alt+F1)과 같은 기능은 존재하지 않는다.

SELECT *
FROM information_schema.columns
WHERE table_schema = 'your_schema'; --public
AND table_name = 'your_table'; --customers, purchases, items

위와 같은 구분으로 실행할 순 있다.

join

join은 python에서 set과 마찬가지다.
유니크한 요소들이 순서가 없다.
그러나, 이들은 교집합/ 합집합 / 여집합 계산이 가능하다는 장점이 있다.
SELECT * FROM 테이블1의 ON조건 대해서 INNER JOIN 테이블2의 ON조건을 테이블1에서부터 하나씩 검사하면서 교집합 있는 것들을 붙혀서 row단위로 나타낸다.

1. INNER JOIN = 교집합
   1) SELECT * FROM 테이블1(Customers)로 다 보이게 할 건데,
   2) INNER JOIN 테이블2(Orders) 을 붙힐건데,
   
   3) ON 테이블1.id = 테이블2.id 로 교집합을 row를 붙힌다. 나는 테이블1의 ON인 id 1에 대해서 테이블2가 걸리는 것들을 검사해서 하나씩 붙힌다고 생각했다. 1-jose에 대해서, orders테이블에는 customer_id가 1인데 3개가 있으니 3개가 연달아 붙을 것이다.
   
   
   
   왼쪽테이블의 2는 걸리는게 없다.
   왼쪽테이블의 3-anna와 걸리는게 1개 있으니 붙힌다

2. LEFT JOIN역시 인기가 많은 JOIN으로서,
   LEFT에 있는 테이블1은 모두 살리면서, 교집합을 검사하는데, 왼쪽테이블1으로 검사시 교집합이 없어도 BLANK(MSSQL : NULL)로 나타내면서 테이블1을 다 살린다.
   1) 테이블1을 기준으로 검사하는데 걸리는게 있으면 나타낸다.
   
   
   

   2) 만약 걸리는게 없어도, 테이블1은 살리면서 테이블2는 NULL로 나타낸다.
   
   

3. RIGHT JOIN은 LEFT JOIN과 반대로, 오른쪽에서 검사를 시작하면서, 교집합이 없어도 오른쪽은 살리면 된다
   

4. FULL JOIN은 교집합 + 여집합까지 다나타내는 것이다. LEFT JOIN과 RIGHT JOIN을 모두 생각해야한다. 왼1 -> 오1 -> 왼2 -> 오2 -> 순으로 하나씩 교집합과 여집합을 챙겨준다.
   1) 일단 나는 왼쪽테이블1에 대해 교집합을 찾고, 오른쪽테이블1은 교집합으로 물려갔으니 생각안한다.

   2) 왼쪽테이블2에 대해서는 교집합이 없으니 여집합으로서 나타낸다.

   3) 오른쪽테이블2에 대해서도 교집합이 없으니 여집합으로 챙긴다.

   4) 왼3에서는 교집합이 있으니 교집합으로 챙긴다.
   

   5) 오3은 이미 왼1로서 교집합으로 잡혀갔다.

   6) 왼4는 여집합으로서 챙긴다.
   

   7) 오4는 이미 교집합으로 잡혀갔다.

join examples

SELECT * FROM purchases;

SELECT * FROM items;

SELECT * FROM customers;

-- 1) item들 중에 구매한 items 목록을 원하는 경우  
SELECT * FROM items  
JOIN purchases ON items.id = purchases.id;

-- left조인을 쓴다면, 구매하지 않은 아이템도 블랭크로 붙는다.  
SELECT * FROM items  
LEFT JOIN purchases ON items.id = purchases.id;

-- 2) 이제 어느 고객이 어느 아이템을 샀는지 join해보자.  
SELECT * FROM customers  
JOIN purchases ON customers.id = purchases.customer_id;

--left join  
SELECT * FROM customers  
LEFT JOIN purchases ON customers.id = purchases.customer_id;

-- 고객 중에 구매를 한 고객 join데이터 중 이름만 select하기  
SELECT customers.first\_name, customers.last_name FROM customers  
JOIN purchases ON customers.id = purchases.customer_id;

-- 3) ***3개 테이블 한꺼번에 join해보기***  
---- (1) items중에 구매한 것들만 item만 나오도록 inner join한다.  
SELECT * FROM items  
INNER JOIN purchases ON items.id = purchases.item_id

---- (2) 여기서 보이는 customer\_id로 고객들을 연결할 수 있을 것이다. *** JOIN한것에 바로 JOIN을 해버린다.  
SELECT * FROM items  
INNER JOIN purchases ON items.id = purchases.item_id  
INNER JOIN customers ON purchases.customer_id = customers.id

---- (3) 필요한 칼럼만 챙겨보자.  
SELECT customers.first_name, customers.last_name, items.name, items.price FROM items  
INNER JOIN purchases ON items.id = purchases.item_id  
INNER JOIN customers ON purchases.customer\_id = customers.id

한* 선생님께서 통계 책을 빌려주셔서 쉽게 이해하였다.

일단 내가 조사한 설문지의 n수가 각각 6, 12, 111인데
이것들의 민감도, 특이도 수치를 비교하기 위해서는

3그룹이 ANOVA 아니면 Kruskal-Willis test 인 것 같다.
하지만, n수가 적어 정규분포를 따르지 않으므로 ANOVA를 제낀다.

• K-W test는 정규분포를 따르지 않으니 3그룹 모두 크기별로 나열하고 순위를 정한 뒤, 순위의 합을 비교하는 방식이다.

비모수검정인 K-W test를 해서 p <0.05인 부분을 찾아봤는데
3가지 약재에서 1개만 집단별 차이를 보였다.

차이를 보인다면 사후검정을 해야한다.

사후검정의 종류를 찾아보니 Mann-whitney test가 있었다.

그 중 박사-한의사, 박사-학생만 차이를 보였다.

이것을 논문에 기술하는 것도 처음인데, 다른 논문을 참고하기가 쉽지가 않다.

그냥 pubmed에 해당 테스트를 사용한 figure를 몇개 찾은 뒤 종합해서 table을 만들고, 기술했다..

잘 될련지 모르겠다. ㅠ

UPDATE

1. UPDATE (테이블명) SET (칼럼) = 대입값 WHERE (특정칼럼 조건) 의 형식이다.
   DML(S, U, I, D) 중 하나이며, U,I,D는 INSERT INTO (테이블) VALUE ()처럼, 테이블명이 먼저나오지만 결국 칼럼을 조작한다.
   numeric(자리수,소수점자리수) 데이터로 integer로 표현할 수 없는 decimal point(소수점)을 표시한다
   .
   UPDATE로 칼럼값을 바꿀 때는, WHERE에 primary key인 id 같은 것으로 검색하자.
   cf) ALTER는 PRIMARY KEY를 주는 등의 테이블 자체 수정을 한다. UPDATE로 칼럼 값을 바꾼다.
   또한, UPDATE와 DELETE는 반드시 WHERE를 달아줘야 전체 데이터를 보호할 수 있다.

DELETE

• DML(S,U,I,D)정리
  SELECT 칼럼 FROM 테이블
  UPDATE 테이블 SET 칼럼=값 WHERE 조건
  INSERT INTO 테이블 VALUES ( , , )
  DELETE FROM 테이블 WHERE 조건

1. DELETE는 위험하기 때문에 SELECT 먼저 하고, DELETE로 수정하라고 배웠다.
   
   

2. 하지만 SQL은 똑똑하기 때문에 아래와 같이 알려준다.
   item 테이블의 id = 4는 purchases테이블에서 fk_purchase_item 칼럼으로 foreign key로 작용하고 있기 때문에 에러가 난다
   

3. 해당 테이블을 조회해보자.
   

4. 맨 처음 purchases테이블을 생성할 때, 아래와 같이 pk, fk를 넣어줬었다.
   
   ALTER TABLE "public"."purchases" ADD CONSTRAINT "fk_purchase_item" FOREIGN KEY ("item_id") **REFERENCES "public"."items" ("id") ON UPDATE NO ACTION ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;

5. 그럼 items 테이블의 id = 4를 지우려면, 먼저 그 것을 foreign key로 걸어둔 purchases 테이블의 id = 4를 삭제해줘야한다.
   
   

   
   

Wildcard for filtering unknowns : LIKE 'wildcard';

여기서는 필드 전체가 아니라 필드의 일부분에 근거해서 필터링 하는 방법을 배워보자.
WHERE 칼럼명 IN ('', '');의 경우에는 해당 값 전체가 있어야한다.
여기서는
WHERE 칼럼명 **LIKE '패턴'**;을 이용해 정규표현식처럼 값 일부로 추출할 수 있다.

1. customers 테이블의 last_name칼럼에는 t가 들어가는 사람이 2명 있다.
   LIKE 칼럼 '%';에서 %는 모든 문자가 줄지어 나타남(any character)을 의미.
   

2. LIKE 칼럼명 '___'; 언더스코어는 갯수만큼 문자열수를 가지는 값(single character)을 필터링한다
   _ 4개
   
   _ 5개
   

3. %를 응용하면,** 중간에 특정문자가 끼여있는 것을 추출**할 수 있다
   예를 들어 %t%
   

4. 또 응용하여, %t_로 모든문자로 시작하다가 t+1개의 문자로 끝나는 것을 추출할 수 있다.
   

DB에서 SQL쿼리 창 띄우고, query문으로 table생성 후 command해보기

1. 돋보기 모양을 클릭하면, Query창이 뜬다. 이것으로 db와 상호작용할 것이다.

2. 쿼리창에는 열기 / 복 / 붙 / 실행 / 실행 후 저장 / 현재 상호작용하는 db명 on 서버명 등이 메뉴로 있다.

3. 가운데 화살표를 내리면, 이전에 작성한 쿼리문 또한 활용할 수 있는 history가 있다.

간단한 DB파일 조작해보기

다운로드

sql-database.zip

select

1. 첨부된 파일에서 7_1, 2, 3 파일을 열고 각각을 실행해서 TABLE들을 생성하고-> 다시 열고 다시 실행을 반복하자. 3가지 테이블이 생성될 것이다.

2. SELECT문으로 customers; 테이블을 가져와보자.
   varchar(100)에서 var는 varying의 의미로 글자수를 제한할때 (숫자)와 같이 쓰는 것 같다.
   
   기본적으로 select 뒤에 칼럼을 가져올 때는, 테이블명.dot 칼럼 을 찍어주고 가져오는 것을 기 본적으로 해야하는 것 같다. F5키는 먹히나, alt+f1의 sp_help의 기능(mssql)은 없는 것 같다.
   

3. alias에 빈칸이 들어갈 경우 " " 반드시 쌍따옴표로 감싸주어야한다. 일반 글자는 쌍따옴표 없이 되는 것을 확인했다.
   또한, 그냥 F5를 누르면 전체 실행, 선택+F5시 해당코드만 실행되는 것 같다.
   

where

4. where 문에 들어가는 조건 칼럼도 테이블명. 을 찍어주자.
   값에 대해서는 ' ' 작은 따옴표(single quote mark)다. alias만 쌍따옴표를 쓰는 것 같다.
   sql문만 single equal =을 쓰는 것도 명심하자.
   WHERE문의 조건에는 AND 와 OR 도 쓸 수 있다.
   
   

limit ***

1. LIMIT row개수는 은 FROM 뒤에서 나타나는 row를 제한하는데,
   단독으로는 useful하지 않지만, 첫번째 손님, 첫번째 가입자 등을 볼 때 사용한다고 한다
   또한 페이지에 100개만 표시할 때도 사용된 다고 한다.

mssql 등의 TOP 1 * 과 차이점있는지는 모르겠음

MSSQL 단축키

1. ALT + F1 : 테이블 정보보기
2. CTRL + K + C : 단번에 주석처리
3. CTRL + K + U : 단번에 주석제거

• DATEDIFF, CONVERT, SUBSTRING()으로 가공한 칼럼을 다시 처리( COUNT, GROUP BY, MIN, MAX )할 때 : SUBQUERY with alias
• WHERE LIKE %(any char) or _(single char)로 필터링
• COUNT(key칼럼)시 중복허용 전체수(약 처방건수, 약 code = 약 몇개 등)이냐 / 유니크한수(Person테이블의 id와 join되는 해당하는 각 테이블별 id칼럼 -> 사람 몇명, 약탄 사람 몇명 등)냐 고려하기
• 백업을 위한 RENAME TABLE a TO b
• 날짜는 CONVERT 후 SUBSTRING or LEFT / 생일은 DATEDIFF로 ( 나이변경 ) -> DATEDIFF + CASE WHEN( 연령대구간 ) 까지 해당테이블에서 한 뒤, subquery로 groupby 
• 집계함수를 적용하는 집계테이블에 대해서는 SELECT로 따로 칼럼 보려면 --> GROUP BY 안에 넣어줘야한다.
• JOIN으로 인해 기준테이이블 + 집계적용할 테이블이   따로 있는 경우는 --> 기준테이블의 id로 GROUP BY하고, 칼럼도 마음대로 SELECT 가능

DB 분석

1. DB 생성 후 use

2. Table을 csv로 가져오기(db우클릭>테스크>데이터가져오기) or CREATE TABLE 생성후 BULK INSERT 칼럼명 FROM '파일'

3. 각 Table 정보보기(ALT+F1) -> 엑셀에서 테이블별 칼럼들 시각화 해보기 + 주석으로 칼럼명 나열해놓기
   이미 했다면, 칼럼명들 복사해놓기

4. 각 테이블의 개수 세기(COUNT(1)) - 여러테이블에 대해 key칼럼을 모르는 상태에서는 1로 count
   -중복허용 전체 데이터수

5. 각 테이블의 각 칼럼들 분석

   - 전체 head보기

   - id칼럼이 겹친다면, 각 테이블마다 DISTINCT id칼럼을 count해서 비율 비교하기 ex> 환자수 : 약을 처방받은 환자수 
       => 각각 cast( as float)한 다음 나눠서 or 분자에 * 1.0 만 곱해서 비율보기
   COUNT(key칼럼)
   ex> 전체 환자수 vs 약을 받은 환자수
   ex> 전체 환자 중 약을 받은 환자의 비율

   각 칼럼이 범주형인지 / 연속형(숫자,코드)/ 시계열인지 보기
   groupby시 항상 key칼럼(id, pk,fk)으로 COUNT하기

범주형

0. 각 칼럼의 head만 보기 (cf. key칼럼 = 사람을 의미)

1. distinct한 범주 종류보기 -> 종류가 너무 많을 때, distinct한 범주의 수 세기(범주가 너무 많으면 세야함)

2. 범주별groupby를 DISTINCT key칼럼(사람수)으로 count -> DISTINCT key칼럼의 전체 개수와 비교해서 범주안의 null찾기or 서로 교집합이 존재유무확인 or where 범주 is null로 확인하기

3. DISTINCT count하는 GROUPBY(환자, 사람) 인지 / 그냥 count하는 GROUPBY(처방된 약의개수, 건수, 빈도) 생각해서 하기
   
   

매우많은 범주형 CODE칼럼 : Drug - druglocalcode

0. 각 칼럼의 head만 보기

1. 많은 범주의 갯수 확인하기 (code들은 DISTINCT한 범주의 수 COUNT하기)
   -범주의 종류는 너무 많으므로, count만 해도 된다.

2. 코드별 빈도 TOP5 확인
   code별로 groupby를 key칼럼 COUNT + code들은 범주의 종류가 너무 많으므로 어떤 약을 많이 썼는지 Cnt로 DESC ORDER BY => TOP5만 복사해놓기

3. 코드로 -> 다른테이블 처방 성분명확인 1)
   top 1 code를 code해석테이블에서 WHERE 코드명 = top1코드명으로 조회하고, 해석칼럼명 기억하기

4. 코드로 -> 다른테이블 처방 성분명확인한 해석칼럼만 SELECT안의 칼럼으로 가져오기(inlineView)   2)
   3.의 조회문장에
   1) 해석테이블의 SELECT 문장에 * 를 해석칼럼명으로 바꾼다.( 그것만 가져올 준비)
   2) 코드테이블안에 1) 쿼리를 ()괄호로 싸서 inlineview형태의 칼럼처럼 가져온다.
   3) 각 테이블에 alias를 주고 -> inlineview 안의 WHERE절에 alias를 달면서 join의 ON처럼 a.코드칼럼 = b.코드칼럼을 조건으로 준다.
   4) 코드테이블안의 다른칼럼들도 다 가져온다 *
   5) 바깥 SELECT 문에서 필요한 칼럼inlineview(해석칼럼), code칼럼 + COUNT()-groupby만 챙기기 + ORDER by cnt DESC 넣기
   inlineview는 가공칼럼이라 group by에 alias가 못들어간다. 그래서 code칼럼만 넣어준다.

5. 코드로 -> 다른테이블 처방 성분명확인 1-2)
   JOIN문으로 4번을 대체하기
   
   
   

의미를 내포하는 매우많은 범주형 CODE칼럼 : Drug - atccode

0. 각 칼럼의 head만 보기

1. 많은 범주의 갯수 확인하기 (code들은 DISTINCT한 범주의 수 COUNT하기)
   -범주의 종류는 너무 많으므로, count만 해도 된다.

2. 단순코드와 다르게, 먼저 의미 잘라내기
   여기서는 첫번째 시작문자만 잘라준다. DATETIME을 자를 때는 CONVERT 후 SUBSTRING or LEFT, 문자열 코드는 바로 SUBSTRING or LEFT로 자르기 => 변형 칼럼 alias + 다시 전체칼럼(*)들과 같이 SELECT하는 subquery로 필요한 칼럼들만 선택해서 처리 or 같은 테이블내 LEFT()등의 1차 처리는 바로 컬럼으로 사용가능 but GROUPBY 에서도 1차 처리한 문장을 주어야함. alias 안들어감

3. 코드별 빈도확인code별로 groupby를 key칼럼 COUNT + code들은 범주의 종류가 너무 많으므로 어떤 약을 많이 썼는지 Cnt로 DESC ORDER BY => TOP5만 복사해놓기

4. 연도별 + 코드별 빈도확인
   1) datetime칼럼에서 연도만 잘라낸 뒤(CONVERT -> SUBSTRING or LEFT)
   2) 연도칼럼을 2에서 제작한 subquery(가상테이블)에 alias와 함께 합친다.
   3) groupby + order by

5. long데이터 엑셀 시각화

만약, subquery를 안쓰고 가공한 칼럼을 SELECT문에 그대로 사용한다면, GROUP BY 자리에서는 alias가 안통하고 통째로 옮겨줘야한다.

범주형처럼 문자열 되어있는 연속형(숫자) : Drug - duration

0. 각 칼럼의 head만 보기

1. 만약, 범주의 종류를 확인해보고 싶다면, CONVERT(int,칼럼명) + alias한 것의 DISTINCT -> ORDER BY alias

2. int형으로 바꿔준다.문자형에 MIN, MAX를 사용한다면, 맨앞의 숫자를 기준으로 정렬되기 때문에, int나 datetime에서만 MIN, MAX를 사용하자 UPDATE는 값에 대한 것들이다. 컬럼자체의 정보는 table 단위로 해석해서 DDL인 ALTER를 사용한다.

   ALTER TABLE drug ALTER COLUMN duration int

   

3. 연속형은 MIN, MAX, AVG로 처리.3개의 통계함수로 한번에 SELECT 후 => 평균 보고 min,max에서 아웃라이어 판단하기

생년월일(시계열,날짜형1) : Person-birthday**

0. 각 칼럼의 head만 보기 (cf. key칼럼 = 사람을 의미)
   (만약, string타입이면 DATETIME 타입으로 바꾸기)

   ALTER TABLE drug ALTER COLUMN drugdate DATETIME

1. 시계열별groupby를 key칼럼으로 count 하기

2. order by까지 완료 후 -> 엑셀로 시각화하기(셀서식-날짜-피벗)

3. DATEDIFF( year/day , 시작날짜, 현재날짜(GETDATE())를 이용하여 생년월일 -> 나이( 해 나이 / 만 나이 )로 변형된 컬럼 보기

4. DATEDIFF문을 그대로 이용해서 CASE WHEN THEN END로 만 나이를 연령대별 구간으로 짤라 범주형으로 만들고 난뒤 +  * 와 같이보기 

5. 변형 칼럼인 연령대별(범주별) key칼럼으로 개수(사람수) 세기 위해 4번에서 SELECT ~ FROM 사이에 INTO 새 테이블명으로 테이블 만들기 or Subquery를 이용해 테이블로 취급하여 칼럼 SELEC하기

6. 연령대별 사람수를 엑셀로 시각화하기

7. subquery로 만든 연령대별 + 범주별(sex) 카운트해보기

8. 연령대별+성별별 count를 엑셀에서 종모양으로 시각화해보기

날짜(시계열,날짜형2) : Drug-drugdate**

0. 각 칼럼의 head만 보기 (cf. key칼럼 = 사람을 의미)
   (만약, string타입이면 DATETIME 타입으로 바꾸기)

   ALTER TABLE drug ALTER COLUMN drugdate DATETIME

1. MIN, MAX로 데이터의 시작과 끝날짜 확인하여 데이터 구성날짜 알기

2. CONVERT(type,칼럼명,style)로 datetime style 바꿔주기 + TOP 100 붙혀서 조금만 조회하기

   • style
     10 = mm-dd-yy <- char(8)
     12 = yymmdd <- char(8)
     112 = yyyymmdd <- char(8)
     20 = yyyy-mm-dd <- char(10)
   • (필요시)연도만 잘라내기 위해 SUBSTRING(칼럼명,시작index,그 뒤로 개수)

3. 연도별 drug처방빈도 확인하기
   날짜CONVERT칼럼 with alias + 원본(*)칼럼들합해서 subquery로 FROM 뒤에 가상테이블화하는 subquery(변형칼럼 alias필수)를 이용하여 (TOP 100 지우기)
   -SELECT 날짜alias 선택후 COUNT, MIN, MAX, GROUP BY 등 FROM SUBQUERY로 가상테이블

4. order by까지 완료 후 -> 엑셀로 시각화하기(셀서식-날짜-추천 피벗-피벗차트)

def class

PosgreSQL 다운 및 설치

1. https://www.postgresql.org/download/ 에 접속하기
   

2. Windwos 클릭 > Download installer 클릭 > x86-64비트 클릭
   

3. 인스톨러 다운받고 설치

• 이 때, 폴더위치나 PORT는 default로 지정하는 것을 추천한다. 마지막에 Builder는 설치해제한다
• C:\Program Files\PostgreSQL\9.5
• C:\Program Files\PostgreSQL\9.5\data
• Port : 5432
  
  

4. 이제 설치폴더 > bin 폴더에 가서 실행파일들을 보자.
   이 가운데 pgAdmin 을 실행시키면, sql서버와 유저에 관한 정보를 쉽게 다룰 수 있다.

• C:\Program Files\PostgreSQL\9.5\bin
  
  

pgAdmin3 - Login Role 만들고 DB 생성하기

1. SQL 서버를 먼저 연결해주자. 우리 컴퓨터가 하나의의 로컬 서버가 된 것이다.
   

2. 마지막의 Login Roles에 등록된 postgres는 절대 관리자에 관한 규칙이니,, 사용하지 않은 편이 좋다.
   Login Roles에서 우클릭을 통해 새로운 유저의룰을 추가해주자.
   

3. definition 탭에서는 비밀번호를 입력해주자.
   Connection Limit을 설정하면, 그 횟수만큼만 접속할 수 있다.
   

4. Role privileges 에서는 기본적으로 부모 역할을 상속받는다고 되어있다.
   이 때, create databases를 선택해서 DB를 생성할 수 있게 끔 해주자.
   

5. •SQL탭에서 보면, 방금 만든 룰이 적용되어있다. 비밀번호는 MD5라는 알고리즘에 의해 암호화 되어있다고 한다.
   

6. 우클릭으로 새 DB 생성
   
   

7. Definition탭에서,
   Encoding은 default로 UTF8로 지정해놓은 상태에서,
   Collation은 아래와 같이 Korea관련된 것으로 했다. 만약 변경이 필요하면 변경해야지..
   그 외의 것들은 default로 생성해보자.
   
   

8. 생성한 DB에서는 여러 기본 메뉴들이 있다. 여기서 가장 중요한 것은
   Schema > Public > Tables 일 것이다.