mysql 월별 count 통계!

일별 통계를 하면서 월별도 같이 작업했습니다 일별과 거이 비슷하지만 조금은 쿼리가 달라집니다.!

월별은 년도와 월만 입력 받아 12개월 최대로 했습니다.

 

SELECT  B.api_id, c.api_nm,
    count(month01)+ count(month02)+ count(month03)+ count(month04)+ count(month05)+
    count(month06)+ count(month07)+ count(month08)+ count(month09)+ count(month10)+
    count(month11)+ count(month12) AS total,
    IFNULL(count(month01),0) month01,IFNULL(count(month02),0) month02,IFNULL(count(month03),0) month03,IFNULL(count(month04),0) month04,IFNULL(count(month05),0) month05,
    IFNULL(count(month06),0) month06,IFNULL(count(month07),0) month07,IFNULL(count(month08),0) month08,IFNULL(count(month09),0) month09,IFNULL(count(month10),0) month10,
    IFNULL(count(month11),0) month11,IFNULL(count(month12),0) month12
FROM(
    SELECT
         api_id,DATE_FORMAT(requested_at, '%Y-%m-%d') AS requested_at,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_FORMAT('2020-01-01', '%Y-%m-%d'),7),2) then count(api_id) end month01,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 1 MONTH),7),2) then count(api_id) end month02,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 2 MONTH),7),2) then count(api_id) end month03,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 3 MONTH),7),2) then count(api_id) end month04,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 4 MONTH),7),2) then count(api_id) end month05,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 5 MONTH),7),2) then count(api_id) end month06,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 6 MONTH),7),2) then count(api_id) end month07,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 7 MONTH),7),2) then count(api_id) end month08,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 8 MONTH),7),2) then count(api_id) end month09,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 9 MONTH),7),2) then count(api_id) end month10,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 10 MONTH),7),2) then count(api_id) end month11,
        case when RIGHT(LEFT(DATE_FORMAT(requested_at, '%Y-%m-%d'),7),2) = RIGHT(LEFT(DATE_ADD(DATE_FORMAT('2020-01-01', '%Y-%m-%d'), INTERVAL 11 MONTH),7),2) then count(api_id) end month12
    FROM api_access_histories
    WHERE DATE_FORMAT(requested_at, '%Y-%m-%d') BETWEEN  '2020-01-01' AND '2020-12-31'
    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
 

 

화면과 결과는 이렇씁니다. 전에 작성한 일별 쿼리와 비슷합니다.