안녕하세요. 박세계입니다.
'연속' 데이터 조회를 위한 쿼리문 작성은 생각만큼 쉽지 않습니다. 커서DECLARE CURSOR 사용 시 다른 프로그래밍 언어와 동일하게 억지로 원하는 목표를 달성 할 수 있지만 권장하지 않습니다. 커서 존재 자체를 머릿속에서 지우는게 좋습니다. SQL 세계에서는 모든 문제를 집합적 사고로 푸는 습관을 들여야 합니다. 그러라고 만든 언어이기 때문입니다.
'연속 XX일 출석체크' 시 혜택을 주는 기능을 회사 서비스에 추가하고 있는데, 이때 사용한 '연속 출석체크일 수' 조회를 위한 쿼리문을 상세설명과 함께 공유합니다. 주제에 집중하기 위해 테이블 구조를 간략화 했습니다.
이하 '출석체크'와 '로그인'을 동일한 의미로 간주하고 혼용하겠습니다.
Microsoft SQL Server에서 작업했습니다.
테스트를 위해 #으로 시작하는 세션 임시 테이블을 생성합니다. 프로그램이 종료되거나 쿼리창을 닫을 시 자동 삭제됩니다.
IF OBJECT_ID('tempdb..#UserDailyLogin') IS NOT NULL
DROP TABLE #UserDailyLogin
CREATE TABLE #UserDailyLogin (
UserId INT NOT NULL,
LoginDate DATE NOT NULL
)
ALTER TABLE #UserDailyLogin ADD PRIMARY KEY (UserId, LoginDate)
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-10')
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-11')
-- No login data on 2018-03-12
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-13')
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-14')
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-15')
조회 기준이 될 연속된 날짜를 위한 테이블입니다.
동일하게 #으로 시작하는 세션 임시 테이블로 생성했지만, 필요한 경우가 생각보다 많으니 일반 테이블로 생성 후 두고두고 써먹어도 좋습니다. 여기서는 간결한 설명을 위해 날짜에 해당하는 Date 컬럼만 생성했지만, '년/월/일/주/요일' 등을 추가하면 더욱 좋습니다.
예약어는 피하는게 좋지만
Date라는 컬럼명보다 좋은 이름을 도저히 찾을 수 없어 그냥 썼습니다. 시스템이 거부하지 않고 최선의 이름이라 판단되면 실무에서도 그냥 쓰는 편입니다. 어제의 비 예약어가 내일의 예약어가 되는 경우도 많기에, 예약어를 절대 쓰지 않으려고 너무 집착할 필요는 없습니다.
IF OBJECT_ID('tempdb..#DateHelper') IS NOT NULL
DROP TABLE #DateHelper
CREATE TABLE #DateHelper (
Date DATE NOT NULL PRIMARY KEY
)
-- 서비스 오픈일을 시작일로 설정 (이전일이 필요한 경우는 거의 없음)
DECLARE @Date DATE = '2018-03-10'
-- 대충 10년치 날짜 미리 입력 (10년치 데이터도 수천건에 불과)
WHILE(@Date <= '2030-12-31')
BEGIN
INSERT INTO #DateHelper (Date) VALUES (@Date)
SET @Date = DATEADD(DAY, 1, @Date)
END
'연속 출석체크'가 뜻하는 바를 잠시 음미하겠습니다. 일단 '오늘'이 반드시 포함되야 합니다. 오늘부터 과거일을 하루씩 빠짐없이 체크할 때, 미출석이 확인되는 날짜 바로 이전일까지가 바로 '연속 출석체크일 집합'입니다.
우리에겐 연속된 날짜가 포함 된 #DateHelper 테이블이 있습니다. 이를 기준삼아 OUTER JOIN을 해보겠습니다. 한명뿐인 UserId 1의 데이터입니다.
DECLARE @UserId int = 1
DECLARE @Today date = CAST(SYSUTCDATETIME() as date)
SELECT b.Date, a.LoginDate
FROM #DateHelper b
LEFT OUTER JOIN #UserDailyLogin a
ON a.LoginDate = b.Date AND a.UserId = @UserId
WHERE b.Date <= @Today
ORDER BY b.Date DESC
결과는 다음과 같습니다. 3월 12일에 로그인을 하지 않았으니, 3월 13일~15일에 해당하는 3일간이 '연속 출석체크일'에 해당합니다. 12일 이전 데이터는 필요 없습니다.
| Date | LoginDate | 비고 |
|---|---|---|
| 2018-03-15 | 2018-03-15 | 오늘 |
| 2018-03-14 | 2018-03-14 | - |
| 2018-03-13 | 2018-03-13 | - |
| 2018-03-12 | NULL | 로그인 안함 |
| 2018-03-11 | 2018-03-11 | - |
| 2018-03-10 | 2018-03-10 | 서비스 오픈일 |
데이터 확인을 위한 위 결과 집합은 필요 없으며, 최종적으로 '연속 출석체크일 수'에 해당하는 3만 알면 됩니다. 최종 조회 쿼리입니다.
SELECT DATEDIFF(DAY, MAX(b.Date), @Today) as ConsecutiveLoginDays
FROM #DateHelper b
LEFT OUTER JOIN #UserDailyLogin a
ON a.LoginDate = b.Date AND a.UserId = @UserId
WHERE b.Date <= @Today
AND a.LoginDate IS NULL
결과는 다음과 같습니다.
| ConsecutiveLoginDays |
|---|
| 3 |
1~5 항목에 나왔던 모든 쿼리문을 친절하게 통합했습니다.
IF OBJECT_ID('tempdb..#UserDailyLogin') IS NOT NULL
DROP TABLE #UserDailyLogin
CREATE TABLE #UserDailyLogin (
UserId INT NOT NULL,
LoginDate DATE NOT NULL
)
ALTER TABLE #UserDailyLogin ADD PRIMARY KEY(UserId, LoginDate)
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-10')
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-11')
-- No login data on 2018-03-12
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-13')
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-14')
INSERT INTO #UserDailyLogin (UserId, LoginDate) VALUES (1, '2018-03-15')
IF OBJECT_ID('tempdb..#DateHelper') IS NOT NULL
DROP TABLE #DateHelper
CREATE TABLE #DateHelper (
Date DATE NOT NULL PRIMARY KEY
)
-- 서비스 오픈일을 시작일로 설정 (이전일이 필요한 경우는 거의 없음)
DECLARE @Date DATE = '2018-03-10'
-- 대충 10년치 날짜 미리 입력 (10년치 데이터도 수천건에 불과)
WHILE(@Date <= '2030-12-31')
BEGIN
INSERT INTO #DateHelper (Date) VALUES (@Date)
SET @Date = DATEADD(DAY, 1, @Date)
END
DECLARE @UserId int = 1
DECLARE @Today date = CAST(SYSUTCDATETIME() as date)
SELECT b.Date, a.LoginDate
FROM #DateHelper b
LEFT OUTER JOIN #UserDailyLogin a
ON a.LoginDate = b.Date AND a.UserId = @UserId
WHERE b.Date <= @Today
ORDER BY b.Date DESC
SELECT DATEDIFF(DAY, MAX(b.Date), @Today) as ConsecutiveLoginDays
FROM #DateHelper b
LEFT OUTER JOIN #UserDailyLogin a
ON a.LoginDate = b.Date AND a.UserId = @UserId
WHERE b.Date <= @Today
AND a.LoginDate IS NULL
감사합니다.