데이터베이스 질문과 답변
오라클
날짜별로 누적 회원수를 구하고 싶습니다.
단 조건은
1. 2023.01.01 ~ 2023.03.30 까지 일자별로 누적회원수
2. 탈퇴하지 않은 고객 중 2023.06.01 이후 가입자 제외
3. 해당일자의 유효한회원수라서 2/3일이면 2/.3 탈퇴된 고객도 포함해야함
원하는 값
| 일자 | 가입자수 | 총고객수 |
| 20230101 | 100 | 1100 (230101이전 가입자수+ 1/1가입자수) |
| 20230102 | 50 | 1150 |
| 20230103 | 200 | 1350 |
| 20230104 | 100 | 1450 |
| 20230105 | 300 | 1750 |
테이블 정보(고객테이블)
* 사용여부 = Y 면 활동하는 고객
* 삭제 여부 = Y 면 탈퇴한 고객( NULL 값이거나 'N' 이면 활동고객)
* 삭제일자가 IS NULL이면 활동하는 고객(값이 있으면 해당날짜 탈퇴고객임)
| 고객번호 | 성명 | 가입일자 | 사용여부 | 삭제여부 | 삭제일자 |
| 000001 | 삭제고객 | 20010303 | N | Y | 20230203 |
| 000002 | 홍길동1 | 20230101 | Y | ||
| 000003 | 홍길동2 | 20230215 | Y | ||
| 000004 | 홍길동3 | 20020502 | Y | N | |
| 000005 | 홍길동4 | 20031215 | Y | N | |
| 000006 | 홍길동5 | 20040610 | Y | ||
| 000007 | 홍길동6 | 20040204 | Y | ||
| 000008 | 삭제고객 | 20031218 | N | Y | 20150804 |
| 000009 | 홍길동8 | 20010224 | Y | ||
| 000010 | 홍길동9 | 20050526 | Y | ||
| 000011 | 홍길동10 | 20040716 | Y | ||
| 000012 | 홍길동11 | 20040622 | N | N | |
| 000013 | 홍길동12 | 20160407 | N | N |
1. 사용여부가 N 인데 활동하는 것 처럼 보이는 마지막 2개 행은 뭔가요?
2. 사용여부, 삭제여부가 필요한 걸까요? 가입일과 삭제일자만 보면 되지 않을런지?
1. 마지막 2개 행은 잘못입력한거 같습니다..잘못된데이터입니다
2. 혹시나 삭제고객인데 사용여부나 삭제여부 NULL값일 수도있을거 같아서 데이터 누락된거 없이 누적유효고객을 찾고 싶어서입니다.
EX)
WHERE NVL(삭제여부, 'N') <> 'Y'
AND NVL(사용여부, 'Y') <> 'N'
AND 삭제일자 IS NULL
이런식으로 생각을했습니다..
삭제일자가 없이는 구현이 불가능한 로직입니다.
반대로 말하면, 삭제일자만 있으면 구현이 가능하다는 말이기도 하구요.
현재 상태만 알면 된다면? 여부항목으로 가능하지만.
누적 개념으로 가려면 여부항목이 아닌 일자항목이 필요합니다.
여부 항목도 두개나 있을 필요가 있을지 의문입니다.(사용여부나 삭제여부 둘 중 하나만으로도 충분합니다.)
WITH usr_t AS
(
SELECT '000001' usr_no, '삭제고객' usr_nm, '20010303' reg_dt, 'N' use_yn, 'Y' del_yn, '20230203' del_dt FROM dual
UNION ALL SELECT '000002', '홍길동1' , '20230101', 'Y', '' , '' FROM dual
UNION ALL SELECT '000003', '홍길동2' , '20230215', 'Y', '' , '' FROM dual
UNION ALL SELECT '000004', '홍길동3' , '20020502', 'Y', 'N', '' FROM dual
UNION ALL SELECT '000005', '홍길동4' , '20031215', 'Y', 'N', '' FROM dual
UNION ALL SELECT '000006', '홍길동5' , '20040610', 'Y', '' , '' FROM dual
UNION ALL SELECT '000007', '홍길동6' , '20040204', 'Y', '' , '' FROM dual
UNION ALL SELECT '000008', '삭제고객', '20031218', 'N', 'Y', '20150804' FROM dual
UNION ALL SELECT '000009', '홍길동8' , '20010224', 'Y', '' , '' FROM dual
UNION ALL SELECT '000010', '홍길동9' , '20050526', 'Y', '' , '' FROM dual
UNION ALL SELECT '000011', '홍길동10', '20040716', 'Y', '' , '' FROM dual
UNION ALL SELECT '000012', '홍길동11', '20040622', 'N', 'N', '' FROM dual
UNION ALL SELECT '000013', '홍길동12', '20160407', 'N', 'N', '' FROM dual
)
SELECT *
FROM (SELECT a.dt
, COUNT(DECODE(b.gb, 1, 1)) cnt_reg -- 당일 가입자수
, COUNT(DECODE(b.gb, 2, 1)) cnt_del -- 당일 탈퇴자수(하루전 탈퇴 기준)
, SUM(COUNT(DECODE(b.gb, 1, 1))) OVER(ORDER BY a.dt) sum_reg -- 누적 가입자수
, SUM(COUNT(DECODE(b.gb, 2, 1))) OVER(ORDER BY a.dt) sum_del -- 누적 탈퇴자수
, SUM(COUNT(DECODE(b.gb, 1, 1))) OVER(ORDER BY a.dt)
- SUM(COUNT(DECODE(b.gb, 2, 1))) OVER(ORDER BY a.dt) sum_use -- 누적 유효자수
FROM (-- 조회 기간 달력 생성(하루 전일까지)
SELECT TO_CHAR(sdt + LEVEL - 2, 'yyyymmdd') dt
FROM (SELECT TO_DATE('20230101', 'yyyymmdd') sdt
, TO_DATE('20230330', 'yyyymmdd') edt
FROM dual)
CONNECT BY LEVEL <= edt - sdt + 2
) a
LEFT OUTER JOIN
(SELECT gb
, GREATEST('20221231', DECODE(gb, 1, dt, TO_CHAR(TO_DATE(dt, 'yyyymmdd') + 1, 'yyyymmdd'))) dt
FROM usr_t
UNPIVOT (dt FOR gb IN (reg_dt AS 1, del_dt AS 2) )
) b
ON a.dt = b.dt
GROUP BY a.dt
)
-- WHERE dt >= '20230101'
;
삭제일자가 있다면 다 가능 하다는 말씀이네요!
gb는 그럼 삭제일자를 뜻하는 컬럼인가요?
오라클 하수라 gb와dt의 컬럼을 ,, 이해 못하겠습니다 ,, ㅠㅠㅠ
UNPIOVT 을 이용해 열을 행으로 바꾼 것입니다.
gb 은 "구분" 으로 1 은 등록, 2 는 삭제 를 의미합니다.
dt 는 "일자" 입니다. gb 1 은 등록일자, gb 2 는 삭제일자+1일 입니다.
삭제일에 하루를 더한 것은 삭제 당일은 포함시키기 위해서 입니다.
조회기간 이전의 자료는 하나의 날짜(20221231)로 몰아 넣었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.