데이터베이스 질문과 답변
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]
답변 감사드립니다^^
) 버튼을 클릭하여 작성 하시면 됩니다.