데이터베이스 질문과 답변

mysql 쿼리질문드립니다 0 2 1,529

by mbj1 [MySQL] [2022.10.05 19:00:09]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
SELECT
    A.BENEFIT_SEQ,
    A.BENEFIT_TYPE_CD
    A.BENEFIT_TITL,
    A.BENEFIT_STRT_DT,
    A.BENEFIT_END_DT,
    SUM(IFNULL(Y.CLICK_CNT1, 0)) AS CLICK_CNT1,
    SUM(IFNULL(X.USE_CNT1, 0)) AS USE_CNT1,
    SUM(IFNULL(Y.CLICK_CNT2, 0)) AS CLICK_CNT2,
    SUM(IFNULL(X.USE_CNT2, 0)) AS USE_CNT2,
    SUM(IFNULL(Y.CLICK_CNT3, 0)) AS CLICK_CNT3,
    SUM(IFNULL(X.USE_CNT3, 0)) AS USE_CNT3,
    SUM(IFNULL(Y.CLICK_CNT4, 0)) AS CLICK_CNT4,
    SUM(IFNULL(X.USE_CNT4, 0)) AS USE_CNT4,
    SUM(IFNULL(Y.CLICK_CNT5, 0)) AS CLICK_CNT5,
    SUM(IFNULL(X.USE_CNT5, 0)) AS USE_CNT5,
    SUM(IFNULL(Y.CLICK_CNT6, 0)) AS CLICK_CNT6,
    SUM(IFNULL(X.USE_CNT6, 0)) AS USE_CNT6,
    SUM(IFNULL(Y.CLICK_CNT7, 0)) AS CLICK_CNT7,
    SUM(IFNULL(X.USE_CNT7, 0)) AS USE_CNT7,
    SUM(IFNULL(Y.CLICK_CNT8, 0)) AS CLICK_CNT8,
    SUM(IFNULL(X.USE_CNT8, 0)) AS USE_CNT8,
    SUM(IFNULL(Y.CLICK_CNT9, 0)) AS CLICK_CNT9,
    SUM(IFNULL(X.USE_CNT9, 0)) AS USE_CNT9,
    SUM(IFNULL(Y.CLICK_CNT10, 0)) AS CLICK_CNT10,
    SUM(IFNULL(X.USE_CNT10, 0)) AS USE_CNT10,
    SUM(IFNULL(Y.CLICK_CNT11, 0)) AS CLICK_CNT11,
    SUM(IFNULL(X.USE_CNT11, 0)) AS USE_CNT11,
    SUM(IFNULL(Y.CLICK_CNT12, 0)) AS CLICK_CNT12,
    SUM(IFNULL(X.USE_CNT12, 0)) AS USE_CNT12,
    SUM(IFNULL(Y.CLICK_CNT1, 0)) + SUM(IFNULL(Y.CLICK_CNT2, 0)) + SUM(IFNULL(Y.CLICK_CNT3, 0)) + SUM(IFNULL(Y.CLICK_CNT4, 0)) +
    SUM(IFNULL(Y.CLICK_CNT5, 0)) + SUM(IFNULL(Y.CLICK_CNT6, 0)) + SUM(IFNULL(Y.CLICK_CNT7, 0)) + SUM(IFNULL(Y.CLICK_CNT8, 0)) +
    SUM(IFNULL(Y.CLICK_CNT9, 0)) + SUM(IFNULL(Y.CLICK_CNT10, 0)) + SUM(IFNULL(Y.CLICK_CNT11, 0)) + SUM(IFNULL(Y.CLICK_CNT12, 0)) AS SUM_CLICK_CNT,
    SUM(IFNULL(X.USE_CNT1, 0)) + SUM(IFNULL(X.USE_CNT2, 0)) +  SUM(IFNULL(X.USE_CNT3, 0)) + SUM(IFNULL(X.USE_CNT4, 0)) +
    SUM(IFNULL(X.USE_CNT5, 0)) + SUM(IFNULL(X.USE_CNT6, 0)) + SUM(IFNULL(X.USE_CNT7, 0)) + SUM(IFNULL(X.USE_CNT8, 0)) +
    SUM(IFNULL(X.USE_CNT9, 0)) + SUM(IFNULL(X.USE_CNT10, 0)) + SUM(IFNULL(X.USE_CNT11, 0)) + SUM(IFNULL(X.USE_CNT12, 0)) AS SUM_USE_CNT,
    CASE WHEN SUM(IFNULL(Y.CLICK_CNT1, 0)) + SUM(IFNULL(Y.CLICK_CNT2, 0)) + SUM(IFNULL(Y.CLICK_CNT3, 0)) + SUM(IFNULL(Y.CLICK_CNT4, 0)) +
              SUM(IFNULL(Y.CLICK_CNT5, 0)) + SUM(IFNULL(Y.CLICK_CNT6, 0)) + SUM(IFNULL(Y.CLICK_CNT7, 0)) + SUM(IFNULL(Y.CLICK_CNT8, 0)) +
              SUM(IFNULL(Y.CLICK_CNT9, 0)) + SUM(IFNULL(Y.CLICK_CNT10, 0)) + SUM(IFNULL(Y.CLICK_CNT11, 0)) + SUM(IFNULL(Y.CLICK_CNT12, 0)) = 0
         THEN 0
         ELSE (SUM(IFNULL(X.USE_CNT1, 0)) + SUM(IFNULL(X.USE_CNT2, 0)) +  SUM(IFNULL(X.USE_CNT3, 0)) + SUM(IFNULL(X.USE_CNT4, 0)) +
               SUM(IFNULL(X.USE_CNT5, 0)) + SUM(IFNULL(X.USE_CNT6, 0)) + SUM(IFNULL(X.USE_CNT7, 0)) + SUM(IFNULL(X.USE_CNT8, 0)) +
               SUM(IFNULL(X.USE_CNT9, 0)) + SUM(IFNULL(X.USE_CNT10, 0)) + SUM(IFNULL(X.USE_CNT11, 0)) + SUM(IFNULL(X.USE_CNT12, 0))
              ) /
              (SUM(IFNULL(Y.CLICK_CNT1, 0)) + SUM(IFNULL(Y.CLICK_CNT2, 0)) + SUM(IFNULL(Y.CLICK_CNT3, 0)) + SUM(IFNULL(Y.CLICK_CNT4, 0)) +
               SUM(IFNULL(Y.CLICK_CNT5, 0)) + SUM(IFNULL(Y.CLICK_CNT6, 0)) + SUM(IFNULL(Y.CLICK_CNT7, 0)) + SUM(IFNULL(Y.CLICK_CNT8, 0)) +
               SUM(IFNULL(Y.CLICK_CNT9, 0)) + SUM(IFNULL(Y.CLICK_CNT10, 0)) + SUM(IFNULL(Y.CLICK_CNT11, 0)) + SUM(IFNULL(Y.CLICK_CNT12, 0))) * 100
    END AS USE_RATE
FROM
    TB_BENEFIT_MST A
LEFT OUTER JOIN(
                SELECT
                    a.BENEFIT_SEQ,
                    CASE MONTH(b.CLICK_DTTM) WHEN 1 THEN 1 ELSE 0 END AS CLICK_CNT1,
                    CASE MONTH(b.CLICK_DTTM) WHEN 2 THEN 1 ELSE 0 END AS CLICK_CNT2,
                    CASE MONTH(b.CLICK_DTTM) WHEN 3 THEN 1 ELSE 0 END AS CLICK_CNT3,
                    CASE MONTH(b.CLICK_DTTM) WHEN 4 THEN 1 ELSE 0 END AS CLICK_CNT4,
                    CASE MONTH(b.CLICK_DTTM) WHEN 5 THEN 1 ELSE 0 END AS CLICK_CNT5,
                    CASE MONTH(b.CLICK_DTTM) WHEN 6 THEN 1 ELSE 0 END AS CLICK_CNT6,
                    CASE MONTH(b.CLICK_DTTM) WHEN 7 THEN 1 ELSE 0 END AS CLICK_CNT7,
                    CASE MONTH(b.CLICK_DTTM) WHEN 8 THEN 1 ELSE 0 END AS CLICK_CNT8,
                    CASE MONTH(b.CLICK_DTTM) WHEN 9 THEN 1 ELSE 0 END AS CLICK_CNT9,
                    CASE MONTH(b.CLICK_DTTM) WHEN 10 THEN 1 ELSE 0 END AS CLICK_CNT10,
                    CASE MONTH(b.CLICK_DTTM) WHEN 11 THEN 1 ELSE 0 END AS CLICK_CNT11,
                    CASE MONTH(b.CLICK_DTTM) WHEN 12 THEN 1 ELSE 0 END AS CLICK_CNT12
                FROM
                    TB_BENEFIT_MST a
                JOIN TB_BENEFIT_CLICK_HIS b ON
                    a.BENEFIT_SEQ = b.BENEFIT_SEQ
                WHERE
                    b.CLICK_DTTM >= '2022-01-01'
                    AND b.CLICK_DTTM < '2023-01-01'
                    AND IFNULL(a.BENEFIT_TYPE_CD, '') != ''
                ) Y ON A.BENEFIT_SEQ = Y.BENEFIT_SEQ
LEFT OUTER JOIN(
                SELECT
                    a.BENEFIT_SEQ,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 1 THEN 1 ELSE 0 END AS USE_CNT1,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 2 THEN 1 ELSE 0 END AS USE_CNT2,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 3 THEN 1 ELSE 0 END AS USE_CNT3,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 4 THEN 1 ELSE 0 END AS USE_CNT4,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 5 THEN 1 ELSE 0 END AS USE_CNT5,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 6 THEN 1 ELSE 0 END AS USE_CNT6,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 7 THEN 1 ELSE 0 END AS USE_CNT7,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 8 THEN 1 ELSE 0 END AS USE_CNT8,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 9 THEN 1 ELSE 0 END AS USE_CNT9,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 10 THEN 1 ELSE 0 END AS USE_CNT10,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 11 THEN 1 ELSE 0 END AS USE_CNT11,
                    CASE MONTH(b.COUPON_USE_DTTM) WHEN 12 THEN 1 ELSE 0 END AS USE_CNT12
                FROM TB_BENEFIT_MST a
                JOIN TB_BENEFIT_COUPON b ON b.COUPON_USE_YN = 'Y' AND a.COUPON_GRP_ID = b.COUPON_GRP_ID
               WHERE b.COUPON_USE_DTTM >= '2022-01-01'
                 AND b.COUPON_USE_DTTM < '2023-01-01'
               UNION ALL
              SELECT
                     a.BENEFIT_SEQ,
                     CASE MONTH(b.REG_DTTM) WHEN 1 THEN 1 ELSE 0 END AS USE_CNT1,
                     CASE MONTH(b.REG_DTTM) WHEN 2 THEN 1 ELSE 0 END AS USE_CNT2,
                     CASE MONTH(b.REG_DTTM) WHEN 3 THEN 1 ELSE 0 END AS USE_CNT3,
                     CASE MONTH(b.REG_DTTM) WHEN 4 THEN 1 ELSE 0 END AS USE_CNT4,
                     CASE MONTH(b.REG_DTTM) WHEN 5 THEN 1 ELSE 0 END AS USE_CNT5,
                     CASE MONTH(b.REG_DTTM) WHEN 6 THEN 1 ELSE 0 END AS USE_CNT6,
                     CASE MONTH(b.REG_DTTM) WHEN 7 THEN 1 ELSE 0 END AS USE_CNT7,
                     CASE MONTH(b.REG_DTTM) WHEN 8 THEN 1 ELSE 0 END AS USE_CNT8,
                     CASE MONTH(b.REG_DTTM) WHEN 9 THEN 1 ELSE 0 END AS USE_CNT9,
                     CASE MONTH(b.REG_DTTM) WHEN 10 THEN 1 ELSE 0 END AS USE_CNT10,
                     CASE MONTH(b.REG_DTTM) WHEN 11 THEN 1 ELSE 0 END AS USE_CNT11,
                     CASE MONTH(b.REG_DTTM) WHEN 12 THEN 1 ELSE 0 END AS USE_CNT12                 
                FROM TB_BENEFIT_MST a
                JOIN TB_BENEFIT_ENTRY b ON a.BENEFIT_SEQ = b.BENEFIT_SEQ
               WHERE b.REG_DTTM >= '2022-01-01'
                 AND b.REG_DTTM < '2023-01-01'
                ) X ON A.BENEFIT_SEQ = X.BENEFIT_SEQ
WHERE IFNULL(a.BENEFIT_TYPE_CD, '') != ''
GROUP BY A.BENEFIT_SEQ
구분 1월 2월 합계
조회수 사용건수(응모건수) 조회수 사용건수(응모건수) 조회수 사용건수(응모건수)
쿠폰 10 5 20 10 30 15
응모 20 10 30 10 50 20

안녕하세요.

위의 표처럼 데이터를 뽑고싶은데 합계를 구하는데 

SUM_CLICK_CNT와 SUM_USE_CNT의 값이 동일하게 출력이됩니다.

무엇이 문제인지 알려주시면 감사하겠습니다.

TB_BENEFIT_MST 혜택마스터테이블 

TB_BENEFIT_CLICK_HIS 조회이력

TB_BENEFIT_COUPON 쿠폰테이블 

TB_BENEFIT_ENTRY 혜택테이블

조회이력테이블엔 쿠폰,혜택 둘다 들어가고있습니다.

 

by 마농 [2022.10.06 09:09:46]

a 와 y 가 1 대 다 조인이 되고
a 와 x 도 1 대 다 조인이 되면
결국 y, x 가 다대다 조인이 되어 카티션곱이 발생되어 건수가 뻥튀기 됩니다.
 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
SELECT a.benefit_seq
     , a.benefit_type_cd
     , a.benefit_titl
     , a.benefit_strt_dt
     , a.benefit_end_dt
     , COUNT(CASE b.m1 WHEN  1 THEN 1 END) click_cnt1
     , COUNT(CASE b.m2 WHEN  1 THEN 1 END)   use_cnt1
     , COUNT(CASE b.m1 WHEN  2 THEN 1 END) click_cnt2
     , COUNT(CASE b.m2 WHEN  2 THEN 1 END)   use_cnt2
     , COUNT(CASE b.m1 WHEN  3 THEN 1 END) click_cnt3
     , COUNT(CASE b.m2 WHEN  3 THEN 1 END)   use_cnt3
     , COUNT(CASE b.m1 WHEN  4 THEN 1 END) click_cnt4
     , COUNT(CASE b.m2 WHEN  4 THEN 1 END)   use_cnt4
     , COUNT(CASE b.m1 WHEN  5 THEN 1 END) click_cnt5
     , COUNT(CASE b.m2 WHEN  5 THEN 1 END)   use_cnt5
     , COUNT(CASE b.m1 WHEN  6 THEN 1 END) click_cnt6
     , COUNT(CASE b.m2 WHEN  6 THEN 1 END)   use_cnt6
     , COUNT(CASE b.m1 WHEN  7 THEN 1 END) click_cnt7
     , COUNT(CASE b.m2 WHEN  7 THEN 1 END)   use_cnt7
     , COUNT(CASE b.m1 WHEN  8 THEN 1 END) click_cnt8
     , COUNT(CASE b.m2 WHEN  8 THEN 1 END)   use_cnt8
     , COUNT(CASE b.m1 WHEN  9 THEN 1 END) click_cnt9
     , COUNT(CASE b.m2 WHEN  9 THEN 1 END)   use_cnt9
     , COUNT(CASE b.m1 WHEN 10 THEN 1 END) click_cnt10
     , COUNT(CASE b.m2 WHEN 10 THEN 1 END)   use_cnt10
     , COUNT(CASE b.m1 WHEN 11 THEN 1 END) click_cnt11
     , COUNT(CASE b.m2 WHEN 11 THEN 1 END)   use_cnt11
     , COUNT(CASE b.m1 WHEN 12 THEN 1 END) click_cnt12
     , COUNT(CASE b.m2 WHEN 12 THEN 1 END)   use_cnt12
     , COUNT(b.m1) sum_click_cnt
     , COUNT(b.m2) sum_use_cnt
  FROM tb_benefit_mst a
  LEFT OUTER JOIN
       (SELECT benefit_seq
             , MONTH(click_dttm) m1
             , null              m2
          FROM tb_benefit_click_his
         WHERE click_dttm >= '2022-01-01'
           AND click_dttm <  '2023-01-01'
         UNION ALL
        SELECT benefit_seq
             , null            m1
             , MONTH(reg_dttm) m2
          FROM tb_benefit_entry
         WHERE reg_dttm >= '2022-01-01'
           AND reg_dttm <  '2023-01-01'
         UNION ALL
        SELECT a.benefit_seq
             , null                     m1
             , MONTH(b.coupon_use_dttm) m2
          FROM tb_benefit_mst a
          JOIN tb_benefit_coupon b
            ON a.coupon_grp_id = b.coupon_grp_id
         WHERE b.coupon_use_yn = 'Y'
           AND b.coupon_use_dttm >= '2022-01-01'
           AND b.coupon_use_dttm <  '2023-01-01'
        ) b
    ON a.benefit_seq = b.benefit_seq
 WHERE a.benefit_type_cd != ''
 GROUP BY a.benefit_seq
     , a.benefit_type_cd
     , a.benefit_titl
     , a.benefit_strt_dt
     , a.benefit_end_dt
;

 

by mbj1 [2022.10.07 22:36:57]

답변 감사드립니다^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입