SQL 데이터 분석의 순서

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