[SQL] '연속 출석체크일 수' 조회 쿼리
안녕하세요. 박세계입니다.
'연속' 데이터 조회를 위한 쿼리문 작성은 생각만큼 쉽지 않습니다. 커서DECLARE CURSOR 사용 시 다른 프로그래밍 언어와 동일하게 억지로 원하는 목표를 달성 할 수 있지만 권장하지 않습니다. 커서 존재 자체를 머릿속에서 지우는게 좋습니다. SQL 세계에서는 모든 문제를 집합적 사고로 푸는 습관을 들여야 합니다. 그러라고 만든 언어이기 때문입니다.
'연속 XX일 출석체크' 시 혜택을 주는 기능을 회사 서비스에 추가하고 있는데, 이때 사용한 '연속 출석체크일 수' 조회를 위한 쿼리문을 상세설명과 함께 공유합니다. 주제에 집중하기 위해 테이블 구조를 간략화 했습니다.
이하 '출석체크'와 '로그인'을 동일한 의미로 간주하고 혼용하겠습니다.
Microsoft SQL Server에서 작업했습니다.
1. 일일 로그인 테이블 생성
테스트를 위해 #으로 시작하는 세션 임시 테이블을 생성합니다. 프로그램이 종료되거나 쿼리창을 닫을 시 자동 삭제됩니다.
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)
2. 일일 로그인 테이블에 샘플 데이터 입력
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')
3. 날짜 도우미 임시 테이블 생성
조회 기준이 될 연속된 날짜를 위한 테이블입니다.
동일하게 #으로 시작하는 세션 임시 테이블로 생성했지만, 필요한 경우가 생각보다 많으니 일반 테이블로 생성 후 두고두고 써먹어도 좋습니다. 여기서는 간결한 설명을 위해 날짜에 해당하는 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
4. 데이터 확인
'연속 출석체크'가 뜻하는 바를 잠시 음미하겠습니다. 일단 '오늘'이 반드시 포함되야 합니다. 오늘부터 과거일을 하루씩 빠짐없이 체크할 때, 미출석이 확인되는 날짜 바로 이전일까지가 바로 '연속 출석체크일 집합'입니다.
우리에겐 연속된 날짜가 포함 된 #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 | 서비스 오픈일 |
5. 최종 조회 쿼리
데이터 확인을 위한 위 결과 집합은 필요 없으며, 최종적으로 '연속 출석체크일 수'에 해당하는 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 |
6. 모든 쿼리문 통합
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
감사합니다.
음... 정말 어렵군요.
마치 모든 것들이 암호처럼 보이네요 ㅠㅠ
맨날 c 코딩만 하는 일인입니다. ^.^;;
저는 연속 로그인체크는 아니지만 영업일관리테이블을 비슷한 방식으로 사용하고 있습니다. ㅎㅎ
다 이해한 척 하고 싶다.....;;
ㅎㅎ 오늘도 비개발자 학살용 포스팅을 올리셨군요.
좋은 하루 되세요
안녕하세요. 박세계님
마이크로소프트웨어 오세용 기자입니다.
마이크로소프트웨어는 1983년 창간된 현 대한민국 소프트웨어 전문지로, 연 4회 계간지로 발행되고 있습니다. 지난 1월 약인공지능을 주제로 출판됐습니다.
마이크로소프트웨어 기술기고 관련해 이야기를 나눠보고 싶습니다. 제 이메일로 메일 한 번 주실 수 있을까요?
[email protected]
감사합니다.
MSSQL을 잘 사용하지 않아서 #이 임시세션테이블이라는건 첨알았네요.^^
또한 이글을 보고 나니, 스티밋 출석체크 이벤트 용 봇을 만들어보는것도 재밌겠다는 생각이 드는구요.^^
개발자분들은 이렇게 어려운 외계어로 뭔가 척척 만들어내시니 솔직히 부럽습니다.
저는 비싼 컴퓨터로 지뢰터뜨리기나 하고 있으니^^
세계님 즐거운 일요일 보내세요~!
저야 평범한 개발자에 불과하지만 그래도 적성에는 맞는거 같아 잼난거 같습니다 ^^
이번 주말에는 딱히 어디 나가지도 않고 가족과 함께 푹 쉬었네요. 손시님도 일요일 마무리 잘 하시고 좋은 한 주 보내세요 ^^
오호 감사합니다 ㅎㅎ 그런데 kr-dev는 개발자 클럽만 쓸수있는건가요??
감사합니다 ㅎㅎ kr-dev는 아무나 쓸 수 있습니다. 단지 개발자 클럽이 아니면 @korea 계정이 추가로 보팅하지 않습니다. 저도 개발자 클럽에 속해있지 않으며 이전부터 그냥 쭈욱 쓰고 있었습니다 ㅎㅎ
ㅎㅎ kr-travel 은 너무 겹쳐서 kr-travelclub으로 이름 바꿨는데...혹시나 dev는 중복인것 같아 궁금했는데 답변 감사합니다