题解 | 统计借阅量

with borrows_cnt as(
    select
        b.book_id,
        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
    from Books b 
    left join BorrowRecords br on b.book_id=br.book_id
    group by b.book_id
),
# 2023 年占比 = 2023 年对应分区借阅量 / 2023 年该书总借阅量 × 100
ratio_2023_cnt as (
    select
        book_id,
        MAX(north_pct_2023) AS north_pct_2023,
        MAX(south_pct_2023) AS south_pct_2023,
        MAX(east_pct_2023)  AS east_pct_2023
    from(
        select
            book_id,
            region,
            round(ifnull((
                case 
                when region = '华北' then (count(record_id) over(partition by book_id, region)*100/count(record_id) over(partition by book_id))
                else null
                end
            ), 0), 2) as north_pct_2023,
            round(ifnull((
                case 
                when region = '华南' then (count(record_id) over(partition by book_id, region)*100/count(record_id) over(partition by book_id))
                else null
                end
            ), 0), 2) as south_pct_2023,
            round(ifnull((
                case 
                when region = '华东' then (count(record_id) over(partition by book_id, region)*100/count(record_id) over(partition by book_id))
                else null
                end
            ), 0), 2) as east_pct_2023
        from Branches b 
        left join BorrowRecords br on b.branch_id =br.branch_id
        where year(borrow_date) = 2023
    )as t
    group by book_id
)
select
    b.book_id,
    b.book_title,
    ifnull(feb_2023_borrows, 0) as feb_2023_borrows,
    ifnull(feb_2024_borrows, 0) as feb_2024_borrows,
    ifnull(jan_2024_borrows, 0) as jan_2024_borrows,
    ifnull(feb_2024_borrows, 0) - ifnull(feb_2023_borrows, 0) as yoy_delta,
    ifnull(feb_2024_borrows, 0) - ifnull(jan_2024_borrows, 0) as mom_delta,
    round(ifnull(north_pct_2023, 0), 2) as north_pct_2023,
    round(ifnull(south_pct_2023, 0), 2) as south_pct_2023,
    round(ifnull(east_pct_2023, 0), 2) as east_pct_2023
from Books b
left join borrows_cnt bc on b.book_id=bc.book_id
left join ratio_2023_cnt on b.book_id=ratio_2023_cnt.book_id
order by b.book_id asc, b.book_title asc

1. 统计不同月份的数据可以用case when在一张表中实现；
2. 在相同book_id，不同region下，合并百分率可以用max实现，不能用sum

基于以上两点，可以使用三张表连接完成解题