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 * 과 차이점있는지는 모르겠음

티스토리 블로그에 SyntaxHighliter 적용

1. 위의 파일을 다운로드 받고 압축을 푼다.
   압축파일에서 scripts폴더 안의 파일 + styles폴더 안의 파일을 업로드할 준비
   
   

2. 블로그에서 [Q]를 눌러 관리자 모드로 진입 > 꾸미기 > 스킨 편집 선택
   

3. html 편집 선택 > 파일 업로드 선택 > 위에서 언급한 2폴더 다 업로드 후 > 적용
   
   
   

4. 다시 HTML 클릭 후, 코드가 끝나는 부분 찾기
   

5. \head 안쪽에다가 아래 코드들 집어넣기 > 적용
   https://gist.github.com/is2js/d12729a9d122b0ae5488276ddf0e467f#file-tistory_syntaxhighliter-js
   

syntaxhighliter 사용해보기

1. 테마 바꾸기
   shThemeEclipse.css (default 테마)가 있는 부분을 찾아서 아래 목록 중에 하나로 바꾸면 된다.
   
   

2. 글쓰기 사용법 : html 를 선택한 상태에서 < pre class= brush:언어명>

   코드

   형식으로 사용한다. 언어명 목록은 아래와 같다.
   

 def class
 

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 일 것이다.
   

DB를 가져와서 엑셀로 시각화하기

각 칼럼 분석하기

통계를 배우는 이유

• 통계 : 실험으로 발견한 차이가 우연에 의한 것이 아니다(우연에 의하여 예측된 차이보다 크다)를 밝힘(증명)
  - 앞뒤가 동일한 동전을 100번 던져, 48:52 49:51은 우연일 수 있지만, 40:60쯤 되면 우연에 의한 것이 아니라는 것을 의심
  - 인터넷광고로 판매량이 늘어날까요? => 차이를 우연에 의한것 인지/아닌지를 통계로 밝힌다(평균차이 T-test)
  - 인터넷광고 하고/안하고가 차이가 있을까요? => 차이를 우연에 의한 것인지/아닌지를 통계로 밝힌다.
  - 쌓여있는 데이터가 너무 없어서 근거가 부족할 것 같아요. 어떻게 해야하죠?=> *근거가 부족할 시 비모수 검정
  - 추천 강의 : K-MOOC 류\*
  **

전통적인 통계학 vs 현대의 통계학

• 전통적인 통계학은

1. n수가 작았다.
2. 농업 분야의 데이터가 실험계획법이 많았다.
3. 수식적인 증명과 수리적인 알고리즘으로 소통

• 현대의 통계학

1. 빅데이터( n수가 많다 )
2. Location(위도, 경도), TimeStamp(mm-dd-yyyy hh:MM:ss) 등의 데이터가 많음.
3. 통계 Tool이 많다.

데이터 분석시 고려해야할 4가지

1. 보정 : Expect(기대되는 차이)가 target변수 때문에 생긴 것인지 알기 위해서, 다른 변수들을 control(통제)
2. Faceting : 전체(수술)을 더 쪼개서 (성형수술/외과수술)나누어 보면, 다른 insight가 있을 수 있다.
3. 아웃라이어와 지대값 : y의 값(outlier)와 x의 값(지대값)이 평균에 너무 떨어져 있어서 -> 평균이나 회귀계수에 영향을 준다.
   - cf) 영향점 : 회귀에서 결정계수를 낮추진 않지만, 회귀 직선의 기울기에 영향을 주는 것
4. 결측치(NA) : R에서는 하나의 NA가 있더라도, 기초통계량을 NA로 반환하므로 없애야한다.
   - R에서는 기초통계함수()의 인자로 na.rm=TRUE를 줘야한다.

통계학의 영역 : 기술통계와 추론통계

• 기술 통계(Descriptive Statistics) : 평균, 분산 등의 숫자와 그래픽으로 기술

1. Numerically

• Centering(중심) 척도 : 평균 / 중앙값, 절삭평균 / 최빈값
  - 자유도 1일 때, 데이터를 가장 잘 나타내는 것
  - 중앙값(median) : Outlier에 의해 극값을 가질 수 있는 평균의 현상을 막기 위한 것
  - Outlier가 있는 경우 쓰는 중심척도
  1) 중앙값(Median) : 데이터를 크기순으로 정렬한 후 가운데 값 (홀수개면 가운데, 짝수개면 가운데2개의 평균)
  2) 절삭평균(Truncated mean) : 10% truncated mean이라면, 양 끝값 10%제거 후 가운데 80%로 평균
  cf) 가중 평균 : 각 요소별로 가중치를 곱해서 구하는 평균 ex> 수능 점수 -> 대학별 유리하게 반영

• Spread 척도 : 분산 / 표준편차 / IQR
  - 자유도 2일 때, 중심척도(자유도 1)만으로 부족한 내용을 설명하는 것
  - 분산 : <평균으로 부터> 얼마나 떨어졌는지를 ( 실제값 - 평균) 제곱의 평균

• 그외 : 왜도 / 첨도
  - 왜도(Skewness) : 치우친 정도 자료의 대칭성을 알아보는 척도,
  - 분포 그래프에서 좌우대칭에 비해 꼬리가 긴쪽이 해당 skewed
  - 첨도(kurtosis) : 정규분포에서 그래프이 봉우리가 뾰족한 정도

3. Graphically : 그래프

• 추론 통계(Inference Statistics) : 알 수 없는 모집단을 잘 대표할 수 있는 sample(표본)을 뽑고 -> 그것으로 모집단 추정
  - 1) 모집단을 잘 대표할 수 있는 데이터를 sampling 하고
  - 2) 수집한 데이터(표본, sample)을 가지고 모집단(Population)을 추정하는 것
  -3) 추정시에는 항상 얼마나 믿음직한지 신뢰구간도 같이 제시한다.

1. 다양한 표본 추출 방법들 ( sampling의 단계 )

2. Proportional to Population Size(PPS) : 가장 많이 이용되는 샘플링
   - 모집단 속 여러 집단들(서울대병원, 연세대병원, ... , 서울성모병원) 중 Size큰 곳에 높은 확률 배정해서 샘플링

3. 층화 : 모집단 속 여러집단들(남자, 여자)에서 몇번 뽑을 것인지 미리 할당하고 샘플링

4. Simple Random Sampling(SRS) : 무작위로 뽑는 것

5. 집락 : 층화와 비슷

6. 계통 : k계통법이라고 하여, k= 5 지나가는 사람들 중 5번째만 뽑음. k=1 전수조사

• 보통 sampling의 단계 : PPS -> 층화 -> SRS

4. sample로 모집단을 추정하는 2가지 방법

• 점 추정 : 170 or 170 이하 or 170 이상 ( 특정 점 or 이상 or 이하)
• 구간 추정 : 160 ~ 180 사이 ( 특정 구간 사이)

6. 추정시 제시하는 신뢰구간은 좁을수록 명사수

코딩시 참고 프로그램

• 여러 line을 동시에 작업할 때 [ Alt + 여러줄 드래그 ] 후 작성
  

JOIN의 핵심

key 칼럼을 잘 설정했느냐

JOIN의 종류

1. (INNER) JOIN : 교집합
2. (LEFT/RIGHT/FULL) OUTER JOIN : 차집합, 여집합
3. CROSS JOIN : 곱집합

for문은 DB에서 크게 의미없다.  오히려 파이썬 for문이 더 빠르다고 한다.

JOIN 연습하기

CREATE TABLE A (
Letter_U CHAR(1),  --문자1개
Number smallint    --126까지 허용
)
INSERT A VALUES ('A', 1)
INSERT A VALUES ('A', 2)
INSERT A VALUES ('B', 1)
INSERT A VALUES ('C', 2)

SELECT * FROM A

CREATE TABLE B (
Letter_U CHAR(1), -- A테이블과 연결 key칼럼
Letter_S CHAR(1)
)

INSERT B VALUES ('A', 'a')
INSERT B VALUES ('D', 'd')

SELECT * FROM A
SELECT * FROM B

--- JOIN 해보기 ---***
-- WHERE가 일반 조건문이라면, JOIN문의 JOIN의 WHERE는 ON이다.!  JOIN ON 테이블.key칼럼!***
-- SELECT FROM A AS - JOIN B AS - ON -.key칼럼 = -.key칼럼
--1. INNER JOIN = JOIN  : 교집합이라 선후관계가 상관없다.
SELECT * FROM A
     JOIN B ON A.Letter_U  = B.Letter_U

--2. alias를 이용해서, 해보기
SELECT * FROM A AS a
     JOIN B AS b ON a.Letter_U = b.Letter_S

--3. OUTER JOIN -LEFT JOIN : 한쪽 테이블을 다 살리면서 붙힌다.***---
-- 실전에서는 잘 안쓰긴 하나, 쓰는 경우는
-- A 진단이 없었던 환자를 찾아줘 -> A 진단 있는 것을 LEFT JOIN 으로 붙힌 다음, B테이블이 NULL인 환자를 찾기

-- 1) LEFT JOIN

SELECT * FROM A AS ta
     LEFT JOIN B tb ON ta.Letter_U = tb.Letter_U

-- 연습. JOIN된 것 중에    칼럼 IS NULL 인것만 뽑아보기
SELECT * FROM A ta
     LEFT JOIN B tb ON ta.Letter_U = tb.Letter_U
     WHERE tb.Letter_U IS NULL

--***참고 DELETE를 할 때는, 먼저 SELECT를 한 뒤 하자.***--
INSERT B VALUES ('C', 'c');
SELECT * FROM B WHERE Letter_S = 'C'
DELETE FROM B WHERE Letter_S = 'C'

-- 2) RIGHT JOIN
-- RIGHT 테이블인 B보다 갯수가 늘었다  = Key칼럼자체가 Unique하지 않은 상태로, 서로 빼먹은 것이 존재해서 늘어남***
SELECT * FROM A ta
    RIGHT JOIN B tb ON ta.Letter_U = tb.Letter_U

-- 3) FULL OUTER JOIN ***
-- 교집합 뿐만 아니라 각각의 테이블이 가지는 여집합(차집합)까지 모두 포함해서(NULL이 한곳에서 뜬 상태)나온다.
SELECT * FROM A ta
     FULL OUTER JOIN B tb ON ta.Letter_U = tb.Letter_U

--- CROSS JOIN (곱집합)---
-- 쓸일 진짜 없다. 데이터 뿔릴 때 씀
-- 예를 들어, 학년 X 반의 모든 콤비네이션을 만들 때,

CREATE TABLE 학년
( 학년 CHAR(1) )

CREATE TABLE 반
( 반 CHAR(1) )

INSERT 학년 VALUES (1)
INSERT 학년 VALUES (2)
INSERT 학년 VALUES (3)
INSERT 학년 VALUES (4)
INSERT 학년 VALUES (5)
INSERT 학년 VALUES (6)

INSERT 반 VALUES (1)
INSERT 반 VALUES (2)
INSERT 반 VALUES (3)
INSERT 반 VALUES (4)

SELECT * FROM 학년
SELECT * FROM 반

SELECT 학년, 반 FROM 학년 ta
     CROSS JOIN 반 tb

-- 2번째 칼럼인 반을 기준으로 정렬되길래 ORDER BY 1로 첫번째 칼럼을 기준으로 정렬시켜보기***
SELECT 학년, 반 FROM 학년 ta
     CROSS JOIN 반 tb
     ORDER BY 1

-- SELECT뒤에 나올 칼럼을 + 연산할 수 있으며, 문자열이 들어가면, 문자열로 연결해준다 ****
-- 이쁘게 만들기 위해, SELECT 부분에 학년 + 반 칼럼을 '-'로 문자열로 이어주면, MSSQL이 알아서 문자열로 취급해준다.
SELECT 학년 + '-' + 반 FROM 학년 ta
     CROSS JOIN 반 tb

-- 연습용 Table 삭제하기
DROP TABLE A
DROP TABLE B
DROP TABLE 학년
DROP TABLE 반

index

인덱스를 해야하는 이유는 쿼리 속도를 늘리기 위해서,

• 원래 50개의 데이터 -> 기계어 -> 메모리 or 하드에 01010101 로 데이터+주소를 파일형태로 저장
• 하나의 쿼리 -> where를 이용해서 특정칼럼을 뽑아오는데, 파일시스템은 전체 데이터를 검색한다.
• 특정칼럼을 인덱스로 지정해놓으면 (여러개 칼럼 중 자주 사용하는 특정칼럼(자주 변하는 체중))
  -> 컴퓨터가 인덱스 칼럼를 뽑아서 새로운 파일로 만들어 놓는다.
  -> 쿼리날릴 때 WHERE조건에 인덱스를 주면, 아주 빠르게 검색해온다.
  
  
• 인덱스의 종류는 2가지가 있는데, Clustered / NonClustered가 있는데 Clustered가 더 빠르다.
• MSSQL에서는 1개의 Clustered 밖에 못 만들고, 보통은 PRIMARY KEY를 인덱스로 지정한다.
  
  
• 이름- 주민번호 / 주민번호-학번  의 2개의 테이블이 있다면,
  주민번호가 한 테이블의 PRIMARY KEY가 되고 -> 다른 테이블의 FOREIGN KEY가 되며 -> 각 KEY는 index로 잡혀 있는게 일반적인 RDB의 기본 index형태이다.
  
  
• T-SQL에서는 1개의 CLUSTERED INDEX를 지정할 수 있으니
  WHERE절이나, JOIN절에 가장 많이 넣는 칼럼을 index로 지정하자.

가진 Table의 index 확인

sp_help 테이블네임 -> index가 있을 경우, index_name이 보인다.

sp_help BANK_A -> index 지정 안해줬으니 안나옴.

--★ Index 예시 ★--------
CREATE CLUSTERED INDEX personid ON Person (personid);
CREATE NONCLUSTERED INDEX birthday ON Person (birthday);

SET SHOWPLAN_ALL { ON | OFF } 
SET SHOWPLAN_TEXT { ON | OFF } 
SET STATISTICS IO { ON | OFF } 
SET STATISTICS TIME { ON | OFF } 
SET STATISTICS PROFILE { ON | OFF } 

Data Science Life Cyle에서의 SQL

1. Business Understanding
2. Data Mining
3. Data Cleaning
4. Data Exploration
5. Feature Engineering
6. Predictive Modeling
7. Data Visualizing

위의 진한 부분에서 SQL은 강력한 툴이 된다.

Data에 대한 이해

1. 빈 값(0)과 Null의 차이 -> Survivalship Bias(생존자 편향의 오류)
   - 빈 값 = 0 = 정보량이 0이라는 말
   - Null 정보량이 아예 없다는 말
   - 0을 빠트리지 말자
   
   
2. 각 부분에 대한 평균이 크다고 해서 전체의 평균이 크지 않다 -> Simpson's Padadox(심슨의 역설)
   - 전체에 대한 이해 = 주요 변수, 주요 차원(한 단계 아래 차원의 변수)에 대한 이해가 없다면 결과를 왜곡
   - 수술 성공률만 보지말고, 무슨 수술인지도 봐야한다.
   
   
   

SQL(Structured Query Language) = 언어의 그룹이다.

BASE : Standard SQL  위에 4가지 종류가 있다.

1. PL/SQL(Procedural Language for SQL)
2. T-SQL(Transaction SQL)
3. Extended SQL/PSM(SQL/Persistent Stored Modules)
4. PL/pgSQL(Procedural Language/PostgreSQL)

DBMS(Database Mnangement System)

데이터를 저장하기 위한 [ 데이터 + 스크립트 + 저장장치 ]를 잘 다루기 위해 기능을 제공하는 시스템

SQL의 구조

1. DML(데이터 조작 어) : SELECT, INSERT ,UPDATE, DELETE ( S U I  D)
2. DDL(데이터 정의 어) : CREATE DROP TRUNCATE(메모리절약) ALTER(수정, TSQL용) RENAME(MySQL용) ( C D T A R )
3. DCL(데이터 제어 어) : GRANT REVOKE ( G R )
4. TCL(트랜젝선 제어 어) : COMMIT ROLLBACK - data의 퀄리티를 높힐 수 있다.

Transacation

data가 양방향에서 깨지지 않게, 한 묶음으로 한번에 실행하는 것

1. 이것을 활용하여 UPDATE 전/후의 비교를 통해 쿼리문을 확인할 수 있다.
2. data 백업이 어려운 상황에서수정을 해야할 때 활용할 수 있다.

연습 쿼리문

--DDL(CREATE)--
--계좌 1--
CREATE TABLE BANK_A (
이름 CHAR(20), -- 이름은 조금 크게 잡을 수 있다.
계좌 TEXT,     -- 20글자보다 이상 들어간다.
잔고 INT
)

--계좌 2--
CREATE TABLE BANK_B (
이름 CHAR(20),
계좌 TEXT,
잔고 INT
)

--DML--
--돈 미리 넣어놓기--***
INSERT BANK_A VALUES ('조재성', '1', '500000');
INSERT BANK_B VALUES ('김석영', '2', '0');

--계좌 확인--
SELECT * FROM BANK_A
SELECT * FROM BANK_B

--TRANSACTION으로 송금해보기--***
--UPDATE 테이블 SET 바꾸고 싶은 칼럼 = 가지고 계산 WHERE ***
--DELETE와 UPDATE시 WHERE 조건 꼭 달아줄 것***

-- 1. 먼저 한쪽에서 보낸 것만 해보고 ROLLBACK 해보기
BEGIN TRAN
UPDATE BANK_A SET 잔고 = 잔고 - 50000 WHERE 이름 = '조재성'

SELECT * FROM BANK_A
SELECT * FROM BANK_B

--여기 전까지 한꺼번에 선택해서 F5
ROLLBACK --TRAN이 맘에 안들어서 취소

-- 2. 한쪽에서 보내고, 한쪽은 받는 로직까지
BEGIN TRAN
UPDATE BANK_A SET 잔고 = 잔고 - 50000 WHERE 이름 = '조재성'
UPDATE BANK_B SET 잔고 = 잔고 + 50000 WHERE 이름 = '김석영'

SELECT * FROM BANK_A
SELECT * FROM BANK_B

--여기 전까지 한꺼번에 선택해서 F5
COMMIT   --TRAN이 맘에 든 것을 저장

--- SELECT FROM 구성
-- SELECT ALL | DISTINCT   칼럼명 | *
-- FROM  테이블명
-- WHERE
-- GROUP BY
-- HAVING
-- ORDER BY

--- 데이터 조회---
SELECT * FROM Person;
SELECT * FROM Drug;

--- 필터링 ---
SELECT * FROM Person WHERE sex = 1; --9307
SELECT * FROM Person WHERE sex = 0; --9263

--- 카운팅 ---
SELECT COUNT(*) FROM Person; -- 18570  -- 전체 row개수만

SELECT COUNT(*) FROM Person -- 9263, 9307 -- sex칼럼별로 <sex값 없이 row개수만>***
GROUP BY Sex;

SELECT Sex, COUNT(*) FROM Person -- 9263, 9307 -- sex칼럼별로 + <SEX칼럼표시> + <row개수>***
GROUP BY Sex;

--- GROUP BY with 집계함수---
SELECT * FROM Person GROUP BY Sex; -- GROUP BY 는 항상 집계함수가 포함되어있어야 된다. 안그러면 오류

SELECT COUNT(*) FROM Person GROUP BY Sex; --칼럼자리에 GROUP BY한 칼럼명을 입력하지 않으면, 해당 칼럼의 요소가 보이질 않음

--- 칼럼별 DISTINCT(유니크한 값) 보기 --- ****
SELECT DISTINCT personid FROM Drug; --- SELECT 다음에 DISTINCT 칼럼명

--- 상위 TOP 몇개만 보기 --- ***
SELECT TOP 3 * FROM Drug;
SELECT TOP 10 * FROM Person;

--- WHERE 조건에 칼럼명 BETWEEN AND 넣어보기---***
SELECT * FROM DRUG;
SELECT * FROM Drug WHERE duration BETWEEN 3 AND 5

SELECT * FROM Electrocardiogram;
SELECT * FROM Electrocardiogram WHERE QTc BETWEEN 400 AND 500;

--- WHERE 조건에 칼럼명 = OR , 칼럼명 = OR ---
SELECT TOP 15 * FROM Drug;
SELECT * FROM Drug WHERE atccode = 'N01BB02' OR atccode = 'N07AA02'; --- ***문자열에 쌍따옴표 x 그냥 따옴표

--- WHERE 조건에 칼럼명 IN ( '', '', '') 써보기 ---***
SELECT * FROM Drug WHERE atccode IN ( 'N01BB02', 'N07AA02');

--- WHERE 조건에 칼럼명 LIKE '% , [A-Z] 정규식' 사용하기 ---***
--- %는 앞뒤로 모든 문자가 줄지어 나타남
--- [A-Z]는 한글자에 대해서 매칭
SELECT * FROM Drug WHERE atccode LIKE 'J01%' --*** J01로 시작하는 모든 것
SELECT * FROM Drug WHERE atccode LIKE 'J01[A-Z][A-Z}10' ---*** J01[?][?]10 가운데 2개 아무거나

--- FROM > WHERE GROUP BY HAVING ORDER BY에서 ---

--- ORDER BY 칼럼명 DESC/ASC ---***
SELECT TOP 15 * FROM Drug;

SELECT * FROM Drug ORDER BY personid DESC;
SELECT * FROM Drug ORDER BY personid, drugdate ASC;

SELECT * FROM Drug WHERE duration > 90 
ORDER BY personid ASC,drugdate DESC     -- WHERE (g) (h) ORDER BY 순

--- 집계함수 후 AS 새로운 칼럼명 ---***
-- cast()해주고 계산하기
-- AVG, SUM, COUNT, MAX, MIN
SELECT AVG(duration) AS avg_duration FROM Drug -- 7 : 문자열로 입력되어있을 시, cast( as float)를 안해주면 소수점 다 짤려나온다.
SELECT AVG( cast(duration AS float)) as avg_duration FROM Drug  -- 7.48

SELECT COUNT(*) AS row_cnt FROM Drug;

SELECT drugdept FROM Drug GROUP BY drugdept; -- 그룹별로, 집계없이 해당 칼럼만 보면, 그 종류만 나온다
SELECT drugdept, COUNT(*) FROM Drug GROUP BY drugdept; -- 그룹별로 종류 + cnt
SELECT drugdept, COUNT(*) AS drugdept_cnt FROM Drug GROUP BY drugdept;

-- route의 그룹별, duration의 최대값 구하기
SELECT DISTINCT route FROM Drug;

SELECT route, MAX(duration) AS max_duration FROM Drug
GROUP BY route

설치

• 구글에서 sql exress 2017 검색
• MSSQL 설치 완료시 뜨는 SSMS( SQL Server Managemnet Studio) 설치
• 데이터(private) : https://github.com/is2js/SQL-study

단축키들

1. F5 : 선택쿼리 실행
2. F8 : 개체탐색기로 이동( 새로고침시 )
3. ALT+F1 : 선택칼럼 도움말(index 등 정보) = sp_help 칼럼명

MSSQL - SSMS로 csv파일 7개 연습하기

1. 연결된 나의 SQL server에서 새 데이터베이스 만들기 (csv파일만 가진 상태면, DB 먼저 새로 만들어놓기)

   
   

2. csv파일을 DB로 가져오기 위해선
   *DB 우클릭 > 테스트 > 데이터 가져오기 >
   - 데이터 원본 선택에서 txt, csv, tsv 등 은 플랫 파일 원본을 선택 >
   - 이 때, 파일 이름으로 찾을 때, txt파일만 볼 수 있으니, 우측하단에서 csv 선택해서 하나씩 가져오기 >
   - 미리보기로 데이터 확인하기
   - 대상선택에서는 Microsoft OLE DB Provider for SQL Server 선택 ! MSSQL 깐 순간
   대상이 내 노트북이 SQL server가 된 것임. 이 때, window 인증을 사용해서 sql도 자동인증 되게 하자.
   sql인증을 사용하면 아뒤 비번 따로 사용해서 강력하게 인증함.
   *
   
   
   
   
   
   

3. DB를 선택한 상태에서, 쿼리문으로 한번에 table(BULK INSERT 테이블 from '경로') 다 올리기 ( 쿼리 실행 F5 )
   - DB를 클릭한 상태 > 우클릭 새쿼리 or Ctrl + N 으로 쿼리창 띄우기
   - 미리 빈 테이블(csv와 칼럼명 + 데이터타입 기)을 만들어놔야한다.
   - 만든 뒤, 개체탐색기(F8)에서 새로고침(F5)를 몇번 해야 생긴다.
   - 생긴 table에 BULK INSERT 테이블 '경로' WITH (firstrow = 2, format='CSV')를 이용한다.

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

   
   
   
   

4. 이제부터는 귀찮아서, DB -> 테스트 -> 데이터 가져오기로 table들을 다 만들었다.

R mark down으로 작성

par(mfrow = c(1,1))
barplot(c(1, 2, 3, 4)) # y값만 입력한 경우

barplot(c(1, 2, 3, 4), names.arg = c("A", "B", "C", "D")) # y값과 x축이름을 지정한 경우

attach(iris)

head(iris)

str(iris)

## 'data.frame':    150 obs. of  5 variables:
##  $ Sepal.Length: num  5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...
##  $ Sepal.Width : num  3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...
##  $ Petal.Length: num  1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...
##  $ Petal.Width : num  0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...
##  $ Species     : Factor w/ 3 levels "setosa","versicolor",..: 1 1 1 1 1 1 1 1 1 1 ...

# bar의 높이(y값)으로 쓰일 각 칼럼의 <종별>  평균 계산하기
# aggregate( 통계구할 칼럼, 기준칼럼(list(col1, col2), 통계함수)
# -> 종을 기준으로 4개의 칼럼의 평균값 구하기
iris.mean <- aggregate(iris[, 1:4], iris["Species"], mean)
iris.mean

# bar의 error bar로 쓰일 각 칼럼 <종별> 표준편차 구하기
iris.sd <- aggregate(iris[,1:4], iris["Species"], sd)
iris.sd

# df + aggregate로 구한 표준편차 df는 첫칼럼이 기준(종별)이다.
iris.sd 

# 기준칼럼 말고, 수치가 있는 [,2:5]칼럼만 가져와서
# error bar의 위쪽값과 아래쪽값을 구하자.
iris.sd.upper <- iris.mean[, 2:5] + iris.sd[, 2:5]
iris.sd.lower <- iris.mean[, 2:5] - iris.sd[, 2:5]

iris.sd.upper

iris.mean[, 2:5]

iris.sd.lower

barplot( as.matrix(iris.mean[,2:5]), 
                   col=c("red", "blue", "purple"))

# beside = TRUE 를 넣어주면, 좌표로 그린 df의 row별로 
# barplot이 옆으로 그려지고, 
# beside = FALSE는 stacked로 그려진다.
barplot( as.matrix(iris.mean[,2:5]), 
         beside = TRUE,
         col=c("red", "blue", "purple"))

b <-barplot( as.matrix(iris.mean[,2:5]), 
         beside = TRUE,
         col=c("red", "blue", "purple"),
         ylim=c(0,8))

# arrows는 이미 그려진곳에 그리는 것 같다. 안 그려놓으면 knit시 오류난다.
barplot( as.matrix(iris.mean[,2:5]), 
         beside = TRUE,
         col=c("red", "blue", "purple"),
         ylim=c(0,8))
arrows(b, as.matrix(iris.sd.upper), #기준, 화살표 끝의 좌표
      b, as.matrix(iris.sd.lower), #기준, 화살표 끝의 좌표
      angle = 90,                  # 몸통에서 나가는 화살표촉의 각도
      length = 0.05,               # 화살표 촉의 길이
      code = 3)                    # 화살표의 갯수 : 1시작점에 화살표 2끝점에 화살표 3 양쪽에 화살표

• 시작프로그램에서 cmd 검색 > 관리자 권환으로 실행
  
  
  
• net user administrator /active:yes
  
  
  
• 재부팅