题目内容
某智能手环公司需统计用户在 2024 年 5 月的健康数据,分析用户的步数达标情况。由于部分设备存在数据上报故障,需在分析中排除故障期间的数据。具体表如下:
用户表( users )存储用户基本信息
- user_id : INT 类型,主键,用户唯一标识。
- name : VARCHAR(20) 类型,用户姓名(可为空)。
- age : INT 类型,用户年龄(非空)。
设备表( devices )存储用户关联的设备信息
- device_id : INT 类型,主键,设备唯一标识。
- user_id : INT 类型,外键,关联到 users.user_id。
- production_batch : VARCHAR(10) 类型,生产批次(如 "2023Q1" ,可为空)。
健康数据表( health_data )存储设备每日上报的步数数据
- data_id : INT 类型,主键,健康数据唯一标识。
- device_id : INT 类型,外键,关联到 devices.device_id。
- record_date : DATE 类型,数据日期。
- steps : INT 类型,当日步数(可为空)。
设备故障记录表( fault_logs )记录设备的故障时间段(闭区间)
- fault_id : INT 类型,主键,故障记录唯一标识。
- device_id : INT 类型,外键,关联到 devices.device_id。
- start_date : DATE 类型,故障开始日期。
- end_date : DATE 类型,故障结束日期。
要求
请编写 SQL 语句,查询 2024 年 5 月份所有用户的用户 ID 、用户姓名、有效达标天数、最长连续达标天数,具体条件如下:
- 用户年龄在 18 至 60 岁之间(包含 18 和 60)
- 排除设备故障期间的所有健康数据(若某天在故障时间区间内,则视为无效)
- 有效达标定义为:当日步数 ≥10000 步
结果按 valid_days 降序,若 valid_days 相同则按 max_streak 降序,若 max_streak 也相同则按用户 ID 升序。
示例
用户表( users )
| user_id |
name |
age |
| 1 |
张三 |
25 |
| 2 |
李四 |
65 |
| 3 |
王五 |
30 |
| 4 |
赵六 |
45 |
| 5 |
陈七 |
18 |
| 6 |
周八 |
60 |
设备表( devices )
| device_id |
user_id |
production_batch |
| 101 |
1 |
2023Q1 |
| 102 |
3 |
2023Q2 |
| 103 |
4 |
| 104 |
5 |
2023Q3 |
| 105 |
6 |
2023Q1 |
健康数据表( health_data )
| data_id |
device_id |
record_date |
steps |
| 1001 |
101 |
2024−05−01 |
12000 |
| 1002 |
2024−05−02 |
8000 |
| 1003 |
2024−05−06 |
15000 |
| 1004 |
102 |
2024−05−04 |
10000 |
| 1005 |
103 |
2024−05−01 |
11000 |
| 1006 |
104 |
2024−05−05 |
9000 |
| 1007 |
105 |
2024−05−06 |
20000 |
设备故障记录表( fault_logs )
| fault_id |
device_id |
start_date |
end_date |
| 201 |
101 |
2024−05−02 |
2024−05−05 |
| 202 |
102 |
2024−05−04 |
按照要求查询出的结果如下:
| user_id |
name |
valid_days |
max_streak |
| 1 |
张三 |
2 |
1 |
| 4 |
赵六 |
1 |
| 6 |
周八 |
样例1
输入
-- 创建用户表
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(20),
age INT NOT NULL
);
-- 创建设备表
CREATE TABLE devices (
device_id INT PRIMARY KEY,
user_id INT UNIQUE, -- 唯一约束:每个用户只能关联一个设备
production_batch VARCHAR(10)
);
-- 创建健康数据表
CREATE TABLE health_data (
data_id INT PRIMARY KEY,
device_id INT,
record_date DATE,
steps INT
);
-- 创建设备故障记录表
CREATE TABLE fault_logs (
fault_id INT PRIMARY KEY,
device_id INT,
start_date DATE,
end_date DATE
);
INSERT INTO users (user_id, name, age) VALUES
(1, '张三', 25),
(2, '李四', 65), -- 年龄超出18-60范围,不参与统计
(3, '王五', 30),
(4, '赵六', 45),
(5, '陈七', 18), -- 边界年龄(有效)
(6, '周八', 60); -- 边界年龄(有效)
INSERT INTO devices (device_id, user_id, production_batch) VALUES
(101, 1, '2023Q1'),
(102, 3, '2023Q2'),
(103, 4, '2023Q2'),
(104, 5, '2023Q3'),
(105, 6, '2023Q1');
INSERT INTO health_data (data_id, device_id, record_date, steps) VALUES
(1001, 101, '2024-05-01', 12000), -- 张三有效数据(不在故障期)
(1002, 101, '2024-05-02', 8000), -- 张三步数不足
(1003, 101, '2024-05-06', 15000), -- 张三有效数据(故障期外)
(1004, 102, '2024-05-04', 10000), -- 王五数据(但因故障被排除)
(1005, 103, '2024-05-01', 11000), -- 赵六有效数据
(1006, 104, '2024-05-05', 9000), -- 陈七步数不足
(1007, 105, '2024-05-06', 20000); -- 周八有效数据
INSERT INTO fault_logs (fault_id, device_id, start_date, end_date) VALUES
(201, 101, '2024-05-02', '2024-05-05'), -- 设备101故障覆盖5-2-5日
(202, 102, '2024-05-04', '2024-05-04'); -- 设备102单日故障
输出
user_id|name|valid_days|max_streak
1|张三|2|1
4|赵六|1|1
6|周八|1|1