오늘도 데이터분석

03. 분산과 표준편차 : 흩어져 있는 데이터 상태를 추정하는 통계량 

- 평균값은 데이터들이 그 주변에 분포되어 있음(데이터의 분포를 대표하는 수치)

  그러나 어느 정도 퍼져 있는지, 흩어져 있는지 알 수 없음

- 평균값보다 불규칙한 상태의 통계량을 아는 것이 중요  

- 편차: 평균값과의 차이 (편차의 평균값은 항상 0 )

- 분산 = (편차제곱의 총합) / (데이터의 총 개수)

- 표준편차: 편차의 제곱평균값, 단위가 달라지는 분산의 단점 보완 

--> 대표값(평균)을 기준으로 대략 어느정도 멀리 위치해 있는지를 나타내는 통계량 

- 도수분포표의 표준편차 :

  {(계급값-평균값)^2 X 상대도수}의 합계  =  분산

  분산의 제곱근 = 표준편차

[연습문제]

1. 평균 계산

평균값: 5

2. 편차 계산 

3. 편차의 제곱과 그 평균(=분산) 계산

편차 제곱의 평균(분산) : 4

4. 표준편차 : 2

04. 표준편차 : 데이터의 특수성을 평가 

- 표준편차를 알면

(1) 데이터 세트 중 한 데이터의 수치가 갖는 의미(특수성)

(2) 여러 데이터 세트를 비교해서 나타나는 차이

를 알 수 있음

- 표준편차로 계산해서 몇 배만큼 평균보다 높다, 낮다 표현하는 방법

   --> (데이터 - 평균값) / 표준편차

- 위 식으로 계산한 결과가  ±1배 라면 '평범한 데이터',  ±2배로 멀리 있으면 '특수한 데이터'

- [데이터에 일정한 수 a 를 더하면] 평균값은 + a, 분산과 표준편차는 그대로

- [데이터에 일정한 수 k를 곱하면] 평균값은 k를 곱한 값, 분산은 k의 제곱배, 표준편차는 k배

-  (데이터 - 평균값) / 표준편차 로 가공하면, 이 데이터로 구한 평균은 0, 표준편차는 1

[연습문제]

* 본 포스팅은 책 <세상에서 가장 쉬운 통계학입문> 을 바탕으로 작성되었습니다.

통계학 기초 스터디를 시작했다. 대학생 때 통계학을 공부하긴 했지만 몇 년 전이라 개념을 다시 쫙 정리하고 싶었는데 

규칙적으로 꾸준히 하고 싶어서 이번에 아예 스터디에 들어갔다. 

책 보니까 다 아는 내용이었는데 그래도 새롭다.... 

앞으로 이곳에는 통계학 개념 정리 및 연습문제 풀이를 기록하려 한다. 

01. 도수분포표와 히스토그램 : 데이터의 특징을 돋보이게 하는 도구 

- 데이터 자체만으로는 정보를 알 수 없음. 통계적 분석으로 정보를 얻음 
-분포한다 : 다양한 수치로 나타나는 것
-불확실성을 지닌 분포 속에서 고유한 특징이나 반복되는 것이 있음. 통계는 이를 찾아내는 방법 
-축약: 나열되어 있는 많은 숫자를 어떤 기준으로 정리정돈해서 의미있는 정보만을 추출하는 것 
축약방법 2가지 (1) 그래프 (2) 통계량
-도수분포표: 데이터를 5~8개 그룹(계급)으로 나눈 것, 데이터의 특성 파악가능
-히스토그램: 도수분포표를 그래프로 바꾼 것 

[연습문제]

도수분포표

- 데이터 중 최대값, 최소값을 찾아 범위(계급)를 만듦

- 계급값 : 계급을 대표하는 값, 주로 가운데 값을 선택

- 도수: 각 계급의 총 개수 

- 상대도수: 도수가 차지하는 비율

*계급 안에 몇 개의 데이터가 있는 지는 알 수 있지만 정확히 각각 어떤 수치인지는 알 수 없음 --> "축약"

우리가 알고 싶은 건 데이터 전체가 아닌 데이터가 가지고 있는 "특징"

히스토그램

02. 평균값의 역할과 평균값을 이해하는 방법 : 평균값은 지렛대가 균형을 이루는 지점 

- 통계량: 데이터의 특징을 하나의 숫자로 요약 , 모든 데이터를 대표하는 수 
- 도수분포표에서의 평균값: {계급값 X 상대도수}의 합 
*계급값으로 계산하는 이유는 같은 계급의 각 데이터의 합계가 {계급값X도수}로 바꿔도 큰 차이가 나지 않기 때문 => 평균값에 큰 영향을 주지 않음
- 히스토그램 평균값은 지렛대가 균형을 이루는 받침점이 됨 

[연습문제]

평균값

-널리 퍼져있는 데이터들 중 모든 데이터를 대표하는 수 로 뽑은 것

- 데이터들은 평균값 주변에 분포

- 많이 나타나는 데이터는 평균값에 영향력이 큼

- 히스토그램이 좌우대칭인 경우 평균값은 대칭이 되는 축에 위치 

- 평균값에는 종류가 많음(산술평균, 기하평균, 제곱평균, 조화평균 등) -> 무조건 산술평균이 아니라 목적에 따라 선택

* 본 포스팅은 책 <세상에서 가장 쉬운 통계학입문> 을 바탕으로 작성되었습니다. 

<과제 목표>

현업이 제출한 데이터 추출 요청서를 확인 후 요구사항의 애매모호성 제거하여 데이터 추출 작업 준비하기.

상황

내년부터 우리 회사에 직원 복지항목으로 자녀 학자금에 대한 지원이 추가됨.

관련하여 인사팀에서 현황을 파악하고자 관련 데이터 추출을 요청함.

1. 현업의 요구사항을 모두 파악 후 추출하려는 데이터의 레이아웃을 그려보세요

*자녀순서는 몇 번째 자녀인지 의미 

데이터는 은퇴한 직원의 정보도 담고 있지만 재직 중인 임직원을 대상으로 하는 복지혜택이기 때문에 재직여부는 필요없을 것 같다.  

FEEDBACK 이후

2열: 테이블

3열: 컬럼

2. 애매모호한 부분에 밑줄을 긋고 어떻게 명확히 할 지 수정해 보세요.

- 기혼의 임직원 중 자녀가 1명 이상의 인 자

미혼모, 미혼부도 있을 수 있다 생각.

재작자중 자녀코드를 가지고 있고 자녀가 1명이상이면 기혼으로 간주

- 자녀 나이가 5~7세인자

나이의 기준이 만나이인지 세는 나이(한국식나이)인지 연나이인지 애매모호함

또한 만나이를 기준으로 삼을 경우 어느 날짜를 기준으로 만나이를 할 것인지도 부정확함

만나이로 할 경우에는 "2022년 01월 01일 기준으로 만5~7세 자녀"로 기준 세우는 것이 명확함.

그리고 나이보다 출생연도를 기준으로 세우는 것도 명확하다고 생각.

예를 들어 2022년 기준으로 한국식 나이 5세~7세에 해당되는 "2016년 01월 01일 ~ 2018년 12월 31일 사이 출생자"

- 해당자녀의 나이, 성별표시

마찬가지로 나이의 기준이 애매모호함.

만나이, 연나이, 세는나이(한국식나이) 기준 명확화 필요

나이, 생년월일, 성별 추출

- 쌍생아의 경우 대표 1명만 표시

쌍생아의 기준: 관계코드가 자녀이면서 생년월일이 같은 경우

쌍생아 대표의 기준이 애매모호함. 

쌍생아의 경우에도 자녀 순서가 나뉘어 있음. 쌍생아 중 첫째를 대표로 표시 

또는 이름으로 오름차순 기준, 상위자로 추출,

+ 추가사항

- 2명이상의 자녀가 나이기준에 충족될 경우에 혜택 중복 여부 -> 나이가 더 많은 자녀를 대표 1명으로 표시 

- 임직원의 연차기준에 따른 복지혜택 적용 여부 -> 입사연도 상관없이 모든 임직원 복지적용 or 입사한 지 2년이상된 직원부터 복지 적용 등 기준세우기

FEEDBACK 이후 추가

배우자가 사내부부인 경우는 남편, 아내 중 한쪽만 가능

사내커플인 경우 부를 대상자에 포함하고 모는 제외

 배우자가 사내부부인 경우 배우자 명을 표시

대상자 부 기준으로 모의  직원번호 및 배우자 명을 함께 추출

3. SQL을 사용하여 문제에 답하세요.

(1) 직원번호 10004141 의 이름은? 

SELECT emp_no, emp_nm
FROM emp_c
WHERE emp_no = 10004141;

결과

(2) 직원번호 10004141 는 몇 명의 가족이 있나요? 

SELECT COUNT(emp_no)
FROM fam_c
WHERE emp_no = 10004141;

결과

(3) 직원번호 10004141 의 소속 부서 코드는? 

SELECT org_cd
FROM emp_c
WHERE emp_no = 10004141;

결과

(4) 직원번호 10004141 의 소속 부서 명은?  

SELECT org_nm
FROM org_c
WHERE org_cd = (
  SELECT org_cd
  FROM emp_c
  WHERE emp_no = 10004141
);

결과

(5) 남자 / 여자 직원이 몇 명인지 한번에 알 수 있는 sql 작성

SELECT gender_cd, COUNT(*)
FROM emp_c
GROUP BY gender_cd
ORDER BY gender_cd

(6) 직원별 자녀가 몇 명인지 한번에 알 수 있는 sql 작성

SELECT emp_no, COUNT(*) AS 자녀수
FROM fam_c
WHERE rel_type_cd = 'A27'
GROUP BY emp_no;

결과

(7) 생년월일이 1970년 1월 1일 이전인 직원의 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE birth_ymd < 19700101

결과

(8) 현재 재직 중인 직원의 전체 수를 구하는 sql 작성

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd LIKE '9999%'

SELECT COUNT(emp_no)
FROM emp_c
WHERE retire_ymd = '9999/12/31'

결과

과제1) 이번 캠프에 사용되는 4개의 테이블에 대해서 탐색해 보고 그 결과를 기술하여 보세요.
과제 목적: 데이터 및 테이블의 전략적 탐색능력 배양

EMP_C

SELECT * FROM EMP_C;

사용목적: 임직원의 입사 및 은퇴 정보가 저장된 테이블

특징 

1. 사번,직원이름,성별,생년월일, 부서코드,은퇴일자,고용일자 정보가 저장됨

2. 사번(EMP_NO)은 중복되지 않는 식별번호

3. 성별은 1, 2 로 분류

4. 사원 당 하나의 행을 가짐 

5. RETIRE_YMD에서 9999년은 지금도 다니고 있는 걸로 추정 

FAM_C

SELECT * FROM FAM_C;

사용목적: 임직원의 가족정보가 저장된 테이블

특징

1. 사번, 가족이름, 관계코드, 성별, 생년월일 등 정보가 저장됨

2. 부모, 자식, 형제 등 가족관계가 코드로 저장

3. EMP_C 테이블의 EMP_NO와 연관

4. 가족의 성별은 ‘1’, ‘2’ 로 분류

SELECT EMP_NO , COUNT(*) FROM FAM_C GROUP BY EMP_NO;

5. 임직원 한 명(사번)당 여러 행을 가질 수 있음

    -> 임직원 한 명당 가족 1명부터 최대13명까지 가족의 정보가 담겨 있음

SELECT rel_type_cd, child_seq FROM FAM_C WHERE child_seq IS NOT NULL;

6.  CHILD_SEQ 속성값이 있는 REL_TYPE_CD는 A27

   -> A27은 자녀 코드, CHILD_SEQ 컬럼은 자녀 순서(첫째,둘째,셋째)로 추정

ORG_C

SELECT * FROM ORG_C;

사용목적: 회사의 부서(지점) 정보가 저장된 테이블

특징

1. 부서코드, 부서이름, 지점타입, 시작날짜, 종료날짜 등 정보가 저장됨

  -> SUPER_ORG_CD, MGR_ORG_CD, AREA_ORG_CD 가 무슨 속성인지 정확히는 모르겠지만

      MGR_ORG_CD는 관리자부서코드, AREA_ORG_CD는 지역코드로 추정됨.

2. 각 지점이 코드로 저장

3. EMP_C 테이블의 ORG_CD와 연관됨

SELECT org_type FROM ORG_C GROUP BY org_type ORDER BY org_type;

4. 지점은 A~N, 총 11개의 타입으로 나뉨

5. 코드, 이름이 동일한 지점이 여러 행을 가질 수 있음

FAM_REL_C

SELECT * FROM FAM_REL_C;

사용목적: 가족 중에 임직원이 있는 경우, 직원과 해당 가족정보가 저장된 테이블

특징

SELECT emp_no, COUNT(*) FROM FAM_REL_C GROUP BY emp_no;

SQL Project Planning 문제 바로 가기 

문제

You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.

Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.

End_Date가 연속적이면(=다음 start_date와 같으면) 같은 프로젝트이다.
서로 다른 프로젝트의 start, end date가 나오도록 쿼리 입력.
결과는 프로젝트 걸린 기간이 작은 순대로, 기간이 동일할 경우 start date 순 대로 정렬

=>  start date가 end date에 없으면 개별 프로젝트가 새로 시작하는(연속이 끊기는) start date
      end date가 start date에 없으면 같은 프로젝트가 끝나는(연속이 끊기는) end date

풀이

1. end date에 없는 start date, start date에 없는 end date 추출

2. 시작날짜가 종료날짜보다 작아야 함

3. 시작날짜 기준으로 그룹화, 종료날짜는 최소날짜

4. 종료날짜와 시작날짜 차이 계산 -> 정렬, 차이 동일하면 start date기준 정렬

* 종료날짜와 시작날짜 차이 계산

   DATEDIFF 함수 사용

DATEDIFF : 두 날짜 사이의 일수 계산
DATEDIFF(날짜1, 날짜2) => 날짜1 - 날짜2 값이 나옴 
*날짜 안에 시간이 포함되도 일수 계산만 나옴
*날짜 범위를 초과하면 (ex. 22-13-01) NULL 반환

최종 쿼리

SELECT start_date, MIN(end_date)
FROM (SELECT start_date 
      FROM Projects
      WHERE start_date NOT IN (SELECT end_date FROM Projects)) s, 
      (SELECT end_date 
      FROM Projects
      WHERE end_date NOT IN (SELECT start_date FROM Projects)) e
WHERE start_date < end_date
GROUP BY start_date
ORDER by DATEDIFF(MIN(end_date), start_date), start_date

Ollivander's Inventory 문제 바로가기 

문제 

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

1. 론이 관심있을만한 wand 기준 : power, age는 높고, non-evil wand(is_evil = 0)이며 코인은 최소로 드는 wand
2. 론이 관심있을만한 wand의 id, age, coins_needed, power 를 결과값으로 나타내는 쿼리 입력
3. power 내림차순(정렬기준1), power같으면 age 내림차순(정렬기준2)
* wand_property 테이블에서 code와 age 매핑은 1:1이다 => 한 코드마다 나이 하나, 즉 동일 코드 = 동일 나이

문제 이해하는데 완전 오래걸림.....; 더 자세한 정보 및 예시는 위 문제링크에서 확인

풀이 

SELECT w.id, p.age, w.coins_needed, w.power
FROM (SELECT code, power, MIN(coins_needed)coins_needed
      FROM wands
      GROUP BY code, power) sub               -- code(age), power를 기준으로 최소 코인값만 저장
      INNER JOIN wands_property p ON sub.code = p.code -- property 조인 
      INNER JOIN wands w ON sub.code = w.code
                        AND sub.coins_needed = w.coins_needed 
                        AND sub.power = w.power  -- id 속성을 위해 wands 다시 조인 
WHERE p.is_evil = 0
ORDER BY power DESC, age DESC;

*PC화면에서 보기 적합하게 작성되었습니다. 

Challenges 문제 바로가기 

문제 

Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.

정리
- Hackers 테이블과 Challenges 테이블을 가지고 해커 id, 이름, 학생이 한 챌린지의 총 수를 결과값으로 나타내는 쿼리 입력 (*한 학생이 여러 챌린지 수행함)
- 결과는 챌린지의 수에 따라 내림차순(정렬기준1), 시행한 챌린지 수가 같으면 해커 id(정렬기준2)로 정렬
- 단, 시행한 챌린지의 수가 같을 때 그 챌린지 수가 MAX값이 아니면 결과에서 제외

Sample Input 0

Hackers Table: 

 Challenges Table: 

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

For Sample Case 0, we can get the following details:

Students  5077 and 62743  both created  4 challenges, but the maximum number of challenges created is 6 so these students are excluded from the result.

풀이 

풀이순서 
- 두 테이블 조인
- hacker_id와 name으로 그룹화 
- having으로 challenge_created(각 학생이 시행한 챌린지 수) 조건문
  1) challenge_created가 MAX값일 경우 
  2) challenge_created가 중복값이 없는 경우
- 정렬기준 충족

STEP 1

두 테이블을 조인하고 해커 id, 이름, challenges_created를 결과로 나타냄

각 학생별로 id, name, 시행한 챌린지 수가 결과로 나옴

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name

이 쿼리의 결과는 challenges_created 학생별로 시행한 챌린지 수가 같은 경우(중복)가 나옴

가장 큰 값(max)을 제외하고는 중복되는 값은 제외해줘야 함. 그래서 조건 필요

STEP 2

HAVING으로 challenges_created 조건

2-1 challenge_created가 MAX값일 경우 

SELECT MAX(challenge_created)
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
            FROM challenges
            GROUP BY hacker_id) sub

2-2 challenge_created가 중복값이 없는 경우

SELECT challenge_created
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
              FROM challenges
             GROUP BY hacker_id) sub
GROUP BY challenge_created
HAVING COUNT(*) = 1

최종 HAVING 문

각 조건을 서브쿼리로 지정

HAVING challenges_created IN (SELECT challenge_created
                                                      FROM (SELECT hacker_id,
                                                                                  COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub
                                                      GROUP BY challenge_created
                                                      HAVING COUNT(*) = 1)
          OR challenges_created = (SELECT MAX(challenge_created)
                                                      FROM (SELECT hacker_id,
                                                                                   COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub)

STEP 3 

정렬기준 포함한 최종 쿼리

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h 
     JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING  challenges_created IN (SELECT challenge_created
                               FROM (SELECT hacker_id,                                  
                                     COUNT(*) challenge_created
                                  FROM challenges
                                  GROUP BY hacker_id) sub
                            GROUP BY challenge_created
                            HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(challenge_created)
                             FROM (SELECT hacker_id, 
                                   COUNT(*) challenge_created
                                    FROM challenges
                                    GROUP BY hacker_id) sub)
ORDER BY challenges_created DESC, h.hacker_id

위와 동일한 쿼리를 WITH문 사용하는 경우 

WITH counter AS(
  SELECT h.hacker_id
       , h.name
       , COUNT(*) created_challenges
  FROM hackers h
      INNER JOIN challenges c ON h.hacker_id = c.hacker_id
  GROUP BY h.hacker_id, h.name
) 

SELECT  counter.hacker_id
      , counter.name
      , counter.created_challenges
FROM counter
WHERE created_challenges = (SELECT MAX(created_challenges) 
                            FROM counter)
OR created_challenges IN (SELECT created_challenges 
                          FROM counter 
                          GROUP BY created_challenges 
                          HAVING COUNT(*) = 1) 
ORDER BY counter.created_challenges DESC, counter.hacker_id

Binary Tree Nodes 문제 바로 가기

문제

문제 정리

- BST 테이블에 노드 값이 담긴 N과 노드의 부모값이 담긴 P  두 가지 속성이 있음.
- 이진트리의 각 노드의 노드타입을 결과로 나타내는 쿼리입력.
   (1)노드가 루트노드면 Root (최상위 노드, 부모가 없는 노드)
   (2)노드가 리프노트면 Leaf (자식이 없는 노드)
   (3)노드가 이너노드면 Inner(루트도, 잎 노드도 아닌 노드)
- N 내림차순 정렬

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

Sample Input

Sample Output

1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf

Explanation

The Binary Tree below illustrates the sample:

풀이

CASE 문 만들기 

* CASE문은 조건문 여러개 가능
EX)  CASE (조건문1)WHEN ~ THEN ~ (조건문2)WHEN ~ THEN ~ (조건문3)ELSE ~  END

1. 노드가 루트노드면 Root (최상위 노드, 부모가 없는 노드)
  
 P가NULL이면 부모가 없는 최상위 노트
-->  CASE WHEN P IS NULL THEN 'Root'

2.노드가 리프노트면 Leaf (자식이 없는 노드)

(1) 자식이 없다 => 부모가 아니다 => N값(부모)이 P에 없다 
N NOT IN P

(2) 속성P의 속성값을 범위로 나타내려면 서브쿼리로 표시
SELECT P FROM BST 

(3) P 속성값 중 NULL이 있는 경우 NOT IN을 하면 아무 row도 선택하지 않음 
ex) N not in (2,, null) => N <> 2 AND N <> NULL 의미,
그러나 NULL은 IS NOT NULL로 찾을 수 있기 때문에 N not in (2,, null) 식은 항상 FALSE를 반환하게 됨 
P의 NULL값을 제거하는 범위 지정

--> WHEN N NOT IN (SELECT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'

3. 노드가 이너노드면 Inner(루트도, 잎 노드도 아닌 노드)

1, 2 조건문이 아니면 이너노드 
--> ELSE 'Inner'

중요 포인트 

1. CASE 문 안에 서브쿼리

2. NOT IN 의 'NULL' 함정

최종 쿼리 

SELECT N,
              (CASE WHEN P IS NULL THEN 'Root'
                          WHEN N NOT IN (SELECT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'
                           ELSE 'Inner'
              END) AS type
FROM BST
ORDER BY N