데이터베이스 질문과 답변
지금 mysql 쿼리문에서 막히는게 있어서 질문드립니다.
현재 DB테이블에
id, 유저id, 아이템id, 아이템 사용한 날짜(사용 안할 시 default 날짜가 들어가있습니다. ex)2020-07-29), 아이템 구매한 날짜 이런식으로 저장되어 있습니다.
현재 구현하고 싶은건 아이템을 사용한 날짜가 디폴트 값에서 바뀌었을 시 중복된 아이템id를 한개만 출력하는 것 + 사용한 날짜가 안바뀌어 있는 모든 아이템 목록 출력
예를 들면 아이템 사용한 날짜가 default 2020-02-02 02:02:02 라고 가정할 시
id | itemid| usedate | purchaseddate | userid
1 | 100 | 2020-07-29 02:02:02 | 2020-01-01 01:01:01 | aaa
2 | 101 | 2020-02-02 02:02:02 | 2020-01-01 01:01:01 | aaa
3 | 100 | 2020-07-28 03:03:03 | 2020-01-01 01:01:01 | aaa
4 | 101 | 2020-06-29 04:04:04 | 2020-01-01 01:01:01 | aaa
출력 결과 값이
1 | 100 | 2020-07-29 02:02:02 | 2020-01-01 01:01:01 | aaa
2 | 101 | 2020-02-02 02:02:02 | 2020-01-01 01:01:01 | aaa
4 | 101 | 2020-06-29 04:04:04 | 2020-01-01 01:01:01 | aaa
이런식으로 나오길 원합니다. 중복된 값은 한가지만 노출되지만 그중 사용을 안한 아이템이라면 중복제거 없이 모두다 노출되게 원합니다.
그래서 제가 처음에 짠 쿼리식이
select *from (테이블명) where (userid='aaa' and usedate='2020-02-02 02:02:02') or (userid='aaa' and usedate != '2020-02-02 02:02:02') group by itemid;
이런식으로 짯더니 이게 group by가 앞에 까지 다 묶어 줘서 사용안한 아이템도 중복으로 묶어서 노출이 안되게 하더라구요
그래서 혹시 group by를 where 조건절에서 일부분에만 적용이 가능한지
아니면 다른 방법으로 쿼리를 짜서 중복체크를 하면서 사용안한 아이템을 노출시키는 방법이 있는지 궁금합니다.
예시자료나 질문의 내용이 전반적으로 모호합니다.
디폴트 값이 뭔지? 변경이 발생되었음을 어떻게 알 수 있는 것인지?
원본 4건 대비 결과 3건이 나오는 이유가 뭔지? 왜 3번만 제외되어야 하는지?
디폴트 값은 말그대로 처음에 사용안했다는 기준을 하기 위해서 지정한 임의의 datetime 형식의 값입니다. 고정값으로 놔두고 유저가 해당 아이템의 사용 버튼을 누르면 그 시각 datetime 값이 저장되고 default 시간값이 아닐 경우 아이템을 사용했다고 판단하고 있습니다.
원본에서 4건인건 같은 itemid 번호가 100인게
default 2020-02-02 02:02:02 의 시간값이 아니기에 2개다 사용했다고 판단하여 하나만 노출된 상태이고
itemid 101번 같은 경우 한개는 default 2020-02-02 02:02:02 시간값과 일치하고 다른 한개는 default 2020-02-02 02:02:02 일치 하지 않기 때문에
2개다 결과값에 나오길 원합니다.
최종적으로 사용한 아이템은 중복체크를 하여서 여러개일 경우 하나만 노출되고 그 결과값에 + 로 사용안한 아이템은 모두 노출되는 결과값은 원하고 있습니다.
사용 여부를 판단하는 기준은 usedate 가 default 2020-02-02 02:02:02 시간값과 같냐 다르냐로 판단하고 있습니다.
*중복체크시 min값이든 max값이든 뭐가 기준이 되든 상관없습니다.
WITH t AS
(
SELECT 1 id, 100 itemid, '2020-07-29 02:02:02' usedate, '2020-01-01 01:01:01' purchaseddate, 'aaa' userid
UNION ALL SELECT 2, 101, '2020-02-02 02:02:02', '2020-01-01 01:01:01', 'aaa'
UNION ALL SELECT 3, 100, '2020-07-28 03:03:03', '2020-01-01 01:01:01', 'aaa'
UNION ALL SELECT 4, 101, '2020-06-29 04:04:04', '2020-01-01 01:01:01', 'aaa'
)
SELECT id, itemid, usedate, purchaseddate, userid
FROM (SELECT id, itemid, usedate, purchaseddate, userid
, ROW_NUMBER() OVER(PARTITION BY userid, itemid ORDER BY usedate DESC) rn
FROM t
) a
WHERE usedate = '2020-02-02 02:02:02' -- 변경이 안된 경우
OR rn = 1 -- 최신자료 1건
;
답변 정말 감사합니다. 근데
여기서 만약 특정 userid의 값만 불러오고 싶다면 조건을 어디쪽에 둬야 할까요?
이런식으로 쿼리문 짜여진것도 처음보는데 저런식의 쿼리를 공부하고 싶다면 검색키워드같은게 있을까요?
WITH t AS
(
SELECT 1 id, 100 itemid, '2020-07-29 02:02:02' usedate, '2020-01-01 01:01:01' purchaseddate, 'aaa' userid
UNION ALL SELECT 2, 101, '2020-02-02 02:02:02', '2020-01-01 01:01:01', 'aaa'
UNION ALL SELECT 3, 100, '2020-07-28 03:03:03', '2020-01-01 01:01:01', 'aaa'
UNION ALL SELECT 4, 101, '2020-06-29 04:04:04', '2020-01-01 01:01:01', 'aaa'
)
SELECT id, itemid, usedate, purchaseddate, userid
FROM (SELECT id, itemid, usedate, purchaseddate, userid
, ROW_NUMBER() OVER(PARTITION BY userid, itemid ORDER BY usedate DESC) rn -- 분석함수
FROM t
WHERE userid = 'aaa' -- 특정 userid 조건
) a
WHERE usedate = '2020-02-02 02:02:02' -- 변경이 안된 경우
OR rn = 1 -- 최신자료 1건
;
정말 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.