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

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


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 가 다대다 조인이 되어 카티션곱이 발생되어 건수가 뻥튀기 됩니다.
 

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() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입