首页 > 试题广场 >

统计借阅量

[编程题]统计借阅量
  • 热度指数：11038 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

为优化馆藏管理与读者服务，图书馆每月需复盘各图书的借阅表现。请基于借阅记录与分馆信息，统计每本书在 2024 年 2 月与 2024 年 1 月的借阅量，并与 2023 年 2 月对比；同时给出 2023 年各分区（华北、华东、华南）的借阅占比，用于评估区域结构变化。数据来自图书表、借阅表与分馆表。

【原始表】
Books（图书）表

  • book_id INT 主键：图书唯一标识
  • book_title VARCHAR：图书标题
  • category VARCHAR：图书所属类别

BorrowRecords（借阅记录）表

  • record_id INT 主键：借阅流水标识
  • book_id INT 外键 → Books.book_id
  • branch_id INT 外键 → Branches.branch_id
  • borrow_date DATE：借阅日期
  • member_id INT：借阅者ID（本题不直接使用）

Branches（分馆）表

  • branch_id INT 主键
  • branch_name VARCHAR：分馆名称
  • region ENUM('华北','华东','华南')：分区

输出每本书的借阅概览，结果需包含如下字段，并按 book_id 升序排列（若并列再按 book_title 升序）：

  • book_id：图书ID
  • book_title：图书名称
  • feb_2023_borrows：2023 年 2 月借阅量
  • feb_2024_borrows：2024 年 2 月借阅量
  • jan_2024_borrows：2024 年 1 月借阅量
  • yoy_delta：同比增量 = feb_2024_borrows - feb_2023_borrows
  • mom_delta：环比增量 = feb_2024_borrows - jan_2024_borrows
  • north_pct_2023：2023 年华北借阅占比（保留两位小数）
  • south_pct_2023：2023 年华南借阅占比（保留两位小数）
  • east_pct_2023：2023 年华东借阅占比（保留两位小数）

【示例输入】

Books

Branches

BorrowRecords

【示例输出】


示例1

输入

DROP TABLE IF EXISTS BorrowRecords;
DROP TABLE IF EXISTS Branches;
DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
  book_id    INT PRIMARY KEY,
  book_title VARCHAR(100),
  category   VARCHAR(50)
);

CREATE TABLE Branches (
  branch_id   INT PRIMARY KEY,
  branch_name VARCHAR(50),
  region      ENUM('华北','华东','华南')
);

CREATE TABLE BorrowRecords (
  record_id   INT PRIMARY KEY,
  book_id     INT,
  branch_id   INT,
  borrow_date DATE,
  member_id   INT
);

INSERT INTO Books VALUES
(1,'Database Systems','IT'),
(2,'Linear Algebra','Math');

INSERT INTO Branches VALUES
(1,'北馆','华北'),
(2,'东馆','华东'),
(3,'南馆','华南');

INSERT INTO BorrowRecords VALUES
(101,1,1,'2023-02-10',11),
(102,1,2,'2024-02-12',12),
(103,1,3,'2024-01-20',13),
(104,2,1,'2023-02-15',21),
(105,2,2,'2023-07-01',22),
(106,2,3,'2024-02-03',23);

输出

book_id|book_title|feb_2023_borrows|feb_2024_borrows|jan_2024_borrows|yoy_delta|mom_delta|north_pct_2023|south_pct_2023|east_pct_2023
1|Database Systems|1|1|1|0|0|100.00|0.00|0.00
2|Linear Algebra|1|1|0|0|1|50.00|0.00|50.00

备注:
约束与说明：
  • 必须连接并使用三张表。
  • 必须使用高级查询（含：CTE/子查询、条件聚合、CASE/COALESCE，至少两类）。
  • 若某月份无借阅，按 0 计算；2023 年占比 = 2023 年对应分区借阅量 / 2023 年该书总借阅量 × 100，保留两位小数，若分母为 0 则各分区占比均为 0.00。
算法知识视频讲解
添加回答
Mysql
MiserT头像 MiserT 
with
    monthly_borrow_info as (
        #1.按照书本id 计算2023-02借阅量/2024-02借阅量/2024-01借阅量
        #2.计算 月同期增量/月环增量
        select
            book_id,
            # 计算2023-02借阅量/2024-02借阅量/2024-01借阅量
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2023-02' then 1
                    else 0
                end
            ) feb_2023_borrows,
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-02' then 1
                    else 0
                end
            ) feb_2024_borrows,
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-01' then 1
                    else 0
                end
            ) jan_2024_borrows,
            # 计算月同期增量
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-02' then 1
                    else 0
                end
            ) - sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2023-02' then 1
                    else 0
                end
            ) yoy_delta,
            # 计算月环期增量
            sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-02' then 1
                    else 0
                end
            ) - sum(
                case
                    when date_format(borrow_date, '%Y-%m') = '2024-01' then 1
                    else 0
                end
            ) mom_delta
        from
            BorrowRecords
        group by
            book_id
    ),
    region_borrow_pct as (
        # 计算2023年每本书在华北/华南/华东的借阅量比例
        # 该书在本区域的借阅量/该书总借阅量
        select
            book_id,
            # 
            round(
                coalesce(
                    sum(
                        case
                            when region = '华北' then 1
                            else 0 
                            end
                        ) / count(*)*100
                        ,0
                    ),
                    2
            ) north_pct_2023,
            round(
                coalesce(
                    sum(
                        case
                            when region = '华南' then 1
                            else 0
                            end
                        ) / count(*)*100,
                        0
                    ),
                    2
            ) south_pct_2023,
            round(
                coalesce(
                    sum(
                        case
                            when region = '华东' then 1
                            else 0
                            end
                        ) / count(*)*100,
                        0
                    ),
                    2
            ) east_pct_2023
        from
            BorrowRecords
            join Branches using (branch_id)
        where
            #2023年
            year(borrow_date) = '2023'
        group by
            book_id
    )

# 合并
# 左连接Books表，没有借阅过的数据填0
    select 
        b.book_id,
        book_title,
        coalesce(feb_2023_borrows,0) feb_2023_borrows,
        coalesce(feb_2024_borrows,0) feb_2024_borrows,
        coalesce(jan_2024_borrows,0) jan_2024_borrows,

        coalesce(yoy_delta,0) yoy_delta,
        coalesce(mom_delta,0) mom_delta,

        coalesce(north_pct_2023,0) north_pct_2023,
        coalesce(south_pct_2023,0) south_pct_2023,
        coalesce(east_pct_2023,0) east_pct_2023
    from
        Books b
        left join monthly_borrow_info using(book_id)
        left join region_borrow_pct using(book_id)
    order by 
        b.book_id,
        book_title
用了两种方法写了，感觉还是很啰嗦，哈哈哈哈哈
发表于 2025-12-03 16:59:43 回复(0)
Mysql
像初雪一样遇见你头像 像初雪一样遇见你 
select book_id,book_title,feb_2023_borrows,feb_2024_borrows,
jan_2024_borrows,(feb_2024_borrows-feb_2023_borrows) as yoy_delta,
(feb_2024_borrows-jan_2024_borrows) as mom_delta,
round(if(cnt_2023=0,0,north_cnt_2023/cnt_2023*100),2) as north_pct_2023,
round(if(cnt_2023=0,0,south_cnt_2023/cnt_2023*100),2) as south_pct_2023,
round(if(cnt_2023=0,0,east_cnt_2023/cnt_2023*100),2) as east_pct_2023
from
(select 
x.book_id
,x.book_title
,sum(if(left(borrow_date,7)="2023-02",1,0)) as feb_2023_borrows
,sum(if(left(borrow_date,7)="2024-02",1,0)) as feb_2024_borrows
,sum(if(left(borrow_date,7)="2024-01",1,0)) as jan_2024_borrows
,sum(if(year(borrow_date)=2023 and region="华北",1,0)) as north_cnt_2023
,sum(if(year(borrow_date)=2023 and region="华南",1,0)) as south_cnt_2023
,sum(if(year(borrow_date)=2023 and region="华东",1,0)) as east_cnt_2023
,sum(if(year(borrow_date)=2023,1,0)) as cnt_2023
from Books x 
left join BorrowRecords y 
on x.book_id=y.book_id
left join Branches z 
on y.branch_id=z.branch_id
group by 1,2)e
order by 1,2

发表于 2025-10-17 15:11:15 回复(0)
Mysql
已注销头像 已注销 
select
a.book_id,a.book_title,
ifnull(feb_2023_borrows,0) feb_2023_borrows,
ifnull(feb_2024_borrows,0) feb_2024_borrows,
ifnull(jan_2024_borrows,0) jan_2024_borrows,
ifnull(yoy_delta,0) yoy_delta,
ifnull(mom_delta,0) mom_delta,
ifnull(north_pct_2023,0.00) north_pct_2023,
ifnull(south_pct_2023,0.00) south_pct_2023,
ifnull(east_pct_2023,0.00) east_pct_2023
from Books a
left join
(
    select
    b.book_id,b.book_title,
    count(if(left(a.borrow_date,7)='2023-02',1,null)) feb_2023_borrows,
    count(if(left(a.borrow_date,7)='2024-02',1,null)) feb_2024_borrows,
    count(if(left(a.borrow_date,7)='2024-01',1,null)) jan_2024_borrows,
    count(if(left(a.borrow_date,7)='2024-02',1,null))-count(if(left(a.borrow_date,7)='2023-02',1,null)) yoy_delta,
    count(if(left(a.borrow_date,7)='2024-02',1,null))-count(if(left(a.borrow_date,7)='2024-01',1,null)) mom_delta,
    round(count(if(left(a.borrow_date,4)='2023' and c.region='华北',1,null))/count(if(left(a.borrow_date,4)='2023',1,null))*100,2) north_pct_2023,
    round(count(if(left(a.borrow_date,4)='2023' and c.region='华南',1,null))/count(if(left(a.borrow_date,4)='2023',1,null))*100,2) south_pct_2023,
    round(count(if(left(a.borrow_date,4)='2023' and c.region='华东',1,null))/count(if(left(a.borrow_date,4)='2023',1,null))*100,2) east_pct_2023
    from BorrowRecords a
    join Books b on a.book_id=b.book_id
    join Branches c on a.branch_id=c.branch_id
    group by b.book_id,b.book_title
) b
on a.book_id=b.book_id
order by book_id,book_title

发表于 2025-09-17 22:25:26 回复(0)
提交观点

问题信息

难度：
3条回答 137浏览

热门推荐

相关试题

统计借阅量

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号