题解 | 统计借阅量

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