mysql 일별 count 통계 쿼리

일별로 사용한 count를 가져와보자 하는 생각으로 테스트 해봣습니다. 옛날에 짠 쿼리이고 검색하며 막 조합해서 

쿼리 작성 하였습니다.

기간을 선택하게 하고 최대 30일까지만 선택가능하게 하여 날짜를 입력 받아 데이터를 가져오는 형식입니다. 

 

select  B.api_id, c.api_nm,
    count(day01)+count(day02)+count(day03)+count(day04)+count(day05)+
    count(day06)+count(day07)+count(day08)+count(day09)+count(day10)+
    count(day11)+count(day12)+count(day13)+count(day14)+count(day15)+
    count(day16)+count(day17)+count(day18)+count(day19)+count(day20)+
    count(day21)+count(day22)+count(day23)+count(day24)+count(day25)+
    count(day26)+count(day27)+count(day28)+count(day29)+count(day30)+count(day31) AS total,
    IFNULL(count(day01),0) day01,IFNULL(count(day02),0) day02,IFNULL(count(day03),0) day03,IFNULL(count(day04),0) day04,IFNULL(count(day05),0) day05,
    IFNULL(count(day06),0) day06,IFNULL(count(day07),0) day07,IFNULL(count(day08),0) day08,IFNULL(count(day09),0) day09,IFNULL(count(day10),0) day10,
    IFNULL(count(day11),0) day11,IFNULL(count(day12),0) day12,IFNULL(count(day13),0) day13,IFNULL(count(day14),0) day14,IFNULL(count(day15),0) day15,
    IFNULL(count(day16),0) day16,IFNULL(count(day17),0) day17,IFNULL(count(day18),0) day18,IFNULL(count(day19),0) day19,IFNULL(count(day20),0) day20,
    IFNULL(count(day21),0) day21,IFNULL(count(day22),0) day22,IFNULL(count(day23),0) day23,IFNULL(count(day24),0) day24,IFNULL(count(day25),0) day25,
    IFNULL(count(day26),0) day26,IFNULL(count(day27),0) day27,IFNULL(count(day28),0) day28,IFNULL(count(day29),0) day29,IFNULL(count(day30),0) day30,
    IFNULL(count(day31),0) day31
from(
    SELECT
        api_id,DATE_FORMAT(requested_at, '%Y-%m-%d') AS requested_at,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_FORMAT('2020-11-01', '%Y-%m-%d')  then count(api_id) end day01,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 1 DAY)  then count(api_id) end day02,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 2 DAY)  then count(api_id) end day03,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 3 DAY)  then count(api_id) end day04,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 4 DAY)  then count(api_id) end day05,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 5 DAY)  then count(api_id) end day06,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 6 DAY)  then count(api_id) end day07,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 7 DAY)  then count(api_id) end day08,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 8 DAY)  then count(api_id) end day09,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 9 DAY)  then count(api_id) end day10,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 10 DAY)  then count(api_id) end day11,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 11 DAY)  then count(api_id) end day12,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 12 DAY)  then count(api_id) end day13,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 13 DAY)  then count(api_id) end day14,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 14 DAY)  then count(api_id) end day15,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 15 DAY)  then count(api_id) end day16,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 16 DAY)  then count(api_id) end day17,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 17 DAY)  then count(api_id) end day18,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 18 DAY)  then count(api_id) end day19,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 19 DAY)  then count(api_id) end day20,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 20 DAY)  then count(api_id) end day21,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 21 DAY)  then count(api_id) end day22,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 22 DAY)  then count(api_id) end day23,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 23 DAY)  then count(api_id) end day24,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 24 DAY)  then count(api_id) end day25,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 25 DAY)  then count(api_id) end day26,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 26 DAY)  then count(api_id) end day27,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 27 DAY)  then count(api_id) end day28,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 28 DAY)  then count(api_id) end day29,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 29 DAY)  then count(api_id) end day30,
        case when DATE_FORMAT(requested_at, '%Y-%m-%d') = DATE_ADD(DATE_FORMAT('2020-11-01', '%Y-%m-%d'), INTERVAL 30 DAY)  then count(api_id) end day31
    from api_access_histories
    WHERE DATE_FORMAT(requested_at, '%Y-%m-%d') BETWEEN  '2020-11-01'
    and '2020-12-01'
    GROUP BY api_id, DATE_FORMAT(requested_at, '%Y-%m-%d')
)A
left join api_access_histories B ON A.api_id = B.api_id AND DATE_FORMAT(A.requested_at, '%Y-%m-%d') = DATE_FORMAT(B.requested_at, '%Y-%m-%d')
LEFT JOIN tb__api_info C ON a.api_id = c.api_id
group by B.api_id

 

화면과 조회 결과입니다.!

쿼리는 조금 어려워하는 편이긴한데 막조합해서 짜느라 조금 이상해보이긴 합니다...ㅎ

혹시 더좋은 방법 알고 계시면 알려주시면 감사합니다.!