‘맞춤 쿼리’ 또는 ‘커스텀 쿼리’라고도 하는데 이 기능은 빅쿼리에서 실행할 SQL문을 루커 스튜디오에서 작성한 뒤에, 빅쿼리에서 실행해서 그 결과를 받아와서 활용하는 기능입니다. 루커 스튜디오에서 기간 컨트롤러나 필터 기능을 통해서 파라미터 값을 조작하면 원하는 데이터를 동적으로 불러올 수 있습니다.
예를 들어, 아래의 차트는 지난 포스트에서 생성하였던 차트입니다. 표시된 기간 컨트롤러를 조작하면 해당 기간이 쿼리에 전달이 되고, 해당하는 기간에 대한 매출, 전년도 매출, 1개월 전 매출, 2개월 전 매출, 3개월 전 매출을 각각 일자별로 보여주는 화면을 구현했습니다.
일자별 매출 비교
여기서 차트의 헤더는 전년도, 1개월 전, 2개월 전, 3개월 전 이라고 되어있지만 각 기간이 언제인지 명확히 알려면 기간 컨트롤러에서 선택된 날짜에서 각 기간을 머릿속으로 계산해야 하지요. 물론 그것이 어렵지 않지만 대시보드는 직관적이어야 하고, 가독성이 중요합니다.
따라서 오늘은 지난번에 작성했던 쿼리를 약간 수정하여 아래 차트를 완성해보겠습니다. 주요 내용은 다음과 같습니다.
(1) 맞춤 쿼리(커스텀 쿼리)를 사용하여 거래처의 기간별 매출 비교하기
(2) 맞춤 쿼리(커스텀 쿼리)를 사용하여 헤더 커스터마이징하기
아래 이미지를 보면 거래처별 매출을 기간별로 비교하고 각 열의 헤더에는 비교하는 기간의 날짜가 표기되어있지요. 지난번에 작성했던 쿼리를 약간 수정하여 거래처별 매출을 보여주고 각 열의 헤더에 날짜를 표기하기 위해서 새로운 맞춤 쿼리를 추가할 것입니다.
거래처별 매출 비교
💡이 포스트에서는 복잡한 SQL쿼리를 다루므로 SQL문법이 익숙하지 않다면 지난 포스트를 먼저 읽어보시길 추천합니다.
아래의 도식은 매개변수를 전달할 때 매개변수가 작동하는 원리를 보여줍니다. 시작 일자와 종료 일자 매개변수를 기준으로 1년 전, 1개월 전, 2개월 전, 3개월 전 기간을 거래처별로 연산합니다.
각 기간에 해당하는 거래처별 매출을 CTE별로 나누어 SELECT 한 뒤에, 각각의 CTE를 통합하면 원하는 차트를 구현할 수 있습니다.
💡CTE란? CTE(Common Table Expression)는 SQL에서 임시로 생성하는 테이블입니다. 이것은 쿼리의 가독성을 높이고 재사용성을 향상시키는 역할을 합니다. WITH문을 사용하여 정의합니다. 예시에서는 각 기간 별로 SELECT하는 구문이 괄호로 묶여있는데, 이 단위들을 CTE라고 합니다.
1-1. 데이터베이스
사용할 데이터는 지난 포스트에서 사용했던 것과 동일하게 ‘LS_salesData’ 테이블을 사용합니다.
기간 컨트롤러와 맞춤 쿼리(커스텀 쿼리)를 추가하는 방법은 지난 포스트에서 소개하였으므로, 쿼리 입력창에서 기간 매개변수를 설정하는 방법부터 소개하겠습니다.
쿼리 입력 창에서 매개변수를 사용하려면 별도 설정이 필요합니다. 앞서 대시보드에 있던 기간 컨트롤러에서 선택한 시작 일자와 종료 일자를 매개변수로 사용하려면 맞춤 쿼리 입력 창 하단의 ‘기간 매개변수 사용 설정’ 버튼을 체크해야 합니다. 시작 일자는 @DS_START_DATE, 종료 일자는 @DS_END_DATE로 정의되어 있습니다.
기본 설정을 마쳤으니 쿼리문을 작성해볼까요?
1-3. 쿼리문 작성
쿼리의 주요 목표는 다음과 같습니다.
선택 기간의 거래처별 매출 데이터 불러오기
선택 기간의 1년 전, 1개월 전, 2개월 전, 3개월 전 거래처별 매출 불러오기
비교 분석할 수 있도록 불러온 데이터들을 하나의 테이블로 통합하기
아래는 완성된 전체 코드입니다. 일자별 매출을 보여주던 쿼리와 비슷하지만 거래처명인 ‘shopName’을 함께 SELECT 하고 있습니다. 각각의 CTE는 최종적으로 비교하는 기간의 거래처별 매출합계를 계산할 것이기 때문에 쿼리의 마지막 구문에 GROUP BY 구문을 사용하여 일자와 거래처명으로 그룹화 하였습니다.
💡GROUP BY 구문에 대해 더 자세히 알아보려면 구글에서 제공하는 아래의 공식 레퍼런스를 확인해주세요.
WHERE salesDate BETWEEN PARSE_DATE('%Y%m%d',@DS_START_DATE)ANDPARSE_DATE('%Y%m%d',@DS_END_DATE)
GROUP BY salesDate,shopName
)
⚠️ 여기서 시작 일자(@DS_START_DATE)와 종료 일자(@DS_END_DATE) 매개변수를 DATE타입으로 파싱하였습니다. 쿼리 입력창 하단에 표기되어 있듯이, 시작일과 종료일 식별자의 SQL유형은 STRING 형태로, 날짜를 ‘YYYYMMDD’ 형식으로 취급하고 있습니다. 참조하는 테이블에서의 날짜는 DATE타입으로 저장되어 있으므로 제공하는 파라미터의 데이터 타입도 DATE타입으로 동일하게 맞춰주기 위해 PARSE_DATE() 함수를 사용하였습니다.
WHERE salesDate BETWEEN DATE_SUB(PARSE_DATE('%Y%m%d',@DS_START_DATE),INTERVAL3MONTH)ANDDATE_SUB(PARSE_DATE('%Y%m%d',@DS_END_DATE),INTERVAL3MONTH)
GROUP BY salesDate,shopName
)
각각의 CTE를 통합하기 위해서는 JOIN 구문을 사용해야 하는데, 무엇을 기준으로 JOIN 할 것인지 조건이 필요하지요. 기준 일자를 선택 기간 기준으로 맞춰주기 위해 각각의 CTE에서 DATE_ADD() 함수와 DATE_SUB()함수를 사용하여 기준 일자인 salesDate를 일치시켰으니 이제 CTE들을 통합하기만 하면 됩니다.
이 쿼리는 생성하였던 선택 기간, 1년 전, 1개월 전, 2개월 전, 3개월 전 매출을 한 번에 비교할 수 있도록 FULL OUTER JOIN 구문을 이용해 데이터를 정리하는 과정입니다.
이해를 돕기 위해 선택 기간, 작년 동기간, 1개월 전 동기간 CTE를 통합하는 원리를 아래와 같이 도식화하였습니다. 앞서 DATE_ADD()와 DATE_SUB()를 사용하여 salesDate를 일치시켰기 때문에 3개 CTE의 기준 일자의 일자가 모두 선택 기간과 동일합니다. 매출은 각각 선택기간/작년동기간/1개월전으로 다른 값을 가집니다.
이렇게 salesDate를 일치시킨 뒤 일치시킨 기준 일자와 거래처명을 키 값으로 사용하여 각각의 CTE를 통합하는 것입니다.
유의할 점은 FULL OUTER JOIN 구문과 COALESCE() 구문을 사용하였다는 것입니다.
FULL OUTER JOIN 은 두 테이블을 조인할 때, 양쪽 테이블의 모든 행을 포함합니다. 즉, 한쪽 테이블에만 있는 행도 모두 가져오며, 매칭되지 않는 행은NULL로 표시됩니다.
COALESCE() 는 여러 개의 인수를 받아 순차적으로 검토하여NULL이 아닌 값을 반환합니다.
💡FULL OUTER JOIN 구문과 COALESCE() 구문에 대해서는 아래의 포스트에서 더 자세히 설명하고 있으므로 참조해보시길 바랍니다.
여기서 FULL OUTER JOIN 구문의 조인 조건에 COALESCE() 를 사용한 이유는 무엇일까요? 이 쿼리에서는 데이터를 날짜와 거래처명을 기준으로 통합하는데, 각 CTE에 해당 날짜와 거래처명이 모두 있는 것이 보장되지 않기 때문입니다.
어떤 매출 데이터는 current_sales CTE에만 있고 다른 테이블에는 없을 수 있습니다.
반대로 과거 매출 데이터만 있고 현재 데이터가 없을 수도 있습니다.
이 문제들을 해결하기 위해 두 구문을 사용한 것이지요.
예를 들어 2025년 1월 1일부터 2025년 1월 31일 까지 기간을 선택했을 때, 2025년 1월 15일에 매출 데이터가 없더라도, 1년 전인 2024년 1월 15일에 매출 데이터가 있다면 그 데이터도 전년도 매출 목록에 불러와야 합니다. 아래와 같이 COALESCE() 구문을 사용하여 선택 기간 매출(o.slaesDate)이 NULL이라면 1년 전 매출(l.salesDate)을 확인하고 그것도 NULL이라면 1개월 전 매출, … 순차적으로 확인하여 값이 존재하는 날짜를 사용합니다.
쿼리를 모두 작성하였으니 이제 데이터를 추가해보겠습니다. 우측 하단의 ‘추가’ 버튼을 클릭합니다.
💡각각의 컬럼명은 아래의 데이터 소스 수정 화면에서 변경할 수 있습니다.
데이터 소스를 추가한 뒤 컬럼명을 수정하고 아래와 같이 차트를 생성하였습니다. 이제 기간 컨트롤러의 기간을 조작하면 맞춤 쿼리를 통해서 매개변수로 전달하고, 조건문에서 해당 기간을 연산하여 각 기간에 대한 매출을 불러오게 됩니다.
거래처의 기간별 매출 비교 차트가 완성되었습니다. 이제 차트의 헤더를 커스터마이징 해볼까요?
2. 맞춤 쿼리(커스텀 쿼리)를 사용하여 헤더 커스터마이징하기
아래의 차트는 각 기간의 구분이 시각적으로 명확하며, 비교하는 기간에 대해서도 명확히 보여주고 있지요. 이렇게 시각적인 요소를 적절히 추가하면 대시보드의 가독성은 훨씬 높아집니다.
이제 그 방법을 소개하겠습니다.
2-1. 디자인 입히기
헤더의 각 열을 색상으로 구분하고 열 이름을 지정하는 것은 루커 스튜디오 수정 화면 상단에 있는 텍스트 박스와 도형 도구를 사용합니다.
아래와 같이 차트의 헤더에 색상을 넣은 사각형 도형을 삽입한 뒤,
그 위에 텍스트 도구를 사용하여 열 이름을 지정합니다.
그리고 그 위에 날짜가 표기되는 ‘표’를 삽입하는 것입니다. 아래의 이미지에 있는 숫자 표시는 몇개의 표가 삽입되어있는지를 보여줍니다. 기간 컨트롤러에서 선택한 날짜를 기준으로 선택 기간의 시작 일자와 마지막 일자, 작년 동기간의 시작 일자와 마지막 일자…. 이렇게 총 10개의 시작일과 종료일을 표기하고 있지요.
기간 컨트롤러가 어느 날짜를 선택했는지에 따라 ‘표’의 날짜도 동적으로 변경되어야 하기 때문에 맞춤 쿼리(커스텀 쿼리)를 사용해야겠죠? 이제 차트의 헤더에 날짜를 표기하기 위한 라벨용 맞춤 쿼리를 작성해보겠습니다.
💡도형 도구와 ‘표’ 차트를 응용하는 방법은 지난 포스트에서 다루었으므로 지난 포스트를 읽어보지 않았다면 아래의 포스트를 먼저 읽어보시기 바랍니다.
(1) SelectedPeriod CTE에서 매개변수로 받은 값을 파싱합니다. 매개변수를 문자열 형태로 받고 있기 때문에 SQL에서 날짜로 인식할 수 있도록 날짜 형식으로 파싱해주어야 합니다. 쿼리에서는 매개변수를 날짜 형식으로 파싱 후 AS 구문을 사용하여 start_date, end_date 로 정의하였습니다.
(2) SelectedPeriod CTE에서 시작일(start_date)와 종료일(end_date)을 기준으로 날짜를 계산하여 SELECT 합니다. 특정 날짜에서 일정한 기간만큼 ‘빼기’를 수행하는DATE_SUB() 함수를 사용하여 각 날짜를 계산하였습니다.
2-2. 표 삽입
맞춤 쿼리 데이터 소스를 추가한 뒤 표를 추가합니다. 표를 디자인 요소로 사용하는 경우의 설정은 지난 포스트에서 다루었으므로 간단히 짚고 넘어가겠습니다.
표 추가 후 스타일 옵션에서 ‘헤더 보기’ 와 ‘행 번호’를 체크 해제합니다.
표 차트의 설정 옵션에서 행 표시 옵션을 아래와 같이 ‘상위 행만 표시’ 로 설정합니다.
표 사이즈를 최소화한 뒤, 측정 기준에 각 날짜를 삽입하여 적절한 위치에 배치합니다.
이렇게 모든 열에 날짜를 삽입하면 아래와 같은 차트가 완성됩니다. 이제 기간 컨트롤러의 날짜를 변경하면 모든 헤더의 날짜도 동적으로 변경됩니다.
오늘은 거래처별 매출의 기간별 비교를 위한 맞춤 쿼리와 헤더를 커스터마이징 하기 위한 맞춤 쿼리를 살펴보았습니다. 복잡한 쿼리를 다룰줄 아는 것 만큼 중요한 것은 얼마나 직관적으로 대시보드를 보여주느냐 하는 것이지요. 맞춤 쿼리와 디자인 요소를 적절히 활용하면 데이터의 전달력과 가독성을 동시에 끌어올릴 수 있습니다. 이제 여러분의 대시보드에도 오늘 살펴본 내용들을 직접 적용해보시길 바랍니다!
다음 포스트는 매개변수 기능을 응용하여 드롭다운을 통해 측정 기준을 변경하는 방법을 소개하겠습니다.
