题解 | 统计借阅量

with a as(
    select 
        book_id,
        book_title,
        sum(case when date_format(borrow_date,'%Y-%m')='2023-02' then 1 else 0 end) as feb_2023_borrows,
        sum(case when date_format(borrow_date,'%Y-%m')='2024-02' then 1 else 0 end) as feb_2024_borrows,
        sum(case when date_format(borrow_date,'%Y-%m')='2024-01' then 1 else 0 end) as jan_2024_borrows,
        sum(case when year(borrow_date)='2023' then 1 else 0 end) as number,
        sum(case when region ='华北' and year(borrow_date)='2023' then 1 else 0 end) as north_2023,
        sum(case when region ='华东' and year(borrow_date)='2023' then 1 else 0 end) as east_2023,
        sum(case when region ='华南' and year(borrow_date)='2023' then 1 else 0 end) as south_2023
    from Books
        left join BorrowRecords using(book_id )
        left join Branches using(branch_id )
    group by book_id,book_title
)
select 
    book_id,
    book_title,
    feb_2023_borrows,
    feb_2024_borrows,
    jan_2024_borrows,
    feb_2024_borrows - feb_2023_borrows as yoy_delta,
    feb_2024_borrows - jan_2024_borrows as mom_delta,
    ifnull(round(north_2023/number*100,2),0.00) as north_pct_2023,
    ifnull(round(south_2023/number*100,2),0.00) as south_pct_2023,    
    ifnull(round(east_2023/number*100,2),0.00) as east_pct_2023
from a
order by book_id,book_title;