[SQL] SQL로 하는 데이터 분석(2)_코드잇

[목차]

- 데이터 조회로 기본 다지기

- 데이터 분석 단계로 나아가기

 

[데이터 조회 실습]

이벤트를 위해 해당 회원들에게 할인 쿠폰 발송 목적

1. 나이가 20대인 회원
2. 코드잇 피자 가게 사이트에 가입한 달이 7월인 회원

 

SELECT * 
FROM member 
WHERE (age BETWEEN 20 AND 29) AND (MONTH(sign_up_day) = 7);

 

[문자열 패턴 매칭 조건 사용 시 주의 점]

LIKE : 문자열 패턴 매칭 조건을 걸기 위해 사용되는 키워드

% : 임의의 길이를 가진 문자열(0자도 포함)

_ : 한자리의 문자

 

1. 이스케이핑(escaping) 문제 

: 특정 의미를 나타내던 문자를 그 특정 의미가 아니라, 일반적인 문자처럼 취급하는 행위

: 어떤 문자가 그것에 부여된 특정한 의미, 기능으로 해석되는 게 아니라 그냥 단순한 문자 하나로 해석되도록 하는 것 

> 이스케이핑을 하려면 역슬래쉬를 문자 앞에 써주면 된다 

 

2. 대소문자 구분 문제

: Table collation 항목에서 문자열 동일 여부 확인 가능 > ci(case-insensitive, 대소문자 구별하지 않겠다) > MySQL 기본 설정

: 구분하기 위해 BINARY 활용 : LIKE BINARY '%g%'

*BINARY : 이진의 0과 1로 된의 의미, 0과 1을 보는 수준까지 문자열을 비교하라

 

[데이터 정렬해서 보기]

- 정렬 : row들을 특정 컬럼을 기준으로 순서대로 출력 

- ORDER BY : 이름을 먼저 쓴 컬럼을 우선으로 해서 정렬이 차례대로 수행 

 

※ 정렬 시 주의점 : 숫자형인 경우와 문자열형에 따라 결과가 달라진다

- INT 타입 값은 숫자의 대소(크고 작음)을 기준으로 정렬 수행

- TEXT 타입 값은 숫자의 대소가 아닌, 한 문자, 한 문자씩 문자 순서를 비교해 정렬 수행

→ CAST () 함수 : 특정 데이터 타입의 컬럼에 저장된 값을, 일시적으로 다른 데이터 타입으로 변경할 수 있게 해주는 함수

→ ORDER BY CAST(data AS signed) ASC; > 데이터 타입을 일시적으로 signed으로 변환하라

*signed : 양수와 음수를 포함한 모든 정수를 나타낼 수 있는 데이터 타입

 

[데이터 일부만 추려보기]

- LIMIT : row 중 일부만 추려서 보게 해주는 키워드, ORDER BY와 함께 사용

1. LIMIT n(row의 개수)

2. LIMIT m, n(첫 번째 row를 기준으로 한 시작 Offset, row의 개수)

*row는 0번째 순서부터 시작 > 가장 첫번째 row가 0

 

 

문법 순서

FROM - WHERE - ORDER BY - LIMIT

 

[데이터 정렬 실습]

고객 리뷰 관리를 위해 review 테이블 정렬

1. star(별점) 컬럼 기준 오름차순
2. 같은 별점인 경우, registration_date(등록일자) 컬럼 기준 내림차순
3. 5번째 row까지

 

SELECT * 
FROM review 
ORDER BY star ASC, registration_date DESC LIMIT 5;

 

데이터 분석 단계로 나아가기

[집계 함수와 산술 함수]

- 집계함수(Aggregate Function) : 어떤 칼럼의 값들을 대상으로 원하는 특징값을 구해주는 함수 

  → 특정 걸럼의 여러 row의 값들을 동시에 고려해서 실행하는 함수

- 개수(COUNT)

- 최댓값(MAX)

- 최솟값(MIN)

- 평균값(AVG)

- 합(SUM)

- 표준편차(STD)

 

- 산술함수(Mathematical Function) : 단순한 산술 연산

  → 특정 컬럼의 각 row의 값마다 실행되는 함수

- ABS() : 절대값을 구하는 함수

- SQRT() : 제곱근을 구하는 함수

- CEIL() : 올림 함수

- FLOOR() : 내림 함수

- ROUND() : 반올림 함수 

 

[NULL을 다루는 방법] 

- NULL : 값이 없음을 의미

  ㄴ NULL 확인 시에는 IS NULL / IS NOT NULL → NULL은 어떤 값이 아니기에, 등호 표시로 비교 불가

  ㄴ NULL에 덧셈, 뺄셈, 곱셈, 나눗셈을 하더라도 값 없이 항상 NULL

- NULL 값을 모두가 구분할 수 있도록 변환 함수

  → COALESCE() 함수를 사용하면 NULL을 다른 값으로 변환

  : SELECT COALESCE(price, 'N/A') FROM menu;

 

[이상한 값을 제외하고 싶을 때]

- 비정상 주소 확인법

: SELECT* FROM copang_main.member WHERE address NOT LIKE '%호';

 

[데이터 분석 실습]

실습 : 피자 가게의 고객들이 남긴 리뷰 관리 목적

1. 고객들이 남긴 리뷰 수: comment 컬럼이 NULL이 아닌 로우의 수
2. 별점 평균값 (반올림): 1번 결과 로우들의 별점 평균값

SELECT COUNT(*),
       ROUND(AVG(star)) 
FROM review 
WHERE comment IS NOT NULL; 

 

[칼럼의 값 변환]

- ALIAS : 별칭

  ㄴ 원래의 칼럼 이름과 ALIAS 사이에 AS 또는 스페이스

 

- CASE() 함수

CASE
    WHEN 조건1 THEN 해당 조건이 TRUE일 때 보여줄 값
    WHEN 조건2 THEN 해당 조건이 TRUE일 때 보여줄 값
    WHEN 조건3 THEN 해당 조건이 TRUE일 때 보여줄 값
    ELSE 그 밖의 모든 경우
END

 

EX) member 테이블의 height(키) 컬럼의 값을 150대, 160대, 170대, 180대 이런 식으로 나누어서 표현

SELECT
      CASE 
          ( a ) height >= 150 AND height < 160 ( b ) '150대'
          ( a ) height >= 160 AND height < 170 ( b ) '160대'
          ( a ) height >= 170 AND height < 180 ( b ) '170대'
          ( a ) height >= 180 AND height < 190 ( b ) '180대'
          ( c ) ‘그 밖의 키’
      END
FROM member;

 

A : WHEN B: THEN C: ELSE 

 

[데이터 분석 실습]

실습 : 원가 대비 가격 효율이 떨어지는 피자 메뉴 파악

1. 원가 기준 가격의 비율 찾기

2. case 함 활용

 

SELECT name, price, price/cost,
       (CASE 
            WHEN price/cost >= 1 AND price/cost < 1.5 THEN 'C. 저효율 메뉴'
            WHEN price/cost >= 1.5 AND price/cost < 1.7 THEN 'B. 중효율 메뉴'
            WHEN price/cost >= 1.7 THEN 'A. 고효율 메뉴'
        END) AS efficiency
FROM pizza_price_cost
ORDER BY efficiency DESC, price ASC
LIMIT 6;

 

[고유값만 보기]

- DISTINCT : 어떤 고유값이 있는지 한 눈에 알아볼 수 있는 함수 

-  SUBSTRING() : 문자열의 일부를 추출하는 함수 

 

- LENGTH() 함수 : 문자열의 길이 구하는 함수- UPPER(), LOWER() 함수 : 문자열을 모두 대문자, 소문자로 바꾸는 함수 - LPAD(), RPAD() 함수 : 문자열의 왼쪽 또는 오른쪽을 특정 문자열로 채워주는 함수  ㄴ LPAD는 LEFT(왼쪽) + PADDING(채우기)의 줄임말, RPAD는 RIGHT(오른쪽) + PADDING(채우기)의 줄임말  ㄴ EX) LPAD(age, 10, ’0’)는 age 컬럼의 값을, 왼쪽에 문자 0을 붙여서 총 10자리로 만드는 함수

 

- TRIM(), LTRIM(), RTRIM() 함수 : 문자열에 존재하는 공백을 제거하는 함수   ㄴ LTRIM() : 왼쪽 공백 삭제  ㄴ RTRIM() : 오른쪽 공백 삭제  ㄴ TRIM() : 왼쪽, 오른쪽 양쪽 다 공백 삭제

 

[그루핑]

- 그루핑 : 로우들을 여러 개 그룹으로 나눈다- GROUP BY -Aggregate function(집계함수) → COUNT, AVG, MIN : 그루핑을 통해 생성된 각 그룹의 수치적인 특성을 구하는 함수   ㄴ 그루핑과의 조합을 통해 상세 분석 가능- HAVING 함수 : 해당 값을 가진 조건 선별  ㄴ GROUP BY로 그루핑 후, 생성된 그룹들 중 특정 그룹만 선별하려면 HAVING 절 사용- WHERE 절 vs HAVING 절  ㄴ WHERE 절은 SELECT 문에서 맨 처음 row 필터링할 때 사용  ㄴ HAVING 절은 그 그룹들을 필터링

 

[GROUP BY를 쓸 때 지켜야하는 규칙]

- GROUP BY 절 뒤에 쓴 컬럼 이름들만, SELECT 절 뒤에도 쓸 수 있다.
- 대신 SELECT 절 뒤에서 집계 함수에 그 외의 컬럼 이름을 인자로 넣는 것은 허용된다.  → GROUP BY 뒤에 쓰지 않은 컬럼 이름을 SELECT 뒤에 쓸 수는 없다

  → 그루핑 기준으로 사용하지 않은 칼럼명을 SELECT 절 뒤에서 조회하려면 어떤 row에서 가져와야 할 지 결정 불가

 

[SELECT 문에서 쓰이는 각 절의 정확한 실행 순서]

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT

 

[그루핑해서 보기 실습]

- category 컬럼, main_month 컬럼 기준 그루핑
- 그룹들 중에서 주 상영 월이 5월이고, view_count의 합이 3000000(삼백만) 이상인 영화 선별
- category 컬럼, main_month 컬럼, 각 그룹에 속한 row의 개수, 각 그룹의 view_count 합 컬럼 조회
- 각 그룹에 속한 row의 개수 컬럼에는 영화 수라는 alias, 각 그룹의 view_count 합 컬럼에는 총 관객 수라는 alias 추가

 

SELECT category, 
       main_month, 
       COUNT(*) AS '영화 수', 
       SUM(view_count) AS '총 관객 수' 
FROM 2020_movie_report
GROUP BY category, main_month
HAVING main_month = 5 AND SUM(view_count) >= 3000000;

 

[그루핑 심화]

- WITH ROLLUP : 세부 그룹들을 좀더 큰 단위의 그룹으로 중간중간에 합쳐준다(말다, 소매를 걷어올리다)→ GROUP BY 뒤에 나오는 그루핑 기중의 등장 순서에 따라 출력 결과가 달라짐

 

만약 WITH ROLLUP을 썼을 때, 이 NULL이 실제로 NULL을 나타내기 위해서 쓰인 건지, 부분 총계를 나타내기 위해 쓰인 건지 확인 > GROUPING() 함수 사용

 

- GROUPING() 함수 : 그 인자를 그루핑 기준에서 고려하지 않은 부분 총계인 경우, 1을 리턴, 그렇지 않은 경우 0을 리턴  1. 실제로 NULL을 나타내기 위해 쓰인 NULL인 경우에는 0,
  2. 부분 총계를 나타내기 위해 표시된 NULL은 1

 

[SELECT 문의 실행 순서]

더 앞에 나와야 하는 순서

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
7. LIMIT

 

해석 및 실행 순서

1. FROM : 어느 테이블을 대상으로 할 것인지를 먼저 결정합니다.
2. WHERE : 해당 테이블에서 특정 조건(들)을 만족하는 row들만 선별합니다.
3. GROUP BY : row들을 그루핑 기준대로 그루핑합니다. 하나의 그룹은 하나의 row로 표현됩니다.
4. HAVING : 그루핑 작업 후 생성된 여러 그룹들 중에서, 특정 조건(들)을 만족하는 그룹들만 선별합니다.
5. SELECT : 모든 컬럼 또는 특정 컬럼들을 조회합니다. SELECT 절에서 컬럼 이름에 alias를 붙인 게 있다면, 이 이후 단계(ORDER BY, LIMIT)부터는 해당 alias를 사용할 수 있습니다.
6. ORDER BY : 각 row를 특정 기준에 따라서 정렬합니다.
7. LIMIT : 이전 단계까지 조회된 row들 중 일부 row들만을 추립니다.