题解 | 统计借阅量

SELECT 
    b.book_id,
    b.book_title,
    COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y-%m') = '2023-02' THEN bo.record_id END) AS feb_2023_borrows,
    COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y-%m') = '2024-02' THEN bo.record_id END) AS feb_2024_borrows,
    COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y-%m') = '2024-01' THEN bo.record_id END) AS jan_2024_borrows,

    (COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y-%m') = '2024-02' THEN bo.record_id END) - COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y-%m') = '2023-02' THEN bo.record_id END)) as yoy_delta,

    (COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y-%m') = '2024-02' THEN bo.record_id END) - COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y-%m') = '2024-01' THEN bo.record_id END)) as mom_delta,

    coalesce(round(COUNT(CASE WHEN br.region = '华北' and DATE_FORMAT(bo.borrow_date, '%Y') = '2023' THEN bo.record_id END)/COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y') = '2023' THEN bo.record_id END)*100,2),0.00) AS north_pct_2023,
    
    coalesce(round(COUNT(CASE WHEN br.region = '华南' and DATE_FORMAT(bo.borrow_date, '%Y') = '2023' THEN bo.record_id END)/COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y') = '2023' THEN bo.record_id END)*100,2),0.00) AS south_pct_2023, 
    
    coalesce(round(COUNT(CASE WHEN br.region = '华东' and DATE_FORMAT(bo.borrow_date, '%Y') = '2023' THEN bo.record_id END)/COUNT(CASE WHEN DATE_FORMAT(bo.borrow_date, '%Y') = '2023' THEN bo.record_id END)*100,2),0.00) AS east_pct_2023
FROM Books b
LEFT JOIN BorrowRecords bo ON b.book_id = bo.book_id
LEFT JOIN Branches br ON bo.branch_id = br.branch_id  
GROUP BY b.book_id, b.book_title;