题解 | 统计借阅量
统计借阅量
https://www.nowcoder.com/practice/280ed56ab3ee49a4b2a4595d38e1d565
select t2.book_id book_id ,book_title ,count(case when year(borrow_date)=2023 and month(borrow_date)=2 then 1 end) feb_2023_borrows ,count(case when year(borrow_date)=2024 and month(borrow_date)=2 then 1 end) feb_2024_borrows ,count(case when year(borrow_date)=2024 and month(borrow_date)=1 then 1 end) jan_2024_borrows ,count(case when year(borrow_date)=2024 and month(borrow_date)=2 then 1 end) - count(case when year(borrow_date)=2023 and month(borrow_date)=2 then 1 end) yoy_delta ,count(case when year(borrow_date)=2024 and month(borrow_date)=2 then 1 end) - count(case when year(borrow_date)=2024 and month(borrow_date)=1 then 1 end) mom_delta ,COALESCE(round(count(case when year(borrow_date)=2023 and region='华北' then 1 end)/count(case when year(borrow_date)=2023 then 1 end)*100,2),0) north_pct_2023 ,COALESCE(round(count(case when year(borrow_date)=2023 and region='华南' then 1 end)/count(case when year(borrow_date)=2023 then 1 end)*100,2),0) south_pct_2023 ,COALESCE(round(count(case when year(borrow_date)=2023 and region='华东' then 1 end)/count(case when year(borrow_date)=2023 then 1 end)*100,2),0) east_pct_2023 from BorrowRecords t1 right join Books t2 on t2.book_id=t1.book_id left join Branches t3 on t1.branch_id=t3.branch_id group by book_id,book_title order by book_id,book_title
关键：count()/sum()与case when的结合，可计算一列中不同值的数目。
