题解 | 统计借阅量

WITH feb_23 AS(
    SELECT
        b.book_id,
        COUNT(b.record_id) AS feb_2023_borrows
    FROM BorrowRecords b
    WHERE YEAR(b.borrow_date) = 2023
        AND MONTH(b.borrow_date) = 2
    GROUP BY
        b.book_id
),
feb_24 AS(
    SELECT
        b.book_id,
        COUNT(b.record_id) AS feb_2024_borrows
    FROM BorrowRecords b
    WHERE YEAR(b.borrow_date) = 2024
        AND MONTH(b.borrow_date) = 2
    GROUP BY
        b.book_id
),
jan_24 AS(
    SELECT
        b.book_id,
        COUNT(b.record_id) AS jan_2024_borrows
    FROM BorrowRecords b
    WHERE YEAR(b.borrow_date) = 2024
        AND MONTH(b.borrow_date) = 1
    GROUP BY
        b.book_id
),
north AS(
    SELECT
        b.book_id,
        COUNT(b.record_id) AS north_2023
    FROM BorrowRecords b
    LEFT JOIN Branches br ON b.branch_id = br.branch_id
    WHERE YEAR(borrow_date) = 2023
        AND br.region = '华北'
    GROUP BY b.book_id
),
south AS(
    SELECT
        b.book_id,
        COUNT(b.record_id) AS south_2023
    FROM BorrowRecords b
    LEFT JOIN Branches br ON b.branch_id = br.branch_id
    WHERE YEAR(borrow_date) = 2023
        AND br.region = '华南'
    GROUP BY b.book_id
),
east AS(
    SELECT
        b.book_id,
        COUNT(b.record_id) AS east_2023
    FROM BorrowRecords b
    LEFT JOIN Branches br ON b.branch_id = br.branch_id
    WHERE YEAR(borrow_date) = 2023
        AND br.region = '华东'
    GROUP BY b.book_id    
),
total AS(
    SELECT
        b.book_id,
        COUNT(b.record_id) AS total_2023
    FROM BorrowRecords b
    LEFT JOIN Branches br ON b.branch_id = br.branch_id
    WHERE YEAR(borrow_date) = 2023
    GROUP BY b.book_id   
)
SELECT DISTINCT
    b.book_id,
    b.book_title,
    COALESCE(feb_23.feb_2023_borrows, 0) AS feb_2023_borrows,
    COALESCE(feb_24.feb_2024_borrows, 0) AS feb_2024_borrows,
    COALESCE(jan_24.jan_2024_borrows, 0) AS jan_2024_borrows,
    COALESCE(feb_24.feb_2024_borrows, 0) - COALESCE(feb_23.feb_2023_borrows, 0) AS yoy_delta,
    COALESCE(feb_24.feb_2024_borrows, 0) - COALESCE(jan_24.jan_2024_borrows, 0) AS mom_delta,
    ROUND(IFNULL(
        100 * COALESCE(north.north_2023, 0) / NULLIF(COALESCE(total.total_2023, 0), 0)
        ,0)
        , 2) AS north_pct_2023,
    ROUND(
        IFNULL(100 * COALESCE(south.south_2023, 0) / NULLIF(COALESCE(total.total_2023, 0), 0),0)
    , 2) AS south_pct_2023,
    ROUND(
        IFNULL(100 * COALESCE(east.east_2023, 0) / NULLIF(COALESCE(total.total_2023, 0), 0),0)
        , 2) AS east_pct_2023
FROM Books b
LEFT JOIN feb_23 ON b.book_id = feb_23.book_id
LEFT JOIN feb_24 ON b.book_id = feb_24.book_id
LEFT JOIN jan_24 ON b.book_id = jan_24.book_id
LEFT JOIN north ON b.book_id = north.book_id
LEFT JOIN south ON b.book_id = south.book_id
LEFT JOIN east ON b.book_id = east.book_id
LEFT JOIN total ON b.book_id = total.book_id
ORDER BY
    b.book_id ASC,
    b.book_title ASC;

粪题一道

没必要刷