题解 | 统计借阅量

select b.book_id, book_title, 
    count(case when date_format(borrow_date, "%Y-%m") = '2023-02' then 1 end) as feb_2023_borrows,
    count(case when date_format(borrow_date, "%Y-%m") = '2024-02' then 1 end) as feb_2024_borrows,
    count(case when date_format(borrow_date, "%Y-%m") = '2024-01' then 1 end) as jan_2024_borrows,
    count(case when date_format(borrow_date, "%Y-%m") = '2024-02' then 1 end) - count(case when date_format(borrow_date, "%Y-%m") = '2023-02' then 1 end) as yoy_delta,
    count(case when date_format(borrow_date, "%Y-%m") = '2024-02' then 1 end) - count(case when date_format(borrow_date, "%Y-%m") = '2024-01' then 1 end) as mom_delta,
    round(ifnull(count(case when year(borrow_date) = '2023' and region = '华北' then 1 end) / count(case when year(borrow_date) = '2023' then 1 end)*100, 0), 2) as north_pct_2023,
    round(ifnull(count(case when year(borrow_date) = '2023' and region = '华南' then 1 end) / count(case when year(borrow_date) = '2023' then 1 end)*100, 0), 2) as south_pct_2023,
    round(ifnull(count(case when year(borrow_date) = '2023' and region = '华东' then 1 end) / count(case when year(borrow_date) = '2023' then 1 end)*100, 0), 2) as east_pct_2023
from Books b 
left join BorrowRecords r on b.book_id = r.book_id
left join Branches c on r.branch_id = c.branch_id
group by b.book_id, book_title