题解 | 统计借阅量

# 各个图书在特定时间的借阅量，和指定时间对比，同时给出各分区占比
with
t1 as(
    select
        book_id,
        book_title,
        count(if(date_format(borrow_date,'%Y%m')='202302',1,null)) as feb_2023_borrows,
        count(if(date_format(borrow_date,'%Y%m')='202402',1,null)) as feb_2024_borrows,
        count(if(date_format(borrow_date,'%Y%m')='202401',1,null)) as jan_2024_borrows,
        count(if(date_format(borrow_date,'%Y')='2023' && branch_id=1,1,null)) as north_pct_2023_cnt,
        count(if(date_format(borrow_date,'%Y')='2023' && branch_id=3,3,null)) as south_pct_2023_cnt,
        count(if(date_format(borrow_date,'%Y')='2023' && branch_id=2,2,null)) as east_pct_2023_cnt,
        count(if(date_format(borrow_date,'%Y')='2023',1,null)) as total_pct_2023_cnt
    from
        BorrowRecords
        right join Books using(book_id)
        left join Branches using(branch_id)
    group by
        book_id,
        book_title
)
,
t2 as(
    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,
        (
            case
                when total_pct_2023_cnt=0 then 0.00
                else round(north_pct_2023_cnt/total_pct_2023_cnt*100,2)
            end
        ) as north_pct_2023,
        (
            case
                when total_pct_2023_cnt=0 then 0.00
                else round(south_pct_2023_cnt/total_pct_2023_cnt*100,2)
            end
        ) as south_pct_2023,
        (
            case
                when total_pct_2023_cnt=0 then 0.00
                else round(east_pct_2023_cnt/total_pct_2023_cnt*100,2)
            end
        ) as east_pct_2023
    from
        t1
    order by
        book_id,
        book_title
)

select * from t2