题解 | 统计借阅量

with t as (select bk.book_id as book_id,book_title,record_id,borrow_date,bc.branch_id as branch_id,branch_name from Books bk
left join BorrowRecords br using(book_id)
left join Branches bc using(branch_id))

select book_id,book_title,
count(if(date_format(borrow_date,'%Y-%m') = '2023-02',record_id,null)) as feb_2023_borrows,
count(if(date_format(borrow_date,'%Y-%m') = '2024-02',record_id,null)) as feb_2024_borrows,
count(if(date_format(borrow_date,'%Y-%m') = '2024-01',record_id,null)) as jan_2024_borrows,
count(if(date_format(borrow_date,'%Y-%m') = '2024-02',record_id,null)) - count(if(date_format(borrow_date,'%Y-%m') = '2023-02',record_id,null)) as yoy_delta,
count(if(date_format(borrow_date,'%Y-%m') = '2024-02',record_id,null)) - count(if(date_format(borrow_date,'%Y-%m') = '2024-01',record_id,null)) as mom_delta,
ifnull(round(count(if(branch_id = 1 and year(borrow_date) = 2023,record_id,null))/ count(if(year(borrow_date) = 2023,record_id,null))*100,2),0) as north_pct_2023,
ifnull(round(count(if(branch_id = 3 and year(borrow_date) = 2023,record_id,null))/ count(if(year(borrow_date) = 2023,record_id,null))*100,2),0) as south_pct_2023,
ifnull(round(count(if(branch_id = 2 and year(borrow_date) = 2023,record_id,null))/ count(if(year(borrow_date) = 2023,record_id,null))*100,2),0) as east_pct_2023
from t
group by book_id,book_title
order by book_id,book_title