题解 | 统计借阅量

with t1 as (
select br.book_id,book_title,
sum(if(borrow_date like '2023-02%',1,0)) as feb_2023_borrows,
sum(if(borrow_date like '2024-02%',1,0)) as feb_2024_borrows,
sum(if(borrow_date like '2024-01%',1,0)) as jan_2024_borrows
from BorrowRecords as br 
left join Books as b on b.book_id = br.book_id
group by br.book_id
),
t2 as (
select br.book_id,book_title,
round(sum(if(region='华北',1,0))/count(br.book_id)*100,2) as north_pct_2023,
round(sum(if(region='华东',1,0))/count(br.book_id)*100,2) as east_pct_2023,
round(sum(if(region='华南',1,0))/count(br.book_id)*100,2) as south_pct_2023
from BorrowRecords as br 
left join Books as b on b.book_id = br.book_id
left join Branches as r on r.branch_id = br.branch_id
where year(borrow_date) = 2023
group by br.book_id
)
select 
b.book_id,b.book_title,
coalesce(feb_2023_borrows,0) as feb_2023_borrows,
coalesce(feb_2024_borrows,0) as feb_2024_borrows,
coalesce(jan_2024_borrows,0) as jan_2024_borrows,
coalesce(feb_2024_borrows,0) - coalesce(feb_2023_borrows,0) as yoy_delta,
coalesce(feb_2024_borrows,0) - coalesce(jan_2024_borrows,0) as mom_delta,
coalesce(north_pct_2023,0.00) as north_pct_2023,
coalesce(south_pct_2023,0.00) as south_pct_2023,
coalesce(east_pct_2023,0.00) as east_pct_2023
from Books as b
left join t1 on t1.book_id = b.book_id
left join t2 on b.book_id = t2.book_id
order by b.book_id