1. 날짜 함수란?

날짜 함수란 날짜 타입 ( DATE, DATETIME ,TIMESTAMP…) 의 데이터를 다룰때 사용하는 함수이다.

날짜의 출력 형식을 지정하거나 날짜를 계산할 때 사용한다.

• Case 1 ) 날짜 출력 형식 :
  
  • YYYY-MM-DD 형식으로 값이 입력되어 있는 날짜 필드를 YYYYMMDD 형식으로 바꾸고 싶다.
    
  • ( ex. 2024-08-02 -> 20240802 )
    
• Case 2 ) 날짜 계산 :
  
  • 현재 날짜 기준, n일전 데이터에서 전일자 데이터까지 조회하고 싶다.
    
  • ( ex. 2024년 8월 28일 기준 , 35일전인 데이터에서 전일자 데이터까지 조회하고 싶다 )
    

날짜 출력 형식을 지정할때는 어떤 함수를 쓰는지, 날짜를 계산할때는 어떤 함수를 쓰는지 아래에서 알아보자.

2. 날짜 함수 소개

앞서 소개했듯이, 날짜 함수는 크게 두가지로 분류할 수 있다.
( 내가 만든 분류이다. 공식적인 분류는 아니다. )

• 출력 형식 지정 함수: 데이터 타입이나 출력 형태를 지정하는 함수
  
  • 대표 함수 : FORMAT_DATE , PARSE_DATE , DATE_TRUNC , EXTRACT
    
• 계산 함수 : 날짜를 계산하는 함수
  
  • 대표 함수 : DATE_ADD , DATE_SUB , DATE_DIFF
    

각 대표 함수에 대한 설명은 아래 표에서 살펴 보자.

1) 출력 형식 지정 함수

2) 계산 함수

특히 출력 형식 지정함수에서 FORMAT_DATE과 PARSE_DATE을 헷갈려하는 경우가 많다.
( ex. 20240825 -> 2024-08-25 )

둘은 서로 역의 관계라고 생각하면 된다.

FORMAT_DATE은 date를 string으로, PARSE_DATE은 string을 date로 바꿔서 출력한다.

특히 PARSE_DATE은 날짜를 나타내는, ‘%Y-%m-%d 형식이 아닌 string값을 날짜 타입으로 변경할때 유용하다.
( ex. 20240825 -> 2024-08-25 )

DATE( ) 사용해서 날짜 타입으로 변경 많이 하는데 %Y-%m-%d 형식이 아닌 string 값은 DATE( ) 함수 사용시 에러가 난다.
이 경우 PARSE_DATE( ) 를 사용해야한다. substr과 concat함수를 이용해서 조합하는 방법도 있지만
구문이 길어지기 때문에 PARSE_DATE( ) 을 사용하는 것을 권장한다.

DB를 일별로 수집한다고 했을때 테이블의 _table_suffix가 ‘%Y%m%d’ 형식으로 되어있는 경우가 많다.
불러온 _table_suffix를 그대로 날짜 필드로 넣고 싶을때 PARSE_DATE를 쓰면 된다.
( 예시: PARSE_DATE(‘%Y%m%d’,_table_suffix) AS yyyymmdd )

3. Appendix

1) 다루고자 하는 날짜 타입 (Date, Datetime ,Timestamp )에 따라 함수명을 변경할 수 있다.

위에서 소개한 함수들은 DATE 타입을 기준으로 함수명을 써놓았는데 예시처럼 다 변경이 가능하다
ㄴ ex) FORMAT_DATE -> FORMAT_DATETIME , FORMAT_TIMESTAMP
ㄴ ex) DATE_ADD -> DATETIME_ADD , TIMESTAMP_ADD

참고로 DATE와 DATETIME, TIMESTAMP는 표현할 수 있는 날짜 범위에 차이가 있다.

• DATE : 날짜를 년, 월 , 일 까지 저장하고 있는 날짜 타입
  
• DATETIME : 날짜를 년 , 월 , 일 , 시 , 분 , 초 ,초 미만으로 저장하고 있는 날짜 타입
  
• TIMESTAMP : 날짜를 마이크로초단위 ( 초 이하 소숫점 6자리 ) 로 정밀하게 저장하고 있는 날짜 타입
  

TIMESTAMP는 빅쿼리상에서는 타임존이 같이 출력된다. ( ex: 2024-01-01 12:30:12.000000 UTC )
빅쿼리 자체적으로는 마이크로초로 날짜값을 저장하고있는데 ( 고정된 초기 시점 이후 경과된 마이크로초 수 )
표기시에 변환해서 출력해주면서 타임존까지 같이 나타내주는것이다.

DATETIME과의 차이는 타임존을 포함하고 있다 정도로 보면 될것같다.

2) 날짜 출력 형식은 아래 표현을 참고하면 된다.

1. 문제 발생

유저 id , 마켓 , 국가 , 고객 유형, 매출이 각각 필드로 저장되어 있는 데이터가 있다.

유저들의 매출 총액을 마켓 , 국가 , 고객 타입별로 구하려고 하며 소계와 총계도 같이 보려고 한다.

즉 , 각 필드를 기준으로 CUBE 계산을 하려고 한다.
( CUBE 계산 : 결합 가능한 모든 값에 대하여 다차원 집계를 생성하는 것을 말함)

결과적으로 ,아래 이미지와 같이 집계 결과를 출력하면 된다.
( (ALL)로 적혀있는 부분이 해당 필드의 전체 경우 라고 보면 된다.)

ROLLUP 구문을 이용하고 싶었으나, ROLLUP은 인수 순서에 따라
소계 집계가 계층별로 이루어져 모든 조합(필드)에 대한 소계치를 볼 수 없었다.

아래 이미지가 rollup 구문으로 출력한 결과다.
( [null]로 적혀있는 부분이 해당 필드의 전체 경우라고 보면 된다.)

-- rollup 구문 
SELECT
 market
 , country
 , customer_type
 , SUM(won) AS krw
FROM sample_table
WHERE 1=1
GROUP BY ROLLUP (market, country , customer_type)

즉, 위의 rollup 구문은 인수인 market,country,customer 순으로 CUBE 계산을 하기 때문에
아래 이미지처럼 노란 형광펜으로 칠해진 부분을 구할수 없다.

어떻게 쿼리 구문을 짜야 모든 필드를 기준으로 소계를 구할수 있을까?

2. 문제 해결

UNNEST를 이용하여 배열을 만들고 ,그 배열을 소계를 구하고자 하는 필드에 덮어씌워
GROUP BY 해줌으로써 이를 구현할 수 있다.

말이 복잡해보일 수 있는데 , 정리하자면 아래 구문을 이용한다고 보면 된다.

/* UNNEST([필드1,'전체']) AS 필드 1
   GROUP BY 필드 1 
*/
	
SELECT
  필드1
  , SUM(won) AS krw
FROM sample_table
, UNNEST([필드1,'전체']) AS 필드1 -- 기존 필드와 UNNEST문에서 배열을 풀면서 지정한 필드명은 동일해야함.
WHERE 1=1
GROUP BY 필드1

소계 계산할 칼럼을 UNNEST로 묶어준다.
‘전체’를 값으로 넣은 배열을 만들어주고 UNNEST 해줘서 풀어버린후 집계해준다.
( 전체 대신 “ALL”로 전체치를 표시하고 싶으면 ‘전체’ 대신 “ALL” 을 넣으면 된다)

이때 주의할점은 UNNEST문에서 배열을 풀어줄때 별칭으로 지정하는 필드명과
SELECT문에서 사용할 필드명이 동일해야 된다는 것이다.

지정하는 필드명이 동일해야 GROUP BY로 집계하면서 필드가 덮어씌어지는 효과가 난다.
만약 UNNEST문의 필드명을 SELECT문의 필드명과 다르게 지정한다면 해당 이름으로 된 필드가 하나 더 생기게 된다.
이렇게 될 경우 헷갈릴 가능성이 크므로 위의 예시처럼 필드명을 동일하게 지정하는 것이 좋다.

문제 상황에 적용해보면 아래 구문이 될것이다.

-- unnest로 cube하기 
# (Google BIGQUERY)

SELECT
 market
 , country
 , customer_type
 , SUM(won) AS user_cnt
FROM  blog.post1_unnest_cube 
,UNNEST([market,'(ALL)']) AS market
,UNNEST([country,'(ALL)']) AS country
,UNNEST([customer_type,'(ALL)']) AS customer_type 
GROUP BY market,country,customer_type
;

-- unnest로 cube하기 
# (PostgreSQL)

SELECT
 market2 AS market
 , country2 AS country
 , customer_type2 AS customer_type
 , SUM(won) AS krw
FROM blog.post1_unnest_cube 
, UNNEST(ARRAY[market,'(ALL)'] ) AS market2
, UNNEST(ARRAY[country,'(ALL)']) AS country2
, UNNEST(ARRAY[customer_type,'(ALL)']) AS customer_type2
GROUP BY market2,country2,customer_type2
;

집계 결과가 원하는대로 나온 것을 알 수 있다.

✋🏾 Google BigQuery을 이용하여 쓰인 포스팅입니다.

1. NULL과 관련된 조건식 _ coalesce, nullif, ifnull 란?

BigQuery 조건식 중에 null을 이용하는 조건식은 대표적으로 3가지가 있다.

• COALESCE(expr1,expr2,[expr3…….)
  
  • 인수로 들어간 표현식(expr) 중에 null이 아닌 첫번째 표현식 값을 반환한다.
    
  • 값이 반환되면 나머지 표현식은 평가되지 않는다.
    
  • 입력 표현식에는 모든 데이터 타입이 입력 가능하다.
    
    
• NULLIF(expr1, expr2)
  
  • expr1과 expr2가 값이 같으면, null을 반환하고 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2는 같은 상위 데이터 타입에 속해야하며, 비교 가능해야 한다.
    
    
• IFNULL(expr1, expr2)
  
  • expr1이 null이면 expr2를 반환한다. 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2에는 모든 데이터 타입이 입력될 수 있다.
    
  • COALESCE(expr1,expr2)와 같은 표현이다.
    
    

2. 예시

예시 데이터셋은 아래와 같다.

-- 예시 데이터 셋 제작 코드 
WITH ex_table AS 
( 
    SELECT 30 AS col1, 37 AS col2 , 55 AS col3
  UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
  UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
  UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3 
)

SELECT 
  co11
  ,co12
  ,col3
FROM ex_table
;

위 예시 데이터에 null과 관련된 조건식( coalesce,nullif,ifnull)을 넣고 결과를 비교해보자
출력 결과는 테이블 맨 우측에 위치한 result 칼럼에서 확인할 수 있다.

• COALESCE 구문 예시
  

/*
coalesce 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3
)

SELECT
  *,
  coalesce(col1,col2,col3) as result_coalesce
FROM ex_table;

• NULLIF 구문 예시
  
  

/*
nullif 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 2 AS col2 , null AS col3 
)

SELECT
  col1,
  col2,
 nullif(col1,col2) as result_nullif -- nullif는 expr 총 2개만 비교 가능
FROM ex_table

• IFNULL 구문 예시
  
  

/*
ifnull 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3
 )

SELECT
  col1,
  col2,
 ifnull(col1,col2) as result_ifnull
FROM ex_table

3. 정리

이처럼 null과 관련된 조건식 3가지를 알아보았다.

나는 특히 실무에서 IFNULL 구문을 주로 활용한다.
(거의 IFNULL만 사용한다)

데이터 추출시 LEFT JOIN을 이용하여 테이블끼리 매칭하고 집계하는 경우가 많은데,
이 때 매칭이 안되는 집계 결과(null)를 0으로 바꿔줄때 많이 사용한다.

ex) IFNULL(t2.won,0) AS won

위의 케이스처럼 IFNULL을 LEFT JOIN과 같이 활용하면 매우 유용하니 한번 활용해봐도 좋을 것 같다.

(tableau) Spotify Music Characteristic Dashboard

1. 대시보드 소개

이 대시보드는 2010년부터 2019년까지 스포티파이의
인기곡 정보를 모아놓은 데이터를 태블로로 제작한 것이다.
(캐글의 Top Spotify songs from 2010-2019-By Year 데이터 활용)

“인기곡의 특성을 한눈에 볼 수 없을까?”라는 주제를 잡고 대시보드를 제작했다.

• Duration : 음악의 길이
  
• Tempo (bpm) : 음악의 빠르기
  
• Energy : 음악의 에너지
  
• Liveness : 음악의 생동감
  
• Valence: 음악이 주는 긍정적인 느낌
  
  

이 5가지 특성을 보았으며 Duration과 Tempo는 음악의 형식과 관련된 특성으로
Energy, Liveness, Valence는 음악이 주는 느낌과 관련된 특성으로 분류하였다.

이 분류에 따라 특성의 표현 색상을 구분했다. Duration과 Tempo는 회색으로
Energy,Liveness,Valence는 주황색으로 표현했다.

또한 각 특성값은 0~1의 값으로 정규화해준후 100을 곱해 100점 척도로 바꾸었다.
(MinMax Scaler 활용)

특성값을 100점 척도로 바꾼 이유는 2가지이다.

• 깂의 범위가 특성에 따라 차이가 커, 한 차트에 나타내기 쉽지 않기 때문이다.
  
• 100점 척도는 보는사람이 인식하기 쉽다.
  
  

위와 같은 작업을 거쳐 대시보드를 완성했다.

대시보드의 세부내용은 아래에서 살펴보자

2. 대시보드 세부 설명

각 인기곡의 5가지 특성 값을 보드를 이용해 나타냈다.

아래에서 언급할 DJ차트는 각 특성의 레이블이 차트에 표현되지 않는다.
따라서 이 보드를 통해 정확한 특성 값을 확인할 수 있도록 했다.

5가지 특성의 연도별 평균값을 이은 라인차트이다.
이 차트를 통해 각 특성의 평균값 추이를 볼 수 있다.

또한 DJ 차트를 통해 파악한 곡의 특성을 이 차트와 비교해
그 해의 유행과 유사한지를 알 수 있다.

전체 년도의 특성값을 보는 차트이기 때문에 이 대시보드에서 사용되는
필터(년도, 타이틀 기준)은 작동되지 않게 해놓았다.

동작무시를 사용하면 이 차트는 필터가 적용되지 않는다.

예를 들어 2019년의 Ed sheeran의 ‘I Don’t Care’는
2019년도의 유행하는 템포보다는 느리고 곡의 길이는 긴 것을 알 수 있다.

또한 이 곡이 주는 생동감과 에너지는 2019년의 유행보다는 적지만
긍정적인 느낌을 더 준다는 것을 파악할 수 있다.

년도를 선택하는 옵션칸이다.

이 옵션칸에서 선택한 년도 기준으로 곡 리스트 테이블이 세팅된다.
(필터로 작용)

이 대시보드의 핵심인 DJ차트이다.
DJ들이 사용하는 이퀄라이저의 형상을 본따 만든 차트이다.

곡 리스트 테이블에서 특정곡을 선택하면 그 곡의 특성값이 이 차트에 나타난다.

곡 리스트 테이블이다.
타이틀 - 아티스트 - 장르 - 인기도 순으로 구성되어있다.
인기도 값을 기준으로 내림차순 정렬해놨다.

위에서부터 아래로 인기있는 곡이라고 보면 될 것같다.

3. 피드백 및 느낀점

여태까지 작업했던 태블로 대시보드 중 가장 만족스럽게 나온 대시보드이다.
디자인과 구성이 깔끔하게 나와 뿌듯한 작업물이다.

이 대시보드의 포맷은 다른 데이터에도 충분히 활용될 수 있을 것 같다.
추후 다른 데이터에도 이 디자인과 구성을 활용해 대시보드를 제작해봐야겠다.

1. 대시보드 소개

이 대시보드는 뉴욕의 2019년 AirBnB 데이터를 활용해 제작한 것이다.
“내가 뉴욕에 간다면 어떤 숙소를 선택하는 것이 좋을까?”라는 주제를 잡고 대시보드를 제작했다.

내가 여행을 할 때, 숙소를 정하는 기준은 크게 2가지이다.

• 가격 ( 숙소에 너무 큰 돈을 쓰고싶지는 않다 )
  
• 리뷰 수 ( 많은 리뷰 수와 좋은 평가는 인증된 숙소라는 느낌을 준다 )
  

이 대시보드는 위 두가지 기준 중 가격을 중점삼았고 태블로(Tableau)를 이용해 작업하였다.

2. 대시보드 세부 설명

숙소당 평균 숙박비용을 지역별로 비교한 차트이다.
많은 이들이 숙소를 선택할 때 숙박비용을 중요하게 생각한다.

이 차트는 평균 숙박비용을 지역별로 나누어 비교하였고,
이를 통해 숙소를 선택할 때 지역을 참고할 수 있도록 대시보드에 넣었다.

나의 경우, 맨해튼의 숙소들의 평균 숙박비용이 타 지역에 비해
약 2배이상 높기 때문에, 맨해튼의 숙소는 일단은 제외하는 방향으로 갈 것이다.

이 차트는 앞서 살펴본 숙소의 지역별 평균 숙박비용 차트를 보완하기 위해 넣어둔 차트이다.
지역별로 숙박비용의 분포를 살펴볼 수 있다.

이 차트를 보니, 맨해튼에도 충분히 낮은 가격대의 숙소가 많다는 점을 알 수 있었으며,
오히려 적당한 가격대(2k~4k)의 숙소가 타 지역보다 많다는 것을 알 수 있다.

따라서 나는 앞서 정한 방향과 달리 최종적으로 맨해튼 지역을 숙소를 찾을 지역으로 선택할 것이다.

종합적으로 앞서 차트에서 살펴본 가격대와 함께 다른 옵션(방 유형, 최소 숙박일수, 리뷰 수)도 선택할 수 있게 해 , 해당되는 숙소를 맵에 표시되게 하였다.

이를 통해, 내가 방문하고자 하는 숙소를 고를 수 있다.