개발 Q&A

제목 회원 및 접속현황 테이블을 이용해서 COUNT 출력하는 질문
카테고리 DB
글쓴이 과학나라 작성시각 2019/01/16 10:03:04
댓글 : 6 추천 : 0 스크랩 : 0 조회수 : 11675   RSS

table : table_a  (접속 현황 테이블)

id create_at member_id
0 2018-01-14 22:17:57 0
1 2018-01-15 17:23:48 0
2 2018-01-17 17:23:47 1
3 2018-01-17 17:23:48 0
4 2018-02-14 23:17:57 0
5 2018-03-15 11:17:57 0
6 2018-04-15 12:17:57 1
7 2019-01-15 13:17:57 0
8    

 

 

table : member (회원 테이블)

id user_id group_id
0 user1 1
1 user2 1

 

이렇게 2개의 테이블을 이용해서

각 회원의 날짜 데이터를 이용해서 접속 현황을 출력하고 싶습니다.

즉 접속현황 테이블하고 회원 테이블을 조인해서 밑에 sql문을 보시면 WHERE 절에 2018 즉 해당 년도를 주입하면

그에 맞는 월단위로 해당 회원이 어느정도 접속되어나 COUNT를 해보았습니다.

 

 

일단 제가 sql문을 짜보았습니다!!!
SELECT  
COUNT(*) AS use_count,
DATE_FORMAT(a.create_at, '%Y-%m') date,
 b.user_id
FROM table_a AS a
LEFT JOIN table_b AS b
ON (a.member_id = b.id)
WHERE DATE_FORMAT(a.create_at, '%Y') = '2018' AND (b.group_id = 1)
GROUP BY b.user_id, date;

 

 

 

결과 

use_count date user_id
1 2018-01 user1
1 2018-04 user1
3 2018-01 user2
1 2018-02 user2
1 2018-03 user2

 

일단 여기까지 count는 잘 나옵니다 ㅠ,ㅠ

 

문제는 해당 테이블에 데이터가 없으면 use_count에 0으로 입력 받고 싶습니다 ㅠ,ㅠ

 

예를들어

 

제가 원하는 데이터 출력

use_count date user_id
1 2018-01 user1
0 2018-02 user1
0 2018-03 user1
1 2018-04 user1
3 2018-01 user2
1 2018-02 user2
1 2018-03 user2
0 2018-04 user2

 

이렇게 데이터가 없으면 해당 유저에 use_count가 0으로 출력되고 date에는 해당되는 날짜로요 ㅠ,ㅠ

 

이거 정말 어렵네요 ㅠ,ㅠ

 

db 고수님들 부탁드립니다 ㅠ,ㅠ

 

 

 

 

 다음글 리눅스 sendmail 질문드립니다 (3)
 이전글 혹시 다국어페이지 만들어보신분 계신가요.. (7)

댓글

배강민 / 2019/01/16 10:20:43 / 추천 0

일단 문제가 있는것이 만약 특정month에 접속한 유저가 단 1명도 없어도 해당 month가 나와야하는건가요? 즉, table_a에 특정 month가 아예 없다면?

만약 이래야한다면 빵꾸난 month까지 만들어내야하는데 이게 될라나 모르겠네요.

kaido / 2019/01/16 10:31:14 / 추천 0

3가지 방법이 있는데 1가지는 기억이 잘 안나네요

우선 첫번째는 가장 많이 하는 방법으로 그냥 날짜 테이블을 하나더 만들어서 left join 거는겁니다

https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query

 

또 하나는 mssql 에서만 사용가능한 방법으로 group by all 걸면 됩니다.

 

마지막 방법이 쿼리로 해결하는 방법인데 @RUM 을 만들어서 일일이 카운터를 세서 빈 값일경우 다시 0값을 넣는 쿼리가 있긴한데

쿼리를 여러번 날려서 억지로 만들어내는 방식이라서 퍼포먼스는 아작이 납니다.

해당 쿼리를 알긴했는데 워낙 옛날에 했던거라서.

 

결론적으로 날짜테이블 만들어서 조인 거는게 가장 현실성 있는 방법입니다.

변종원(웅파) / 2019/01/16 10:55:52 / 추천 0
기본적으로 날짜가 모두 있어야 합니다. 날짜가 있다면 0으로 나옵니다. ^^
조현 / 2019/01/16 12:16:35 / 추천 0

날짜테이블에 유저키를 모두 넣는게 쿼리가 쉬울겁니다.

* 날짜테이블에 회원정보가 없을경우.

- 회원테이블을 기준으로 로그와 날짜를 조인할경우 회원이 많을수록 쿼리 부하는 더 커지고, 사이트 악영향이니 제외.

- 날짜테이블을 기준으로 로그테이블을 조인하면 날짜별 회원이 결과에 잡히지 않습니다.

- 로그테이블을 기준으로 날짜테이블을 조인해도 해당날짜에 회원로그가 없으면 잡히지 않습니다.

배강민 / 2019/01/16 12:25:17 / 추천 0

일단 꾸역꾸역 해보긴했습니다.

테스트하느라 스키마를 조금 다르게 해서 아래 이미지 참고해보시고요. 정말 억지로라서 올바른지 exprain 결과는 안봤습니다.

아마 그대로 쓰면 문제가 많을것이니 참고만 해보세요.

data는 동일하게 넣고 했으니 큰 차이는 없을겁니다.

 

SELECT
	calendar.yyyymm,
    IFNULL(history.connect_cnt, 0) AS cnt,
    calendar.user_id
FROM
(
    SELECT
        temp1.yyyymm,
        temp2.user_id
    FROM
        (SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 0 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 1 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 2 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 3 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 4 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 5 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 6 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 7 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 8 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 9 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 10 MONTH), 7) AS yyyymm FROM dual
        UNION ALL
        SELECT LEFT(DATE_ADD('2018-01-01', INTERVAL 11 MONTH), 7) AS yyyymm FROM dual                                  
        ) AS temp1
    LEFT JOIN test_connect_users AS temp2 ON 1 = 1
) AS calendar
LEFT JOIN (
    SELECT
        COUNT(*) AS connect_cnt,
        DATE_FORMAT(tch.create_at, '%Y-%m') AS yyyymm,
        tch.user_id
    FROM
        test_connect_history AS tch,
        test_connect_users AS tcu
    WHERE
        tch.user_id = tcu.user_id
    GROUP BY
        tcu.user_id, yyyymm
) AS history 
ON calendar.yyyymm = history.yyyymm AND calendar.user_id = history.user_id
ORDER BY
    calendar.user_id ASC,
    calendar.yyyymm ASC

 

 

과학나라 / 2019/01/16 15:31:26 / 추천 0

모두 감사합니다 ㅠㅠ 어떻게 해든 출력은 되게 했습니다ㅠㅠ

특히 배강민님 정말로 감사합니다.