题解 | 统计借阅量

WITH                -- 使用CTE分模块计算输出结果。（这道题的本质是连接表后，进行列转行的操作）
zongbiao AS (               -- 将所有要用到的表连接，并输出所有要用的字段
    SELECT
        dan.record_id               -- 借阅单id
        ,dan.book_id                -- 书籍id
        ,dan.branch_id              -- 图书馆id
        ,shu.book_title             -- 书名（其实可以不取，后续会说原因）
        ,dan.borrow_date            -- 借阅日期
        ,guan.region                -- 图书馆所属区域
        ,DATE_FORMAT(borrow_date,'%Y-%m') AS yue            -- 转换借阅日期格式（年月日-年月），后续的计算都是以月维度进行
    FROM BorrowRecords AS dan       -- 以借阅表为主表
    INNER JOIN Books AS shu         -- 连接书籍表
        ON (dan.book_id = shu.book_id)
    INNER JOIN Branches AS guan     -- 连接图书馆表
        ON (dan.branch_id = guan.branch_id)
),

yuejieyue AS (              -- 计算每种书籍在这三个月的借阅数
    SELECT 
        book_id
        ,SUM(CASE WHEN yue = '2023-02' THEN 1 ELSE 0 END) AS feb_2023_borrows   -- 在这一列中，借阅月为2023-02的书籍，记为1，不是这个月的记为0，最后按照书籍id分组，再统计每一本书在这个月的借阅总数
        ,SUM(CASE WHEN yue = '2024-02' THEN 1 ELSE 0 END) AS feb_2024_borrows   -- 同上
        ,SUM(CASE WHEN yue = '2024-01' THEN 1 ELSE 0 END) AS jan_2024_borrows   -- 同上
    FROM zongbiao
    GROUP BY book_id
),

tonghuanbi AS (             -- 计算每一本书的同环比，所需数据在上一步已得出，仅需套同环比公式即可，题目只要求输出绝对值，所以不用算百分比（否则会更繁琐）
    SELECT 
        book_id
        ,feb_2024_borrows - feb_2023_borrows AS yoy_delta   -- 同比=本期-去年同期
        ,feb_2024_borrows - jan_2024_borrows AS mom_delta   -- 环比=本期-上期
    FROM yuejieyue
),

quzhanbi AS (               -- 计算每一本书2023年的借阅区域占比，公式为：书籍2023年目标区域借阅数/书籍2023年所有区域借阅数*100，注意处理小数点后的位数
    SELECT
        book_id
        ,ROUND(SUM(CASE WHEN region = '华北' THEN 1 ELSE 0 END) * 100  / COUNT(*),2) AS north_pct_2023
        ,ROUND(SUM(CASE WHEN region = '华南' THEN 1 ELSE 0 END) * 100  / COUNT(*),2) AS south_pct_2023
        ,ROUND(SUM(CASE WHEN region = '华东' THEN 1 ELSE 0 END) * 100  / COUNT(*),2) AS east_pct_2023
    FROM zongbiao
    WHERE DATE(borrow_date) >= '2023-01-01'
      AND DATE(borrow_date) <= '2023-12-31'
    GROUP BY book_id
)

SELECT              -- 最后把所有计算向左连接书籍表（因为书籍3借阅数为0，用内连接会不显示书籍3）
    b.book_id,
    b.book_title,
    COALESCE(y.feb_2023_borrows,0) AS feb_2023_borrows, -- 注意处理zone值
    COALESCE(y.feb_2024_borrows,0) AS feb_2024_borrows,
    COALESCE(y.jan_2024_borrows,0) AS jan_2024_borrows,
    COALESCE(t.yoy_delta,0) AS yoy_delta,
    COALESCE(t.mom_delta,0) AS mom_delta,
    COALESCE(q.north_pct_2023,0) AS north_pct_2023,
    COALESCE(q.south_pct_2023,0) AS south_pct_2023,
    COALESCE(q.east_pct_2023,0) AS east_pct_2023
FROM Books AS b
LEFT JOIN yuejieyue AS y
    ON (b.book_id = y.book_id)
LEFT JOIN tonghuanbi AS t
    ON (b.book_id = t.book_id)
LEFT JOIN quzhanbi AS q
    ON (b.book_id = q.book_id)