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

SQL로 하는 데이터 분석 완강 

 

[목차]

- 서브쿼리와 뷰를 활용한 유연한 데이터 분석

 

[서브쿼리]

- 서브쿼리 : SQL 문 안에 '부품'처럼 들어가는 SELECT 문 > 다른 SQL 문의 일부로 쓰이는 SELECT 문
  ㄴ 전체 SQL 문을 outer query, 서브쿼리를 inner query

- SELECT 절에 있는 서브쿼리

- WHERE 절에 있는 서브쿼리

 

[ANY(SOME), ALL]

1. ANY(= SOME) : WHERE 절에서 사용될 때는, 서브쿼리의 결과에 있는 각 row의 값들 중 하나라도 조건을 만족하는 경우가 있으면 TRUE를 리턴한다

ex) WHERE view_count > ANY(서브쿼리) 

2. ALL : 모든 경우에 대해서 해당 조건이 성립해야 TRUE를 리턴

 

[서브쿼리 기초 실습]

- 리뷰가 달리는 스테디 셀러 상품들의 리뷰 모니터링

SELECT * 
FROM review 
WHERE item_id IN 
     (
      SELECT id 
      FROM item 
      WHERE registration_date < '2018-12-31'
     );

 

[FROM 절에 있는 서브쿼리]

- FROM 절 뒤에도 서브쿼리 사용 가능 > derived table

- derived table에는 꼭 AILAS를 생성해야 함

*derived table : FROM 뒤에서 서브쿼리가 리턴하는 테이블 > Oracle이라는 DBMS에서는 이것을 inline view

 

[서브쿼리의 종류 총정리]

1. 단일값을 리턴하는 서브쿼리

: 단일값은 수학, 물리분야에서 스칼라라고 불림 > 이러한 서브쿼리를 스칼라 서브쿼리라 부름

: 스칼라 서브쿼리는 SELECT 절에서 하나의 컬럼처럼 WHERE 절에서 =,> 등 조건 표현식과 비교하는 값으로 쓸 수 있음 

 

2. 하나의 column에 여러 row들이 있는 형태의 결과를 리턴하는 서브쿼리

: 이러한 서브쿼리는 IN, ANY(SOME), ALL 등의 키워드와 함께 쓸 수 있음

 

3. 하나의 테이블 형태의 결과(여러 column, 여러 row)를 리턴하는 서브쿼리

: 테이블 형태의 값을 리턴하는 서브쿼리 > derived table 일시적 탄생 > derived table에는 alias를 붙여줘야 한다는 규칙

 

서브쿼리 분류

- 비상관 서브쿼리(Non-correlated Subquery) :  outer query와 상관 관계가 없는 서브쿼리 > 서브쿼리가 그것을 둘러싼 outer query와 별개로, 독립적으로 실행

- 상관 서브쿼리(Correlated Subquery) : outer query에 적힌 테이블 이름 등과 상관 관계를 갖고 있어서 그 단독으로는 실행되지 못하는 서브쿼리

ㄴ 한 테이블에서 어떤 특정 테이블에 연관된 row가 있는 것들만, 혹은 없는 것들만 추려낼 때는 상관 서브쿼리를 사용하고 그 앞에 EXISTS 또는 NOT EXISTS

 

[서브쿼리 종합 실습]

-  최고 가격, 리뷰의 평균 별점, 리뷰를 남긴 고유한 회원 이메일의 수

 

SELECT MAX(copang_report.price) AS max_price, 
       AVG(copang_report.star) AS avg_star,
       COUNT(DISTINCT(copang_report.email)) AS distinct_email_count 
FROM (
       SELECT price, 
              star, 
              email 
       FROM item AS i INNER JOIN review AS r ON r.item_id = i.id
       INNER JOIN member AS m ON r.mem_id = m.id
       ) AS copang_report;

 

[서브쿼리 vs 조인]

- 상관 서브쿼리 사용 결과와 조인을 통해서도 똑같은 결과 조회 가능

 > 정답은 없으며, 분석 시 익숙하고 직관적인 것을 선택

 > 수 많은 row가 있을 때는 두 가지 방법에서 속도 차이가 발생할 수 있음

 

[데이터 분석가의 자산, 뷰]

- 뷰 : 조인 등의 작업을 해서 만든 결과 테이블이 가상으로 저장된 형태 > 가상 테이블

  ㄴ 테이블 : 테이블은 우리가 표 형식의 데이터가 실제 컴퓨터에 저장

  ㄴ 뷰 : 표 형식으로 데이터를 본다는 점은 동일하나, 테이블처럼 그 내용이 실제 컴퓨터에 일일이 저장되어 있는 건 아님 > DBMS가 그 뷰를 생성하는 SQL문을 재실행하는 방식으로 가상의 테이블 생성

 ㄴ three_tables_joined 라는 뷰를 사용할 때마다 AS 다음에 있는 빨간 박스 안의 SELECT 문이 재실행된다는 뜻

 

[뷰 활용의 장점]

1) 사용자에게 높은 편의성을 제공

2) 각 직무별 데이터 수요에 알맞은, 다양한 구조의 데이터 분석 기반을 구축

3) 데이터 보안을 제공 : 민감 정보가 담긴 컬럼을 제외하고 보여줄 수 있는 방법

 

[데이터베이스 현황 파악]

1. 존재하는 데이터베이스들 파악

 

2. 한 데이터베이스 안의 테이블(뷰도 포함)들 파악

 

3. 한 테이블의 컬럼 구조 파악

- DESCRIBE를 사용하면 테이블의 컬럼 구조만 깔끔하게 파악

 

4. Foreign Key(외래키) 파악

- 테이블들 간의 관계를 파악하려면 데이터베이스에 존재하는 Foreign Key들을 파악 필요

- 두 테이블의 각 컬럼 간에 Foreign Key 관계가 성립한다고 해도 관리자가 그것을 Foreign Key로 설정하지 않는 경우도 있음