题解 | 统计借阅量

with
    time_encoder_tb as (
        select
            book_id,
            branch_id,
            date_format(borrow_date, "%Y-%m") as Ym_date,
            case
                when date_format(borrow_date, "%Y-%m") = '2023-02' then 1
                else 0
            end as feb_2023,
            case
                when date_format(borrow_date, "%Y-%m") = '2024-02' then 1
                else 0
            end as feb_2024,
            case
                when date_format(borrow_date, "%Y-%m") = '2024-01' then 1
                else 0
            end as jan_2024
        from
            BorrowRecords
    ),
    region_encoder_tb as (
        select
            BorrowRecords.book_id,
            BorrowRecords.branch_id,
            year(borrow_date) as years,
            case
                when Branches.region = '华北' then 1
                else 0
            end as is_huabei,
            case
                when Branches.region = '华东' then 1
                else 0
            end as is_huadong,
            case
                when Branches.region = '华南' then 1
                else 0
            end as is_huanan
        from
            BorrowRecords
            join Branches on BorrowRecords.branch_id = Branches.branch_id
    ),
    region_result as (
        select
            Books.book_id,
            Books.book_title,
            ifnull(north_pct_2023, 0) as north_pct_2023,
            ifnull(south_pct_2023, 0) as south_pct_2023,
            ifnull(east_pct_2023, 0) as east_pct_2023
        from
            Books
            left join (
                select
                    Books.book_id,
                    Books.book_title,
                    
                        round(
                            (
                                sum(region_encoder_tb.is_huabei) / (
                                    sum(region_encoder_tb.is_huabei) + sum(region_encoder_tb.is_huadong) + sum(region_encoder_tb.is_huanan)
                                )
                            ) * 100,
                            2
                        ) as north_pct_2023,
                    
                        round(
                            (
                                sum(region_encoder_tb.is_huanan) / (
                                    sum(region_encoder_tb.is_huabei) + sum(region_encoder_tb.is_huadong) + sum(region_encoder_tb.is_huanan)
                                )
                            ) * 100,
                            2
                        )  as south_pct_2023,
                    
                        round(
                            (
                                sum(region_encoder_tb.is_huadong) / (
                                    sum(region_encoder_tb.is_huabei) + sum(region_encoder_tb.is_huadong) + sum(region_encoder_tb.is_huanan)
                                )
                            ) * 100,
                            2
                        ) as east_pct_2023
                from
                    Books
                    join region_encoder_tb on region_encoder_tb.book_id = Books.book_id
                where
                    region_encoder_tb.years = 2023
                group by
                    region_encoder_tb.book_id,
                    Books.book_title
            ) tb2 on Books.book_id = tb2.book_id
    ),
    time_results as (
        select
            Books.book_id,
            Books.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(yoy_delta, 0) as yoy_delta,
            ifnull(mom_delta, 0) as mom_delta
        from
            Books
            left join (
                select
                    Books.book_id,
                    Books.book_title,
                    sum(feb_2023) as feb_2023_borrows,
                    sum(feb_2024) as feb_2024_borrows,
                    sum(jan_2024) as jan_2024_borrows,
                    sum(feb_2024) - sum(feb_2023) as yoy_delta,
                    sum(feb_2024) - sum(jan_2024) as mom_delta
                from
                    Books
                    left join time_encoder_tb on Books.book_id = time_encoder_tb.book_id
                where
                    time_encoder_tb.Ym_date = '2023-02'
                    or time_encoder_tb.Ym_date = '2024-02'
                    or time_encoder_tb.Ym_date = '2024-01'
                group by
                    Books.book_id,
                    Books.book_title
            ) tb1 on Books.book_id = tb1.book_id
    )
select
    time_results.book_id,
    time_results.book_title,
    time_results.feb_2023_borrows,
    time_results.feb_2024_borrows,
    time_results.jan_2024_borrows,
    time_results.yoy_delta,
    time_results.mom_delta,
    region_result.north_pct_2023,
    region_result.south_pct_2023,
    region_result.east_pct_2023
from
    time_results
    join region_result on time_results.book_id = region_result.book_id

代码最长的一集（没有优化，大概率存在冗余）