• 정규표현식은 검색할 패턴을 명시하는 방법임.

출처: https://dev.mysql.com/doc/refman/8.0/en/regexp.html

• 정규표현식 튜토리얼: https://regexone.com/lesson/introduction_abcs
• 정규표현식 테스트 사이트: https://regexr.com/

<예제> [문제] Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

[풀이1]

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiou].*'

- ^: 문자열의 시작을 매칭함.
- []: 집합에 있는 어떤 문자와 매칭함. 
- .: 어떤 문자와 매칭함. 
- *: 이전 토큰의 0개 이상에 매칭함. 

=> 'aeiou' 중 어떤 문자로든 시작하고 이후에 어떤 문자가 0개 이상 등장하는 도시 이름을 중복 없이 선택함.

[풀이2]

SELECT DISTINCT city
FROM station
WHERE city REGEXP '^[aeiou]'

=> 'aeiou' 중 어떤 문자로든 시작하는 도시 이름을 중복 없이 선택함.

• 참고: SQL은 대소문자에 민감하지 않기 때문에 [AEIOUaeiou]로 표현하지 않아도 되지만 다른 프로그래밍 언어(예: 파이썬)에서는 대소문자 구분함.

출처: https://www.hackerrank.com/challenges/weather-observation-station-6/problem?h_r=internal-search

<예제> [문제] Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

[풀이]

SELECT DISTINCT city
FROM station
WHERE city NOT REGEXP '(^[aeiou]|[aeiou]$)'

- $: 문자열의 끝에 매칭함.
- (조건1|조건2): 조건1 혹은 조건2에 매칭함. 

=> 도시 이름 중에서 'aeiou'중 어떤 문자로 시작하거나 'aeiou'중 어떤 문자로 끝나는 이름 패턴과 같지 않은 도시 이름을 중복없이 선택함.

출처: https://www.hackerrank.com/challenges/weather-observation-station-12/problem?h_r=internal-search

<예제> 프로그래머스 - SQL 고득점 Kit - JOIN - 보호소에서 중성화한 동물

[문제]

• ANIMAL_INS 테이블 ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

  NAME	TYPE	NULLABLE
  ANIMAL_ID	VARCHAR(N)	FALSE
  ANIMAL_TYPE	VARCHAR(N)	FALSE
  DATETIME	DATETIME	FALSE
  INTAKE_CONDITION	VARCHAR(N)	FALSE
  NAME	VARCHAR(N)	TRUE
  SEX_UPON_INTAKE	VARCHAR(N)	FALSE

• ANIMAL_OUTS 테이블 ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.

  NAME	TYPE	NULLABLE
  ANIMAL_ID	VARCHAR(N)	FALSE
  ANIMAL_TYPE	VARCHAR(N)	FALSE
  DATETIME	DATETIME	FALSE
  NAME	VARCHAR(N)	TRUE
  SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요. 중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact, 중성화를 거친 동물은 Spayed 또는 Neutered라고 표시되어있습니다.

[풀이]

SELECT i.animal_id, i.animal_type, i.name
FROM animal_ins AS i
INNER JOIN animal_outs AS o ON i.animal_id = o.animal_id
WHERE i.sex_upon_intake REGEXP '^Intact' AND o.sex_upon_outcome REGEXP '^(Spayed|Neutered)'
ORDER BY i.animal_id

=> 보호소에 들어올 때에 성별 및 중성화 여부는 Intact로 시작하고, 보호소에서 나갈 때 성별 및 중성화 여부는 Spayed 혹은 Neutered로 시작하는 동물의 아이디, 종, 이름을 선택함.

출처: https://programmers.co.kr/learn/challenges

지도 학습(supervised learning)

: 입력과 정답 데이터를 사용하는 학습 알고리즘.

비지도 학습(unsupervised learning)

: 정답 없이 입력 데이터만 사용하는 학습 알고리즘.

훈련 데이터 세트와 테스트 데이터 세트

훈련 데이터 세트 (training dataset)

: 학습에 사용되는 데이터

테스트 데이터 세트(test dataset)

: 평가에 사용되는 데이터

K-NN을 이용한 도미와 빙어 분류 모델 - 훈련 데이터 세트와 테스트 데이터 세트 분리

입력 데이터, 정답 데이터 numpy 배열로 변환하기

numpy.array(리스트 이름)로 리스트를 numpy 배열로 변환할 수 있다.

[코드]

import numpy as np
from sklearn.neighbors import KNeighborsClassifier
import numpy as np

fish_length = [25.4, 26.3, 26.5, 29.0, 29.0, 29.7, 29.7, 30.0, 30.0, 30.7, 31.0, 31.0, 
                31.5, 32.0, 32.0, 32.0, 33.0, 33.0, 33.5, 33.5, 34.0, 34.0, 34.5, 35.0, 
                35.0, 35.0, 35.0, 36.0, 36.0, 37.0, 38.5, 38.5, 39.5, 41.0, 41.0, 9.8, 
                10.5, 10.6, 11.0, 11.2, 11.3, 11.8, 11.8, 12.0, 12.2, 12.4, 13.0, 14.3, 15.0]
fish_weight = [242.0, 290.0, 340.0, 363.0, 430.0, 450.0, 500.0, 390.0, 450.0, 500.0, 475.0, 500.0, 
                500.0, 340.0, 600.0, 600.0, 700.0, 700.0, 610.0, 650.0, 575.0, 685.0, 620.0, 680.0, 
                700.0, 725.0, 720.0, 714.0, 850.0, 1000.0, 920.0, 955.0, 925.0, 975.0, 950.0, 6.7, 
                7.5, 7.0, 9.7, 9.8, 8.7, 10.0, 9.9, 9.8, 12.2, 13.4, 12.2, 19.7, 19.9]

fish_data = [[l, w] for l, w in zip(fish_length, fish_weight)]
fish_target = [1] * 35 + [0] * 14

input_arr = np.array(fish_data)
target_arr = np.array(fish_target)

print(input_arr.shape)
print(input_arr)
print('_____________________________________________________________________________')
print(target_arr.shape)
print(target_arr)

[결과]

(49, 2)
[[  25.4  242. ]
 [  26.3  290. ]
 [  26.5  340. ]
 [  29.   363. ]
 [  29.   430. ]
 [  29.7  450. ]
 [  29.7  500. ]
 [  30.   390. ]
 [  30.   450. ]
 [  30.7  500. ]
 [  31.   475. ]
 [  31.   500. ]
 [  31.5  500. ]
 [  32.   340. ]
 [  32.   600. ]
 [  32.   600. ]
 [  33.   700. ]
 [  33.   700. ]
 [  33.5  610. ]
 [  33.5  650. ]
 [  34.   575. ]
 [  34.   685. ]
 [  34.5  620. ]
 [  35.   680. ]
 [  35.   700. ]
 [  35.   725. ]
 [  35.   720. ]
 [  36.   714. ]
 [  36.   850. ]
 [  37.  1000. ]
 [  38.5  920. ]
 [  38.5  955. ]
 [  39.5  925. ]
 [  41.   975. ]
 [  41.   950. ]
 [   9.8    6.7]
 [  10.5    7.5]
 [  10.6    7. ]
 [  11.     9.7]
 [  11.2    9.8]
 [  11.3    8.7]
 [  11.8   10. ]
 [  11.8    9.9]
 [  12.     9.8]
 [  12.2   12.2]
 [  12.4   13.4]
 [  13.    12.2]
 [  14.3   19.7]
 [  15.    19.9]]
_____________________________________________________________________________
(49,)
[1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0
 0 0 0 0 0 0 0 0 0 0 0 0]

랜덤으로 훈련 데이터 세트와 테스트 데이터 세트 나누기

numpy.random.shuffle() : numpy random 패키지의 shuffle() 함수는 주어진 배열을 무작위로 섞음. numpy.random.seed() : seed의 값을 파라미터로 받음. seed의 값이 같으면 같은 숫자들의 집합이 결과물로 나옴. 즉, 랜덤으로 나오는 숫자들을 예측할 수 있게 함.

• 본 실습에서는 같은 결과물을 얻기 위해 seed = 42를 사용함.
• numpy.arange(start, stop, step): 일정한 간격의 정수 혹은 실수 배열을 만듦. start는 범위의 시작을 의미하고, 해당 값을 포함함. 기본값은 0. stop은 범위의 끝을 의미하지만 해당 값을 포함하지 않음. 즉, start 이상 stop 미만까지로 보면 됨. 기본 step은 1.

<단계 1> 0부터 48까지 인덱스 만들기

[코드]

index = np.arange(49)

print(index)

[결과]

[ 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
 48]

<단계 2> 0부터 48까지 인덱스를 랜덤하게 섞기

[코드]

np.random.seed(42)
index = np.arange(49)
np.random.shuffle(index)

print(index)

[결과]

[13 45 47 44 17 27 26 25 31 19 12  4 34  8  3  6 40 41 46 15  9 16 24 33
 30  0 43 32  5 29 11 36  1 21  2 37 35 23 39 10 22 18 48 20  7 42 14 28
 38]

<단계 3> 훈련 데이터 세트, 테스트 데이터 세트 나누기

• 훈련 데이터 세트는 35개, 테스트 데이터 세트는 14개

[코드]

train_input = input_arr[index[: 35]]
train_target = target_arr[index[: 35]]

test_input = input_arr[index[35:]]
test_target = target_arr[index[35:]]

print(train_input.shape)
print(train_target.shape)
print(test_input.shape)
print(test_target.shape)

[결과]

(35, 2)
(35,)
(14, 2)
(14,)

훈련 데이터 세트, 테스트 데이터 세트 분포 확인하기

[코드]

import matplotlib.pyplot as plt
plt.scatter(train_input[:,0], train_input[:,1])
plt.scatter(test_input[:,0], test_input[:,1])
plt.xlabel('length')
plt.ylabel('weight')
plt.legend(('training dataset', 'test dataset'))
plt.show()

[결과]

모델 훈련하고, 평가하기

[코드]

kn = KNeighborsClassifier()
kn.fit(train_input, train_target)
kn.score(test_input, test_target)

[결과] 1.0

• 100%의 정확도로 테스트 세트에 있는 모든 생선을 맞혔다.

모델을 이용하여 테스트 데이터 예측하기

[코드]

kn.predict(test_input)

[결과] array([0, 0, 1, 0, 1, 1, 1, 0, 1, 1, 0, 1, 1, 0])

[코드]

test_target

[결과] array([0, 0, 1, 0, 1, 1, 1, 0, 1, 1, 0, 1, 1, 0])

• 테스트 데이터에 대한 예측 결과가 정답과 모두 일치한다.

- 출처: 혼자 공부하는 머신러닝+딥러닝 (박해선)

• 도미 35마리와 빙어 14마리의 길이와 무게 데이터를 준비한다. 각 길이와 무게에 대해 도미인지, 빙어인지 정답 데이터를 준비한다.
• K-NN을 이용하기 위하여 사이킷런에서 KNeighborsClassifier 클래스의 객체를 생성한다.
• fit() 메소드로 훈련한다.
• score() 메소드로 정확도를 확인한다.
• predict() 메소드로 예측한다.

[코드]

import matplotlib.pyplot as plt
from sklearn.neighbors import KNeighborsClassifier
# 도미 길이, 무게 데이터
bream_length = [25.4, 26.3, 26.5, 29.0, 29.0, 29.7, 29.7, 30.0, 30.0, 30.7, 31.0, 31.0, 31.5, 32.0, 32.0, 32.0, 33.0, 33.0, 33.5, 33.5, 34.0, 34.0, 34.5, 35.0, 35.0, 35.0, 35.0, 36.0, 36.0, 37.0, 38.5, 38.5, 39.5, 41.0, 41.0]
bream_weight = [242.0, 290.0, 340.0, 363.0, 430.0, 450.0, 500.0, 390.0, 450.0, 500.0, 475.0, 500.0, 500.0, 340.0, 600.0, 600.0, 700.0, 700.0, 610.0, 650.0, 575.0, 685.0, 620.0, 680.0, 700.0, 725.0, 720.0, 714.0, 850.0, 1000.0, 920.0, 955.0, 925.0, 975.0, 950.0]

# 빙어 길이, 무게 데이터
smelt_length = [9.8, 10.5, 10.6, 11.0, 11.2, 11.3, 11.8, 11.8, 12.0, 12.2, 12.4, 13.0, 14.3, 15.0]
smelt_weight = [6.7, 7.5, 7.0, 9.7, 9.8, 8.7, 10.0, 9.9, 9.8, 12.2, 13.4, 12.2, 19.7, 19.9]

length = bream_length + smelt_length
weight = bream_weight + smelt_weight

# 도미, 빙어 전체 길이, 무게 데이터
fish_data = [[l, w] for l, w in zip(length, weight)]
# 도미, 빙어 정답 데이터 (도미: 1, 빙어: 0)
fish_target = [1]*35 + [0]*14

# KNeighborsClassifier 객체 생성
kn = KNeighborsClassifier()
# 모델 훈련
kn.fit(fish_data, fish_target)
# 모델 정확도
kn.score(fish_data, fish_target)
# 새로운 데이터 예측
print(kn.predict([[30, 600]]))

# 도미, 빙어, 테스트 데이터 산점도
plt.scatter(bream_length, bream_weight)
plt.scatter(smelt_length, smelt_weight)
plt.scatter(30, 600, marker='^')
plt.xlabel('length')
plt.ylabel('weight')
plt.legend(('bream', 'smelt', 'test_fish'))
plt.show

[결과]

array([1]) ➡️ 도미로 예측함.

키워드

• 특성(feature): 데이터를 표현하는 하나의 성질. 예제에서 생선의 길이, 무게 각각이 특성이 될 수 있음.

• 훈련(training): 머신러닝 알고리즘이 데이터에서 규칙을 찾는 과정을 훈련이라고 함.

  • 사이킷런(sklearn)에서 fit() 메소드로 훈련을 시킴.

• K-최근접 이웃 알고리즘(K-NN: K-Nearest Neighbors): 가장 가까운 이웃을 참고하여 정답을 예측하는 머신러닝 알고리즘. 규칙을 찾기보다는 전체 데이터를 메모리에 가지고 있다가 새로운 데이터가 등장하면 가장 가까운 데이터를 참고하여 어디에 분류되는지 예측함.

  • KNeighborsClassifier는 기본으로 가까운 5개의 데이터를 참고해서 결과를 예측함. 참고할 데이터의 개수는 n_neighbors = n 을 매개변수로 변경할 수 있음. (예: kn = KNeighborsClassifier(n_neighbors = 49))

• 정확도(accuracy): 정확한 답을 몇개 맞혔는지 백분율로 나타낸 값. 사이킷런에서는 0~1 사이로 값으로 출력됨.

  정확도 = 정확히 맞힌 개수 / 전체 데이터 개수

  • 사이킷런에서 score() 메소드로 모델이 잘 훈련되었는지 확인함.

추가 문제

KNeighborsClassifier를 사용할 때 n_neighbors의 기본값인 5부터 49까지 바꿔가면서 점수가 1.0 아래로 내려가기 시작하는 개수를 찾아보세요.

[방법 1]

[코드]

from sklearn.neighbors import KNeighborsClassifier

bream_length = [25.4, 26.3, 26.5, 29.0, 29.0, 29.7, 29.7, 30.0, 30.0, 30.7, 31.0, 31.0, 31.5, 32.0, 32.0, 32.0, 33.0, 33.0, 33.5, 33.5, 34.0, 34.0, 34.5, 35.0, 35.0, 35.0, 35.0, 36.0, 36.0, 37.0, 38.5, 38.5, 39.5, 41.0, 41.0]
bream_weight = [242.0, 290.0, 340.0, 363.0, 430.0, 450.0, 500.0, 390.0, 450.0, 500.0, 475.0, 500.0, 500.0, 340.0, 600.0, 600.0, 700.0, 700.0, 610.0, 650.0, 575.0, 685.0, 620.0, 680.0, 700.0, 725.0, 720.0, 714.0, 850.0, 1000.0, 920.0, 955.0, 925.0, 975.0, 950.0]

smelt_length = [9.8, 10.5, 10.6, 11.0, 11.2, 11.3, 11.8, 11.8, 12.0, 12.2, 12.4, 13.0, 14.3, 15.0]
smelt_weight = [6.7, 7.5, 7.0, 9.7, 9.8, 8.7, 10.0, 9.9, 9.8, 12.2, 13.4, 12.2, 19.7, 19.9]

length = bream_length + smelt_length
weight = bream_weight + smelt_weight

fish_data = [[l, w] for l, w in zip(length, weight)]
fish_target = [1]*35 + [0]*14

kn = KNeighborsClassifier()
kn.fit(fish_data, fish_target)

for n in range(5, 50):
    kn.n_neighbors = n
    score = kn.score(fish_data, fish_target)

    if score < 1:
        print(n, score)
        break

[결과] 18 0.9795918367346939

[방법 2]

[코드]

from sklearn.neighbors import KNeighborsClassifier

bream_length = [25.4, 26.3, 26.5, 29.0, 29.0, 29.7, 29.7, 30.0, 30.0, 30.7, 31.0, 31.0, 31.5, 32.0, 32.0, 32.0, 33.0, 33.0, 33.5, 33.5, 34.0, 34.0, 34.5, 35.0, 35.0, 35.0, 35.0, 36.0, 36.0, 37.0, 38.5, 38.5, 39.5, 41.0, 41.0]
bream_weight = [242.0, 290.0, 340.0, 363.0, 430.0, 450.0, 500.0, 390.0, 450.0, 500.0, 475.0, 500.0, 500.0, 340.0, 600.0, 600.0, 700.0, 700.0, 610.0, 650.0, 575.0, 685.0, 620.0, 680.0, 700.0, 725.0, 720.0, 714.0, 850.0, 1000.0, 920.0, 955.0, 925.0, 975.0, 950.0]

smelt_length = [9.8, 10.5, 10.6, 11.0, 11.2, 11.3, 11.8, 11.8, 12.0, 12.2, 12.4, 13.0, 14.3, 15.0]
smelt_weight = [6.7, 7.5, 7.0, 9.7, 9.8, 8.7, 10.0, 9.9, 9.8, 12.2, 13.4, 12.2, 19.7, 19.9]

length = bream_length + smelt_length
weight = bream_weight + smelt_weight

fish_data = [[l, w] for l, w in zip(length, weight)]
fish_target = [1]*35 + [0]*14

for n in range(5, 50):
  kn.n_neighbors = KNeighborsClassifier(n_neighbors = n)
  kn.n_neighbors.fit(fish_data, fish_target)
  score = kn.n_neighbors.score(fish_data, fish_target)

  if score < 1:
    print(n, score)
    break

[결과] 18 0.9795918367346939

- 출처: 혼자 공부하는 머신러닝+딥러닝 (박해선)

• 필요성: 측정하는 단위가 달라서 원점수별 비교가 어려운 문제가 있음.

  예) A 테스트는 점수가 0점100점, B 테스트는 점수가 0점80점 일 때 A 테스트에서 80점과 B 테스트에서 80점을 어떻게 비교할 것인가.

• 원점수(raw score)를 표준점수(standard score)로 변환함.

z-score (z-점수 또는 표준점수)

• 어떤 원점수도 z-점수로 변환할 수 있음.
• z-점수는 평균으로부터 몇 SD 큰지, 작은지 나타냄. 예: z-점수가 1이면 평균보다 1_SD_ 크다.
• z-점수가 + 이면 원점수가 평균보다 크다는 것을 의미하고, -이면 원점수가 평균보다 작다는 것을 의미함.
• 어떤 분포를 가진 점수도 z-점수로 변환할 수 있으며, z-점수로 변환한다고 해서 원점수의 분포가 바뀌지 않음.
• 정규분포만 z-점수로 변환할 수 있는 것은 아님. 다만, 정규분포에서 z-점수가 유용하게 사용될 수 있음.

z-점수 기본 공식

$z = \frac{x – μ}{σ}$

표집분포의 원점수를 z-점수로 표준화하기

$z = \frac{\overline{X} – μ}{\frac{σ}{\sqrt{N}}}$

• 중심극한정리로 인해 $μ{\overline{x}} = μ$, $σ{\overline{x}} = \frac{σ}{\sqrt N}$

z-distribution (z-분포)

• 평균은 0, 표준편차는 1인 정규 분포
• 특정한 값이 평균으로부터 몇 SD 떨어져 있는지 안다면(즉, z-점수를 안다면) 그 값보다 크거나 작은 값을 얻을 확률을 구할 수 있음.

<예시>

• 평균보다 1_SD_ 작은 값보다 작은 값을 얻을 확률은 얼마인가? ➡️ z-점수가 -1인 경우에 그보다 작은 값을 얻을 확률이므로 P = 13.6 + 2.1 + 0.1 = 15.8%

• 평균보다 1_SD_ 큰 값보다 작은 값을 얻을 확률은 얼마인가? ➡️ z-점수가 1인 경우에 그보다 작은 값을 얻을 확률이므로 P = 34.1 + 34.1 + 13.6 + 2.1 + 0.1 = 84%

z-test(z-검정)

• z-분포를 이용하여 가설을 검정하는 통계적 방법.
• 모집단의 평균과 표준편차를 알 수 있어야 함.
• 표본의 크기가 크거나 (n > 30) 모집단의 정규분포여야 함.
• 등분산 가정이 충족될 때 사용함. 두 모집단을 비교할 경우 두 모집단의 분산이 같아야 함.

(1) 단일 표본 z-검정 (one sample z-tset)

• 모집단을 대표하도록 추출된 표본의 평균을 연구자가 이론적 혹은 경험적으로 얻은 특정한 값과 비교하는 통계적 방법.

<예제> [문제] 백문이불여일타아카데미에서 수년간 파이썬 강의를 통한 파이썬 코딩 평균 점수가 80점이었고 표준편차는 15점이었다. 이번학기에 프로젝트를 활용한 파이썬 수업을 100명의 수강생들에게 실시하였고, 파이썬 코딩 평균 점수는 85점이었다. 새로운 강의 방식에 따른 수강생들의 평균 점수 85점이 80점과 같은지 유의수준 .05에서 검증하라.

[풀이]

• 귀무가설: 새로운 강의 방식에 의한 파이썬 코딩 평균 점수는 80점과 같다. $\mu = 80$
• 대립가설: 새로운 강의 방식에 의한 파이썬 코딩 평균 점수는 80점이 아니다. $\mu ≠ 80$

z = $\frac{{85} – 80}{\frac{15}{\sqrt{100}}}$ = 3.33

유의수준 .05에서 기각값은 $\pm$ 1.96이다.

새로운 강의 방식에 의한 파이썬 코딩 평균 점수 85점의 z-점수는 3.33이어서 1.96보다 크기 때문에 귀무가설을 기각한다.

따라서 유의수준 .05에서 새로운 강의 방식에 의한 파이썬 코딩 평균 점수는 80점이 아니다.

(2) 독립 표본 z-검정 (independent sample z-tset)

• 두 모집단의 평균을 비교하기 위해서 각 모집단을 대표하도록 추출된 독립적인 두 표본을 비교하여 두 모집단을 비교하는 통계적 방법.
• 두 모집단의 분산이 동일하다는 것을 이론적 혹은 경험적 배경을 통해 알고 있어야 함.

z-score 공식

$z = \frac{(\overline{X_{1}} - \overline{X_{2}}) - (\mu_{1} - \mu_{2})}{\sqrt{ \frac{\sigma_{1}^{2}}{n_{1}} + \frac{\sigma_{2}^{2}}{n_{2}}}}$

<예제> [문제] 전국 30세 남녀 각각 100명을 무작위 추출하여 체중을 측정했다. 남성의 체중 평균은 68kg, 여성의 체중 평균은 60kg였다. 연구자는 이론적 배경에 의해 30세 남성 모집단의 체중 표준편차는 10kg이고 여성 모집단의 체중 표준편차는 9kg임을 알고 있다. 30세 성인 남녀의 체중에 차이가 있는지 여부를 유의수준 .05 수준에서 검정하라.

[풀이]

• 귀무가설: 30세 성인 남녀 체중에는 차이가 없다. $\mu_{1} = \mu_{2}$
• 대립가설: 30세 성인 남녀 체중에는 차이가 있다. $\mu_{1} ≠ \mu_{2}$

$z = \frac{({68} - {60}) - (0 - 0)}{\sqrt{ \frac{10^{2}}{100} + \frac{9^{2}}{100}}}$ = 5.95

유의수준 .05에서 기각값은 $\pm$ 1.96이다.

30세 성인 남녀의 체중 비교를 위한 z-점수는 5.95여서 +1.96보다 크기 때문에 귀무가설이 기각된다.

따라서 유의수준 .05에서 30세 성인 남녀의 체중은 통계적으로 유의미한 차이가 있다.

참고:

• 현대기초통계학 (성태제 저)
• https://sphweb.bumc.bu.edu/otlt/MPH-Modules/PH717-QuantCore/PH717-Module6-RandomError/PH717-Module6-RandomError5.html

1) 집단의 등분산성 검정

• Levene's test

[코드]

import pingouin as pg
pg.homoscedasticity(dv = 'score', group = 'school', data = schools_df)

[결과 판별]

• p < .05 : 모든 집단의 분산이 같지 않다.
• p > .05 : 모든 집단의 분산이 같다.

2) 일원 분산 분석

• 등분산성 가정을 충족하면 ANOVA

[코드]

pg.anova(dv = 'score', between = 'school', data = schools_df, detailed=True)

• 등분산성 가정을 충족하지 않으면 Welch's ANOVA

[코드]

pg.welch_anova(dv = 'score', between = 'school', data = schools_df)

[결과 판별]

• p < .05 : 모든 집단의 평균이 같지 않다. ➡️ 사후 검정을 진행한다.
• p > .05 : 모든 집단의 평균이 같다. ➡️ 사후 검정을 진행하지 않는다.

3) 사후 검정

• 등분산성 가정을 충족하면 Tukey's test

[코드]

pg.pairwise_tukey(dv = 'score', between = 'school', data = schools_df)

• 등분산성 가정을 충족하지 않으면 Games Howell's test

[코드]

pg.pairwise_gameshowell(dv = 'score', between = 'school', data = schools_df)

[결과 판별]

• p < .05 : 집단 간 평균 차이가 있다.
• p > .05 : 집단 간 평균 차이가 없다.

[실습]

0. 데이터 준비

• 데이터 셋: 캐글 경매 데이터 https://www.kaggle.com/onlineauctions/online-auctions-dataset

데이터 불러오기

[코드]

import pandas as pd
import pingouin as pg

auction = pd.read_csv('auction.csv')

print(auction.shape)
print(auction.head(3))
print(auction.tail(3))

[결과]

auction.shape: (10681, 9)

auction.head(3):

auction.tail(3):

1. 등분산성(homoscedasticity) 검정

• Levene's test
  • 귀무가설: 모든 집단의 분산이 같다.
  • if p-value < .05: 귀무가설 기각 성공. 모든 집단의 분산이 같지 않다. ➡️ pg.welch_anova 사용
  • if p-value > .05: 귀무가설 기각 실패. 모든 집단의 분산이 같다. ➡️ pg.anova 사용

[코드]

pg.homoscedasticity(dv = 'openbid', group = 'item', data = auction)

[결과]

• Levene's test를 이용하여 등분산성을 검정한 결과 등분산성 가정을 만족하지 않았다, p < .05.

2. 일원 분산 분석 (one-way ANOVA)

분석 목적

: item별 평균 open bid가 통계적으로 유의미하게 차이나는지 알아보고자 함.

(1) 각 그룹의 평균과 표준편차

[코드]

watch = auction[auction['item']=='Cartier wristwatch']['openbid']
PDA = auction[auction['item']=='Palm Pilot M515 PDA']['openbid']
console = auction[auction['item']=='Xbox game console']['openbid']

print('watch 평균 가격:', watch.mean(), 'watch 표준 편차:', watch.std())
print('PDA 평균 가격:', PDA.mean(), 'PDA 표준 편차:', PDA.std())
print('console 평균 가격:', console.mean(), 'console 표준 편차:', console.std())

[결과] watch 평균 가격: 153.44 watch 표준 편차: 360.69

PDA 평균 가격: 31.56 PDA 표준 편차: 60.37

console 평균 가격: 25.48 console 표준 편차: 32.68

(2) 일원 분산 분석 (one-way ANOVA)

• 등분산가정을 만족하지 않으므로 Welch's ANOVA를 이용함.

[코드]

pg.welch_anova(dv = 'openbid', between = 'item', data = auction)

[결과]

• 세 item간 평균 open bid의 차이가 유의미한지 알아보기 위하여 일원 분산 분석을 하였다. 그 결과, p-value가 .05 보다 작기 때문에 집단 간 평균 open bid 차이가 통계적으로 유의미하였다.

3. 사후 검정

• 집단 간 open bid 평균 차이가 어디서 발생하는지 알아보기 위하여 사후 검정을 실시함. 등분산가정을 만족하지 않기 때문에 Games Howell 검정을 이용하여 사후 검정을 실시함.

[코드]

pg.pairwise_gameshowell(dv = 'openbid', between = 'item', data = auction)

[결과]

• Games Howell 검정을 이용하여 사후 분석을 실시한 결과, watch와 PDA 사이, watch와 console 사이, 그리고 PDA와 console 사이 평균 open bid 차이가 통계적으로 유의미하였다.

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

ANIMAL_OUTS 테이블

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.

없어진 기록 찾기

문제

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

솔루션

[방법1] LEFT JOIN

SELECT o.animal_id
      , o.name
FROM animal_outs AS o 
LEFT JOIN animal_ins AS i ON o.animal_id = i.animal_id
WHERE i.animal_id IS NULL

[방법2] 서브쿼리

SELECT animal_id
      , name
FROM animal_outs 
WHERE animal_id NOT IN (SELECT animal_id FROM animal_ins)

있었는데요 없었습니다

문제

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

솔루션

SELECT i.animal_id
     , i.name
FROM animal_ins AS i
INNER JOIN animal_outs AS o ON i.animal_id = o.animal_id
WHERE i.datetime > o.datetime
ORDER BY i.datetime 

오랜 기간 보호한 동물 (1)

문제

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

솔루션

[방법1] LEFT JOIN

SELECT i.name
     , i.datetime
FROM animal_ins AS i
LEFT JOIN animal_outs AS o ON i.animal_id = o.animal_id
WHERE o.animal_id IS NULL
ORDER BY i.datetime
LIMIT 3

[방법2] 서브쿼리

SELECT name
     , datetime
FROM animal_ins 
WHERE animal_id NOT IN (SELECT animal_id FROM animal_outs)
ORDER BY datetime
LIMIT 3

보호소에서 중성화한 동물

문제

보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요. 중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact, 중성화를 거친 동물은 Spayed 또는 Neutered라고 표시되어있습니다.

솔루션

[방법 1] NOT LIKE

SELECT i.animal_id
     , i.animal_type
     , i.name
FROM animal_ins AS i
INNER JOIN animal_outs AS o ON i.animal_id = o.animal_id
WHERE (i.sex_upon_intake LIKE 'Intact%') AND (o.sex_upon_outcome LIKE 'Spayed%' OR o.sex_upon_outcome LIKE 'Neutered%')
ORDER BY i.animal_id

⚠️ 주의: WHERE절에 AND와 OR를 같이 사용할 경우, ()를 통해 AND에 해당하는 조건과 OR에 해당하는 조건을 잘 구분해야함.

⚠️ 한계점: 만약 WHERE절에 column에 들어가는 문자열 조건이 추가된다면 해당 코드처럼 AND나 OR로 연결하다보면 코드가 길어질 가능성이 있음. 그런 경우, 정규표현식을 사용하는 것이 더 가독성이 좋고, 효율적일 수 있음.

[방법 2] 정규표현식

SELECT i.animal_id
     , i.animal_type
     , i.name
FROM animal_ins AS i
INNER JOIN animal_outs AS o ON i.animal_id = o.animal_id
WHERE i.sex_upon_intake REGEXP '^(Intact)' AND o.sex_upon_outcome REGEXP '^(Spayed|Neutered)'
ORDER BY i.animal_id 

• ^(Intact) : Intact로 시작되는 것을 매칭함.
• ^(Spayed|Neutered): Spayed 혹은 Neutered로 시작되는 것을 매칭함.

참고:

• MySQL 정규표현식 문법: https://dev.mysql.com/doc/refman/8.0/en/regexp.html
• 정규표현식 튜토리얼: https://regexone.com/lesson/introduction_abcs
• 정규표현식 테스트 사이트: https://regexr.com/

출처:

• 문제 풀이 https://programmers.co.kr/learn/challenges

<예제> HackerRank: Revising Aggregations - The Count Function

[문제]

Query a count of the number of cities in CITY having a Population larger than 100,000.

[풀이]

SELECT COUNT(*)
FROM city
WHERE population > 100000;

출처: https://www.hackerrank.com/challenges/revising-aggregations-the-count-function/problem

<예제> HackerRank: Revising Aggregations - Averages

[문제]

Query the average population of all cities in CITY where District is California.

[풀이]

SELECT AVG(population)
FROM city
WHERE district = 'California';

출처: https://www.hackerrank.com/challenges/revising-aggregations-the-average-function/problem

<예제> HackerRank: Average Population

[문제]

Query the average population for all cities in CITY, rounded down to the nearest integer.

[풀이]

SELECT FLOOR(AVG(population))
FROM city;

출처: https://www.hackerrank.com/challenges/average-population/problem

<예제> HackerRank: Revising Aggregations - The Sum Function

[문제] Query the total population of all cities in CITY where District is California.

[풀이]

SELECT SUM(population)
FROM city
WHERE district = 'California';

출처: https://www.hackerrank.com/challenges/revising-aggregations-sum/problem

<예제> HackerRank: Weather Observation Station 15

[문제]

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345. Round your answer to decimal places.

[풀이]

SELECT ROUND(LONG_W, 4)
FROM station
WHERE LAT_N = (SELECT MAX(LAT_N) FROM station WHERE LAT_N < 137.2345)

⚠️ 주의점: WHERE절에 바로 집계함수 사용할 수 없음. GROUP BY 하고 HAVING에 집계함수를 쓰거나 아니면 WHERE절의 서브쿼리에 집계함수를 사용할 수 있음.

출처: https://www.hackerrank.com/challenges/weather-observation-station-15/problem

<예제> TestDome: Regional Sales Comparison

[문제]

An insurance company maintains records of sales made by its employees. Each employee is assigned to a state. States are grouped under regions. The following tables contain the data:

TABLE regions
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL

TABLE states
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  regionId INTEGER NOT NULL REFERENCES regions(id)

TABLE employees
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  stateId INTEGER NOT NULL REFERENCES states(id)

TABLE sales
  id INTEGER PRIMARY KEY
  amount INTEGER NOT NULL
  employeeId INTEGER NOT NULL REFERENCES employees(id)  

Management requires a comparative region sales analysis report.

Write a query that returns:

• The region name.
• Average sales per employee for the region (Average sales = Total sales made for the region / Number of employees in the region).
• The difference between the average sales of the region with the highest average sales, and the average sales per employee for the region (average sales to be calculated as explained above).

A region with no sales should be also returned. Use 0 for average sales per employee for such a region when calculating the 2nd and the 3rd column.

[풀이]

WITH c AS (SELECT r.name AS regionName
      ,(CASE WHEN SUM(s.amount) IS NOT NULL THEN SUM(s.amount) / COUNT(DISTINCT e.id) ELSE 0 END) AS averageSales
FROM regions AS r
LEFT JOIN states AS st ON r.id = st.regionId
LEFT JOIN employees AS e ON st.id = e.stateId
LEFT JOIN sales AS s ON e.id = s.employeeId
GROUP BY r.name)
SELECT regionName
      , averageSales
      , (SELECT MAX(averageSales) FROM c) - averageSales AS difference
FROM c

[결과]

<주의할 점>

• Average sales = Total sales made for the region / Number of employees in the region 으로 정의된다. *AVG 함수를 사용하면 안되고 지역의 sales의 총합을 구하고 해당 지역의 총 직원 수로 나눠야 한다. 직원의 경우, 중복되어 나타날 수 있기 때문에 직원 수를 셀 때 COUNT DISTINCT를 사용해야 한다. *
• 전체 지역 중 averageSales의 최댓값에서 각 지역의 averageSales를 뺀 값을 구해야한다. 이를 위해 CTE에 averageSales를 컬럼을 만들어 두고, 참조하여 사용한다.
• average sales의 최댓값을 구할 때는 SELECT내에 다시 SELECT를 사용해서 SELECT MAX(averageSales) FROM c 으로 가져와야 한다. 만약 SELECT 내에 다시 SELECT를 사용하지 않고, MAX(averageSales)를 바로 사용한다면 결과가 하나의 row로 리턴된다.

** 참고 [코드]

WITH c AS (SELECT r.name AS regionName
      ,(CASE WHEN SUM(s.amount) IS NOT NULL THEN SUM(s.amount) / COUNT(DISTINCT e.id) ELSE 0 END) AS averageSales
FROM regions AS r
LEFT JOIN states AS st ON r.id = st.regionId
LEFT JOIN employees AS e ON st.id = e.stateId
LEFT JOIN sales AS s ON e.id = s.employeeId
GROUP BY r.name)
SELECT regionName
      , averageSales
      , MAX(averageSales) - averageSales AS difference
FROM c

[결과]

출처: https://www.testdome.com/questions/sql/regional-sales-comparison/36141

• 집계 함수는 값들의 집합을 계산해서 하나의 값을 리턴하는 함수임.

• COUNT(*)를 제외하고, 집계 함수는 NULL 값을 무시함.

• 집계 함수는 자주 SELECT문에서 GROUP BY절과 함께 사용됨.

• WHERE절에 바로 집계함수 사용할 수 없음. GROUP BY를 하고 HAVING절에 집계함수를 쓰거나 WHERE절에 서브쿼리에서 집계함수를 사용할 수 있음. <예시>

   잘못된 사용
  
   SELECT math_score
   FROM scores
   WHERE english_score = MIN(english_score)

   올바른 사용

   SELECT math_score
   FROM scores
   WHERE english_score = (SELECT MIN(english_score) FROM scores)

1) COUNT

• 특정한 기준을 충족하는 rows의 수 리턴함.
• *COUNT()를 제외하고 NULL 값은 세지 않음. **

[기본 코드]

SELECT COUNT(column_name)
FROM table_name
WHERE condition

<예시>

[테이블 생성 및 값 삽입]

CREATE TABLE Customers(Id integer, Name varchar(100), Visits integer);
INSERT INTO Customers(Id, Name, Visits) values(1, "Amy", 1), (2, "Amy", 2), (3, "Jake", 3), (4, "Terry", 5), (5, NULL, NULL);

Table: Customers

[코드]

SELECT COUNT(*) 
FROM Customers

[결과] 5

• 설명: 모든 rows의 수를 세서 총 5를 리턴함.

[코드]

SELECT COUNT(Name)
FROM Customers

[결과]

4

• 설명: 이름 중 Amy, Amy, Jake, Terry 를 세서 총 4를 리턴함. (이름에서 NULL 값 제외함.)

[코드]

SELECT COUNT(DISTINCT Name)
FROM Customers

[결과] 3

• 설명: 이름 중 중복되는 이름을 제거하여 Amy, Jake, Terry를 세서 총 3을 리턴함. (이름에서 NULL값 제외함.)

2) AVG

• 숫자형 column의 평균값을 리턴함.
• ** NULL 값은 제외됨. **
• NULL 값을 데이터에서 제외하고 평균을 구하려면 AVG 쓰면 됨. 그러나 NULL 값을 포함해서 평균을 구하려면 SUM으로 총합을 구하고 COUNT(*)로 나눠야함.

[기본 코드]

SELECT AVG(column_name)
FROM table_name
WHERE condition

<예시> Table: Customers

NULL값을 제외하고 평균을 구하는 경우

[코드]

SELECT AVG(Visits)
FROM Customers

[결과] 2.75

• 설명: (1+2+3+5)/4 = 2.75

NULL값을 포함하여 평균을 구하는 경우

[코드]

SELECT SUM(Visits) / COUNT(*)
FROM Customers

[결과] 2.2

• 설명: (1+2+3+5)/5 = 2.2

3) SUM

• 숫자형 column의 총합을 리턴함.
• *NULL 값은 제외됨. *

[기본 코드]

SELECT SUM(column_name)
FROM table_name
WHERE condition

<예시> Table: Customers

[코드]

SELECT SUM(visits)
FROM Customers

[결과] 11

• 설명: 1+2+3+5 = 11

4) MIN

• 선택한 column의 가장 작은 값을 리턴함.

[기본 코드]

SELECT MIN(column_name)
FROM table_name
WHERE condition

<예시> Table: Customers

[코드]

SELECT MIN(visits)
FROM Customers

[결과] 1

5) MAX

• 선택한 column의 가장 큰 값을 리턴함.

[기본 코드]

SELECT MAX(column_name)
FROM table_name
WHERE condition

<예시> Table: Customers

[코드]

SELECT MAX(visits)
FROM Customers

[결과] 5

참고: https://www.w3schools.com/sql/sql_count_avg_sum.asp https://www.w3schools.com/sql/sql_min_max.asp https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15

• 데이터 셋: 캐글 중고차 데이터 https://www.kaggle.com/austinreese/craigslist-carstrucks-data

데이터 불러오기

[코드]

import pandas as pd
import scipy.stats as stats 
import pingouin as pg

vehicles = pd.read_csv('vehicles.csv') # csv 파일을 dataframe으로 읽어옴. 

print(vehicles.shape) # dataframe의 차원 확인함.
print(vehicles.head(3)) # dataframe의 처음 3개 열을 리턴함.
print(vehicles.tail(3)) # dataframe의 마지막 3개 열을 리턴함.

[결과]

vehicles.shape:

(426880, 26)

vehicles.head(3):

vehicles.tail(3):

데이터 전처리하기

• 가격이 0인 데이터들을 결측치로 판단하여 제거함.
• 가격의 z-score가 3이상인 데이터를 이상치로 판단하여 제거함.

[코드]

vehicles = vehicles[vehicles['price'] != 0]
# vehicles의 가격이 0인 rows를 제거함.
vehicles = vehicles.reset_index(drop=True)
# vehicles의 index를 재정렬함.

vehicles = vehicles[(np.abs(stats.zscore(vehicles['price'])) < 3)]
# vehicles의 가격의 z-score가 3이상인 rows를 제거함. 

vehicles.shape # dataframe의 차원 확인함.

[결과]

vehicles.shape:

(393965, 26)

1. 단일 표본 t 검정(one sample t-test)

분석 목적

: auid의 가격이 중고차의 평균 가격인 25000달러와 통계적으로 유의미한 차이가 나는지 확인하고자 함.

(1) 그룹의 평균과 표준편차

[코드]

audi = vehicles[vehicles['manufacturer'] == 'audi']

print('audi 평균 가격: ', audi.price.mean())
print('audi 가격 표준편차: ', audi.price.std())

[결과] audi 평균 가격: 24865.23 audi 가격 표준편차: 15600.28

(2) 단일 표본 t 검정

[코드]

pg.ttest(audi.price, 25000, confidence = 0.95)

[결과]

(3) 결과 보고

auid의 가격이 중고차의 평균 가격인 25000달러와 통계적으로 유의미한 차이가 나는지 확인하기 위하여 단일 표본 t 검정을 실시하였다. audi의 가격(M = 24865.23, SD = 15600.28)은 중고차 평균 가격인 25000달러와 통계적으로 유의미한 차이가 없다, t(7179) = -0.73, p > .05.

2. 독립 표본 t 검정(independent samples t-test))

분석 목적

: audi와 lexus의 가격 차이가 통계적으로 유의미한지 확인하고자 함.

(1) 각 그룹의 평균과 표준편차

[코드]

audi = vehicles[vehicles['manufacturer'] == 'audi']
lexus = vehicles[vehicles['manufacturer'] == 'lexus']

print('audi 평균 가격: ', audi.price.mean())
print('audi 가격 표준편차: ', audi.price.std())

print('lexus 평균 가격: ', lexus.price.mean())
print('lexus 가격 표준편차: ', lexus.price.std())

[결과] audi 평균 가격: 24865.23 audi 가격 표준편차: 15600.28

lexus 평균 가격: 20344.98 lexus 가격 표준편차: 12079.34

(2) 독립표본 t 검정

• lexus와 audi의 평균 가격의 차이가 통계적으로 유의미한 차이인지 검증하기 위하여 독립표본 t 검정을 실시함.

[코드]

pg.ttest(audi.price, lexus.price, confidence = 0.95)

[결과]

(3) 효과 크기

1) eta-squared

[코드]

pg.compute_effsize(audi.price, lexus.price,  eftype = 'eta-square')

[결과]

0.03

2) cohen's d

[코드]

pg.compute_effsize(audi.price, lexus.price, eftype = 'cohen')

[결과]

0.33

(4) 결과 보고

audi의 가격과 lexus의 가격에 차이가 있는지 알아보기 위하여 독립 표본 t검정을 실시하였다. audi의 가격(M = 24865.23, SD = 15600.28)이 lexus의 가격(M = 20344.98, SD = 12079.34)보다 통계적으로 유의미하게 높았다, t(13508) = 19.68, p < .05, $$η^2$$ = 0.03.

<예제> Hackerrank: African Cities

[문제]

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

[풀이]

SELECT city.name
FROM city
INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Africa'

출처: https://www.hackerrank.com/challenges/african-cities/problem

<예제> Hackerrank: Population Census

[문제]

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

[풀이]

SELECT SUM(city.population)
FROM city
INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Asia'

출처: https://www.hackerrank.com/challenges/asian-population/problem?h_r=internal-search

<예제> Hackerrank: Average Population of Each Continent

[문제]

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

[풀이]

SELECT country.continent
     , FLOOR(AVG(city.population))
FROM city
INNER JOIN country ON city.countrycode = country.code
GROUP BY country.continent

출처: https://www.hackerrank.com/challenges/average-population-of-each-continent/problem?h_r=internal-search

<예제> HackerRank: Placements

[문제]

You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month). Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

[풀이]

SELECT s.name
FROM students AS s
INNER JOIN friends AS f ON s.id = f.id
INNER JOIN packages AS ss ON s.id = ss.id
INNER JOIN packages AS fs ON f.friend_id = fs.id
WHERE ss.salary < fs.salary
ORDER BY fs.salary 

출처: https://www.hackerrank.com/challenges/placements/problem

참고: https://www.sqlshack.com/learn-sql-join-multiple-tables/

SELF JOIN 문제 풀이

<예제> LeetCode: 181. Employees Earning More Than Their Managers

[문제]

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+ Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+ | Employee | +----------+ | Joe | +----------+

[풀이]

SELECT e.name AS Employee
FROM employee AS e
INNER JOIN employee AS m ON e.managerid = m.id
WHERE e.salary > m.salary 

출처: https://leetcode.com/problems/employees-earning-more-than-their-managers/submissions/

<예제> LeetCode: 197. Rising Temperature

[문제]

Table: Weather

+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | recordDate | date | | temperature | int | +---------------+---------+ id is the primary key for this table. This table contains information about the temperature in a certain day.

Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example:

Weather +----+------------+-------------+ | id | recordDate | Temperature | +----+------------+-------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +----+------------+-------------+

Result table: +----+ | id | +----+ | 2 | | 4 | +----+ In 2015-01-02, temperature was higher than the previous day (10 -> 25). In 2015-01-04, temperature was higher than the previous day (20 -> 30).

[풀이]

SELECT today.id
FROM Weather AS yesterday
INNER JOIN Weather AS today ON DATE_ADD(yesterday.recorddate, INTERVAL 1 DAY) = today.recorddate
WHERE today.temperature > yesterday.temperature

출처: https://leetcode.com/problems/rising-temperature/solution/

참고: MySQL 시간 더하기, 빼기

DATE_ADD (기준날짜, INTERVAL) : *날짜에 시간/날짜 간격을 더해서 리턴함. *

<예시>

• SELECT DATE_ADD(NOW(), INTERVAL 1 SECOND)
• SELECT DATE_ADD(NOW(), INTERVAL 1 MINUTE)
• SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR)
• SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)
• SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH)
• SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)
• SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR)

DATE_SUB (기준날짜, INTERVAL) : *날짜에 시간/날짜 간격을 빼서 리턴함. *

<예시>

• SELECT DATE_SUB(NOW(), INTERVAL 1 SECOND)

<예제> Hackerrank: Symmetric Pairs

[문제]

You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

Sample Input

Sample Output

20 20
20 21
22 23

[풀이1]

-- x와 y가 다른 경우
(SELECT f1.x
     , f1.y
FROM functions AS f1
INNER JOIN functions AS f2 ON f1.x = f2.y AND f1.y = f2.x
WHERE f1.x < f1.y)
UNION
-- x와 y가 같은 경우
(SELECT x
      , y
FROM functions
GROUP BY x, y
HAVING COUNT(*) > 1)
ORDER BY x

[풀이2]

SELECT f1.x
     , f1.y 
FROM Functions AS f1
INNER JOIN Functions AS f2 ON f1.x = f2.y AND f1.y = f2.x
GROUP BY f1.x, f1.y 
HAVING COUNT(*) > 1 OR f1.x < f1.y 
ORDER BY f1.x

출처: https://www.hackerrank.com/challenges/symmetric-pairs/problem

** 참고 (INNER JOIN vs. LEFT JOIN) Functions 테이블

(1) LEFT JOIN

SELECT f1.id, f1.x, f1.y, f2.id, f2.x, f2.y    
FROM Functions f1
LEFT JOIN Functions f2 ON f1.x = f2.y AND f2.x = f1.y

➡️ LEFT JOIN을 사용하면 f1의 x = 20, y = 21에 대응하는 x = 21, y = 20인 row가 f2에 존재하지 않아도, null 값으로 채워져서 row를 리턴하게 됨. 이와 같은 대응이 되지 않는 rows를 제거하고, 대응되는 rows만 리턴하기 위해 INNER JOIN을 사용함.

(2) INNER JOIN

SELECT f1.id, f1.x, f1.y, f2.id, f2.x, f2.y    
FROM Functions f1
INNER JOIN Functions f2 ON f1.x = f2.y AND f2.x = f1.y

LEFT JOIN 문제 풀이

<예제> LeetCode: 183. Customers Who Never Order

[문제]

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+ Table: Orders.

+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+ Using the above tables as example, return the following:

+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+

[풀이]

SELECT name AS Customers
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customerid
WHERE o.id IS NULL

출처: https://leetcode.com/problems/customers-who-never-order/

<예제> LeetCode: 175. Combine Two Tables

[문제]

Table: Person

+-------------+---------+ | Column Name | Type | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId is the primary key column for this table. Table: Address

+-------------+---------+ | Column Name | Type | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

[풀이]

SELECT firstname
     , lastname
     , city
     , state
FROM person AS p 
LEFT JOIN address AS a ON p.personid = a.personid

출처: https://leetcode.com/problems/combine-two-tables/

• 두개 이상 테이블에서 테이블 사이 연관된 colum을 바탕으로 rows를 결합함.

1) (INNER) JOIN

두 테이블에 모두 매치되는 값을 가진 rows를 리턴함.

[코드]

SELECT * FROM TableA A
INNER JOIN TableB B ON A.key = B.key

2) LEFT (OUTER) JOIN

왼쪽 테이블의 모든 rows를 리턴하고, 오른쪽 테이블에서는 왼쪽 테이블에 매치되는 rows를 리턴함.

[코드]

SELECT * FROM TableA A
LEFT JOIN TableB B ON A.key = B.key 

3) RIGHT (OUTER) JOIN

오른쪽 테이블의 모든 rows를 리턴하고, 왼쪽 테이블에서는 오른쪽 테이블에 매치되는 rows를 리턴함.

[코드]

SELECT * FROM TableA A
RIGHT JOIN TableB B ON A.key = B.key 

4) FULL (OUTER) JOIN

왼쪽 테이블 혹은 오른쪽 테이블에 매치되는 모든 rows를 리턴함.

[코드]

SELECT * FROM TableA A 
FULL OUTER JOIN TableB B ON A.key = B.key

JOIN 예시

[테이블 생성 및 값 삽입]

CREATE TABLE Students(StudentId integer, Name varchar(100));
INSERT INTO Students(StudentId, Name) values(1, "Ted"), (2, "Lily"), (3, "Marshall");
CREATE TABLE Scores(ScoreId integer, StudentId integer, Math integer, English integer);
INSERT INTO Scores(ScoreId, StudentId, Math, English) values(1, 1, 90, 95), (2, 2, 80, 100), (4, NULL, 75, 100);

<Student 테이블>

<Score 테이블>

1) INNER JOIN

[코드]

SELECT st.StudentId 
     , st.name
     , sc.math
     , sc.english
FROM Student AS st
INNER JOIN Score AS sc ON st.StudentId = sc.StudentId;

[결과]

2) LEFT JOIN

[코드]

SELECT st.StudentId 
     , st.name
     , sc.math
     , sc.english
FROM Student AS st
LEFT JOIN Score AS sc ON st.StudentId = sc.StudentId;

[결과]

3) RIGHT JOIN

[코드]

SELECT st.StudentId 
     , st.name
     , sc.math
     , sc.english
FROM Student AS st
RIGHT JOIN Score AS sc ON st.StudentId = sc.StudentId;

[결과]

4) FULL OUTER JOIN

[코드]

SELECT st.StudentId 
     , st.name
     , sc.math
     , sc.english
FROM Student AS st
FULL OUTER JOIN Score AS sc ON st.StudentId = sc.StudentId;

[결과]

⚠️ 주의 : MySQL에서는 FULL OUTER JOIN을 제공하지 않음. MySQL에서는 다음과 같은 코드로 FULL OUTER JOIN를 구현할 수 있음.

[코드]

SELECT *
FROM Student AS st
LEFT JOIN Score AS sc ON st.StudentId = sc.StudentId
UNION
SELECT *
FROM Student AS st
RIGHT JOIN Score AS sc ON st.StudentId = sc.StudentId;

[결과]

참고: https://www.w3schools.com/sql/sql_join.asp https://sql-joins.leopard.in.ua/ MySQL online editor: https://paiza.io/en/projects/new?language=mysql PostgreSQL online editor: https://extendsclass.com/postgresql-online.html

1) 가설을 설정한다.

• 귀무가설(null hypothesis/H0) : 가설 검정의 직접적인 대상이 되는 가설. 기각하고자 하는 가설. ➡️ 연구의 목적은 독립변인에 따라 종속변인에 차이가 난다는 것을 확인하기 위한 경우가 많음. 이로 인해 귀무가설은 '차이가 없다', '영향을 미치지 않는다' 등으로 표현되는 경우가 많음. 예) 종이에 필기한 집단과 노트북에 필기한 집단 사이 성적에 차이가 없다.

• 대립가설(alternative hypothesis/H1) : 귀무가설이 기각될 때 받아들여지는 가설. 직접적으로 검정의 대상이 되지 않음. 예) 종이에 필기한 집단과 노트북에 필기한 집단 사이 성적에 차이가 있다.

⚠️ 주의: 귀무가설과 대립가설은 모든 가능성을 포함해야 하고, 상호 배타적이어야 한다.

• 가설에는 차이가 특정한 방향에서 나타날 것을 명시하는 방향적 가설(directional hypothesis)과 특정한 방향을 포함하지 않는 비방향적 가설(nondirectional hypothesis)이 있음. 예) 방향적 가설: 종이에 필기한 집단이 노트북에 필기한 집단보다 성적이 높을 것이다. 비방향적 가설: 종이에 필기한 집단과 노트북에 필기한 집단 사이 성적에 차이가 있다.

• 선행연구가 존재하여 방향성에 대해 기대할 수 있을 때 방향적 가설을 사용하고, 방향성에 대한 강한 근거가 없다면 비방향적 가설을 사용하는 것이 좋음.

• 방향적 가설을 검정할 때 단측(one-tailed) 검정, 비방향적 가설을 검정할 때 양측(two-tailed) 검정을 사용함.

2) 유의 수준을 설정한다.

p-value와 비교할 유의수준($$α$$)을 설정함. 일반적으로 $$α$$은 .05로 설정함.

[양측 검정]

[단측 검정]

출처: Hartmann, K., Krois, J., Waske, B. (2018): E-Learning Project SOGA: Statistics and Geospatial Data Analysis. Department of Earth Sciences, Freie Universitaet Berlin.

3) 표본을 추출하여 검정통계량을 계산한다.

4) 귀무가설을 평가한다.

p-value: 귀무가설이 참일 때 관측된 검정통계량이 관찰될 확률

p-value와 유의수준($$α$$)을 비교:

• p-value < $$α$$ 이면 귀무가설을 기각한다.

⚠️ 주의:

• p-value는 effect size에 대한 지표로 사용되면 안된다. p-value가 낮다고 해서 effect size가 큰 것이 아니다.
• p-value가 낮다고 해서 재현(replication)될 확률이 높은 것이 아니다.
• 일반적으로 표본이 많아지면 p-value가 낮아진다.

• 표집: 모집단에서 표본을 추출

  1) 확률(무작위) 표집(random sampling)

모집단에서 각 사례가 동일한 확률로 추출됨.

(1) 단순 무작위 표집(simple random sampling)

모집단의 모든 사례를 같은 확률로 추출함. 예)모집단 사례 모두에게 번호를 부여하고 표본의 수만큼 번호를 무작위로 뽑아서 표본을 추출함.

(2) 체계적 표집(systematic sampling)

첫번재 사례를 무작위로 추출한 뒤, 매번 k번째 사례를 추출함. 주기성이 없을 때 사용해야함. 예)출구조사(투표소에서 나오는 사람들 k번째마다 조사)

(3) 층화 표집(stratified sampling)

모집단을 구성하는 계층별로 무작위로 표집함. 모집단이 서로 다른 집단으로 구성될 때 활용함. 계층은 지역, 연령, 성별 등으로 연구 목적에 따라 달라짐. 예)

• 비율 층화 표집: 모집단에 대한 각 계층의 비율대로 각 계층에서 사례를 추출. 예) 모집단 1000명에서 표본 100명을 추출할 때 남자와 여자 비율이 6:4이면 남자 600명 중 60명을, 여자 400명 중 40명을 추출함.
• 비비율 층화 표집: 모집단에서 각 계층마다 추출할 때 모집단에 대한 계층의 비율이 아닌 다른 비율로 사례를 추출. 예) 모집단 10000명에서 표본 100명을 추출할 때 남자와 여자 비율이 1:9이면 남자 100명 중 20명, 여자 900면 중 80명을 추출함.

(4) 집락 표집(clustered sampling)

자연스럽게 형성된 집락에 따라서 무작위로 표집함. 예) 서울시 상인 대상 설문 조사시 특정 구를 5개를 무작위로 선정해서 사례를 추출함.

2) 비확률(편파된) 표집(biased sampling)

모집단에서 어떤 사례가 다른 사례에 비해 추출될 확률이 높음.

(1) 의도적 표집 (purposive sampling)

특정 집단이 모집단을 잘 대표할 것으로 판단하여 특정 집단에서 사례를 추출함.

(2) 편의(우연적) 표집 (convenience sampling)

연구자가 쉽게 얻을 수 있는 사례를 표본으로 추출함. 예) 심리학 연구 참가자를 심리학 교양 수업을 수강하는 대학생들을 대상으로 함.

(3) 할당 표집(quota sampling)

모집단을 구성하는 계층별로 작위적으로 표집함. 층화 표집과의 차이점은 무작위로 표집하지 않는다는 점임. 층화표집과 마찬가지로 비율, 비비율로 구분됨.

(4) 눈덩이 표집(snowball sampling)

한 사례로부터 시작하여 표본을 점차적으로 늘려가는 방법. 사례로부터 얻는 다른 사례에 대한 정보를 바탕으로 표본을 늘려감. 모집단에 대한 접근이 어려울 때 활용함.

2. 표집분포(sampling distribution of the means)

동일한 모집단에서 크기가 n인 표본을 무한히 반복 추출한 뒤, 그 평균을 가지고 만든 이론적 분포.

표집분포 시뮬레이션

• n=5인 표본을 1회 추출
• n=5인 표본을 10회 추출
• n=5인 표본을 10,000회 추출
• n=5인 표본을 100,000회 추출

출처: https://onlinestatbook.com/stat_sim/sampling_dist/index.html

표준오차(SE: standard error)

: 표집분포의 표준편차

중심 극한 정리

1) 표본의 크기가 충분히 크면(n > 30 이면) 표집분포는 근사적으로 정규 분포이다. 2) 모집단의 분포가 정규분포이면 표본의 크기와 상관 없이 표집분포는 정규분포이다. 3) 표집분포의 평균은 $$μ$$ 이다. 4) 표집분포의 표준편차(표준오차)는 $$\frac{σ}{\sqrt N}$$ 이다.

• 표준오차의 특징
  • 표준오차는 표본의 크기가 커지면 작아진다. ⬅️ 표본의 크기가 커지면 각 표본의 평균 사이 거리가 가까워지기 때문이다.
  • 표준오차는 모표준편차에 비해 작다.

모집단 분포, 표본분포, 표집분포 평균과 표준편차

➡️ 대부분의 경우 집단 전체에 대한 전수조사가 가능하지 않으므로 표본을 통해 모집단에 대해 추론함.

2. 모수와 통계량

⚠️ 주의: 표본의 모수, 모집단의 통계량으로 표현하지 않도록 하기.

모표준편차

<예제> 4명의 학생들의 수학 성적은 70, 50, 80, 90이다. 다음 성적의 표준편차를 구하라.

(풀이) 𝜇 = $$\frac{(70 + 50 + 80 + 90)}{4}$$ = 70 $$SS$$ = $$(70-70)^2$$ + $$(50-70)^2$$ + $$(80-70)^2$$ + $$(90-70)^2$$ = 1000 $$σ^2$$ = $$\frac{SS}{N}$$ = $$\frac{1000}{4}$$ = 250 𝜎 = 15.81

정답: 15.81

표본표준편차

<예제> 학생들이 몇개의 과목을 수강하는지 조사하기 위해 4명을 표본으로 뽑았다. 과목 개수는 4, 6, 7, 3개이다. 다음 과목 개수의 표준편차를 구하라.

(풀이) $$\overline{x}$$ = $$\frac{(4 + 6 + 7 + 3)}{4}$$ = 5 $$SS$$ = $$(4-5)^2$$ + $$(6-5)^2$$ + $$(7-5)^2$$ + $$(3-5)^2$$ = 10 $$s_{x}^2$$ = $$\frac{SS}{n-1}$$ = $$\frac{10}{(4-1)}$$ = $$\frac{10}{3}$$ = 3.33 $$s_{x}$$ = 1.83

정답: 1.83

• 참고: 모집단과 표본의 평균, 분산, 표준편차를 구할 수 있는 사이트 https://www.calculatorsoup.com/calculators/statistics/variance-calculator.php

NULL VALUE 값이 없음을 의미함. ⚠️ 주의: 값이 0이 아님. 값이 비어 있는 것.

** IS NULL, IS NOT NULL ** NULL VALUE인지 확인하기 위해 IS NULL 혹은 IS NOT NULL을 사용함. ⚠️ 주의: 컬럼명 = NULL 형태로 쓰지 않음.

ANIMAL_INS 테이블

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

이름이 없는 동물의 아이디

문제

동물 보호소에 들어온 동물 중, 이름이 없는 채로 들어온 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

솔루션

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID;

이름이 있는 동물의 아이디

문제

동물 보호소에 들어온 동물 중, 이름이 있는 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

솔루션

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID;

NULL 처리하기

문제

입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.

솔루션

SELECT ANIMAL_TYPE
     , IFNULL(NAME, 'No name') AS NAME
     , SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

IFNULL SELECT IFNULL(expression, alt_value) : expression의 값이 null이면 alt_value를 리턴함. null이 아니면 expression을 리턴함.

출처:

• 문제 풀이 https://programmers.co.kr/learn/challenges
• 개념 설명 https://www.w3schools.com/sql/sql_null_values.asp

0. 데이터 준비

• 데이터 셋: 캐글 중고차 데이터 https://www.kaggle.com/austinreese/craigslist-carstrucks-data

  데이터 불러오기

  [코드]

import pandas as pd
vehicles = pd.read_csv('vehicles.csv') # csv 파일을 dataframe으로 읽어옴. 

vehicles.shape # dataframe의 차원 확인함.
vehicles.head(3) # dataframe의 처음 3개 열을 리턴함.
vehicles.tail(3) # dataframe의 마지막 3개 열을 리턴함.

[결과]

vehicles.shape:

(426880, 26)

데이터 전처리하기

• 가격이 0인 데이터들을 결측치로 판단하여 제거함.
• 가격의 z-score가 3이상인 데이터를 이상치로 판단하여 제거함.

[코드]

import scipy.stats as stats

vehicles = vehicles[vehicles['price'] != 0]
# vehicles의 가격이 0인 rows를 제거함.
vehicles = vehicles.reset_index(drop=True)
# vehicles의 index를 재정렬함.

vehicles = vehicles[(np.abs(stats.zscore(vehicles['price'])) < 3)]
# vehicles의 가격의 z-score가 3이상인 rows를 제거함. 

vehicles.shape # dataframe의 차원 확인함.

[결과]

vehicles.shape:

(393965, 26)

1. 중심경향값 구하기

1) 가격의 평균

[코드]

vehicles.price.mean()

[결과]

19383.20

2) 가격의 중앙값

[코드]

vehicles.price.median()

[결과]

15000

• 평균은 극단값의 영향을 많이 받기 때문에 가격의 평균과 중앙값 사이 차이가 많이 나는 것으로 보임. 현재 데이터에서는 평균보다 중앙값이 데이터를 더 잘 대표하는 것으로 판단됨.

3) 제조사 최빈값

[코드]

vehicles.manufacturer.mode()

[결과]

0 ford dtype: object

• 제조사의 최빈값은 ford이다.

2. 변산도 구하기

1) 가격의 범위

[코드]

  vehicles.price.max() - vehicles.price.min()

[결과]

25002999

2) 가격의 사분위수 범위(IQR)

[코드]

vehicles.price.quantile(.75) - vehicles.price.quantile(.25)

[결과]

20571

3) 가격의 분산

[코드]

  vehicles.price.var()

[결과]

5754159737.77

4) 가격의 표준편차

[코드]

  vehicles.price.std()

[결과]

75856.18

기술 통계(descriptive statistics): 자료를 요약(summarize)하고, 정리(organize)하여 이해하기 쉽게 제시함.

1. 중심경향값(central tendency)

1) 평균(mean): 자료의 모든 숫자를 더한 뒤 값의 개수로 나눈 값

• 분포에 있는 모든 값에 민감함.
• 분포에 있는 극단값에 영향을 많이 받음.

  <예시>
  1, 2, 7, 100
  평균: (1+2+7+100)/4 = 27.5

2) 중앙값 (median): 값을 크기 순서대로 나열했을 때 가장 중앙에 있는 값

• 자료의 개수가 짝수일 경우, 가장 중앙에 있는 값 자료의 개수가 홀수일 경우, 중앙에 있는 두 값의 평균
• 평균과 달리 분포에 있는 극단값에 영향을 받지 않음.

  <예시>
  1, 2, 7, 100
  중앙값: (2+7)/2 = 4.5

3) 최빈값(mode): 가장 빈번하게 발생하는 값

• 연속 변수보다는 범주형 변수에서 유용함.

  <예시>
  1, 1, 3, 7
  최빈값: 1 

  데이터 분석 적용점 : 데이터 분석에서 일반적으로 평균을 많이 활용함. 평균은 데이터에 있는 모든 값에 민감하고, 특히 극단값에 영향을 많이 받는다는 특징이 있음. *따라서 데이터가 정규분포가 아니거나 극단값이 있는 상황에서는 평균보다 중앙값을 사용하는 것이 자료를 더 잘 대표할 수 있음. *

2. 변산도(variability)

1)범위(range): 분포에서 가장 큰 값에서 가장 작은 값을 뺀 값.

• 양 극단에 있는 값에 의해 구하기 때문에 다른 값들을 반영하지 않음.극단 값의 영향을 많이 받음.

데이터 분석 적용점: 데이터에 오류가 있는지 확인할 때 유용함. 척도가 있는 데이터라면 척도 범위를 벗어난 범위의 값이 존재한다면 데이터에 오류가 있을 가능성이 있음.

2)사분위수 범위(interquartile range): 75%와 25%에 위치한 값의 차이

IQR = Q3 - Q1

(Q1 = 25% , Q2 = 50%, Q3 = 75%)
Q2는 중앙값과 동일함.

• 극단값의 영향이 적음.

<예시>
1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Q1 --> 3
Q2 --> 5.5
Q3 --> 8

IQR = Q3 - Q1 = 5

• 집단의 분포가 매우 흩어졌거나 양 극단에 있는 값이 변산도에 크게 영향을 준다고 판단할 때 사용하면 좋음.

3)분산(variance): 편차제곱의 평균, 값들이 평균에서 떨어진 거리를 제곱한 값의 평균

• 편차(deviation): 특정 값이 평균으로부터 떨어진 거리
• 편차의 합은 0이 되기 때문에 분산 혹은 표준편차를 이용함.

[모집단의 분산]

[표본집단의 분산]

4)표준편차(standard deviation): 분산의 제곱근

• 가장 신뢰로운 변산도를 원할 때 사용함. 편차를 제곱하면 크기가 커지므로 표준편차를 많이 사용함.

[모집단의 표준편차]

[표본집단의 표준편차]

데이터 분석 적용점: 중심경향값과 변산도를 함께 고려해야함. 그렇지 않으면 분포에 대해 단편적인 정보만 얻게 됨. 보통 평균과 표준편차를 함께 제시함.

1) SELECT 2) FROM 3) JOIN ** 4) *ON * 5) **WHERE 6) GROUP BY 7) HAVING 8) ORDER BY 9) LIMIT

Join을 하는 경우 WHERE은 JOIN ~ ON 뒤에 사용되니 주의하기.

SQL 쿼리 실행 순서

1) FROM and JOINs 2) WHERE 3) GROUP BY 4) HAVING 5) SELECT 6) DISTINCT 7) ORDER BY 8) LIMIT / OFFSET

출처: https://mode.com/sql-tutorial/sql-having/ https://sqlbolt.com/lesson/select_queries_order_of_execution

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

고양이와 개는 몇 마리 있을까

문제

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

솔루션

SELECT ANIMAL_TYPE
    , COUNT(ANIMAL_ID) AS COUNT
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC;

동명 동물 수 찾기

문제

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

솔루션

SELECT NAME
    , COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >= 2
ORDER BY NAME ASC;

ANIMAL_OUTS 테이블

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

입양 시각 구하기(1)

문제

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

솔루션

SELECT HOUR(DATETIME) AS HOUR
    , COUNT(*) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING HOUR BETWEEN 9 AND 19
ORDER BY HOUR ASC;

HOUR(datetime) : datetime에서 시간(hour) 부분만 리턴함.

입양 시각 구하기(2)

문제

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

솔루션

[step1]

WITH RECURSIVE time (HOUR) AS (
    SELECT 0
    UNION ALL
    SELECT HOUR + 1 FROM time WHERE HOUR < 23
)
SELECT * FROM time;

[step2]

WITH RECURSIVE time AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM time WHERE HOUR < 23
)

SELECT HOUR
     , COUNT(HOUR(ao.DATETIME)) AS COUNT
FROM time
LEFT JOIN ANIMAL_OUTS AS ao
ON time.HOUR = HOUR(ao.DATETIME)
GROUP BY HOUR
ORDER BY HOUR

Common Table Expressions(공통테이블식) : 공통테이블식은 쿼리 실행동안 존재하는 일시적인 쿼리 실행의 결과물임. 스스로 참조하거나(재귀공통테이블) 혹은 같은 쿼리에서 여러번 참조할 수 있음.

Recursive Common Table Expressions

[사용법]

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

[예시]

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

출처:

• 문제 풀이 https://programmers.co.kr/learn/challenges
• 개념 설명 https://www.mysqltutorial.org/mysql-recursive-cte/

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

최댓값 구하기

문제

가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

솔루션

SELECT MAX(DATETIME)
FROM ANIMAL_INS;

최솟값 구하기

문제

동물 보호소에 가장 먼저 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

솔루션

SELECT MIN(DATETIME)
FROM ANIMAL_INS;

동물 수 구하기

문제

동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성해주세요.

솔루션

SELECT COUNT(ANIMAL_ID)
FROM ANIMAL_INS;

중복 제거하기

문제

동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성해주세요. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 칩니다.

솔루션

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS;

COUNT (column) : column의 rows의 수를 셈. ** NULL 값 제외하고 수를 셈. **

COUNT (DISTINCT column) : column의 rows 중 중복된 값을 제외하고 고유한 값의 수를 셈. ** NULL 값 제외하고 수를 셈. **

출처: https://programmers.co.kr/learn/challenges