题解 | 统计借阅量

select 
    b1.book_id,book_title,
    ifnull(sum(if(borrow_date like '2023-02%',1,0)),0) as feb_2023_borrows,
    ifnull(sum(if(borrow_date like '2024-02%',1,0)),0) as feb_2024_borrows,
    ifnull(sum(if(borrow_date like '2024-01%',1,0)),0) as jan_2024_borrows,
    ifnull(sum(if(borrow_date like '2024-02%',1,0)),0)-ifnull(sum(if(borrow_date like '2023-02%',1,0)),0) as yoy_delta,
    ifnull(sum(if(borrow_date like '2024-02%',1,0)),0)-ifnull(sum(if(borrow_date like '2024-01%',1,0)),0) as mom_delta,
    ifnull(round(ifnull(sum(if(region='华北' and borrow_date like '2023%',1,0)),0)/sum(if(borrow_date like '2023%',1,0))*100,2),0) as north_pct_2023,
    ifnull(round(ifnull(sum(if(region='华南' and borrow_date like '2023%',1,0)),0)/sum(if(borrow_date like '2023%',1,0))*100,2),0) as south_pct_2023,
    ifnull(round(ifnull(sum(if(region='华东' and borrow_date like '2023%',1,0)),0)/sum(if(borrow_date like '2023%',1,0))*100,2),0) as east_pct_2023
from BorrowRecords b3
right join Books b1 on b3.book_id=b1.book_id
left join Branches b2 on b2.branch_id=b3.branch_id
group by b1.book_id,book_title
order by b1.book_id,book_title