设计一个用于分析用户复购率的用户行为数据表，需存储用户ID、行为类型（浏览、购买、加购）、行为时间、商品ID等字段，并考虑数据量（每日千万级记录）、时序分析需求（如最近30天行为）、以及后续分析（如计算复购率、用户生命周期价值），请说明表结构设计、索引策略、数据分区方式及数据保留策略。
答案
1) 【一句话结论】采用宽表结构+时间分区+多级索引的设计，通过按天分区存储千万级行为数据，结合覆盖索引优化复购率等时序分析，满足后续用户生命周期价值等聚合需求。
2) 【原理/概念讲解】
首先解释宽表模型：宽表（Wide Table）是数据仓库中的一种模式，将多个维度（如用户、行为、商品）的属性字段横向展开，减少join操作，适合多维度分析（比如复购率需要关联用户、行为、商品等多维度）。
接着说明时间分区：按行为时间（如按天）分区，便于快速查询最近30天数据（时序分析），同时按天分区可以按需保留数据（比如保留30天+历史数据）。
再讲索引策略：主键（用户ID+行为时间+行为类型+商品ID）确保数据唯一性，覆盖索引（包含计算复购率所需字段，如用户ID、行为时间、行为类型）减少回表，提升查询效率。
最后提数据保留策略：按分区保留策略（如最近365天数据保留，更久的数据归档），平衡存储成本和查询需求。
3) 【对比与适用场景】
| 分区策略 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 按天分区 | 每日一个分区 | 粒度细，便于按天查询 | 需要精确时序分析（如最近30天） | 分区数量多，存储成本高 |
| 按周分区 | 每周一个分区 | 粒度粗，分区少 | 基础时序分析，存储成本低 | 无法精确查询单日数据 |
| 索引类型 | 定义 | 适用场景 | 注意点 |
|---|---|---|---|
| 主键索引 | 唯一标识，覆盖主键字段 | 确保数据唯一性，快速定位单条记录 | 不能用于非唯一查询 |
| 覆盖索引 | 包含查询所需所有字段 | 优化聚合查询（如复购率计算） | 需要合理设计索引列 |
4) 【示例】
用SQL创建表（假设使用MySQL）：
CREATE TABLE user_behavior (
user_id BIGINT NOT NULL,
behavior_type ENUM('browse', 'purchase', 'add_to_cart') NOT NULL,
behavior_time TIMESTAMP NOT NULL,
product_id BIGINT,
-- 分区字段
partition_date DATE,
PRIMARY KEY (user_id, behavior_time, behavior_type, product_id),
-- 覆盖索引，包含计算复购率的关键字段
INDEX idx_behavior_user_time (user_id, behavior_time, behavior_type),
INDEX idx_behavior_product (product_id, behavior_time)
) PARTITION BY RANGE (TO_DAYS(behavior_time)) (
PARTITION p_recent_30 VALUES LESS THAN (TO_DAYS(NOW()) - 29), -- 最近30天
PARTITION p_recent_365 VALUES LESS THAN (TO_DAYS(NOW()) - 366), -- 最近365天
PARTITION p_archive VALUES LESS THAN (TO_DAYS('2020-01-01')) -- 归档数据
);
解释：分区按行为时间的日期（TO_DAYS）划分，最近30天分区存储高频数据，便于快速查询，最近365天分区用于长期用户生命周期价值分析，归档分区存储历史数据。主键包含用户ID、行为时间、行为类型、商品ID，确保唯一性。覆盖索引idx_behavior_user_time包含用户ID、行为时间、行为类型，用于复购率计算时快速过滤用户行为。
5) 【面试口播版答案】
各位面试官好，针对卫龙数字化岗位的复购率分析需求，我的设计思路是采用宽表结构+时间分区+多级索引的方案。首先，表结构上，我们设计宽表user_behavior，包含用户ID、行为类型（浏览/购买/加购）、行为时间、商品ID等核心字段，同时添加分区字段partition_date按天划分，满足最近30天时序分析。索引方面，主键设为(user_id, behavior_time, behavior_type, product_id)，确保唯一性；同时建立覆盖索引idx_behavior_user_time（包含用户ID、行为时间、行为类型），用于复购率计算时快速过滤用户行为，减少回表。数据分区上，按行为时间的日期分区，最近30天分区存储高频数据，便于快速查询，最近365天分区用于长期用户生命周期价值分析，归档分区存储历史数据，平衡存储成本。数据保留策略上，最近30天数据保留，最近365天数据保留，更久的数据归档，满足时序分析和长期分析需求。这样设计既能应对每日千万级数据的高并发写入，又能高效支持复购率、用户生命周期价值等后续分析。
6) 【追问清单】
- 问：如果数据量增长到每日亿级，如何优化？答：考虑分库分表（按用户ID哈希分库），或者使用列式存储（如ClickHouse）提升写入和查询性能。
- 问：复购率计算的具体逻辑是什么？答：复购率=（购买过至少两次的用户数）/（总购买用户数），需要按用户ID分组，统计购买次数，然后筛选购买次数≥2的用户。
- 问：为什么选择按天分区而不是按周分区？答：按天分区粒度更细，能精确查询最近30天数据，满足时序分析需求，而按周分区无法精确到单日，影响复购率等时序指标的计算精度。
- 问：数据保留策略中，为什么保留365天数据？答：用户生命周期价值分析需要长期数据，365天数据能覆盖用户从注册到活跃的完整周期，支持长期用户价值评估。
7) 【常见坑/雷区】
- 坑1：分区粒度选择不当（如按月分区），导致时序分析效率低，无法快速查询最近30天数据。
- 坑2：索引覆盖不足，比如只建主键索引，没有覆盖复购率计算所需字段，导致聚合查询慢。
- 坑3：字段类型选择错误（如时间字段用字符串存储），影响时序分析和查询性能。
- 坑4：未考虑后续分析需求（如用户生命周期价值需要更细粒度的用户行为数据），导致表结构无法支持复杂分析。
- 坑5：数据保留策略过于激进（如保留所有历史数据），导致存储成本过高，或者过于保守（如只保留30天数据），无法支持长期分析。