[data.world] query template 과 형변환 처리

 

SQL tutorial excersize data

Data was genererated using Synthea, a synthetic patient generator that models the medical history of synthetic patients. (...)De-identified real data still presents a challenge in the medical field because there are peopel who excel at re-identification of these data.

예시로 사용할 dataset 은 data.world 의 사용법을 익히라고 제공된 연습 데이터입니다. 하지만 완전 더미 데이터가 아니고 실제 미국의 합병증 환자에 대한 의료기록입니다. 물론 의료기록은 개인정보로, 개인이 식별되지 않는 정보 전문용어로 비식별정보 de-identified data 로 저장되어 있습니다. 

- data.world 환경에서 SQL 질의하는 튜토리얼: How to use the SQL tutorial on data.world.
- What is data.world: https://help.data.world/hc/en-us/articles/360042163494-Why-data-world-​

 

query template 소개

나는 그 중에서도 new template 기능을 더 살펴보았다. 쿼리를 할 때 많은 조건값을 통해 원하는 일부 데이터를 추출한다. new template 기능은 사용자가 이 조건값을 직접 입력하도록 지원하는 기능이다. 

공식 가이드: https://help.data.world/hc/en-us/articles/360008637414-Using-query-templates

Using query templates

공식 가이드는 있지만, 자주 user input 으로 받는 숫자형과 날짜형이 나오는 예시 쿼리로 query template 기능을 사용해보자. 

---

1. 기본 Query

환자 정보와 환자의 알러지 증상 정보를 통해 각 환자가 가진 알러지 갯수를 집계해보려고 한다. 나는 2000 년 밀레니얼 세대에 태어난 아이들이 알러지 갯수가 더 많을 거라고 가정하고 해당 결과를 보려고 쿼리를 작성했다. 

- 대상: 2000 ~ 2009 년에 출생한, 알러지 갯수가 5 개 이상인 환자

 

2. Query 에서 사용자 입력값 지정

이 대상에서 user input 을 받을 값을 ? 로 표시해 보았다.

- 대상: ? ~ ? 년에 출생한, 알러지 갯수가 ? 개 이상인 환자

 

그리고 쿼리창 우측상단에 [new template] 을 클릭하자. 아래와 같이 가이드가 뜬다. [Add a sample statement] 클릭

 

3. 숫자형부터 user input 처리

DECLARE ?cnt AS INTEGER = 5

 

비교적 간단한 숫자형부터 변수 선언을 해보았다. 위 구문을 단어 단위로 해석하면 다음과 같다.  

- DECLARE: 변수 선언
- ?cnt : ? 와 변수명, 동적변수를 표시할 때 앞에 ? 를 붙인다.
- AS INTEGER: 해당 파라미터의 사전 정의된 데이터타입 datatype
- 5: default 값 value initialization

그리고 쿼리에 내가 작성한 값 5 위치에 동적변수를 지칭하는 ?cnt 를 입력해준다. ?cnt 로 입력받은 값을 여기에 넣고 실행시키라는 뜻이다.

 

4. 날짜 처리

조금 더 까다로운 날짜 처리이다. 흔히 할 수 있는 실수와 트러블슈팅 과정을 서술하였다. 참고로 timezone 이슈는 해결된 상태라고 가정했다. 일반적으로 서비스의 날짜 데이터는 a. timezone 이 고정되어있거나 b. timezone 을 포함한 값 (2000-01-01T00:00+09:00) 이거나 c. GMT 기준 date 값  (2000-01-01T00:00, GMT 기준으로 해석)을 가지고 있다.   

숫자를 하듯이 선언문을 작성하고, 선언문의 초기값에 쿼리에 있었던 값을 옮겨적었다. 그리고 에러!

Default value of parameter '?bd_start' has incorrect type: 'string' detected, 'date' declared: (line 2, column 1)

파라미터의 Default value 가 잘못되었다고 한다. date 로 선언되어있는데 (넣기로 약속했는데) string 이 발견되었다고 한다! 그래서 datatype 불일치로 에러가 떨어졌다. 

 

선언한 데이터타입이나 초기값의 데이터타입이 일치해야하므로, 둘 중 하나는 맞춰야 한다. 

이 때, 유저는 string 만 입력 가능하므로 데이터타입을 String 으로 선언한다. 사실 SQL 문에서는 상호 형변환 가능한 것에 대해 SQL 이 우리를 대신해서 처리해주었다. 이번에도 user input 을 받아오기만 하면 대신 처리해줄 것이다. 그리고 성공.

 

그런데 생각해보니 유저는 2000-01-01 부터 2009-12-31 로 입력할 것이다. 컴퓨터를 위해 2010-01-01 로 입력해주지 않을 것이다. 이부분은 우리가 처리해주어야 한다. 그래서 출생일자 기간 마지막일자를 받고 day + 1 을 처리해 준 후 미만 논리연산을 진행한다. 

받아온 user input 에 바로 date 관련 함수를 썼더니 결과값이 0 개이다. 기대결과와 다르다. 그리고 노랑경고문구가 보인다. 

Parameter 1 of date_add: type 'string' detected, one of ('dateTime', 'date', 'time', 'dateTimeStamp', 'gYear', 'gYearMonth') expected: line 9, column 63 

date 함수이니 dateTime, date 등 날짜와 관련된 값만 인자로 받을 수 있는데, string 이 보였다고 경고한다. 그리고 [DATE_ADD 함수 레퍼런스] 를 읽어보라고 링크를 제공한다. 함수 레퍼런스에서 인자의 데이터타입을 다시 확인한 후, 쿼리를 수정해보자.

user input 을 string 으로 받았으나, 우리는 date 로 활용해야한다. 그러므로 형변환 CAST 를 해준다. [CAST 함수 레퍼런스]

 

5. Query template 모드 프리뷰

[Preview query template] <--> [Edit query]

마치 어드민 기능과 같이 변수마다 값을 입력받고 쿼리를 실행시킬 수 있다. 작성자만 수정모드로 진입할 수 있다. 이 프로젝트를 보는 뷰어는 query template 을 통해서 결과를 조회할 수 있다. 

---

글이 user input 받는 기능에서 데이터타입의 중요성으로 끝났다. input 과 처리과정에서 달라질 수 있기 때문.

데이터를 다루다보면 숫자와 날짜와 같은 데이터타입에 충분히 민감해야한다. 잘못된 데이터타입에 대한 처리는 잘못된 결과를 낳는다. (차라리 에러면 다행이지, 잘못된 결과는 못 찾을 수도 있다.) 형변환은 실수하기 좋은 요소라서 쿼리하는 좋은 습관을 되새길 겸, 그 부분을 조금 더 자세히 써봤다.