문제

회원 정보 테이블과 로그인 정보 테이블이 있다.
회원의 경우 탈퇴를 하게 되면 회원 정보가 남아있지 않게 된다.
UV는 UNIQUE VISIT의 약자로 방문 횟수가 아닌 방문 회원의 수, 즉 중복을 제거한 수치를 뜻한다.
2023년 3월을 조회 대상으로 한다.

SQL 쿼리문을 입력시 다음과 같이 출력되어야 한다.

DROP DATABASE IF EXISTS QUIZE;
CREATE DATABASE QUIZE;
USE QUIZE;

-- 회원 테이블 
CREATE TABLE MEMBERS(
    MEMBER_NO INT, -- 회원번호
    SEX VARCHAR(1), -- M:남자 F:여자
    NAME VARCHAR(10) -- 회원명
);

-- 로그인 정보
CREATE TABLE LOGIN_LOG(
    MEMBER_NO INT, -- 회원번호
   LOGIN_DT DATETIME, -- 로그인 일시 
   LOGOUT_DT DATETIME  -- 로그 아웃 일시
);


INSERT INTO MEMBERS VALUES(1,'M','하하');
INSERT INTO MEMBERS VALUES(3,'M','박명수');
INSERT INTO MEMBERS VALUES(4,'M','유재석');
INSERT INTO MEMBERS VALUES(5,'F','이미나');



INSERT INTO LOGIN_LOG VALUES(1,'2023-03-02 01:55:46','2023-03-02 03:55:46');
INSERT INTO LOGIN_LOG VALUES(1,'2023-03-02 23:55:46','2023-03-03 03:55:46');
INSERT INTO LOGIN_LOG VALUES(1,'2023-03-03 04:55:46','2023-03-03 05:55:46');
INSERT INTO LOGIN_LOG VALUES(1,'2023-03-03 11:55:46','2023-03-03 13:55:46');

INSERT INTO LOGIN_LOG VALUES(2,'2023-03-01 01:55:46','2023-03-01 03:55:46');
INSERT INTO LOGIN_LOG VALUES(2,'2023-03-02 23:55:46','2023-03-03 03:55:46');
INSERT INTO LOGIN_LOG VALUES(2,'2023-03-03 04:55:46','2023-03-03 05:55:46');
INSERT INTO LOGIN_LOG VALUES(2,'2023-03-03 11:55:46','2023-03-03 13:55:46');

INSERT INTO LOGIN_LOG VALUES(3,'2023-03-01 01:55:46','2023-03-01 03:55:46');
INSERT INTO LOGIN_LOG VALUES(3,'2023-03-02 23:55:46','2023-03-03 03:55:46');
INSERT INTO LOGIN_LOG VALUES(3,'2023-03-04 01:55:46','2023-03-04 03:55:46');
INSERT INTO LOGIN_LOG VALUES(3,'2023-03-04 11:55:46','2023-03-06 13:55:46');

INSERT INTO LOGIN_LOG VALUES(4,'2023-03-01 01:55:46','2023-03-01 03:55:46');
INSERT INTO LOGIN_LOG VALUES(4,'2023-03-02 23:55:46','2023-03-03 03:55:46');
INSERT INTO LOGIN_LOG VALUES(4,'2023-03-04 01:55:46','2023-03-04 03:55:46');
INSERT INTO LOGIN_LOG VALUES(4,'2023-03-04 11:55:46','2023-03-06 13:55:46');

INSERT INTO LOGIN_LOG VALUES(5,'2023-03-01 01:55:46','2023-03-01 03:55:46');
INSERT INTO LOGIN_LOG VALUES(5,'2023-03-02 23:55:46','2023-03-03 03:55:46');
INSERT INTO LOGIN_LOG VALUES(5,'2023-03-04 01:55:46','2023-03-04 03:55:46');
INSERT INTO LOGIN_LOG VALUES(5,'2023-03-04 11:55:46','2023-03-06 13:55:46');

문제 해결

1. 탈퇴 회원은 누락되긴 했지만 로그인 테이블로부터 회원번호만큼은 알 수 있고 탈퇴 회원의 로그인 이력도 조회대상이기 때문에 회원 테이블에 추가되어야 한다.

2. 회원마다 언제 로그인되어 있었는지 구해야 한다. 이때 성별 별로 그룹화한다.

3. 3월 1일부터 3월 31일까지 2번을 통해 구한 정보를 활용해 하루하루 정보를 기입해나간다.

4. UNION을 통해 마지막 합계를 구한 것을 합쳐주면 된다.

이를 MYSQL 쿼리문으로 나타내어주면 아래와 같다.

SET @NUM := 0;
SET @DAYS := 0;

SELECT 
	DATE_ADD('2023-03-01', INTERVAL DDIFF - 1 DAY) AS '날짜', 
    SUM(CASE WHEN SSS.S = 'M' THEN 1 ELSE 0 END) AS '남자회원 방문수',
	SUM(CASE WHEN SSS.S = 'F' THEN 1 ELSE 0 END) AS '여자회원 방문수',
	SUM(CASE WHEN SSS.S IS NULL AND SSS.M IS NOT NULL THEN 1 ELSE 0 END) AS '탈퇴회원 방문수'
FROM (SELECT @DAYS := @DAYS + 1 AS DDIFF FROM LOGIN_LOG, MEMBERS) AS NN
	LEFT JOIN (SELECT 
					DATE_ADD('2023-03-01', INTERVAL DIFF - 1 DAY) AS D, 
                    L.MEMBER_NO AS M, 
                    L.SEX AS S
				FROM (SELECT @NUM := @NUM + 1 AS DIFF FROM LOGIN_LOG, MEMBERS) AS N
					JOIN (SELECT 
							A.MEMBER_NO, 
                            B.SEX, 
                            A.LOGIN_DT, 
                            A.LOGOUT_DT 
						FROM LOGIN_LOG AS A 
							LEFT JOIN MEMBERS AS B 
                            ON A.MEMBER_NO = B.MEMBER_NO
						) AS L
					ON DATE_ADD('2023-03-01', INTERVAL N.DIFF - 1 DAY) 
                    	BETWEEN DATE(DATE_FORMAT(L.LOGIN_DT, '%Y-%m-%d')) 
                        AND DATE(DATE_FORMAT(L.LOGOUT_DT, '%Y-%m-%d'))
				WHERE DIFF <= DATEDIFF('2023-04-01', '2023-03-01')
				GROUP BY D, M, S) AS SSS
	ON DATE_ADD('2023-03-01', INTERVAL DDIFF - 1 DAY) = SSS.D
WHERE DDIFF <= DATEDIFF('2023-04-01', '2023-03-01')
GROUP BY DDIFF

UNION

SELECT 
	'총 방문한 회원 수', 
    SUM(CASE WHEN H.SEX = 'M' THEN 1 ELSE 0 END), 
    SUM(CASE WHEN H.SEX = 'F' THEN 1 ELSE 0 END), 
    SUM(CASE WHEN H.SEX IS NULL AND H.MEMBER_NO IS NOT NULL THEN 1 ELSE 0 END)
FROM (SELECT 
		A.MEMBER_NO, 
        B.SEX 
	FROM LOGIN_LOG AS A 
		LEFT JOIN MEMBERS AS B 
        ON A.MEMBER_NO = B.MEMBER_NO
	WHERE A.LOGOUT_DT BETWEEN '2023-03-01' AND '2023-04-01'
	GROUP BY A.MEMBER_NO, B.SEX) AS H;

해당 예시에 대한 결과는 다음과 같다.

차근차근 생각해보면 그렇게 어렵지는 않았지만 초보자에겐 꽤나 복잡했던 문제였다.