오라클 연속된 값 찾기

이번에 개발을 하면서

- 1시간 안에 본인증 문자 발송이 5회 이상 안되도록

- 본인증이 완료가 되면 5회 카운터 초기화

이 2가지 조건을 만족하게 열심히 검색하고 쿼리를 만들어봤어요. 찾아보니 "5일 연속 지각" 이런 게시글이 많이 보여서 참고를 했어요.

01011112222	발송완료	2024/02/06 18:01:01
01011112222	인증완료	2024/02/06 18:00:01
01011112222	발송완료	2024/02/06 17:59:01
01011112222	발송완료	2024/02/06 17:58:01
01011112222	발송완료	2024/02/06 17:57:01
01011112222	발송완료	2024/02/06 17:56:01

샘플 데이터가 있어요

발송완료	2024/02/06 18:01:01
인증완료	2024/02/06 18:00:01
발송완료	2024/02/06 17:59:01
발송완료	2024/02/06 17:58:01
발송완료	2024/02/06 17:57:01
발송완료	2024/02/06 17:56:01

5번째에 인증을 완료했으므로 발송 횟수를 초기화

발송완료	2024/02/06 18:01:01	N
인증완료	2024/02/06 18:00:01	Y
발송완료	2024/02/06 17:59:01	N
발송완료	2024/02/06 17:58:01	N
발송완료	2024/02/06 17:57:01	N
발송완료	2024/02/06 17:56:01	N

6번째에는 발송이되야 하고

발송완료	2024/02/06 18:01:01	N
발송완료	2024/02/06 18:00:01	N
발송완료	2024/02/06 17:59:01	N
발송완료	2024/02/06 17:58:01	N
발송완료	2024/02/06 17:57:01	N
발송완료	2024/02/06 17:56:01	N

6번째에 발송이 안되어야 해요.

그럼 어떻게 이걸 체크를 했는지 쿼리를 작성해 볼게요.

 

SELECT
	CASE WHEN LISTAGG(YN) WITHIN GROUP (ORDER BY 등록시간) LIKE '%YYYYY%' THEN 'Y' ELSE 'N' END
FROM (
		SELECT 핸드폰번호,결과,등록시간
      		DECODE(결과,'발송완료','Y','N') AS YN
        FROM TABLE
        WHERE 등록시간 > SYSDATE - 1 / 24 --지금부터 1시간이내
        AND 핸드폰번호 = '01011112222'
        ORDER BY 등록시간 DESC
)

해당방법으로 Y가 연속으로 몇 개 있냐 이걸로 5번 발송요청 4번 발송요청 이렇게 조절을 할 수 있어요.

 

해당쿼리로

- 1시간 안에 본인증 문자 발송이 5회 이상 안되도록

- 본인증이 완료가 되면 5회 카운터 초기화

두 가지 조건을 만족하여 개발을 했습니다. 처음에 요건을 들었을 때 JAVA에서 풀어야 하나 고민을 했지만 역시 검색으로 힌트를 얻어 쿼리로 해결을 했어요.