데이터베이스 질문과 답변
member 테이블
| id | user_id | name | city | gu | |
| 1 | 111@naver.com | 111 | 홍길동 | 서울시 | 성북구 |
| 2 | 222@naver.com | 222 | 김길동 | 경기도 | 동구 |
| 3 | 333@gmail.com | 333 | 고길동 | 광주시 | 북구 |
reading 테이블
| id | user_email | reading_time | total_reading_date | create_date |
| 1 | 111@naver.com | 2022-04-19 17:43:06 | 0 | 2022-04-19 17:43:06 |
| 2 | 111@naver.com | 2022-04-19 17:45:06 | 120000 | 2022-04-19 17:45:06 |
| 3 | 333@gmail.com | 2022-04-14 23:33:02 | 0 | 2022-04-14 23:33:02 |
위와 같이 두 개의 테이블이 있습니다.
reading테이블에 데이터가 등록되는 조건은
1. 특정 회원이 reading테이블의 게시글을 하나를 클릭한다.(total_reading_date가 0으로 입력이 됩니다.)
2. 2분 후 같은 아이디로 reading테이블의 다른 글을 클릭한다(total_reading_date의 total_reading_date가 2분의 값이 밀리세컨 값으로 저장이 된다.)
이런식으로 reading테이블의 데이터가 쌓입니다.
회원이 한 게시물을 클릭하고, 다른 게시물을 클릭할 때 까지의 시간을 계산해서 그 값이 total_reading_date로 저장이 됩니다.(처음에는 어느 게시물을 클릭해도 무조건 0으로 저장이 됩니다.)
위와같은 식으로 데이터가 쌓이도록 만들었는데..
회원의 지역(시/구)별로 월별로 총 읽은 시간(total_reading_date) 값의 합계를 구하고 싶습니다.
예를들어 회원들이 1년동안 읽었던 total_reading_date값을 월별로 나눠서 합계를 구하고
일별로 회원들이 읽었던 total_reading_date값의 합계를 구하고 싶습니다.
아래와 같이 쿼리를 만들면 월별로 특정 회원의 총 읽은 시간의 값이 계산이 됩니다.
조건문에서 회원 아이디를 제외하면 전체 회원의 월별 총 읽은 시간값이 나옵니다.
SELECT id
, reading_time
, date_format(reading_time, '%Y-%m') month
, max(total_reading_date) - min(total_reading_date) totalReadingDate
FROM reading
WHERE id = '111@naver.com'
AND city = '서울시'
AND gu = '성북구'
GROUP BY id, month
ORDER BY month ASC
그리고 월별로 총 읽은 시간을 구할려면 아래와 같이 쿼리를 만들면 월별로 값이 구해집니다.
SELECT date_format(reading_time, '%Y-%m') month
, sum(totalReadingDate) AS totalReadingDate
, m.email
FROM (SELECT id
, reading_time
, date_format(reading_time, '%Y-%m') month
, max(total_reading_date) - min(total_reading_date) totalReadingDate
FROM reading
WHERE id = '111@naver.com'
GROUP BY id, month
ORDER BY month ASC
) r
LEFT OUTER JOIN member m ON m.id = r.id
AND m.city = '서울시'
AND m.gu = '성북구'
정확하게 검증은 하지를 못했습니다.
제가 작성한 쿼리가 제대로 작성이 된건지, 아니면 제가 놓치고 있는 부분이 있는지 궁금합니다.
그리고 중요한것이..
만약 월별로 합계를 뽑거나, 일별로 합계를 뽑을 때
값이 없는 월이나 일에는 0으로 표기되게 하고 싶은데 그럴때는 어떻게 해야할까요??
내용이 좀 정신이 없는데
정리를 하면
1. 월별로/일별로 회원의 total_reading_date(총 읽은 시간)을 구하고 싶습니다.
2. 월/일별로 총 읽은 시간이 없을 경우에는 해당 월/일에는 0으로 표기가 되게 하고 싶습니다.
3. 최종적으로 만들고 싶은 결과물은 총 읽은 시간의 통계를 특정 회원의 통계와 나머지 전체 회원의 통계값을 구하고 싶습니다.
예를들어서
111@naver.com 회원이 월별로 총 읽은 시간의 통계는 1시간 10분 21초
나머지 회원의 총 읽은 시간의 통계는 1시간 20분 30초
이런식으로 월별/일별로 특정회원과 특정회원을 제외한 전체 회원의 총 읽은 시간의 통계값을 구하고 싶습니다.
total_reading_date 의 의미가 뭔지 잘 이해가 안가네요.
이게 누적값인가요?
월별로 초기화 되는게 아니라면?
지금 사용하신 쿼리는 문제가 있어 보입니다.
지역별로 다르게 저장되는게 아니라면 지역별 집계가 맞는지도 의문입니다.
이 컬럼의 의미와 집계하고자 하는 의도가 뭔지 잘 모르겠습니다.
마농님 답변 감사합니다.
total_reading_date 값은
처음에 0으로 되어있다가 회원이 게시글을 클릭을 했을 때
마지막에 클릭했던 시간에서 그 바로 전에 클릭했던 시간을 빼서 밀리세컨으로 저장한 값입니다.
그러니까 최근에 클릭했던 시간과 바로 이전에 클릭했던 시간차를 단순히 저장한 값입니다.
쿼리는 제가 봐도 문제가 있는 것 같은데..ㅠㅠ 어느 부분이 문제이고, 어떻게 개선을 해야할지 방향을 잘 잡지 못하고 있습니다.ㅠㅠ
ㅇ 집계 SQL 이 문제가 이전에 자료 생성의 문제가 제일 큰 문제 입니다.
1) 일별 / 월별 경계의 구분이 없다.
2) 시작 = 0 , 다음 클릭시 ( 이전 정보 차이 저장 ? )
< 문제점 확인 및 기본 자료 생성의 오류 >
가) 최초 이후 다음 클릭의 정의가 필요하다.
- 그냥 2차 클릭 하지 아니하고, 그냥 닫아 버렸다면 어찌 저장 되나?
- 상기 내용에 준하여 자료를 저장 한다면, ( 3일 후 열어서 다른 게시물 클릭시 )
최초 = 0
Next ( 3일 후 ) = 3일 * 24시간 * 60분 * 60 초 <===이자료를 저장 하게 되는 것인지?
즉 세션의( login / logout ) 등의 부분까지 고려 된 자료 저장이 되어야 원하는 자료르 산출 가능하다.
< 결론 >
1) 자료를 산출하고자 하는 최소한의 자료까지는 생성 되어야 한다.
- 일단위 : 즉 최초 이후 24시간 넘어갈때 자료 생성 24시간에서, 끊어서 저장하고,
다음날 00시 자료를 추가생성하여, 기본자료를 생성 한다.
- 일단위 아니고, 그냥 무작위 발생시.
. 일단위 자료를 일일이 다시 산출해야 하는 일이 필요하며,
. 일단위 자료이외에 월단위도 구분하여, 해당 자료를 분선처리 되어야 한다.
상기 자료를 집계를 내려 하는 부분을 SQL 로 구현하기 이전에 손으로,
계산 되어 지는지?
그리고, 해당 자료가 정확하게 원하는 자료를 생성하고 있는지, 최우선으로 생각하고 고려되어야 합니다.
이후 자료 집계는 그냥 나올것으로 보입니다. 수고하십시요..
^^
혹시 설계 변경이 가능한 모델 인가요?
그것이 아니라면 어찌 되었던지(정확하지는 않더라도) 주어진 조건으로 데이터를 뽑아야 하는 상황인지도 모르겠습니다.
남이 설계한 모델에서 내가 추출하려는 데이터가 정확히 뽑히지 않는 경우는 생각보다 많으니까요.
문제가 되는 테이블은 아마 reading 테이블일듯 합니다.
1. id 와 user_email 의 의미가 이상합니다.
- 예시로 쓴 쿼리에서는 id 에서 user_email을 사용하셨네요.
- member 테이블의 email 컬럼이 user_email 과 동일하다면 해당 컬럼은 삭제해도 무방해보입니다.
2. reading_time 과 create_date 의 의미가 동일해 보입니다.
- 게시물을 클릭할 때의 시간을 reading_time 과 create_date 에 기록하는 것이 맞나요?
- 설명에서 유추해본 결과 reading_time 과 create_date 는 동일한 데이터값을 가질듯 하네요.(하나는 삭제 해도 될듯...)
3. total_reading_date의 데이터가 정확한지가 중요합니다.
- 위의 두가지 컬럼은 불필요한 컬럼의 기술이라 있어도, 없어도 결과에 영향을 주지 않지만, 이 데이터의 의미가 문제가 있습니다.
- 다른 분들의 의견처럼 1.강제종료 2.세션종료 3.날짜가 변경되는 상황에서의 처리 등등
- 만약 이 내용을 해결하지 못했지만 어쩔 수 없이 주어진 모델로 데이터를 뽑아야 한다... 라고 한다면 total_reading_date 데이터가 위의 상황을 다 고려한 정확한 내용이다. 라는 가정으로 쿼리를 작성해야 합니다.
-- id별, 월별 독서시간 -- 해당 month 부분의 로직을 일자로 변환하면 일별 독서시간 추출 가능 SELECT T2.email ,DATA_FORMAT(T1.reading_time, '%Y-%m') AS MONTH -- 월별 / 일별 변환시 변경 ,SUM(T1.total_reading_date) AS READ_TIME FROM reading T1 INNER JOIN member T2 ON T1.id = T2.id WHERE 1=1 -- 아래 조건은 필요하지 않은 항목을 주석 처리해서 검색 AND T2.email = '111@naver.com' -- 특정이메일로 검색시 AND T2.city = '서울시' -- 특정시로 검색시 AND T2.gu = '성북구' -- 특정구로 검색시 GROUP BY T2.email ,DATA_FORMAT(T1.reading_time, '%Y-%m') AS MONTH -- 월별 / 일별 변환시 변경시
실제로 돌려보지 못해서 오류가 있을 수는 있지만, 이런 형태의 쿼리가 될거 같네요.
) 버튼을 클릭하여 작성 하시면 됩니다.