题解 | 统计借阅量

select
book_id,
book_title,
ifnull( count( case when borrow_date between '2023-02-01' and '2023-02-28' then record_id end ),0) as feb_2023_borrows,
ifnull( count( case when borrow_date between '2024-02-01' and '2024-02-29' then record_id end ),0) as feb_2024_borrows,
ifnull( count( case when borrow_date between '2024-01-01' and '2024-01-31' then record_id end ),0) as jan_2024_borrows,
ifnull( count( case when borrow_date between '2024-02-01' and '2024-02-29' then record_id end ) -count(case when borrow_date between '2023-02-01' and '2023-02-28' then record_id end ), 0) as yoy_delta,
ifnull(count( case when borrow_date between '2024-02-01' and '2024-02-29' then record_id end) -count(case when borrow_date between '2024-01-01' and '2024-01-31' then record_id end ), 0) as mom_delta,
round( ifnull( count( case when region = '华北' and YEAR( borrow_date) = 2023 then record_id end ) / count( case when YEAR( borrow_date) = 2023 then record_id end ), 0 )*100 , 2 ) as north_pct_2023,
round( ifnull( count( case when region = '华南' and YEAR( borrow_date) = 2023 then record_id end ) / count( case when YEAR( borrow_date) = 2023 then record_id end ), 0 )*100 , 2 ) as south_pct_2023,
round( ifnull( count( case when region = '华东' and YEAR( borrow_date) = 2023 then record_id end ) / count( case when YEAR( borrow_date) = 2023 then record_id end ), 0 )*100 , 2 ) as east_pct_2023
from Books b left join BorrowRecords br using(book_id)
left join Branches using(branch_id) 
group by book_id,book_title
order by book_id,book_title;