일자별 시간별 10분 단위 집계하기 / CASE WHEN 구문, SUBSTR , CONCAT 활용

테이블 구성이 아래와 같다고 해보자

(예시를 위해서 간단하게 작성했을 뿐 실제 로그에는 더 많은 정보가 담겨 있습니다)

* Table name : sample1

time	userid
2020-08-22 10:01:30.33	14321
2020-08-22 10:15:56.11	12341
2020-08-22 10:17:21.33	43563
2020-08-22 10:20:32.34	23456

 

원하는 결과물 >

dt	hhmm	au
2020-08-22	10:00	1
2020-08-22	10:10	2
2020-08-22	10:20	1

 

 

---

 

 

일자별 시간별 10분 단위 집계 방법 

작업하면서 유용하다고 생각했던 SQL 조건절입니다.
개인에 따라 추구하는 성능, 함수 등의 차이로 서로 옳다고 생각하는 방법이 다를 수 있습니다.
작업자의 데이터 구성에 따라 아래 쿼리의 숫자는 변경되어야 할 수도 있습니다.

SELECT
	substr(time,1,10) as dt,
    case when substr(time,15,2)<'10' then concat(substr(time,12,2),':00')
    	 when substr(time,15,2) >='10' and substr(time,15,2)<'20' then concat(substr(time,12,2),':10'
         when substr(time,15,2) >='20' and substr(time,15,2)<'30' then concat(substr(time,12,2),':20'
         when substr(time,15,2) >='30' and substr(time,15,2)<'40' then concat(substr(time,12,2),':30'
         when substr(time,15,2) >='40' and substr(time,15,2)<'50' then concat(substr(time,12,2),':40'
         when substr(time,15,2) >='50' and substr(time,15,2)<'60' then concat(substr(time,12,2),':50'
     END as hhmm,
     count(distinct userid) as au
FROM sameple 1
group by substr(time,1,10) ,
    case when substr(time,15,2)<'10' then concat(substr(time,12,2),':00')
    	 when substr(time,15,2) >='10' and substr(time,15,2)<'20' then concat(substr(time,12,2),':10'
         when substr(time,15,2) >='20' and substr(time,15,2)<'30' then concat(substr(time,12,2),':20'
         when substr(time,15,2) >='30' and substr(time,15,2)<'40' then concat(substr(time,12,2),':30'
         when substr(time,15,2) >='40' and substr(time,15,2)<'50' then concat(substr(time,12,2),':40'
         when substr(time,15,2) >='50' and substr(time,15,2)<'60' then concat(substr(time,12,2),':50'
     END
order by dt , hhmm
limit 500
;
         

해당 쿼리 실행 시 대용량의 로그 데이터의 일자별 시간별 10분 단위 AU 집계 시 매우 유용할 수 있다.
쿼리 내용이 이해가 가지 않을 경우 아래 구문 구문 설명을 참고해주세요

 

select
	substr(time,1,10) as dt
from

일자별 추출을 위해서 가져온 값으로 time의 표현식을 확인해야 한다.
time은 yyyy-mm-dd hh:mm:ss.ss 로 값이 구성되어 있으며 여기서 yyyy-mm-dd 만 가져오기 위해서는
1번째 문자열 부터 시작해서 총 10개의 문자열을 가져오면 된다.

의미 : time 의 1번째 부터 총 10개의 문자열을 출력.

 

SELECT
    case when substr(time,15,2)<'10' then concat(substr(time,12,2),':00')
    	 when substr(time,15,2) >='10' and substr(time,15,2)<'20' then concat(substr(time,12,2),':10'
         when substr(time,15,2) >='20' and substr(time,15,2)<'30' then concat(substr(time,12,2),':20'
         when substr(time,15,2) >='30' and substr(time,15,2)<'40' then concat(substr(time,12,2),':30'
         when substr(time,15,2) >='40' and substr(time,15,2)<'50' then concat(substr(time,12,2),':40'
         when substr(time,15,2) >='50' and substr(time,15,2)<'60' then concat(substr(time,12,2),':50'
     END as hhmm
FROM sameple 1

목적 : 시간별 10분 단위로 구성을 바꿔주려는 것으로
아래와 같이 예시를 들 수 있다.
10:22 > 10:20
12:56 > 12:50
(hh:'단위값')

case when 조건 절에서 우선 분 단위 값만 추출해서 10분 단위로 구분짓고 여기에 해당 value의 hh 값을 가져와 쪼갤 분단위를 붙여주는 작업이다!

의미 : 
substr(time,15,2) = 분 mm만 추출
substr(time,12,2) = 시간 hh 만 추출
분이 10보다 작으면 time의 hh 와 :00 이라는 문자열을 합쳐서 출력,
분이 10보다 크거나 같고 20보다 작으면 time의 hh 와 :10 이라는 문자열을 합쳐서 출력.
.....
분이 50보다 크거나 같고 60보다 작으면 time의 hh 와 :50 이라는 문자열을 합쳐서 출력.



이렇게 하면 별도로 hour값을 추출할 필요도 없이
하루치 혹은 몇개월 치의 시간대별 10분 단위 지표를 좀 더 간단하게 뽑아낼 수 있다!

 

---

 

 

CASE WHEN 구문 / 

SELECT
  CASE WHEN 조건1 then 변환값1
       WHEN 조건2 then 변환값2
       ELSE 그외 변환값3 END AS <희망컬럼명>
FROM sample1     

조건1에 해당하면 변환값1을 조건2에 해당하면 변환값2를 조건1, 조건2 그 어떤 것도 해당 하지 않으면 변환값3을 출력.

 

 

SUBSTR / (문자열 추출, 문자열 자르기)

SELECT 
	SUBSTR(컬럼명, 추출 시작 위치 , 추출하려는 문자열 수)
FROM sample1

예/ substr( time, 10,2) >> time컬럼의 10번째 문자열부터 2개까지만 추출해서 출력.

 

 

CONCAT / (문자열 합치기)

SELECT 
	CONCAT(문자열A , 문자열B)
FROM sample1

순서대로 문자열A 와 문자열B를 하나로 합쳐서 출력