题解 | 统计借阅量

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