[MSSQL] WHILE을 이용한 LOOP 생성 - 구간별 데이터 한번에 추출하는 반복문 만들기

서비스 지표가 시간이 지남에 따라 변화한 양상을 확인하기 위해, 똑같은 내용의 SQL문을 일자 정보만 계속 바꿔서 반복 추출하곤 했는데요. 이번에 WHILE을 이용해서 실행 한 번에 데이터를 추출하는 방법을 찾게 되어 이를 공유하고자 합니다. 이해를 위해 solvessql에서 제공하는 '서울시 공공자전거 (따릉이) 이용현황' 데이터 예제를 가지고 WHILE을 어떻게 사용하는 지에 대한 예시를 포스팅하고자 합니다.

( 데이터 출처 : https://solvesql.com/ 의 rental_history 테이블)

따릉이는 대여 후 4시간이 경과했는데도 반납이 되지 않을 경우, 도난으로 간주합니다. 서비스 측면에서 4시간 이후에 반납된 자전거의 개수와 비율은 꾸준히 줄어들고 있는지 관리해야 하는 지표라고 가정해봅시다.

 

WHILE

While의 개념 파악하기

DECLARE @NUM INT 
SET @NUM= 1;
WHILE @NUM < 5 -- 조건문
  PRINT '현재 값은'+ CONVER(varchar(3),@NUM) 
  SET @NUM = @NUM + 1
END

 

1. 데이터 설명

주간 4시간 경과 반납비율을 알고자 합니다. Rental_history에서 반납시각(return_at) 이 대여 시각(rent_at)기준으로 4시간이 경과되었을 경우, 분실되었거나 반납이 정상적으로 이루어지지 않은 경우라고 가정하겠습니다.

따릉이 대여/반납 내역 테이블

 

2. While 문 사용

21월 7월 15일부터 21월 11일 15일까지 기간별로 늦게 반납 건수를 보고자 합니다. 최초의 SET는 21월 7월 15일로 설정되었고, 그 다음 회차에서는 7일이 경과된 7월 22일로 설정되었습니다. 기간의 시작일자가 11월 15일을 넘기 직전까지 구간별 정보를 TEMP라는 임시 테이블에 INSERT 처리합니다.

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP
CREATE TABLE #TEMP -- 임시테이블을 생성해서, 기간정보와 늦게 반납된 건수를 쌓고자 합니다.
( FD VARCHAR(8),
  TD VARCHAR(8),
  CNT INT,
)

DECLARE @ST_DT  Datetime = CAST('20170915'AS DateTime);
DECLARE @ED_DT  Datetime = CAST('20171115'AS DateTime);

WHILE @ST_DT < @ED_DT -- 2달간의 기록을 보고자 해서, 조건은 11월 15일 이전으로 설정하였습니다.
	BEGIN
        INSERT INTO #TEMP
        SELECT CONVERT(VARCHAR(8),@ST_DT,112), 
        CONVERT(VARCHAR(8),DATEADD (DAY, 6, @ST_DT),112),
        COUNT(bike_id)
        FROM rental_history
        WHERE return_at NOT BETWEEN rent_at AND DATEADD(HOUR, 4, rent_at)
        AND rent_at BETWEEN @ST_DT AND DATEADD (DAY, 6, @ST_DT)
        SET @ST_DT = DATEADD (DAY, 7, @ST_DT);
    END