[spotfire] 21. data table 내 변환 - calculated column

calculated column은 데이터 처리 방법 중에 data table 내 변환, 즉 data transform 중에 하나이지만 매우 활용도가 높은 기능입니다. 또한 calculated column 기능에 사용되는 설정창은 대부분의 data transform에도 유사하게 사용하고 있기 때문에, calculated column 기능을 확실하게 숙지하면 다른 기능들에도 잘 활용할 수 있을 것 입니다. calculated column 기능을 실행하는 방법은 크게 2가지 입니다.

위치 : data canvas - plus 버튼 - data transform - calculated new column / Data - add calculated column

add calculated column 접근 위치

2가지 접근 방법이 이름이 약간 다르지만 data table에 column을 추가하는 기능은 동일합니다. 각 방법은 취향에 따라 사용하시면 되지만, 필자의 경우 data table의 개수가 적고 간단한 분석을 할 때에는 Data - add caculated column을 사용하고, data table이 여러개이고 복잡한 처리가 많이 사용될 때에는 data canvas를 통해 calculated new column 방법을 사용합니다. 이는 동일한 기능을 하지만 데이터를 처리함에 있어 data canvas를 활용하는 것이 더 체계적이고 절차를 구성함에 있어 효율적이기 때문입니다. 자세한 내용은 data canvas에 대해 배울 때 설명 드리겠습니다.

calcuated column의 사용범위

spotfire에서 column은 논리 단위입니다. 때문에 column을 기본으로 다른 수식들이나 내용들을 추가하여 차트를 그릴 수 있습니다. calculated column으로 생성할 수 있는 범위는 data table 내의 data를 변형한 내용 또는 임의로 지정하는 상수 범위로 제한됩니다. 가령 부동산 가격을 나타내는 data table 예제에서는 뜬금없는 서울 인구 밀도 데이터와 같은 내용은 넣을 수 없고, data table 간의 변환과 관련된 기능을 사용해야 합니다.

calculated column의 function

Add calculated column 위치와 add calculated column 윈도우창 설명

add calculated column에서는 column name과 함께 properties나 function을 넣을 수 있습니다. function의 종류들을 살펴보면 어디선가 많이 사용했던 function과 유사해 보입니다. 평소 MS office 문서를 많이 다뤄보신 분이시라면 excel에서 사용하던 function들과 비슷하다라고, 또는 DBMS와 SQL을 많이 다루신 분이라면 SQL function과 거의 같다라는 생각이 드실 수 있습니다. 설령 사용법을 잘 모르더라도 오른쪽에 function을 선택하거나 검색을 하면 설명과 사용법 등이 자세히 나옵니다. function들을 다 소개할 수 없지만, 알고 있으면 좋은 fnuction들을 조금 소개할까 합니다.

통계함수 : AVG, MED, MAX, MIN..
변환함수 : Boolean, Date, DateTime, Integer, String...
수학함수 : Cos, Sin, Tan, Log, Mod, PI, Round, Power...
문자함수 : Split, Substring, Left, Right, Len
순위함수 : Rank, DenseRank
논리함수 : if, case when, Is Null,
날짜함수 : Today, DateDiff, Year, Month, Week
키워드 : AS, NEST, OVER

아래에서는 자주 사용하는 함수들의 몇 가지 예시를 소개 하겠습니다.

변환함수

변환함수는 사용방법은 크게 어렵지 않습니다. 함수명을 바꿔줄 형식으로 작성하기만 하면 되지만, 날짜로 변환하는 DateTime나 Date 사용 시에는 어떻게 넣어야 되는지 알고 계셔야 됩니다.

날짜 형식으로 변환: DateTime(년, 월, 일, 시, 분, 초) / Date(년, 월, 일)

사용자가 날짜로 변환하는 목적은 날짜 계산이나 주(week) 등을 표시하기 위함입니다. 따라서 아래의 날짜함수도 익혀 두시면 좋습니다. 반대로 날짜에서 숫자나 문자로 변환하는 함수는 아래와 같습니다.

yyyy-mm-dd 형식의 문자로 출력: string([날짜])
yyyymmdd 형식의 문자로 출력:
Substring(string([날짜]),1,4) & Substring(string([날짜]),6,2) & Substring(string([날짜]),9,2)
yymmdd 형식의 숫자 정수로 출력:
int(Substring(string([날짜]),1,2) & Substring(string([날짜]),6,2) & Substring(string([날짜]),9,2))

날짜함수

날짜 계산은 아래와 같습니다.

오늘날짜 : today()
날짜차이계산 : DateDiff("day",[앞날짜],[뒷날짜])
요일계산 : DayOfWeek([날짜]) : 숫자로 표기됨, 0: 일/1:월/2:화/3:수/4:목/5:금/6:토
주차계산 : Week([날짜]), 1주 : 연도의 최초 일요일까지 / 2주~ : 월요일~일요일

OVER함수

다른 function들은 다른 프로그램들에서 비슷한 용법으로 사용해봤거나, 설명이 비교적 이해하기 쉽지만, OVER함수는 생소하기도 하고 바로 이해하기도 어렵습니다. 하지만 알고 있다면 활용도가 높은 함수이기 때문에 알아두면 많은 곳에 써먹을 수 있을 것 입니다.
OVER함수는 집계함수라고도 합니다. 보통의 function들은 동일 row에서 다른 column들을 참조해서 작동하는 경우가 많습니다. 하지만 OVER함수는 전체 data table 또는 다른 row에서 다른 column들을 참조해서 작동합니다. 아래는 OVER 함수의 사용 방법입니다.

집계함수([col_name]) OVER(옵션[col_name])
AVG([VALUE]) OVER([DATE])

over function 작동 원리

over함수는 하나의 row를 참조하는 것이 아닌 전체 data table을 참조하며, over 뒤에 나오는 column을 정렬하고 묶어서 사용할 수 있게 해줍니다.

over function 사용 방법

over함수의 옵션들은 over 뒤 column 정렬 및 사용 할 때의 방법을 옵션들로 제공합니다. 예를들어 Allprevious는 해당 column 정렬 후 해당 내용보다 앞선 내용의 값들만 사용하겠다는 의미입니다. 예제로 치면 해당 row의 DATE보다 이전 시간대의 DATE 전체를 사용하겠다는 것이고, 주로 누적함수를 만들때 사용합니다. intersection의 경우 여러개의 column에 대해 over함수를 적용하고 싶을 때 사용합니다.

calculated column의 expression

function은 여러가지 함수를 의미하고 실제 동작은 expression에 표시된 내용으로 column을 생성합니다. 따라서 function을 포함한 모든 수식이나 변수들은 expression에 표현할 수 있어야 합니다. 표현 방법은 excel의 수식을 적용하는 것과 동일합니다. function을 정해진 문법에 따라 작성하고 필요한 수식과 절차를 적절히 작성하며 됩니다. 이 expression 표현은 calculated column 뿐만 아니라 spotfire 내의 다양한 기능에서 쓸 수 있습니다.

- expression 작성 참고

[column 이름]으로 column을 표현 할 수 있습니다. 이 값은 하나의 row에서 해당 column에 대한 값을 대변합니다. function(함수내용) 으로 함수를 적용할 수 있습니다. 이 값은 data table 전체에서 구하는 값을 대변합니다.
위의 두 사실이 중요한 것은 정확한 계산 결과를 얻기 위함입니다. 아래의 그림은 전체에 대한 비율을 구하는 expression 입니다. 각 row의 비율을 구하기 위해서 분자에는 column을 사용하여 각 row 값이 사용되었고, 분모에는 function을 사용하여 data table 전체 합을 구했습니다.

expression에서의 column과 function의 계산 차이

위에서 설명한 calculated column의 범위는 expression이 표현할 수 있는 범위가 되고, 그것은 spotfire에서 제공하는 function과 다항식과 절차 등으로 제한됩니다. 이외의 복잡한 알고리즘이나 표현 방법은 분석을 통해 좀 더 확장된 내용들을 다룰 수 있습니다.

특정 조건을 만족하는 표현식 작성

over 함수는 특정 column을 대상으로 동일한 조건끼리 모아 계산하였습니다. 표현식과 if 함수를 잘 활용하면 over 함수와 비슷하게 특정 column의 특정 조건만 모아 계산 할 수 있습니다. 이 표현식은 자주 사용되기 때문에 알아 두시면 좋을 것 같습니다.

avg(if([조건column]조건식,[대상column]))

이 표현식은 조건 column의 조건식을 만족하는 row에서만 대상 column의 값을 계산합니다.