피벗 관련 질문드립니다 . 0 5 4,967

by yellowplac [MySQL] [2024.06.25 14:50:46]


결과222.png (91,831Bytes)
결과3333333.png (50,619Bytes)

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

이런식으로 한번만 나와야 하는데 지금 여러번이 나옵니다 .혹시 어떤 식으로 바꿔야 할까요 ?

 

 

 

 

by 마농 [2024.06.25 15:56:52]
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
;

 


by 마농 [2024.06.25 16:06:04]

1. 원하는 결과가 안나오는 이유는
- GROUP BY SUM 을 안헤서 그렇습니다.
- GROUP BY menu_id, access_device_nm 하고 SUM(CASE ...) 하시면 됩니다.
2. 쿼리 개선
- 222 이라는 결과를 얻기 위해서는 달력 데이터를 만들고 조인에 이용해야 하지만
- 333 이라는 결과를 얻기 위해서는 222 라는 중간 과정이 필요하지 않습니다.
- 달력 테이블 없이 바로 추출하는 방식으로 개선해야 합니다.


by yellowplac [2024.06.25 16:13:49]

저 혹시 궁금한게 그럼 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 


요런식으로 하긴 했습니다. 

by 마농 [2024.06.25 16:39:31]

롤업을 한다고 해서 count 를 sum 으로 바꿀 필요는 없습니다.
count 든 sum 이든 롤업은 그대로 동작합니다.
지금 쿼리는 불필요한 부분, 비효율적인 부분, 잘못된 부분, 범용적이지 못한 부분(비표준) 등이 존재하므로
제가 제시한 쿼리로 대체하는게 좋을 듯 합니다.


by yellowplac [2024.06.25 17:03:31]

감사합니다!@

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