题解 | 统计借阅量

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