题解 | 统计借阅量

WITH t1 AS(
    SELECT 
        b.book_id,
        b.book_title,
        SUM(IF(borrow_date BETWEEN '2023-02-01' AND '2023-02-28', 1, 0)) feb_2023_borrows,
        SUM(IF(borrow_date BETWEEN '2024-02-01' AND '2024-02-29', 1, 0)) feb_2024_borrows,
        SUM(IF(borrow_date BETWEEN '2024-01-01' AND '2024-01-31', 1, 0)) jan_2024_borrows
    FROM BorrowRecords br
    RIGHT JOIN Books b ON b.book_id = br.book_id 
    GROUP BY b.book_id
),
t2 AS (
    SELECT
        book_id,
        ROUND(SUM(IF(region = '华北', 1, 0))*100/COUNT(*), 2) north_pct_2023,
        ROUND(SUM(IF(region = '华南', 1, 0))*100/COUNT(*), 2) south_pct_2023,
        ROUND(SUM(IF(region = '华东', 1, 0))*100/COUNT(*), 2) east_pct_2023
    FROM Branches bra 
    JOIN BorrowRecords br ON bra.branch_id = br.branch_id
    WHERE YEAR(borrow_date) = 2023
    GROUP BY book_id
)
SELECT 
    t1.book_id,
    book_title,
    feb_2023_borrows,
    feb_2024_borrows,
    jan_2024_borrows,
    feb_2024_borrows - feb_2023_borrows yoy_delta,
    feb_2024_borrows - jan_2024_borrows mom_delta,
    IFNULL(north_pct_2023, 0.00) north_pct_2023,
    IFNULL(south_pct_2023, 0.00) south_pct_2023,
    IFNULL(east_pct_2023, 0.00) east_pct_2023
FROM t1 
LEFT JOIN t2 ON t1.book_id = t2.book_id 
ORDER BY t1.book_id, book_title