- 数据库建表语句
用户表 (users)
sql">CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,register_date DATE NOT NULL,user_name VARCHAR(255) NOT NULL
);
这个表用于存储用户的基本信息， user_id 是用户的唯一标识， register_date 记录用户注册日期， user_name 是用户名。
行为日志表 (behavior_logs)
sql">CREATE TABLE behavior_logs (log_id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,log_date DATETIME NOT NULL,page_visited VARCHAR(255),click_count INT,停留时间 INT,FOREIGN KEY (user_id) REFERENCES users(user_id)
);
此表记录用户的行为日志， log_id 是日志的唯一标识， user_id 关联到 users 表的 user_id ， log_date 记录行为发生的时间， page_visited 记录访问的页面， click_count 记录点击次数， 停留时间 记录在页面停留的时间（单位可以自定义，例如秒）。
- 查询语句
统计每日活跃用户数（包括新老用户）
sql">SELECT DATE(log_date) AS active_date,COUNT(DISTINCT user_id) AS total_active_users,COUNT(DISTINCT CASE WHEN DATE(users.register_date) = DATE(log_date) THEN users.user_id END) AS new_active_users,COUNT(DISTINCT CASE WHEN DATE(users.register_date) < DATE(log_date) THEN users.user_id END) AS old_active_users
FROM behavior_logs
JOIN users ON behavior_logs.user_id = users.user_id
GROUP BY DATE(log_date);
这个查询语句通过 behavior_logs 表和 users 表的连接，按日期统计活跃用户数，同时区分新用户（当天注册并活跃）和老用户（之前注册并当天活跃）。
统计每周活跃用户数（包括新老用户）
sql">SELECT YEARWEEK(log_date) AS active_week,COUNT(DISTINCT user_id) AS total_active_users,COUNT(DISTINCT CASE WHEN YEARWEEK(users.register_date) = YEARWEEK(log_date) THEN users.user_id END) AS new_active_users,COUNT(DISTINCT CASE WHEN YEARWEEK(users.register_date) < YEARWEEK(log_date) THEN users.user_id END) AS old_active_users
FROM behavior_logs
JOIN users ON behavior_logs.user_id = users.user_id
GROUP BY YEARWEEK(log_date);
这个查询按周统计活跃用户数，并区分新老用户， YEARWEEK 函数用于获取年份和周数。
统计每月活跃用户数（包括新老用户）
sql">SELECT DATE_FORMAT(log_date, '%Y-%m') AS active_month,COUNT(DISTINCT user_id) AS total_active_users,COUNT(DISTINCT CASE WHEN DATE_FORMAT(users.register_date, '%Y-%m') = DATE_FORMAT(log_date, '%Y-%m') THEN users.user_id END) AS new_active_users,COUNT(DISTINCT CASE WHEN DATE_FORMAT(users.register_date, '%Y-%m') < DATE_FORMAT(log_date, '%Y-%m') THEN users.user_id END) AS old_active_users
FROM behavior_logs
JOIN users ON behavior_logs.user_id = users.user_id
GROUP BY DATE_FORMAT(log_date, '%Y-%m');
这个查询按月统计活跃用户数，并区分新老用户， DATE_FORMAT 函数用于格式化日期为年月格式。
查询用户行为轨迹（以某一天为例）
sql">SELECT users.user_id,users.user_name,behavior_logs.page_visited,behavior_logs.click_count,behavior_logs.停留时间
FROM behavior_logs
JOIN users ON behavior_logs.user_id = users.user_id
WHERE DATE(behavior_logs.log_date) = '2025-01-01';
这个查询获取指定日期（这里是 2025-01-01 ）的用户行为轨迹，包括用户ID、用户名、访问页面、点击次数和停留时间。你可以根据需要修改日期。
