본문 바로가기
v3 | Tableau Deep Dive

[2]-6. 사용자 지정 SQL

by VizLab 2019. 10. 6.

두번째 대주제, Data Preparation의 마지막 토픽입니다

사용자 지정 SQL, Custom SQL (편의상 본 포스트에서는 커스텀 SQL로 지칭하도록 하겠습니다)

 

쿼리를 좀 쓸 줄 아는 사용자들에게는 Data Prep 과정에서의 자유도를 높여줄 수 있으며,

잘 모르는 사용자들도 SELECT문 등 간단한 몇 개만 사용할 줄 알면, 아주 큰 도움을 받을 수 있는 히든 카드라고 할 수 있죠 :D

 

오늘은 여기에 대해서 이야기를 좀 해보도록 하죠

 

 

 

We Are Here


[1] Tableau Fundamentals

[2] Data Preparation

   ▪  Data Interpreter

   ▪  Pivot

   ▪  Split

   ▪  Data Source Filter

     Custom SQL

[3] Manipulating Data

[4] Building a Chart

[5] Analytics Pane

[6] Filter

[7] Table Calculation

[8] Parameter

[9] Level of Detail Expressions

[10] Map

[11] Dashboard Rendering

 

 

 

Custom SQL을 위한 준비


DB에서 데이터를 끌어오면 별도의 설치 없이 커스텀 SQL을 활용할 수 있습니다

 

구글 빅쿼리에서 Sample - Superstore의 Orders 테이블 중 일부의 데이터만 불러와보겠습니다

 

 

왼쪽 부분의 '새 사용자 지정 SQL'을 주황색 영역으로 끌어오게 되면 쿼리를 입력할 수 있는 창이 뜨게 됩니다

 

SELECT order_date, category, sales FROM `sample_superstore.orders` LIMIT 10

 

Orders 테이블에서 주문일자와 제품 카테고리, 그리고 매출만 불러오되, 위에서부터 10개의 행만 가져오라는 쿼리입니다

아래쪽의 결과 미리 보기를 눌러주면 불러올 데이터의 프리뷰가 별도의 창에 뜨면서 의도한 데이터가 맞는지 확인할 수 있도록 도와주죠

 

DB를 연결했을 때에는 왼쪽 하단에 나타나는 '새 사용자 지정 SQL'을 Drag & Drop만 하면 쉽게 사용할 수 있습니다 :D

 

로컬에 있는 엑셀 파일을 가지고 와서 커스텀 SQL을 사용하기 위해서는 약간의 준비작업이 필요합니다

 

여기를 클릭하고 들어가서 나오는 드라이버를 설치하면 됩니다

 

 

커스텀 SQL을 사용하기 위해서는 엑셀 데이터를 레거시 연결을 통해 불러와야합니다

그런데 위의 드라이버가 설치되어있지 않으면 레거시 연결이 불가능합니다

아래의 표에 나와있듯이 기본 연결은 커스텀 SQL 사용을 허용하지 않지만, 레거시 연결은 커스텀 SQL을 지원하고 있습니다

 

한 가지 중요한 사실은 Mac에서는 지원되지 않습니다. 슬프네요 ㅠㅠ

 

설치가 완료되었으면 몇 가지 사용법을 익혀보겠습니다

 

 

 

피벗, 그리고 더블 피벗


Data Prep 과정에서 다양한 니즈에 따라 커스텀 SQL을 사용할 수 있겠지만,

개인적으로는 이 피벗, 그리고 더블 피벗 기능이 가장 유용한 것 중 하나가 아닐까 싶습니다

 

근데 피벗(가로로 긴 데이터를 세로로 긴 형태로 바꾸는 것)은 굳이 커스텀 SQL을 쓰지 않고도 데이터를 불러온 후 처리가 가능했었죠?

지난 포스트를 통해서 그 과정을 상세히 살펴봤습니다

 

그런데, 커스텀 SQL의 활용법에서 피벗을 다시 한 번 강조하는 이유는 다음 두 가지 때문입니다

 

▪  DB에 있는 데이터를 불러오면 피벗을 활용할 수 없습니다

▪  엑셀 데이터를 불러오더라도, 필요에 따라 피벗을 두 번 사용해야할 경우가 있는데, 데이터를 불러온 후에는 단 1회의 피벗만 가능합니다

 

이 두번째 이유를 커스텀 SQL 실습을 통해 해결해보겠습니다

 

실습을 위해서 가벼운 가상 데이터셋을 만들어보았습니다

Custom_SQL_Example.xlsx
0.01MB

 

Exercise 시트를 열어볼까요?

 

 

총 20명의 영업사원이 있고, 각자가 관할하는 구역이 나와있습니다 (A열과 B열)

2015년 매출액부터 2017년 매출액까지 가로로 붙어있고, (C열 ~ E열)

2015년부터 2017년까지 각 영업사원에 대한 평가점수(1-100점)가 옆으로 늘어져있습니다 (F열 ~ H열)

 

이런 식으로 서로 다른 성격의 측정값 2개 이상이 차원 성격을 가지는 값(여기에서는 연도)과 함께 가로로 길게 붙어있을 때,

한 번의 피벗만으로는 태블로에서 쓰기 편한 Columnar 형태의 데이터셋을 만들어내기 어렵습니다

피벗을 두 번 사용해야하는데, 커스텀 SQL을 사용하면 해결할 수 있습니다!

 

태블로로 데이터를 불러오겠습니다

데이터를 불러오는 단계가 중요합니다

 

 

태블로를 열고 Microsoft Excel을 누르면 대화창이 뜨고, 불러오고자 하는 엑셀 파일을 선택합니다

그리고 열기를 바로 누르지 말고, 옆의 세모 모양을 눌러주면 '레거시 연결로 열기'를 선택할 수 있습니다

앞서 드라이브 설치가 제대로 되지 않았다면, '레거시 연결로 열기'가 보이지 않을 수도 있습니다

 

데이터를 불러온 후 아래의 그림처럼 좌측 상단의 데이터 메뉴에서 '사용자 지정 SQL'로 변환을 클릭합니다

 

 

그러면 아래의 그림처럼 쿼리가 나옵니다

지금 보여지는 저 데이터들은 오른쪽 쿼리에 의해서 생성된 것들입니다. 쿼리를 바꿔주면 데이터가 나열되는 방식도 바뀌어지겠지요

 

 

어떻게 바꿀까요? 스키마를 한 번 상상해봅시다

우선 Employee_ID는 그대로 들어가야할 것 같습니다, Region도 마찬가지로 그대로 넣어보죠

다음으로 Year라는 필드를 하나 만들어서 연도를 집어 넣으면 좋을 것 같고,

Sales와 Evaluation 필드를 각각 만들어서 해당 연도의 값들을 집어넣겠습니다

 

먼저 아래와 같이 입력하면,

 

SELECT 

[Exercise$].[Employee_ID] AS [Employee_ID],

[Exercise$].[Region] AS [Region],

2015 AS [Year],

[Exercise$].[Sales_2015] AS [Sales],

[Exercise$].[Evaluation_2015] AS [Evaluation]

FROM [Exercise$]

 

Exercise 테이블의 Employ_ID와 Region을 가지고 오고, Year라는 필드에는 2015라는 숫자를 넣습니다

참고로 2015 대신 "2015"를 넣으면 Year 필드는 문자열로 인식됩니다

 

다음으로 Sales_2015의 값을 가지고 와서 Sales 필드에 집어넣고

Evaluation_2015의 값을 가지고 와서 Evaluation 필드에 넣으라는 의미이죠

 

그러면 데이터가 이렇게 되겠죠

 

 

위의 쿼리에서는 2015년 값만 들어갔기 때문에 2016년 값과 2017년 값도 함께 넣어줘야합니다

이를 위해서는 UNION을 활용하면 됩니다

 

SELECT 

[Exercise$].[Employee_ID] AS [Employee_ID],

[Exercise$].[Region] AS [Region],

"2015" AS [Year],

[Exercise$].[Sales_2015] AS [Sales],

[Exercise$].[Evaluation_2015] AS [Evaluation]

FROM [Exercise$]

 

UNION ALL

 

SELECT 

[Exercise$].[Employee_ID] AS [Employee_ID],

[Exercise$].[Region] AS [Region],

"2016" AS [Year],

[Exercise$].[Sales_2016] AS [Sales],

[Exercise$].[Evaluation_2016] AS [Evaluation]

FROM [Exercise$]

 

UNION ALL

 

SELECT 

[Exercise$].[Employee_ID] AS [Employee_ID],

[Exercise$].[Region] AS [Region],

"2017" AS [Year],

[Exercise$].[Sales_2017] AS [Sales],

[Exercise$].[Evaluation_2017] AS [Evaluation]

FROM [Exercise$]

 

커스텀 SQL 편집창에 위의 쿼리를 실행시킨 결과 데이터가 어떻게 변하는지 확인해보겠습니다

 

 

의도했던 형태로 필드가 정렬되었고, 오른쪽 상단에 60개 행의 개수를 확인할 수 있습니다

2015년 20개 행, 2016년 20개 행, 2017년 20개 행, 모두 합쳐 60개입니다

 

두번의 피벗을 사용할 수 없기 때문에 커스텀 SQL을 활용하여 피벗을 두 번 실행시킨 결과를 도출해봤습니다

현업에서 유용하게 사용할 수 있지 않을까 싶습니다 :D

 

 

 

Data Densification & Domain Padding


한국말로 만들어진 태블로 콘텐츠 중에 이 둘에 대해 설명되어있는 것을 아직까지는 본 적이 없습니다

비즈니스 도메인에서는 거의 사용되지 않지만, 화려한 시각화 결과물을 만들 때에는 중요하게 사용되는 기법 중 하나입니다

 

Data Densification과 Domain Padding은 듬성듬성한 데이터를 빽빽하게 채워주는 과정으로 이해하면 됩니다

아래와 같이 곡선으로 그리는 차트를 구현할 때는 거의 대부분 이 두 기법을 이용한다고 이해하시면 되겠습니다

 

 

Data Densification을 구현하는 방법이 세 가지 있는데, 그 중의 하나가 커스텀 SQL을 활용하는 방법입니다

 

▪  엑셀 데이터 직접 수정 ( -> 원본을 변형시키는 부담 + 대용량 데이터에서 적용하기 어려움)

▪  커스텀 SQL ( -> Mac에서는 사용 불가)

▪  Fixed LOD (-> 가장 많이 활용하는 방법이지만, 차원이 2개 이상 있어야만 활용 가능)

 

데이터를 불러와 커스텀 SQL을 활용한 Data Densification을 실습해보도록 하겠습니다

 

엑셀 데이터를 레거시 연결을 통해 불러온 후 Exercise2 시트를 올립니다

그리고 데이터 --> 사용자 지정 SQL로 변환 누르면 아래와 같은 쿼리 편집창이 뜨게 되지요

 

 

쿼리를 다음과 같이 약간 수정해줍니다

 

SELECT [Exercise2$].[Lost] AS [Lost],

[Exercise2$].[Team] AS [Team],

[Exercise2$].[Tie] AS [Tie],

[Exercise2$].[Win] AS [Win],

1 AS [Path]

FROM [Exercise2$]

 

UNION ALL

 

SELECT [Exercise2$].[Lost] AS [Lost],

[Exercise2$].[Team] AS [Team],

[Exercise2$].[Tie] AS [Tie],

[Exercise2$].[Win] AS [Win],

10 AS [Path]

FROM [Exercise2$]

 

어떻게 변형되는지 확인해보겠습니다

 

 

동일한 데이터가 복사되어서 원본 아래에 붙었는데, 원본은 Path = 1로 되어있고 복사되어 붙은 데이터는 Path = 10으로 나와있네요

 

약간 Off-Topic이긴 하지만 말이 나왔으니 Data Densification이 어떻게 이루어지는지도 한 번 살펴보죠

 

먼저 측정값 부분에 생긴 Path를 차원으로 올려준 후 마우스 우클릭 --> 만들기 --> 구간차원을 선택합니다

 

 

그러면 구간차원을 만들 수 있는 대화창이 아래와 같이 뜨게 되는데, 구간차원의 크기를 1로 설정해줍니다

 

 

그 다음 아래 두 그림을 비교해보죠

팀을 삼성 라이온즈로 필터를 걸고, 행에는 Win을 올렸습니다

좌측은 Path를 열에 올린 그림이고, 우측은 Path(구간차원)을 열에 올린 그림입니다

혹시 오른쪽 그림처럼 1, 2, 3, ..., 10까지 보이지 않는다면, 구간차원의 누락된 값 표시가 체크되어 있는지 확인해보면 됩니다

 

 

우리가 가진 데이터 셋에는 Path가 1과 10밖에 없습니다 (좌측 그림)

그런데, 구간차원을 만든 후 누락된 값 표시를 체크했더니 중간에 숫자들이 생겼습니다! (우측 그림)

데이터셋에 없던 부분(2~9)이 생겨서, 1과 10 사이를 꽉 채워주고(densifying)있죠

 

아래 그림을 보면 조금 더 확실합니다

 

 

원래는 Path = 1과 Path = 10에만 값이 있었는데, (위쪽)

테이블 계산 누계를 활용하니 2 ~ 9에도 값이 채워진 것을 확인할 수 있습니다 (아래쪽)

 

Data Densification을 언제 어떻게 활용하는지에 대한 구체적인 이야기는

별도의 포스트 또는 추가 리소스를 통해서 제공해드리도록 하겠습니다

 

여기서 말씀드리고자 했던 것은, 커스텀 SQL을 활용하면 Data Densification이 가능하다! 입니다 :D

 

 

 

매개변수를 활용한 데이터 선택


마지막으로 소개하고 싶은 커스텀 SQL의 활용법은 매개변수를 활용해 데이터를 선별적으로 불러오는 기능입니다

 

데이터를 바로 확인해보겠습니다

엑셀 데이터를 레거시 연결을 통해 불러온 후 Exercise3 시트를 Drag & Drop으로 올립니다

데이터 --> 사용자 지정 SQL로 변환 누르면 아래와 같은 쿼리 편집창이 나옵니다

 

 

여기에서 WHERE절을 추가하여 데이터의 범위를 좁혀보겠습니다

 

SELECT [Exercise3$].[Area] AS [Area],

[Exercise3$].[Region] AS [Region],

[Exercise3$].[Sales] AS [Sales]

FROM [Exercise3$]

WHERE [Area] = '수도권'

 

데이터가 수도권만 필터링된 결과군요

 

 

이걸 다이나믹하게도 만들어줄 수 있습니다. 바로 매개변수를 활용하면 됩니다
위의 쿼리에서 수도권을 지운 후 커서를 그대로 둔 채로 '매개 변수 삽입' --> '새 매개변수 만들기'를 클릭합니다

 

 

다음과 같이 매개변수를 만들어줍니다

 

 

확인을 누르면 쿼리 편집창이 이렇게 바뀌게 됩니다

 

 

한 번 더 확인을 누른 후 시트로 넘어가보겠습니다

 

 

제일 하단에 보면 매개변수가 만들어져서 들어와 있습니다. 어떻게 작동하는지 간단하게 차트를 만들어보겠습니다

 

 

Region을 열에 올리고, Sales를 행에 올렸습니다. 그리고 매개변수를 통해 Area를 바꾸어보았습니다
위의 그림에서 보이듯이 사용자의 매개변수 선택에 따라 차원 값인 Region이 선별적으로 나오고 있음을 확인할 수 있습니다 :D

 

 

 

SQL은 기본 중의 기본!


태블로에서 커스텀 SQL이 활용되는 방법이 이 포스트에 소개된 것이 다가 아니겠지요
쿼리를 자유롭게 쓰는 사용자는 원하는 데이터를 얼마든지 가공해서 만들어낼 수 있습니다. SQL은 데이터를 다루는 기본 중에 기본이지요!

저는 개인적으로 SQL 활용 능력과 태블로 활용 능력이 결합되었을 때 어마어마한 시너지를 낼 수 있다고 믿고 있습니다

본 포스트에서는 대표적인 몇 가지 기능들만 가볍게 살펴보았습니다
쿼리를 자유자재로 활용하는 그 날까지, 우리 모두 함께 열심히 연습해봅시다 :D

여섯번에 걸쳐 살펴본 Data Preparation 이슈를 마무리하고, 이젠 조인, 유니온, 블렌딩 등의 Manipulating Data 파트로 넘어갑니다!

'v3 | Tableau Deep Dive' 카테고리의 다른 글

[3]-1. 조인 (2)  (3) 2019.10.07
[3]-1. 조인 (1)  (1) 2019.10.06
[2]-5. Data Prep Hands-On  (1) 2019.10.05
[2]-4. 데이터 원본 필터  (1) 2019.10.05
[2]-3. 사용자 지정 분할  (1) 2019.10.04