[1,이론] 데이터 분석을 위한 SQL 레시피

---

목차

1. 빅데이터 시대에 요구되는 분석력이란?

2. 이 책에서 다루는 도구와 데이터

3. 데이터 가공을 위한 SQL

  3-1. 하나의 값 조작하기

  3-2. 여러 개의 값에 대한 조작

  3-3. 하나의 테이블에 대한 조작

4. 매출을 파악하기 위한 데이터 추출

  4-1. 시계열 기반으로 데이터 집계하기

  4-2. 다면적인 축을 사용해 데이터 집약하기

5. 사용자를 파악하기 위한 데이터 추출

  5-1. 사용자 전체의 특징과 경향 찾기

  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기

  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기

6. 웹사이트에서 행동을 파악하는 데이터 추출하기

  6-1. 사이트 전체의 특징/경향 찾기

  6-2. 사이트 내의 사용자 행동 파악하기

  6-3. 입력 양식 최적화하기

7. 데이터 활용의 정밀도를 높이는 분석 기술

  7-1. 데이터를 조합해서 새로운 데이터 만들기

  7-2. 이상값 검출하기

  7-3. 데이터 중복 검출하기

  7-4. 여러 개의 데이터셋 비교하기

8. 데이터를 무기로 삼기 위한 분석 기술

  8-1. 검색 기능 평가하기

  8-2. 데이터 마이닝

  8-3. 추천

  8-4. 점수 계산하기

9. 지식을 행동으로 옮기기

  9-1. 데이터 활용의 현장

---

분석 담당자에게 SQL은

1) 데이터 가공 방법,

2) 분석에 사용되는 SQL,

3) 리포팅/분석 방법에 대한 정보를 제공한다.

• 구글 애널리틱스 등의 도구를 사용해도 SQL을 이용해야 활용할 수 있음 
• 기존의 리포팅 업무에 대한 새로운 안목을 습득할 수 있음
• 특정 데이터로 어떤 리포트를 만들지 다양하게 상상할 수 있음
• SQL로 가능한 것을 이해하고, 최종적으로 직접 SQL을 작성할 수 있게됨

---

2. 이 책에서 다루는 도구와 데이터 - 도구

 

1. PostgreSQL

• 오픈소스 RDB(관계형 데이터베이스)
• 표준 SQL을 잘 준수하여 윈도 함수, CTE(WITH 구문) 등 분석에 필수적으로 사용하는 구문 등을 모두 구현 

2. Apache Hive

• 고속으로 데이터를 처리하기 위한 아키텍처로 분산 파일 시스템이 고안되었다.
• Hive는 HDFS라고 부르는 분산 파일 시스템 위의 데이터를 SQL스러운 인터페이스로 간단하게 처리해 주는 시스템이다. 
• 분산 파일 시스템 위의 데이터 순서를 맞추기 위해 고안된 알고리즘 MapReduce와 함께 구현된 시스템이 Apache Hadoop
• Hive는 Hadoop 생태계의 일부분
• PostgreSQL로 대표되는 RDBMS와 비교해서 파일 기반 시스템이라는 특징이 있음
  • 파일 기반 시스템이라 특정 레코드 하나를 변경하거나 제거하는 것이 어려움 
  • 인덱스도 디폴트로 존재하지 않아 쿼리 실행 때 파일 전체를 조작해야 함 
• 동적으로 데이터를 정의할 수 있다는 장점
  • EX. Korea, Seoul, GangseoGu 데이터가 있다고 하면,
  • 국가, 시, 구 처럼 3개의 컬럼으로 다룰 수 있고, 하나의 문자열로 다룰 수도 있다.
• UDF(User-Defined Function)를 활용해서 SQL만으로는 구현하기 어려운 문자열 처리를 간단히 할 수 있음. 

3. Amazon Redshift

• AWS(Amazon Web Service)에서 제공하는 분산 병렬 RDB이다.
• 분산 환경에서 병렬처리를 해주는 것은 Hive와 비슷하지만, 
  • Hive는 파일 기반의 배치 처리를 SQL스러운 인터페이스로 구현할 수 있는 시스템
  • Redshift는 그냥 RDB, 따라서 레코드 업데이터/제거 가능, 트랜잭션 지원
  • Redshift의 접속 인터페이스는 PostgreSQL과 호환성을 가지므로, 전용 드라이버 또는 psql클라이언트에서 곧바로 Redshift에 접속할 수도 있음
  • 일반적 RDB에서 다룰 수 없는 대량의 데이터와 상호 작용하는 쿼리를 실행하고 싶을 때 효과적
  • 성능 튜닝을 하거나 비용을 줄이려면 최적의 노드 수와 스펙을 예측해서 인스턴스의 실행과 종료를 관리해야 함 - 전문적 인력 필요
  • 컬럼 지향 스토리지 사용
    • 테이블의 데이터를 물리적으로 저장할 때 레코드별로 저장하는 것이 아닌, 컬럼별로 저장
    • 쿼리 실행 때도 'SELECT*'처럼 모든 컬럼을 추출하는 쿼리는 성능이 낮게 나와서 필요 컬럼만 추출하는 쿼리를 실행해야 함

4. Google BigQuery

• 클라우드 서비스라는 점에서 Redshift와 비슷하지만, 다르게 직접 노드 인스턴스를 관리할 필요가 없으며 사용 시간이 아니라 읽어 들인 데이터의 양으로 비용이 발생한다는 점이 다름 
• 유료 버전의 구글 애널리틱스를 사용하면 데이터를 BigQuery로 쉽게 넘겨서 처리할 수 있다. 또한, 구글이 제공하는 다른 클라우드 서비스와도 쉽게 연동할 수 있다.
• 스탠다드 SQL과 레거시 SQL 두 종류가 있는데, 다른 미들 웨어와의 호환성을 위해 이 책에서는 BigQuery쿼리를 모두 스탠다드SQL로 설명할 것
• 읽어들이는 데이터 양을 기반으로 비용이 발생하여 사용 요금을 예측하기가 어려움
  • 따라서 쿼리 실행 시 데이터 로드를 줄일 수 있도록 자주 읽어들이는 데이터만 모아서 별도의 테이블로 분할하거나 필요 컬럼만 선택하는 SELECT구문을 활용하는 등 테크닉이 필요함
• RedShift처럼 컬럼 지향 스토리지 아키텍처, 실행 때 필요 컬럼 압축 시 읽어들이는 데이터 양을 크게 줄일 수 있음

5. SparkSQL

• MapReduce를 사용한 분산 처리 프레임워크인 Apache Spark의 기능 중에서 SQL 인터페이스와 관련된 기능을 나타내는 용어

오픈소스 프레임워크, 무료 사용 가능

• 기계 학습, 그래프 처리, 실시간 스트리밍 등 다양한 처리를 쉽게 분산 처리할 수 있음
• 데이터 추출을 파이썬 스크립트로 작업하고, 수집한 데이터를 SQL로 가공하고, R로 통계분석과 기계학습을 걸고, 결과를 엑셀로 출력해서 리포트를 만드는 등의 과정을 모두 Spark 프로그램 내부에서 한 번에 구현할 수 있음
• SQL스러운 선언적인 구문으로 데이터르 조작할 수 있고, 절차적인 프로그래밍과 비슷한 방법으로 프로그램을 구현할 수 있도록 DataFrames API가 표준으로 추가됨
  • 예를 들어 중간 데이터를 순서대로 표준 출력으로 내보내 중간 과정을 확인하며 처리할 수 있음
  • 데이터 처리를 작은 모듈로 분할해서 다양한 처리를 할 수도 있음
  • Spark에는 다음과 같이 기능이 많지만 너무 많으므로 이 책에서는 SparkSQL에 대해서만 다룸

1. PostgreSQL : 관계형 데이터베이스 RDB
2. Apache Hive : 분산 처리 / 파일 기반 시스템
3. Amazon RedShift : 분산 병렬 RDB / 컬럼 지향 스토리지
4. Google BigQuery : 컬럼 지향 스토리지 
5. SparkSQL : 분산 처리 

---

2. 이 책에서 다루는 도구와 데이터 - 데이터

데이터의 종류

1. 업무 데이터

• '서비스와 시스템을 운용하기 위한 목적으로 구축된 데이터베이스에 존재하는 데이터 
• 대부분 '갱신형' 데이터임
  • EX. 상품 하나를 추가할 때, 새로운 데이터로 레코드 하나를 삽입. 그리고 변경사항이 있을 때, 새로운 데이터를 삽입하는 대신 기존의 데이터를 갱신한다.
• 이러한 데이터는 다시 '트랜잭션 데이터'와 '마스터 데이터'로 분류됨

1-1. 트랜잭션 데이터

• 구매 데이터, 리뷰 데이터, 게임 플레이 데이터처럼 서비스와 시스템을 통해 사용자 행동을 기록한 데이터를 나타냄
• 데이터에는 날짜, 시각, 마스터 데이터의 회원ID, 상품ID, 수량, 가격 등이 포함되는 경우가 많고, 사용자 또는 운용상의 이유로 변경되거나 제거될 수 있음
• 트랜젝션 데이터를 기반으로 리포트를 만드는 경우가 많음
• 회원ID,상품ID로 저장된 경우가 많아 회원의 성별, 주소지, 상품의 이름 등을 곧바로 추출할 수 없음
  • 따라서 이러한 데이터를 기반으로 리포트를 만들 때 필요한 게 마스터 데이터

1-2. 마스터 데이터

• 카테고리 마스터, 상품 마스터처럼 서비스와 시스템이 정의하고 있는 데이터
• 회원과 관련된 정보도 사용자 마스터 테이블에 저장하므로 마스터 데이터로 분류함
• 이전의 트랜잭션 데이터는 회원ID, 상품 코드등이 저장되어있어 트랜잭션 데이터만으로는 잘 팔리는 상품의 명칭 또는 카테고리 등을 알 수 없는 경우가 많음
  • 따라서 트랙잭션 데이터의 상품ID와 마스터 데이터를 결합해서 상품 이름, 카테고리, 발매일 등을 명확하게 만들어 리포트의 업무 폭을 넓힐 수 있음
• 트랜잭션 데이터에 포함된 마스터 데이터는 리포트 업무 전에 확인해두어 분석 범위가 한정되지 않도록 해야 함

2. 로그 데이터

• 통계 또는 분석을 주 용도로 설계된 데이터
• 특정 태그를 포함해서 전송된 데이터
• 특정 행동을 서버 측에 출력한 데이터
• 엔지니어에게 로그 데이터는 웹 서버의 접근 로그 또는 파일로서 저장된 것이지만, 이 책에서는 저장된 형식과 상관없이 모두 로그 데이터라 부르고, 전송 형식도 따지지 않음
• 중요한 것은 '누적형' 데이터라는 것 : 출력 시점의 정보를 축적해두는 것, 후에 변경되더라도 기존의 데이터 수정 X

---

업무 데이터

1. 업무 데이터의 특징
   1. 데이터의 정밀도가 높다 : 데이터 처리 중 문제 발생시, '트랜잭션'과 '롤백' 기능을 사용해 문제를 제거할 수 있다. 따라서 데이터 정합성이 보증됨. 정확한 값이 요구되는 매출 관련 리포트를 만들 때에는 업무 데이터 사용
   2. 갱신형 데이터 : 매일 다양한 데이터 추가, 갱신, 제거가 실행 됨
      • 사용자 탈퇴시 데이터를 물리적으로 제거
      • 주문 취소시 플래그를 통해 상태를 변경해 논리적으로 제거
      • 이사 등 주소가 변경된 경우 사용자 정보 갱신
      • 따라서 데이터를 추출하는 시점에 따라 추출되는 데이터가 바뀔 수 있음을 의식해야 한다.
   3. 다루어야 하는 테이블의 수가 많다 
      • 대부분의 서비스는 데이터베이스로 RDB를 사용. 데이터 확장성을 배제하고 데이터의 정합성을 쉽게 유지하며 데이터를 저장하기 위함. 
      • 하나의 테이블을 참조해서는 해당 데이터의 특성을 파악하기 어렵기 때문에, 업무 데이터를 다룰 때에는 'ER다이어그램'이라고 부르는 데이터 구조를 나타낸 설계 문서를 파악하고 여러 테이블을 결합해야 데이터 전체 내용 파악 가능
2. 업무 데이터 축적 방법
   • 업무 데이터를 분석 전용 환경에 전송하려면 'Apache Sqoop'처럼 RDB에서 빅데이터 분석 기반으로 데이터 로드를 해주는 시스템 등을 사용해야 한다. 
   • 업무 데이터가 어떻게 축적된 데이터인지 이해할 필요가 있음
     1. 모든 데이터 변경하기
        1. 날짜를 기반으로 계속 누적되는 경우가 아니라면, 데이터 전체를 한꺼번에 바꿔 최신 상태로 만들기
        2. 빈번하게 변경되는 테이블, 또는 날짜 경과시 상태가 변화하는 테이블의 경우, 모든 데이터를 한꺼번에 바꿔버리면 최신상태가 저장되므로 리포트 만들 때에는 편하더라도, 과거 정보를 잃어버려 주의해야 함
     2. 모든 레코드의 스냅샷을 날짜별로 저장하기
        1. 마스터 데이터라도 날짜 경과에 따라 상태가 변할 수 있다. 
        2. 업무 데이터의 특징인 갱신형 데이터임을 고려하였을 때, 추출시점에 따라 출력 결과가 달라지면 신뢰성이 낮아짐
        3. 데이터 용량이 커지기는 하지만, 시점별로 모든 레코드를 누적하면 신뢰성을 어느정도 보장할 수 있음 
3. 업무 데이터 다루기
   • 업무 데이터를 다룰 때 고려해야하는 점
     1. 매출액, 사용자 수처럼 정확한 값을 요구할 경우 활용하기
        • 업무 데이터는 '트랜잭션'기능 덕분에 데이터의 정합성이 보장된다. 따라서 추출 결과를 신뢰할 수 있음. 
        • 반면, 로그 데이터는 전송 방법에 따라 중간 손실 발생 가능
        • 따라서 정확한 값이 요구될 경우 업무 데이터 사용하기
     2. 서비스의 방문 횟수, 페이지 뷰, 사용자 유도 등의 데이터 분석에는 사용할 수 없다.
        • 예를 들어 데이터를 저장하는 경우, 어떤 장치에서 구매했는지는 업무적으로는 크게 필요 없는 정보이다. 따라서 사용자 에이전트를 따로 저장하지 않을 것
        • 어떤 페이지를 확인하고 구매했는지, 확인 때의 사용자 에이전트는 어떤지 모두 저장하면 서비스 처리에 영향을 줄 수 있어 그런 데이터는 업무 데이터로 저장하지 않는 경우가 많다.
        • 따라서 사이트 방문 횟수, 사용자 유도 상태 등을 분석하려면 업무 데이터가 아니라, 로그 데이터를 사용해야 함
     3. 데이터 변경이 발생할 수 있으므로 추출 시점에 따라 결과가 변화할 수 있음
        • 업무 데이터의 트랜잭션 기능으로 데이터의 정합성이 보장된다는 특징은 데이터가 변경될 수 있어 추출시점에 따라 추출 결과가 바뀔 수 있다는 뜻이기도 하다. 
        • 따라서 리포트를 만들어 제출할 때, '추출 시점의 정보를 기반으로 작성된 리포트'라고 명시해야 혼동이 없을 것
        • 업무 데이터 변경의 영향을 최소화하여 데이터를 축적할 수 있는 방법을 찾는 것이 좋음

---

로그 데이터

• 많은 접근 해석 도구는 로그 데이터를 집계하고, 열람하기 위한 도구를 제공한다. 따라서 로그 데이터를 이해하면 도구를 더 깊게 이해할 수 있을 것
• 로그 데이터의 특징
  1. 시간, 사용자 엔드 포인트,IP, URL, 레퍼러, Cookie 등의 정보 저장하기
     • 업무 데이터는 서비스,시스템 구축시 필요한 데이터인 반면, 로그 데이터는 서비스 처리에 영향이 거의 없는 사용자 엔드포인트,IP, 주소, URL, 레퍼러, Cookie 등의 정보를 저장한 것
  2. 로그 데이터는 추출 방법에 따라 데이터의 정밀도가 달라짐
     • 로그는 어떻게 추출하는지, 집계 대상 데이터가 어떠한 상태로 있는지 파악하지 않고 사용하면 잘못된 판단을 내릴 수 있다. 
  3. 계속 기록을 추가하는 것일 뿐, 과거의 데이터가 변경되지는 않음
     • 출력 시점의 정보를 기록하는 것이라, 상품의 가격을 변경하더라도 과거의 로그 데이터에는 변화가 없음
• 로그 데이터 축적 방법
  1. 태그, SDK를 통해 사용자 장치에서 데이터를 전송하고 출력하기(비컨 형태)
     • 구글 애널리틱스처럼 html에 특정 태그를 집어넣고 데이터를 전송하는 형식
     • 웹사이트 개발시 이용하는 일반적인 방법
     • 크롤러의 영향을 적게 받는 방법 - 웹사이트에서 자바스크립트를 통해 전송하는 경우, 해석할 수 없는 크롤러/브라우저의 로그로 출력되지 않는다. 

*SDK : Software Development Kit 소프트웨어 개발 도구 모음

• 1. 서버에서 데이터를 추출하고 출력하기 (서버 형태)
     • 서버에 요청이 있을 때 로그를 출력
     • 따로 크롤러의 접근을 확인하고 조건을 걸지 않는 이상 크롤러의 접근도 출력됨
     • 크롤러의 접근을 막는 것은 거의 불가능
     • 이러한 데이터를 사용해 사용자의 행동을 분석하면 잘못된 판단을 내릴 수 있음. 의도치 않은 로그를 제거하는 과정을 반드시 거쳐야 한다.
• 로그 데이터 다루기
  1. 사이트 방문 횟수, 페이지뷰, 사용자 유도 상황을 집계하고 분석할 때 주로 사용 
     • 업무 데이터로 관리할 수 없는 열람 페이지, 레퍼러, 사용자 에이전트 등을 저장할 수 있어 접근 분석도구를 함께 활용하여 방문 횟수, 페이지 뷰, 액션 수, 장치별 방문 수 등의 지표를 구할 때 사용
  2. 최신 상태를 고려한 분석에는 적합하지 않음
     • 로그 출력 시점 정보가 기록되어 당시 상황 분석에는 편리하나, 로그 출력 이후 데이터 변경 내용을 모두 고려해서 분석할 때에는 별도의 데이터 가공이 필요함. ex. 상품 카테고리 변경, 사용자 주소 변경
  3. 계속 기록을 누적하는 형태이므로 추출 결과가 변할 가능성이 적음
     1. 로그 데이터는 변경/제거되지 않으므로, 기간을 지정해서 집계했을 때 쿼리 결과가 바뀌지 않는다. 
     2. 반면에, 업무 데이터의 경우, 시스템에 따라 레코드를 제거할 가능성이 있음
  4. 데이터의 정확도는 업무 데이터에 비해 낮음
     1. 로그 추출 방법에 따라 사용자가 누락될 가능성
     2. 크롤러의 로그가 함께 포함되어 집계될 가능성
     3. 정확한 값이 필요한 경우 적절치 않음
     4. 로그 데이터는 기본적으로 출력 이후 변경되지 않고 누적할 뿐이다. 

< 업무 데이터(트랜잭션/마스터 데이터)와 로그 데이터 비교>

- 업무 데이터 : 갱신형 데이터 / 추출 시점에 따른 변화 주의 / 다루어야 하는 테이블의 수가 많음
- 업무 데이터 1. 트랜잭션 데이터 : 사용자 행동 데이터 / 변경,제거 O
- 업무 데이터 2. 마스터 데이터 : 서비스와 시스템이 정의하고 있는 데이터 (ex.카테고리 마스터)
- 업무 데이터 축적 방법 : 1) 모든 테이블 최신상태로 변경 2) 모든 레코드의 스냅샷을 날짜별로 저장 (추출 시점에 따른 변화로 인한 과거 데이터 손실 방지)
- 3. 로그 데이터 : 분석 목적, 특정 태그 포함, 특정 행동 출력을 위한 데이터
- 로그 데이터 축적 방법 : 1) 태그, SDK를 이용해 사용자 장치에서 데이터를 전송,출력 (ex. 구글 애널리틱스) 2) 서버에서 데이터를 추출하고 출력
=> 업무 데이터는 '정확한' 값이 요구될 때, 데이터가 변경될 수 있는 가능성이 있기에 변경의 영향을 최소화하여 축적할 수 있는 방법에 대한 고안 필요
=> 로그 데이터는 방문 횟수, 페이지 뷰, 사용자 유도 상황 등을 집계 분석할 때 적합하고, 추출 결과가 변할 가능성이 적으나, 정확도가 떨어지고 최신 상태를 고려한 분석에 적합하지 않다는 단점이 있음

---

두 데이터를 사용해서 생성되는 가치

• 업무 데이터의 가치
  • 매출액의 추이는 물론 어떤 상품이 인기있는지 파악하고자할 때, 사용자에게 더 많은 관심을 주어 구매하게 만들 수 있음.
  • EX. 상품의 계절성, 특정 시간에 많이 팔리는지 등 과거의 경향 파악
• 로그 데이터의 가치
  • 어느정도 바꿀 수는 있으나, 스스로 생각한 리포트의 형식과 집계 방식을 접근 분석 도구에서 제공하지 않는다면, 원하는 대로 집계할 수 없음.
  • 하지만, 빅데이터 기반이 있다면, 원하는 리포트를 자유롭게 정의할 수 있음.
  • 데이터 수집,가공,집계를 자유롭게 할 수 있어 접근 분석 도구의 제한을 받지 않고 원하는 결과를 도출해낼 수 있음
• 두 데이터를 사용했을 때 발생하는 새로운 가치
  
  • 로그 데이터는 주로 웹사이트에서의 행동 기록용
  • 업무 데이터는 웹사이트 뿐만 아니라, 오프라인에서의 데이터도 사용 가능 (EX. POS데이터, 음식점 예약 데이터, 대응 이력 등 다양한 데이터) 
  • 함께 활용시, 웹사이트에서의 행동이 오프라인의 행동에 어떠한 영향을 미치는지 조사할 수 있음 
    • EX. 특정 미디어 또는 광고로 유입된 사용자가 오프라인에서 계약할 가능성이 더 높다면, 해당 미디어와 광고를 더 활용
    • EX. 음식점 방문 전에 사람들이 웹사이트에서 많이 본 상품이 실제 매장에서도 많이 팔린다는 경향이 있다면, 고객이 방문했을 때 어떤 식으로 접객해야 하는지 더 명확하게 만들 수 있음
    • 따라서 웹사이트에서 오프라인으로 사용자를 유도하는 서비스라면, 두 가지 데이터를 함께 활용했을 때 분석 가능성이 훨씬 넓어짐

---

어떻게 해야 가치있는 데이터 분석을 할 수 있을까

데이터 활용 사례

1. 목표 관리
   1. 목표를 관리하고 설계하고, 서비스/조직의 성장에 기여하기
   2. 매출, 접근 수, 사용자 수처럼 서비스가 지향하는 목표가 현재 어느정도 진행되었는지 파악
   3. 부족할 경우 달성할 수 있도록 시책을 검토/실시하여 성장에 기여
2. 서비스 개선
   1. 사용자 행동을 기반으로 경향을 발견하고, 매출과 서비스 개선에 기여
   2. 사용자 인터뷰는 비용,시간적으로 좋지 않고, 샘플 수가 적어 일반화가 힘듦
   3. 따라서 대량의 데이터를 기반으로 사용자 경향을 발견하는 것이 더 좋을 수 있다. 
3. 미래 예측
   1. 과거의 경향을 기반으로 미래의 행동 예측
   2. 웹사이트에서 특정 행동을 취한 사용자가 이탈하는 경향이 있을시, 미리 파악하여 사전 대응 가능 (전조 감지)
   3. 상품 추천 등 추천 시스템도 데이터를 활용한 미래 예측 중 하나

---