题解 | 统计借阅量

select
    bk.book_id,
    book_title,
    ifnull(sum(
        if(date_format(borrow_date, '%Y%m') = '202302', 1, 0)
    ),0) feb_2023_borrows,
    ifnull(sum(
        if(date_format(borrow_date, '%Y%m') = '202402', 1, 0)
    ),0) feb_2024_borrows,
    ifnull(sum(
        if(date_format(borrow_date, '%Y%m') = '202401', 1, 0)
    ),0) jan_2024_borrows,
     ifnull(-sum(
        if(date_format(borrow_date, '%Y%m') = '202302', 1, 0)
    ) +sum(
        if(date_format(borrow_date, '%Y%m') = '202402', 1, 0)
    ),0) yoy_delta,
     ifnull(sum(
        if(date_format(borrow_date, '%Y%m') = '202402', 1, 0)
    ) - sum(
        if(date_format(borrow_date, '%Y%m') = '202401', 1, 0)
    ) ,0)mom_delta,
    ifnull(round(
        sum(
            if(
                branch_name = '北馆'
                and year(borrow_date) = 2023,
                1,
                0
            )
        ) * 100 / sum(if(year(borrow_date) = 2023, 1, 0)),
        2
    ),0) north_pct_2023,
   ifnull( round(
        sum(
            if(
                branch_name = '南馆'
                and year(borrow_date) = 2023,
                1,
                0
            )
        ) * 100 / sum(if(year(borrow_date) = 2023, 1, 0)),
        2
    ),0) south_pct_2023,
    ifnull(round(
        sum(
            if(
                branch_name = '东馆'
                and year(borrow_date) = 2023,
                1,
                0
            )
        ) * 100 / sum(if(year(borrow_date) = 2023, 1, 0)),
        2
    ),0) east_pct_2023
from
    Books bk
    left join BorrowRecords br using (book_id)
    left join Branches bc using (branch_id)
group by
    1,
    2
order by
    1,
    2