데이터베이스 질문과 답변
MySql 5.7버전입니다.
순서대로
Temp_date Table
User Table
login_log Table
pa Table
입니다
Temp_date 테이블은 날짜 데이터를 더미로 생성해 놓은 테이블이고
user,login_log,pa 테이블을 합쳐서
제일 마지막에있는 결과처럼 나오게 하고 싶습니다
제가 작성한 쿼리인데 mysql 8.0 버전에서는 정상적으로 동작하지만
5.7버전을 사용하면 조회시간이 너무 오래 걸립니다.
해결할 방법이 있을까요?
select * from
(select
date_format(td.day,'%Y-%m') date,
count(distinct(lg.user_no)) accessCount,
count(u.regidate) signupCount,
count(pa.regidate) subscriptionCount,
IFNULL(sum(pa.amount), 0) subscriptionAmount
from
temp_date td
left join
login_log lg on (date_format(td.day,'%Y-%m-%d') = date_format(lg.login_time,'%Y-%m-%d'))
left join
user u on date_format(td.day,'%Y-%m-%d') = (date_format(u.regidate,'%Y-%m-%d'))
left join
product_application pa on date_format(td.day,'%Y-%m-%d') = (date_format(pa.regidate,'%Y-%m-%d'))
group by date) a
where
date between '2021-01' and '2021-12'
by 마농
[2022.04.21 13:06:11]
SELECT DATE_FORMAT(a.day, '%Y-%m') ym
, IFNULL(accessCount , 0) accessCount
, IFNULL(accessCount_distinct, 0) accessCount_distinct
, IFNULL(signupCount , 0) signupCount
, IFNULL(subscriptionCount , 0) subscriptionCount
, IFNULL(subscriptionAmount , 0) subscriptionAmount
FROM temp_date a
LEFT OUTER JOIN
(SELECT DATE_FORMAT(login_time, '%Y-%m') ym
, COUNT(*) accessCount
, COUNT(DISTINCT user_no) accessCount_distinct
FROM login_log
WHERE login_time >= '2021-01-01'
AND login_time < '2022-01-01'
GROUP BY DATE_FORMAT(login_time, '%Y-%m')
) b
ON DATE_FORMAT(a.day, '%Y-%m') = b.ym
LEFT OUTER JOIN
(SELECT DATE_FORMAT(regidate, '%Y-%m') ym
, COUNT(*) signupCount
FROM user
WHERE regidate >= '2021-01-01'
AND regidate < '2022-01-01'
GROUP BY DATE_FORMAT(regidate, '%Y-%m')
) c
ON DATE_FORMAT(a.day, '%Y-%m') = c.ym
LEFT OUTER JOIN
(SELECT DATE_FORMAT(regidate, '%Y-%m') ym
, COUNT(*) subscriptionCount
, SUM(amount) subscriptionAmount
FROM product_application
WHERE regidate >= '2021-01-01'
AND regidate < '2022-01-01'
GROUP BY DATE_FORMAT(regidate, '%Y-%m')
) d
ON DATE_FORMAT(a.day, '%Y-%m') = d.ym
WHERE a.day >= '2021-01-01'
AND a.day < '2022-01-01'
AND DATE_FORMAT(a.day, '%d') = '01'
;
by 구루루룽
[2022.04.21 14:21:44]
쿼리 응용해서 해보겠습니다. 감사합니다!!!
) 버튼을 클릭하여 작성 하시면 됩니다.