题解 | 统计借阅量

WITH book_monthly_borrows AS (
   SELECT 
        a.book_id,
        a.book_title,
        -- 2023年2月借阅量 用left选取日期的前七位
        COUNT(CASE WHEN LEFT(b.borrow_date,7) = '2023-02' THEN b.record_id END) AS feb_2023_borrows,
        -- 2024年2月借阅量
        COUNT(CASE WHEN LEFT(b.borrow_date,7) = '2024-02' THEN b.record_id END) AS feb_2024_borrows,
        -- 2024年1月借阅量
        COUNT(CASE WHEN LEFT(b.borrow_date,7) = '2024-01' THEN b.record_id END) AS jan_2024_borrows
    FROM Books a 
    LEFT JOIN BorrowRecords b ON a.book_id = b.book_id 
    GROUP BY a.book_id, a.book_title
),
book_2023_region AS (
    SELECT 
        book_id,
        ROUND((SUM(north) / COUNT(*)) * 100, 2) AS north_pct_2023,
        ROUND((SUM(south) / COUNT(*)) * 100, 2) AS south_pct_2023,
        ROUND((SUM(east) / COUNT(*)) * 100, 2) AS east_pct_2023
--先查询出2023年地区 后再进行求和并求出占比
    FROM (
        SELECT 
            h.book_id,
            IF(g.region = '华北', 1, 0) AS north,
            IF(g.region = '华南', 1, 0) AS south,
            IF(g.region = '华东', 1, 0) AS east
        FROM Branches g 
        LEFT JOIN BorrowRecords h USING(branch_id) -连接表查询出2023年的地区
        WHERE LEFT(h.borrow_date,4) = '2023'-left字段截取
    ) t 
    GROUP BY book_id
)
SELECT 
    mb.book_id,
    mb.book_title,
    mb.feb_2023_borrows,
    mb.feb_2024_borrows,
    mb.jan_2024_borrows,
    -- 同比：2024.02 - 2023.02
    mb.feb_2024_borrows - mb.feb_2023_borrows AS yoy_delta,
    -- 环比：2024.02 - 2024.01
    mb.feb_2024_borrows - mb.jan_2024_borrows AS mom_delta,
    IFNULL(r.north_pct_2023, 0) AS north_pct_2023, 错误就等于0
    IFNULL(r.south_pct_2023, 0) AS south_pct_2023,
    IFNULL(r.east_pct_2023, 0) AS east_pct_2023
FROM book_monthly_borrows mb
LEFT JOIN book_2023_region r USING(book_id)
ORDER BY mb.book_id, mb.book_title;