题解 | 统计借阅量

WITH t1 AS (
			SELECT b1.book_id,
			       b1.book_title,
				   b1.category,
				   b2.branch_id,
				   b2.borrow_date,
				   b2.member_id,
				   b3.branch_name,
				   b3.region
            FROM Books b1
			LEFT JOIN BorrowRecords b2 ON b1.book_id = b2.book_id
			LEFT JOIN Branches b3 ON b3.branch_id = b2.branch_id
),
t2 AS (
		SELECT book_id,
			   ROUND(
			   		  COUNT(CASE WHEN region = '华北'THEN book_id END)/
					  COUNT(book_id)* 100,
					  2) AS north_pct_2023,
				ROUND(
				      COUNT(CASE WHEN region = '华南'THEN book_id END)/
				      COUNT(book_id)* 100,
					  2) AS south_pct_2023,
				ROUND(
				      COUNT(CASE WHEN region = '华东'THEN book_id END)/
					  COUNT(book_id)* 100,
					  2) AS east_pct_2023
		FROM t1 
		WHERE YEAR(borrow_date) = 2023
		GROUP BY book_id
)
SELECT t1.book_id,
	   t1.book_title,
	   COALESCE(
	        COUNT(CASE 
					WHEN DATE_FORMAT(t1.borrow_date,'%Y-%m') = '2023-02' THEN t1.book_id
				  END),
		0) AS feb_2023_borrows,
	   COALESCE(
	        COUNT(CASE 
			        WHEN DATE_FORMAT(t1.borrow_date,'%Y-%m') = '2024-02' THEN t1.book_id
				  END),
		0) AS feb_2024_borrows,
	   COALESCE(
	        COUNT(CASE 
			        WHEN DATE_FORMAT(t1.borrow_date,'%Y-%m') = '2024-01' THEN t1.book_id
				  END),
		0) AS jan_2024_borrows,
	   COALESCE(
	        COUNT(CASE 
			        WHEN DATE_FORMAT(t1.borrow_date,'%Y-%m') = '2024-02' THEN t1.book_id
			      END) - 
			COUNT(CASE 
			        WHEN DATE_FORMAT(t1.borrow_date,'%Y-%m') = '2023-02' THEN t1.book_id
				  END),
		0) AS yoy_delta,
		COALESCE(
		     COUNT(CASE 
			         WHEN DATE_FORMAT(t1.borrow_date,'%Y-%m') = '2024-02' THEN t1.book_id
				   END) - 
			 COUNT(CASE 
			         WHEN DATE_FORMAT(t1.borrow_date,'%Y-%m') = '2024-01' THEN t1.book_id
				   END),
		0)  AS mom_delta,
		COALESCE(t2.north_pct_2023,0) AS north_pct_2023,
		COALESCE(t2.south_pct_2023,0) AS south_pct_2023,
		COALESCE(t2.east_pct_2023,0) AS east_pct_2023
FROM t1
LEFT JOIN t2 ON t1.book_id = t2.book_id
GROUP BY t1.book_id