데이터베이스 질문과 답변
안녕하세요 쿼리 고민중에 도저히 답이 나오질 않아 찾아와 질문글 남깁니다..
현재 다니는 회사에서 광고성 메일 관련 작업을 시작했는데요.
관련 법령에따라 2년마다 가입된 유저에게 확인 안내 메일을 보내야하는 것으로 알고 있습니다.
다만, 홈페이지 개시는 9년 이상되었고 한번도 광고메일을 보낸적이 없어
여태 신경쓰지 않았던 문제인데
이번에는 하게되어 먼저 안내메일로 동의 여부를 다시 체크해야하는 상황인데요..
일단은 먼저 체크메일을 한번 다 돌리고.
그 다음은 법을 찾아보니 가입일 기준 2년 주기라고 하여(가입일 기준으로 동의를 받았으므로) 2년 주기로 (17년도 가입자면 19년->21년->23년)
보낼 데몬을 만들 예정인데..
쿼리를 어떻게 짜야할지 좀 막막합니다.
일단 한번 싹 돌릴때
광고 확인메일용 테이블을
tableAdMail 이름으로
id / mail / execute_date(보낸날짜) / next_date(다음 보낼예정일, execute_date보다 높을수없음. 가입일 기준 2년주기 날짜)
/ between_day(보낸,다음예정 날짜차이) / input_date
이런식의 구조로 만들고
처음 직접(데몬을 사용하지 않고)보낼 때에는 조건을
1. member table 에서 가입 2년 이상 경과한 자.
2. tableAdMail 에 데이터가 없는 자.
로 쫙 돌린 후 데몬으로 쿼리를 돌려야되는데
일단 생각해본 조건은
1. member table 가입 2년 이상 and tableAdMail에 데이터가 없음.
union
2. between_day 가 0 인 데이터 and 당일 기준 next_daterk 2년 이전인 데이터.
union
3. between_day 가 0 보다 큼 and execute_date가 1년이상(between_day가 365 이상?)인 데이터.
group by
이렇게 생각해보았는데요.. 이러면
처음 데몬을 실행시켰을 때 2년이 되기전에 1년1일 ~ 1년 364일 안에 다들 한 번은 더 받게 될거같드라구요..
이것보다 더 정확하게 2년을 충족하는 쿼리를 뽑을수 없을까요 ??
2, 3 번 조건이 잘 이해가 안가네요. 왜 그래야 하는지?
각 컬럼의 의미도 잘 이해가 안가요. 왜 이리 많은 일자 컬럼이 필요한지?
예시자료 보여주시면서 설명 가능한가요?
2,3번의 조건은.. 본문 내용중에 2년이상인 유저들에게 일괄적으로 안내 메일을 발송하는데,
각 가입일이 다르고 딱 2년되는날도 가입일에 따라 다를수밖에 없으므로 tableAdMail 테이블에 따로 발송일을 작성하고, 원래 보내야 하는 예정일도 기록해 놓음으로서
원래 보내야하는 예정일 next_date 와 이번에 일괄적으로 보낸 실행일 execute_date의 싱크를 맞추어 궁극적으로 데몬 실행후의 두번 째부터는 유저의 가입일 기준 2년주기로 맞추어 보낼 수 있게 싱크를 맞추기 위함입니다 !
옆 컬럼 between_date는 두 컬럼(next_Date,execute_date)의 날짜차이를 기록하여 쿼리로 계산하여 하는것보다 조금이라도 나은 속도를 뽑아내기 위함과 싱크가 맞추어진 데이터 및 아닌 데이터의 차이를 캐치해내기 위함으로 넣어보았습니다!
예시자료라고 하기엔 부족하지만 설명을 따로 기록해보겠습니다.
member테이블
| id | 가입일 | |
| 유저1 | user1@mail.com | 2017-05-28 |
| 유저2 | user2@mail.com | 2020-05-27 |
이런 유저가 있다면 이제 유저에게 광고성 확인메일을 보내면 tableAdMail 테이블은 다음과 같을겁니다.
tableAdMail
| id | execute_date | next_date | between_day | input_date | |
| 유저1 | user1@mail.com | 2022-05-27 | 2021-05-26 | 364 | 2022-05-27 |
| 유저2 | user2@mail.com | 2022-05-27 | 2022-05-27 | 0 | 2022-05-27 |
위와 같이 저장이 될텐데요
이제 execute_date와 next_date의 싱크를 맞추어 원래 각 유저에게 보내야하는 날을 바로잡아 줄수있도록 계산합니다.
2. between_day 가 0 인 데이터 and 당일 기준 next_date 가2년 이전인 데이터.
- 2번 유저는 원래 보내야 하는 날인 오늘 보냈으므로 betwwen_day가 0인 데이터지만 next_date가 2년 이전이 아니므로 선택되지 않습니다. 앞으로 2024-05-27 까지 선택되지 않겠죠. 싱크가 맞추어져 있으니까요.
3. between_day 가 0 보다 큼 and execute_date가 1년이상(between_day가 365 이상?)인 데이터.
- between_day가 0보다큰 1번 유저, 하지만 execute_date가 1년 이상 넘지 않아 선택되지 않습니다. 만약 23년 5월 28일이 된다면 선택이 될 것입니다. 처음 보낸지 1년 1일만이네요..
이렇게 되면 3번 조건이 좀 부실한게 보이는데 and로 next_date가 2년 이상인 조건도 추가해야할거 같습니다.
아직 모르는게 많은데 더 괜찮은 조건이 있다면 부디 가르침을 청해봅니다 (_ _)
불필요한 파생 컬럼이 너무 많네요.
회원테이블에 가입일과
메일발송 테이블에 발송일만 있으면 간단할 것 같은데요.
답변 감사드립니다 !
-- 가입일과 발송일만 간단하게 관리한다면 --
-- 발송한지 2년 지난 회원 검색 --
SELECT a.id
, a.가입일자
, IFNULL(MAX(b.발송일자), a.가입일자) 최근발송일
FROM 회원 a
LEFT OUTER JOIN 발송 b
ON a.id = b.id
GROUP BY a.id, a.가입일자
HAVING IFNULL(MAX(b.발송일자), a.가입일자) <= CURRENT_DATE - INTERVAL 2 YEAR
;
조언 감사드립니다 ㅜㅜ
제 짧은 식견으로 볼때 발송일이 기준이 되는거같은데요,
1. 첫 일괄발송은 가입일 2년 이상인 유저만..
2. 데몬 실행시 궁극적 목표는 '가입일(마케팅최초동의시점)' 주기 2년으로 해야하기때문에 발송일자는 그 해에 다시 발송되지 않게 하는 목적성이 좀 강합니다.. 그래서 최초 조건이 발송일 +1 년인것을 조건으로 하였습니다!
컬럼을 많이 가져다 쓴 이유도 발송일을 기준으로 삼는게 아닌 가입일2년 주기를 기준으로 삼기 위함이었습니다 !
설명이 부족하여 헷갈리게 해드린것 같습니다 ㅜㅜ
간단한걸 어렵게 생각하는 건 아닌지 모르겠네요.
제 쿼리가 조건을 만족시킬 것 같은데요?
제 쿼리는 테이블에 복잡한 다른 항목 다 빼고
가입일과 발송일자만 가지고 풀었습니다.
최근 발송항 일자를 기준으로 2년이 경과되었는지?
또는 발송내역이 없으면 가입일자를 기준으로 2년이 경과되었는지?
를 확인하여 발송 대상 회원을 조회하는 쿼리입니다.
넵 맞습니다 ! 아예 질문에서 부터 제 설명이 부족한게 이제 파악했습니다 !
발송 테이블에 데이터가 채워져있다고 가정하고, 결론적으로는 원래 유저 가입일로부터의 2년주기
(2020.01.01 가입자라면, 2022.01.01 -> 2024.01.1 -> 2026.01.01)
로 최종적으로는 실제 발송일을 원래 유저 가입일로부터의 2년주기로 맞추기 위해 쿼리를 고민해보았습니다.. 결론은 between_date 가 최대 2회 안에 0으로 만들기 위해서입니다 !
근데 질문 내용은 2년인걸 강조하여 2년인사람만 뽑으려면 어케하냐~ 이렇게 보이네요 ㅜㅜ 제 불찰입니다.
1. 예시로 보여주신 쿼리로 2년인사람에게 모두 보냄
2. 각각 가입일이 다 다름. 고로 광고동의일도 다름. 법령은 광고 최초동의일을 기준으로 삼게 되어있음.
3. 회사입장에서는 광고메일을 이제 보내야하기 때문에 동의 확인 메일을 일괄발송함.
(일괄발송을 22.05.27 으로 execute_date 에 넣더라도 홈페이지 개시부터 지금까지 보내지 않앗던 동의확인메일을 한번에 보내는거라.. '다음 혹은 다다음'은 유저의 최초동의 날로 기준이 변경되어야합니다.. 예를들면 19.01.01 가입유저라면 21.01.01은 과거이므로 패스 23.01.01은 가능...인데.. 흠.. 여기도 좀 애매합니다.. 이렇게 빨리보내도되나..)
4. 이제 한번 다 뿌렸으니, 다음날부터
최초 2년 이상이 되는사람과, 뿌린사람중에 지난번 발송일(일괄 발송한날 execute_date)의 연수가 다르고, 원래 뿌려야하는 시기(next_date 22.01.01이었다면 24.01.01 // 일괄발송한 날짜X )가 도래한사람들만 쿼리조회하여 메일 발송.
요게 원래 질문 목적이었는데
제가 질문한 것은 발생 문제가 예상되는 .. 1년1일~364일 어쩌구
이것만 적어놧었네요 ㅜㅜ 그래서 제가 더 어렵게 생각하는게 아니냐 말씀해주신것같습니다! 혼란을 드려 죄송합니다
질문의 요지가 이건가요?
1. 회원 가입 시점에는 광고동의 여부를 체크했다. 2. 홈페이지 최초 운영시점(9년 전) 이후 추가적인 메일발송(2년마다)을 통해 광고 동의를 받지 않았다. 3. 이제부터 2년 이내의 광고메일 동의자들에게는 광고메일을 보내려고 한다. 3-1. 2년 이상 광고 동의를 받지 않는 사용자에게는 우선 동의 메일부터 보내서, 동의를 받고 동의자들에게 메일을 보낼 것이다. 3-2. 최근 2년 이내 가입자들은 동의 여부가 저장되어 있어, 동의자들에게는 광고메일 발송하는 데 문제가 없다. 요청사항 : 회원테이블 a 발송이력테이블 b 이렇게 있는데 일배치 쿼리를 어떻게 만들어요?
넵 전부 맞습니다 !
다만 걱정되는것은
금일 기준으로 보낸 후.... 최초 동의시점(지금으로선 가입일)이 기준이 되어야합니다 ..
안내 메일을 보낸 시점이 기준이 되어선 안된다고 하더라구요..
근데 최초로 일괄발송하고.. 가까운시일에 2년이 되는 유저가 또 받으면 좀 그러니 1년의 텀..을 주자라는 쪽으로
생각이 기울어서 애매한데 일단은 정리해주신게 다 맞습니다 깔끔한 정리 감사합니다!
1.
1. 최초 일괄 발송 : 가입 2년 이상 경과자
2. 이후 매일 배치 : 가입 2년 마다
(단, 1번의 일괄발송 이후 바로 또 발송되는 일이 없도록 예외 적용 가준 필요)
(처리 방안)
가입일 기준 2년 주기 도래일이 오늘인 회원 추출
2년 안에 메일을 보낸 이력이 있는 회원 제외
이런식으로 하면 어떤지요?
) 버튼을 클릭하여 작성 하시면 됩니다.