题解 | 统计借阅量

with
t1 as(
    select 
        b1.book_id,
        b1.book_title,
        coalesce(substr(b2.borrow_date,1,7),null) as borrow_date,
        count(*) as borrows
    from Books b1 
    left join BorrowRecords b2 on b1.book_id=b2.book_id
    group by 
        b1.book_id,b1.book_title,coalesce(substr(b2.borrow_date,1,7),null)),
t2 as( 
    select
        t6.book_id,
        t6.book_title,
        sum(t6.feb_2023_borrows) as feb_2023_borrows,
        sum(t6.feb_2024_borrows) as feb_2024_borrows,
        sum(t6.jan_2024_borrows) as jan_2024_borrows,
        sum(t6.feb_2024_borrows) - sum(t6.feb_2023_borrows) as yoy_delta,
        sum(t6.feb_2024_borrows) - sum(t6.jan_2024_borrows) as mom_delta
    from(
        select 
            book_id,
            book_title,
            if(borrow_date='2023-02',borrows,0) as feb_2023_borrows,
            if(borrow_date='2024-02',borrows,0) as feb_2024_borrows,  
            if(borrow_date='2024-01',borrows,0) as jan_2024_borrows   
        from t1) t6
    group by t6.book_id,t6.book_title),
t3 as(
    select 
        t7.book_id,
        sum(t7.north)/count(*) as north_pct_2023,
        sum(t7.south)/count(*) as south_pct_2023,
        sum(t7.east)/count(*) as east_pct_2023
    from(
        select
            b1.book_id,
            if(b2.region='华北',1,0) as north,
            if(b2.region='华南',1,0) as south,
            if(b2.region='华东',1,0) as east
        from BorrowRecords b1 left join Branches b2 on b1.branch_id=b2.branch_id
        where b1.borrow_date like '2023%') t7
        group by t7.book_id),
t4 as(
    select
        t5.book_id,
        coalesce(t3.north_pct_2023,0) as north_pct_2023,
        coalesce(t3.south_pct_2023,0) as south_pct_2023,
        coalesce(t3.east_pct_2023,0) as east_pct_2023
    from Books t5 left join t3 on t5.book_id=t3.book_id)

select
    t2.*,
    round(t4.north_pct_2023*100,2) as north_pct_2023,
    round(t4.south_pct_2023*100,2) as south_pct_2023,
    round(t4.east_pct_2023*100,2) as east_pct_2023
from t2 
left join t4 on t2.book_id=t4.book_id
order by t2.book_id,t2.book_title