[데이터베이스] Data Analytics with SQL - DW, OLAP

Data Analytics with SQL

Overview

Data analytics

• 예측을 위해 패턴, 관계, 모델 등을 추론하는 데이터 처리
• 주로 비즈니스적 결정에 사용됨

Common steps in data analytics

• 여러 소스로부터의 데이터를 한 곳에 모은다
• 데이터를 요약한 집합과 보고서를 생성한다
  • OLAP(Online analytical processing system)이 interactive qeury를 가능하게 함
• 예측 모델을 만든다

Data Warehousing

Data warehouse (DW)

• 의사결정을 위해 다양한 데이터베이스들을 수집한 후 하나의 통일된 형식으로 저장한 데이터베이스들
• 관계형 DB를 근간으로 많은 데이터를 다차원 분석하여 의사결정에 도움을 주는 시스템
• 데이터 소스들은 보통 현재의 데이터만 가지고, 과거의 데이터는 갖고있지 않는다.
  • 의사결정에는 과거의 데이터들도 필요함
  • 이를 DW가 제공할 수 있음

warehouse가 갖는 데이터의 종류

• Dimension table
  • 데이터 소스에 존재하는 테이블
• Fact table
  • 분석을 위해 생성한 테이블
  • 여러 dimension table의 PK를 속성으로 가짐, 그리고 이 속성들이 fact table의 PK가 됨
  • measure 속성도 가짐, 이는 PK가 아닌 속성 (number, price

Online Analytical Processing

OLAP

• allowing data to be summarized and viewed in different ways
• 데이터를 요약하고 다른방식으로 표현해주는 과정

OLAP in SQL

다음과 같은 dimension 테이블을 조인한 fact 테이블이 있다고 해보자

• saels(item_name, color, clothes_size, quantity)
• item_name, color, clothes_size 은 dimension table

기본 쿼리

 

• select sum(quantity) from sales; → 전체 quantity 출력 (164)
• select sum(quantity) from sales group by color; → color로 나뉜 quantity 출력
• select sum(quantity) from sales group by color, item_name → color, item_name으로 나뉜 quantity 출력 

cube, rollup

• select sum(quantity) from sales group by cube(color, item_name)
  • 모든 집합을 생성
  • {(item_name, color), (item_name), (color), ()}
  • 다음의 쿼리와 같은 효과
  •  

    

• select sum(quantity) from sales group by rollup(color, item_name)
  • 오른쪽부터 하나씩 제거한 집합 생성
  • {(item_name, color), (item_name), ()}

• cube, rollup 을 여러개 사용할 경우
  
  • group by rollup(item_name), rollup(color, size)
    • {(item_name), ()} X {(color, size), (color), ()}
    • {(item_name, color, size), (item_name, color), (item_name)
    • , (color, size), (color), ()}

pivot

• select * from sales pivot( sum(quantity) for color in ('dark', 'pastel', 'white') );
• color의 속성값(dark, pastel, white)를 필드로 추가해 각각의 quantity를 보여줌

좌: 원래 데이터, 우: pivot 쿼리 결과

 

 

---

reference : 상명대학교 김종욱 교수님