题解 | 统计借阅量

with details as(
    select book_id
    ,book_title
    ,region
    ,borrow_date
    from Books
    /*必须左连接，不然有数据缺失风险*/
    left join BorrowRecords using(book_id)
    left join Branches using(branch_id)
),
t1 as(
    select book_id
    ,count(borrow_date) as feb_2023_borrows
    from details
    where year(borrow_date) = 2023
    and month(borrow_date) = 2
    group by book_id
),
t2 as(
    select book_id
    ,count(borrow_date) as feb_2024_borrows
    from details
    where year(borrow_date) = 2024
    and month(borrow_date) = 2
    group by book_id
),
t3 as(
    select book_id
    ,count(borrow_date) as jan_2024_borrows
    from details
    where year(borrow_date) = 2024
    and month(borrow_date) = 1
    group by book_id
),
t4 as(
    select book_id
    ,count(borrow_date) as north_2023
    from details
    where year(borrow_date) = 2023
    and region = "华北"
    group by book_id
),
t5 as(
    select book_id
    ,count(borrow_date) as south_2023
    from details
    where year(borrow_date) = 2023
    and region = "华南"
    group by book_id
),
t6 as(
    select book_id
    ,count(borrow_date) as east_2023
    from details
    where year(borrow_date) = 2023
    and region = "华东"
    group by book_id
),
t7 as(
    select book_id
    ,count(*) as borrows
    from details
    where year(borrow_date) = 2023
    group by book_id
)

select distinct book_id
,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
/*警惕除数为0*/
,coalesce(round(100*north_2023/nullif(borrows,0),2),0) as north_pct_2023
,coalesce(round(100*south_2023/nullif(borrows,0),2),0) as south_pct_2023
,coalesce(round(100*east_2023/nullif(borrows,0),2),0) as east_pct_2023
from details
left join t1 using(book_id)
left join t2 using(book_id)
left join t3 using(book_id)
left join t4 using(book_id)
left join t5 using(book_id)
left join t6 using(book_id)
left join t7 using(book_id)
order by book_id
,book_title