题解 | 统计借阅量

易错点：

1. 确定题干要求：保留2位小数
2. 确定是否有排序要求
3. JOIN时需要考虑是否可能出现空值

with t1 as (# 2023 年 2 月借阅量
        select book_id
        ,book_title
        ,ifnull(count(record_id),0) feb_2023_borrows
        from Books
        left join BorrowRecords using(book_id)
        where year(borrow_date)=2023
        and month(borrow_date)=2
        group by book_id,book_title 
),
t2 as ( # 2024 年 2 月借阅量
        select book_id
        ,book_title
        ,ifnull(count(record_id),0)  feb_2024_borrows
        from Books
        left join BorrowRecords using(book_id)
        where year(borrow_date)=2024
        and month(borrow_date)=2
        group by book_id,book_title
),
t3 as (# 2024 年 1 月借阅量
        select book_id
        ,book_title
        ,ifnull(count(record_id),0)  jan_2024_borrows
        from Books
        left join BorrowRecords using(book_id)
        where year(borrow_date)=2024
        and month(borrow_date)=1
        group by book_id,book_title
),
t4 as (# 23年总借阅量
        select book_id
        ,book_title
        ,count(if(region='华北',record_id,null))/count(record_id)*100  north_pct_2023
        ,count(if(region='华南',record_id,null))/count(record_id)*100 south_pct_2023
        ,count(if(region='华东',record_id,null))/count(record_id)*100  east_pct_2023
        from Books
        left join BorrowRecords using(book_id)
        left join Branches using(branch_id)
        where year(borrow_date)=2023
        group by book_id,book_title
)
select book_id
,book_title
,ifnull(feb_2023_borrows,0) feb_2023_borrows
,ifnull(feb_2024_borrows,0) feb_2024_borrows
,ifnull(jan_2024_borrows,0) jan_2024_borrows
,ifnull(feb_2024_borrows,0) - ifnull(feb_2023_borrows,0) yoy_delta
,ifnull(feb_2024_borrows,0) - ifnull(jan_2024_borrows,0) mom_delta
,round(ifnull(north_pct_2023,0),2) north_pct_2023
,round(ifnull(south_pct_2023,0),2) south_pct_2023
,round(ifnull(east_pct_2023,0),2) east_pct_2023
from Books
left join t1 using(book_id,book_title)
left join t2 using(book_id,book_title)
left join t3 using(book_id,book_title)
left join t4 using(book_id,book_title)
order by book_id,book_title