데이터베이스 질문과 답변
select tbl.menu_id,tbl.dt,tbl.access_device_nm,COALESCE (tb2.cnt,0) as cnt from (
select a.menu_id,b.dt,c.ACCESS_DEVICE_NM from MENU a cross join
(
with recursive test as (
select '2024-05-01' as dt
union all
select DATE_ADD(dt,interval 1 day)
from test
where dt < '2024-06-01'
)
select * from test
) as b
cross join
(
select 'Android' as 'ACCESS_DEVICE_NM' union all select 'IOS' union all select 'WEB'
) as c
order by a.menu_id,b.dt,c.access_device_NM
) as tbl left join(
select ACCESS_MENU_ID as menu_id ,ACCESS_DEVICE_MM ,DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d') dt ,count(*) as cnt from MENU_ACCESS_HISTORY MAH
where DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d') between '2024-05-01' and '2024-06-01'
group by dt,ACCESS_MENU_ID ,ACCESS_DEVICE_MM
order by DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d')
) as tb2
on tbl.menu_id = tb2.menu_id
and tbl.dt = tb2.dt
and tbl.ACCESS_DEVICE_NM = tb2.ACCESS_DEVICE_MM
where tbl.menu_id ='MENU_00001'
order by tbl.menu_id,tbl.dt,tbl.ACCESS_DEVICE_NM
을 했을떄 첨부파일과 같은 결과가 발생합니다 ..(결과 222)
select tbl3.menu_id,tbl3.access_device_nm,
case when day(tbl3.dt) = 1 then TBL3.cnt else 0 end as '1일',
CASE WHEN DAY(tbl3.dt) = 2 THEN tbl3.cnt else 0 END AS '2일',
CASE WHEN DAY(tbl3.dt) = 3 THEN tbl3.cnt else 0 END AS '3일',
CASE WHEN DAY(tbl3.dt) = 4 THEN tbl3.cnt else 0 END AS '4일',
CASE WHEN DAY(tbl3.dt) = 5 THEN tbl3.cnt else 0 END AS '5일',
CASE WHEN DAY(tbl3.dt) = 6 THEN tbl3.cnt else 0 END AS '6일',
CASE WHEN DAY(tbl3.dt) = 7 THEN tbl3.cnt else 0 END AS '7일',
CASE WHEN DAY(tbl3.dt) = 8 THEN tbl3.cnt else 0 END AS '8일',
CASE WHEN DAY(tbl3.dt) = 9 THEN tbl3.cnt else 0 END AS '9일',
CASE WHEN DAY(tbl3.dt) = 10 THEN tbl3.cnt else 0 END AS '10일',
CASE WHEN DAY(tbl3.dt) = 11 THEN tbl3.cnt else 0 END AS '11일',
CASE WHEN DAY(tbl3.dt) = 12 THEN tbl3.cnt else 0 END AS '12일',
CASE WHEN DAY(tbl3.dt) = 13 THEN tbl3.cnt else 0 END AS '13일',
CASE WHEN DAY(tbl3.dt) = 14 THEN tbl3.cnt else 0 END AS '14일',
CASE WHEN DAY(tbl3.dt) = 15 THEN tbl3.cnt else 0 END AS '15일',
CASE WHEN DAY(tbl3.dt) = 16 THEN tbl3.cnt else 0 END AS '16일',
CASE WHEN DAY(tbl3.dt) = 17 THEN tbl3.cnt else 0 END AS '17일',
CASE WHEN DAY(tbl3.dt) = 18 THEN tbl3.cnt else 0 END AS '18일',
CASE WHEN DAY(tbl3.dt) = 19 THEN tbl3.cnt else 0 END AS '19일',
CASE WHEN DAY(tbl3.dt) = 20 THEN tbl3.cnt else 0 END AS '20일',
CASE WHEN DAY(tbl3.dt) = 21 THEN tbl3.cnt else 0 END AS '21일',
CASE WHEN DAY(tbl3.dt) = 22 THEN tbl3.cnt else 0 END AS '22일',
CASE WHEN DAY(tbl3.dt) = 23 THEN tbl3.cnt else 0 END AS '23일',
CASE WHEN DAY(tbl3.dt) = 24 THEN tbl3.cnt else 0 END AS '24일',
CASE WHEN DAY(tbl3.dt) = 25 THEN tbl3.cnt else 0 END AS '25일',
CASE WHEN DAY(tbl3.dt) = 26 THEN tbl3.cnt else 0 END AS '26일',
CASE WHEN DAY(tbl3.dt) = 27 THEN tbl3.cnt else 0 END AS '27일',
CASE WHEN DAY(tbl3.dt) = 28 THEN tbl3.cnt else 0 END AS '28일',
CASE WHEN DAY(tbl3.dt) = 29 THEN tbl3.cnt else 0 END AS '29일',
CASE WHEN DAY(tbl3.dt) = 30 THEN tbl3.cnt else 0 END AS '30일',
CASE WHEN DAY(tbl3.dt) = 31 THEN tbl3.cnt else 0 END AS '31일'
from
(
select tbl.menu_id,tbl.dt,tbl.access_device_nm,COALESCE (tb2.cnt,0) as cnt from (
select a.menu_id,b.dt,c.ACCESS_DEVICE_NM from MENU a cross join
(
with recursive test as (
select '2024-05-01' as dt
union all
select DATE_ADD(dt,interval 1 day)
from test
where dt < '2024-06-01'
)
select * from test
) as b
cross join
(
select 'Android' as 'ACCESS_DEVICE_NM' union all select 'IOS' union all select 'WEB'
) as c
order by a.menu_id,b.dt,c.access_device_NM
) as tbl left join(
select ACCESS_MENU_ID as menu_id ,ACCESS_DEVICE_MM ,DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d') dt ,count(*) as cnt from MENU_ACCESS_HISTORY MAH
where DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d') between '2024-05-01' and '2024-06-01'
group by dt,ACCESS_MENU_ID ,ACCESS_DEVICE_MM
order by DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d')
) as tb2
on tbl.menu_id = tb2.menu_id
and tbl.dt = tb2.dt
and tbl.ACCESS_DEVICE_NM = tb2.ACCESS_DEVICE_MM
order by tbl.menu_id,tbl.dt,tbl.ACCESS_DEVICE_NM
) as tbl3
러 피벗을 했는데 결과가 이상하게 나옵니다 .(결과333)
원래 만들고 싶은건
1일 , 2일 ~~~31일
MENU_00001 Android 1 3 4
MENU_00001 IOS
MENU_00001 WEB
이런식으로 한번만 나와야 하는데 지금 여러번이 나옵니다 .혹시 어떤 식으로 바꿔야 할까요 ?
SELECT a.menu_id
, b.access_device_nm
, COUNT(CASE DAY(c.access_time) WHEN 1 THEN 1 END) "1일"
, COUNT(CASE DAY(c.access_time) WHEN 2 THEN 1 END) "2일"
, COUNT(CASE DAY(c.access_time) WHEN 3 THEN 1 END) "3일"
, COUNT(CASE DAY(c.access_time) WHEN 4 THEN 1 END) "4일"
, COUNT(CASE DAY(c.access_time) WHEN 5 THEN 1 END) "5일"
, COUNT(CASE DAY(c.access_time) WHEN 6 THEN 1 END) "6일"
, COUNT(CASE DAY(c.access_time) WHEN 7 THEN 1 END) "7일"
, COUNT(CASE DAY(c.access_time) WHEN 8 THEN 1 END) "8일"
, COUNT(CASE DAY(c.access_time) WHEN 9 THEN 1 END) "9일"
, COUNT(CASE DAY(c.access_time) WHEN 10 THEN 1 END) "10일"
, COUNT(CASE DAY(c.access_time) WHEN 11 THEN 1 END) "11일"
, COUNT(CASE DAY(c.access_time) WHEN 12 THEN 1 END) "12일"
, COUNT(CASE DAY(c.access_time) WHEN 13 THEN 1 END) "13일"
, COUNT(CASE DAY(c.access_time) WHEN 14 THEN 1 END) "14일"
, COUNT(CASE DAY(c.access_time) WHEN 15 THEN 1 END) "15일"
, COUNT(CASE DAY(c.access_time) WHEN 16 THEN 1 END) "16일"
, COUNT(CASE DAY(c.access_time) WHEN 17 THEN 1 END) "17일"
, COUNT(CASE DAY(c.access_time) WHEN 18 THEN 1 END) "18일"
, COUNT(CASE DAY(c.access_time) WHEN 19 THEN 1 END) "19일"
, COUNT(CASE DAY(c.access_time) WHEN 20 THEN 1 END) "20일"
, COUNT(CASE DAY(c.access_time) WHEN 21 THEN 1 END) "21일"
, COUNT(CASE DAY(c.access_time) WHEN 22 THEN 1 END) "22일"
, COUNT(CASE DAY(c.access_time) WHEN 23 THEN 1 END) "23일"
, COUNT(CASE DAY(c.access_time) WHEN 24 THEN 1 END) "24일"
, COUNT(CASE DAY(c.access_time) WHEN 25 THEN 1 END) "25일"
, COUNT(CASE DAY(c.access_time) WHEN 26 THEN 1 END) "26일"
, COUNT(CASE DAY(c.access_time) WHEN 27 THEN 1 END) "27일"
, COUNT(CASE DAY(c.access_time) WHEN 28 THEN 1 END) "28일"
, COUNT(CASE DAY(c.access_time) WHEN 29 THEN 1 END) "29일"
, COUNT(CASE DAY(c.access_time) WHEN 30 THEN 1 END) "30일"
, COUNT(CASE DAY(c.access_time) WHEN 31 THEN 1 END) "31일"
FROM menu a
CROSS JOIN
(SELECT 'Android' access_device_nm
UNION ALL SELECT 'IOS'
UNION ALL SELECT 'WEB'
) b
LEFT OUTER JOIN menu_access_history c
ON a.menu_id = c.access_menu_id
AND b.access_device_mm = c.access_device_mm
AND c.access_time >= '2024-05-01'
AND c.access_time < '2024-06-01'
WHERE a.menu_id = 'MENU_00001'
GROUP BY a.menu_id, b.access_device_nm
ORDER BY a.menu_id, b.access_device_nm
;
1. 원하는 결과가 안나오는 이유는
- GROUP BY SUM 을 안헤서 그렇습니다.
- GROUP BY menu_id, access_device_nm 하고 SUM(CASE ...) 하시면 됩니다.
2. 쿼리 개선
- 222 이라는 결과를 얻기 위해서는 달력 데이터를 만들고 조인에 이용해야 하지만
- 333 이라는 결과를 얻기 위해서는 222 라는 중간 과정이 필요하지 않습니다.
- 달력 테이블 없이 바로 추출하는 방식으로 개선해야 합니다.
저 혹시 궁금한게 그럼 rollup을 해서 각각의 메뉴마다 합계를 내고 싶다면 저기 case문에 count 대신에 ,sum을 넣어도 상관없을까요 /
select IFNULL( tbl3.menu_id,'전체메뉴'),IFNULL( tbl3.access_device_nm,'합계'),
sum(case when day(tbl3.dt) = 1 then TBL3.cnt else 0 end) as '1일',
sum(CASE WHEN DAY(tbl3.dt) = 2 THEN tbl3.cnt else 0 end) AS '2일',
sum(CASE WHEN DAY(tbl3.dt) = 3 THEN tbl3.cnt else 0 END) AS '3일',
sum(CASE WHEN DAY(tbl3.dt) = 4 THEN tbl3.cnt else 0 end) AS '4일',
sum(CASE WHEN DAY(tbl3.dt) = 5 THEN tbl3.cnt else 0 end) AS '5일',
sum(CASE WHEN DAY(tbl3.dt) = 6 THEN tbl3.cnt else 0 end) AS '6일',
sum(CASE WHEN DAY(tbl3.dt) = 7 THEN tbl3.cnt else 0 end) AS '7일',
sum(CASE WHEN DAY(tbl3.dt) = 8 THEN tbl3.cnt else 0 end) AS '8일',
sum(CASE WHEN DAY(tbl3.dt) = 9 THEN tbl3.cnt else 0 end) AS '9일',
sum(CASE WHEN DAY(tbl3.dt) = 10 THEN tbl3.cnt else 0 end) AS '10일',
sum(CASE WHEN DAY(tbl3.dt) = 11 THEN tbl3.cnt else 0 end) AS '11일',
sum(CASE WHEN DAY(tbl3.dt) = 12 THEN tbl3.cnt else 0 end) AS '12일',
sum(CASE WHEN DAY(tbl3.dt) = 13 THEN tbl3.cnt else 0 end) AS '13일',
sum(CASE WHEN DAY(tbl3.dt) = 14 THEN tbl3.cnt else 0 end) AS '14일',
sum(CASE WHEN DAY(tbl3.dt) = 15 THEN tbl3.cnt else 0 end) AS '15일',
sum(CASE WHEN DAY(tbl3.dt) = 16 THEN tbl3.cnt else 0 end) AS '16일',
sum(CASE WHEN DAY(tbl3.dt) = 17 THEN tbl3.cnt else 0 end) AS '17일',
sum(CASE WHEN DAY(tbl3.dt) = 18 THEN tbl3.cnt else 0 end) AS '18일',
sum(CASE WHEN DAY(tbl3.dt) = 19 THEN tbl3.cnt else 0 end) AS '19일',
sum(CASE WHEN DAY(tbl3.dt) = 20 THEN tbl3.cnt else 0 end) AS '20일',
sum(CASE WHEN DAY(tbl3.dt) = 21 THEN tbl3.cnt else 0 end) AS '21일',
sum(CASE WHEN DAY(tbl3.dt) = 22 THEN tbl3.cnt else 0 end) AS '22일',
sum(CASE WHEN DAY(tbl3.dt) = 23 THEN tbl3.cnt else 0 end) AS '23일',
sum(CASE WHEN DAY(tbl3.dt) = 24 THEN tbl3.cnt else 0 end) AS '24일',
sum(CASE WHEN DAY(tbl3.dt) = 25 THEN tbl3.cnt else 0 end) AS '25일',
sum(CASE WHEN DAY(tbl3.dt) = 26 THEN tbl3.cnt else 0 end) AS '26일',
sum(CASE WHEN DAY(tbl3.dt) = 27 THEN tbl3.cnt else 0 end) AS '27일',
sum(CASE WHEN DAY(tbl3.dt) = 28 THEN tbl3.cnt else 0 end) AS '28일',
sum(CASE WHEN DAY(tbl3.dt) = 29 THEN tbl3.cnt else 0 end) AS '29일',
sum(CASE WHEN DAY(tbl3.dt) = 30 THEN tbl3.cnt else 0 end) AS '30일',
sum(CASE WHEN DAY(tbl3.dt) = 31 THEN tbl3.cnt else 0 end) AS '31일'
from
(
select tbl.menu_id,tbl.dt,tbl.access_device_nm,tb2.cnt as cnt from (
select a.menu_id,b.dt,c.ACCESS_DEVICE_NM from MENU a cross join
(
with recursive test as (
select '2024-05-01' as dt
union all
select DATE_ADD(dt,interval 1 day)
from test
where dt < '2024-06-01'
)
select * from test
) as b
cross join
(
select 'Android' as 'ACCESS_DEVICE_NM' union all select 'IOS' union all select 'WEB'
) as c
order by a.menu_id,b.dt,c.access_device_NM
) as tbl left join(
select ACCESS_MENU_ID as menu_id ,ACCESS_DEVICE_MM ,DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d') dt ,count(*) as cnt from MENU_ACCESS_HISTORY MAH
where DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d') between '2024-05-01' and '2024-06-01'
group by dt,ACCESS_MENU_ID ,ACCESS_DEVICE_MM
order by DATE_FORMAT(ACCESS_TIME,'%Y-%m-%d')
) as tb2
on tbl.menu_id = tb2.menu_id
and tbl.dt = tb2.dt
and tbl.ACCESS_DEVICE_NM = tb2.ACCESS_DEVICE_MM
order by tbl.menu_id,tbl.dt,tbl.ACCESS_DEVICE_NM
) as tbl3
group by tbl3.menu_id,tbl3.access_device_nm with ROLLUP
요런식으로 하긴 했습니다.
롤업을 한다고 해서 count 를 sum 으로 바꿀 필요는 없습니다.
count 든 sum 이든 롤업은 그대로 동작합니다.
지금 쿼리는 불필요한 부분, 비효율적인 부분, 잘못된 부분, 범용적이지 못한 부분(비표준) 등이 존재하므로
제가 제시한 쿼리로 대체하는게 좋을 듯 합니다.
감사합니다!@
) 버튼을 클릭하여 작성 하시면 됩니다.