题解 | 统计借阅量

--看的难其实理解逻辑很简单，要啥给啥，然后一计算就完事。
--需要把没有book_id全部输出

with tiaojian as (
select 
bs.book_id,
bs.book_title,
count(case when date_format(borrow_date,"%Y%m")=202302 then record_id end) as feb_2023_borrows,
count(case when date_format(borrow_date,"%Y%m")=202402 then record_id end) as feb_2024_borrows,
count(case when date_format(borrow_date,"%Y%m")=202401 then record_id end) as jan_2024_borrows,
count(case when  year(borrow_date)=2023  and region='华北' then record_id end) as north_2023,
count(case when  year(borrow_date)=2023  and region='华南' then record_id end) as south_2023,
count(case when  year(borrow_date)=2023  and region='华东' then record_id end) as east_2023,
count(case when  year(borrow_date)=2023  then record_id end) as pct_2023
from BorrowRecords b inner join Books bs on 
b.book_id=bs.book_id
inner join 
Branches bes on bes.branch_id=b.branch_id
group by bs.book_id
)


select 
bs.book_id,
bs.book_title,
ifnull(feb_2023_borrows,0) as feb_2023_borrows,
ifnull(feb_2024_borrows,0) as feb_2024_borrows,
ifnull(jan_2024_borrows,0) as jan_2024_borrows,
ifnull(feb_2024_borrows,0) - ifnull(feb_2023_borrows,0) as yoy_delta,
ifnull(feb_2024_borrows,0) - ifnull(jan_2024_borrows,0) as mom_delta,
ifnull(
round(
(north_2023/pct_2023)*100,2),0) as north_pct_2023,
ifnull(
round(
(south_2023/pct_2023)*100,2),0) as south_pct_2023,
ifnull(
round(
(east_2023/pct_2023)*100,2),0) as east_pct_2023
from tiaojian t right join Books bs on bs.book_id=t.book_id
order by book_id