一、需求分析与设计目标

数据源：
- 用户基本信息：用户ID、性别、出生日期、注册时间、常驻地域（省、市、区）、职业等。
- 用户体检报告：每次体检的报告ID、体检时间、各项指标（如血压、血糖、血脂、BMI等）。
- 用户关注的保健品：用户ID、关注/浏览/收藏的保健品ID、行为时间、行为类型（点击、收藏、加购）、保健品品类等。
核心需求：
- 支持以用户为主体的 360° 视图查询。
- 支持在时间（年、季度、月、周、日）和地域（国家、省、市）维度上进行灵活的上卷（Roll-up）和下钻（Drill-down）分析。
- 支持对用户标签（如“高血压风险人群”、“VC补充剂潜在客户”）的快速群体圈选和统计分析。
- 查询响应速度快，即使在大数据量下也能亚秒级响应。
设计目标：
- 高性能：利用 Doris 的预聚合、列式存储和索引技术实现极速查询。
- 可扩展性：模型能够轻松应对数据量的持续增长。
- 易维护性：表结构清晰，数据更新和ETL流程高效。

二、技术选型与 Doris 特性利用

选择 Apache Doris 的原因及其核心特性的利用：

MPP 架构：天然支持大规模并行处理，适合复杂的分析查询。
列式存储：高压缩比，查询时只需读取相关列，极大降低 I/O。
智能预聚合（Aggregate 模型）：本方案核心，可预先计算常用维度的汇总数据，使聚合查询速度极快。
分区与分桶：支持按时间分区，便于数据管理（如淘汰旧数据）和查询时剪枝。结合分桶（Hash分桶）实现数据均匀分布和高效点查。
物化视图（Materialized View）：另一核心，可自动或手动为不同维度组合创建预聚合表，查询时路由到最优的物化视图，灵活支持上卷下钻。
Duplicate 模型：用于存储需要保留原始明细数据的表，如用户行为流水。
高效数据导入：支持 Broker Load / Stream Load / Routine Load，便于从 Kafka、HDFS、MySQL 等数据源批量或实时导入数据。

三、数据模型详细设计

建议采用维度建模的思想，构建雪花模型或星型模型。这里我们采用星型模型，以保持查询的简洁和高效。

1. ODS 层（操作数据层）

目的：贴源数据层，结构与数据源保持一致。
表设计：使用 Duplicate 模型，保留原始明细。
- ods_user_basic (用户基本信息表)
- ods_user_medical_report (用户体检报告表)
- ods_user_behavior (用户保健品关注行为表)
数据导入：使用 Routine Load 从 Kafka 实时接入用户行为数据，使用 Broker Load 每天批量导入体检报告和基本信息变更数据。

2. DWD 层（数据明细层）

目的：对 ODS 层数据进行清洗、整合、轻度聚合，形成明细事实表和维度表。

表设计：

事实表：

dwd_user_medical_fact (用户体检事实表)

模型：Aggregate 模型
分区：按 exam_date (体检日期) 进行 Range Partitioning（按月分区）。
分桶：按 user_id 分桶。
排序键：(user_id, exam_date)

Schema:

sql

CREATE TABLE dwd_user_medical_fact (`user_id` BIGINT,`exam_date` DATE,           -- 衍生自exam_time`exam_time` DATETIME,`province` VARCHAR(20),`city` VARCHAR(20),`blood_pressure_high` SMALLINT REPLACE_IF_NOT_NULL, -- 替换式聚合`blood_pressure_low` SMALLINT REPLACE_IF_NOT_NULL,`blood_sugar` FLOAT REPLACE_IF_NOT_NULL,`bmi` FLOAT REPLACE_IF_NOT_NULL,-- ... 其他指标`is_high_blood_pressure` BOOLEAN REPLACE, -- 是否高血压，根据指标计算得出`is_high_blood_sugar` BOOLEAN REPLACE     -- 是否高血糖
) ENGINE=OLAP
AGGREGATE KEY(user_id, exam_date, exam_time, province, city)
PARTITION BY RANGE(exam_date) (...)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

dwd_user_behavior_fact (用户行为事实表)

模型：Aggregate 模型
分区：按 event_date (行为日期) 分区。
分桶：按 user_id 分桶。
排序键：(user_id, event_date,保健品品类)

Schema:

sql

CREATE TABLE dwd_user_behavior_fact (`user_id` BIGINT,`event_date` DATE,`event_time` DATETIME,`behavior_type` VARCHAR(20), -- 'click', 'favor', 'cart'`health_product_id` BIGINT,`product_category` VARCHAR(50),`click_count` BIGINT SUM,     -- 计数型聚合`is_latest_behavior` BOOLEAN REPLACE -- 是否为该商品最新行为
) ENGINE=OLAP
AGGREGATE KEY(user_id, event_date, event_time, behavior_type, health_product_id, product_category)
PARTITION BY RANGE(event_date) (...)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

维度表：

dim_user (用户维度表)

模型：Unique 模型（确保用户唯一性）
分区：可按注册日期分区。
分桶：按 user_id 分桶。

Schema:

sql

CREATE TABLE dim_user (`user_id` BIGINT,`gender` VARCHAR(10),`birth_date` DATE,`register_date` DATE,`常住省份` VARCHAR(20),`常住城市` VARCHAR(20),`occupation` VARCHAR(50),-- ... 其他属性
) ENGINE=OLAP
UNIQUE KEY(user_id)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

时间维度表

CREATE TABLE dim_date (
`date_id` DATE, -- 代理键，也是自然键，格式 'YYYY-MM-DD'
`day` TINYINT, -- 本月中的第几天 (1-31)
`month` TINYINT, -- 年份中的第几月 (1-12)
`quarter` TINYINT, -- 年份中的第几季度 (1-4)
`year` SMALLINT, -- 年份 (e.g., 2023)
`day_of_week` TINYINT, -- 本周中的第几天 (1=Sunday, 7=Saturday)
`week_of_year` TINYINT, -- 年份中的第几周 (ISO标准 1-53)
`is_weekend` BOOLEAN, -- 是否是周末
`is_holiday` BOOLEAN, -- 是否是法定节假日
`holiday_name` VARCHAR(50), -- 节假日名称 (e.g., '国庆节', '元旦')
`is_workday` BOOLEAN, -- 是否是工作日（考虑调休后）
`month_name` VARCHAR(10), -- 月份英文缩写 (e.g., 'Jan', 'Feb')
`quarter_name` VARCHAR(2), -- 季度名称 (e.g., 'Q1', 'Q2')
`year_quarter` VARCHAR(7), -- 年份季度 (e.g., '2023-Q1')
`year_month` VARCHAR(7), -- 年份月份 (e.g., '2023-01')
`year_week` VARCHAR(7), -- 年份周数 (e.g., '2023-W01')
`last_year_same_day` DATE, -- 去年同一天
`last_month_same_day` DATE, -- 上月同一天（可能为空）
`day_num_in_epoch` INT, -- 自某个固定日期（如1970-01-01）以来的天数
`week_begin_date` DATE, -- 本周第一天（周日或周一）
`week_end_date` DATE, -- 本周最后一天
`month_begin_date` DATE, -- 本月第一天
`month_end_date` DATE, -- 本月最后一天
`quarter_begin_date` DATE, -- 本季度第一天
`quarter_end_date` DATE, -- 本季度最后一天
`year_begin_date` DATE, -- 本年第一天
`year_end_date` DATE -- 本年最后一天
) ENGINE=OLAP
DUPLICATE KEY(date_id)
COMMENT "时间维度表"
DISTRIBUTED BY HASH(date_id) BUCKETS 1
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "YEAR",
"dynamic_partition.start" = "-3", -- 动态创建过去3年和未来2年的分区
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "1"
);

3. DWS 层（数据服务层 / 宽表层）

目的：面向业务场景构建宽表，进一步聚合数据，减少多表关联，提升查询性能。这是支持用户画像上卷下钻的核心层。

表设计：使用 Aggregate 模型，并创建物化视图。

dws_user_profile_wide (用户画像宽表)

模型：Aggregate 模型
分区：按 date (日期) 分区。
分桶：按 user_id 分桶。
排序键：(date, province, city, user_id)

Schema:

sql

CREATE TABLE dws_user_profile_wide (`date` DATE,                    -- 日期粒度`province` VARCHAR(20),`city` VARCHAR(20),`user_id` BIGINT,`age_group` VARCHAR(10) REPLACE, -- 年龄段，e.g., '20-30'`gender` VARCHAR(10) REPLACE,`latest_blood_pressure_status` VARCHAR(20) REPLACE, -- 最新血压状态`latest_blood_sugar_status` VARCHAR(20) REPLACE,`is_high_risk_cardio` BOOLEAN REPLACE, -- 是否心脑血管高风险`favorited_vc_product_count` BIGINT SUM, -- 收藏的VC类产品总数`clicked_protein_powder_count` BIGINT SUM, -- 点击的蛋白粉类产品次数-- ... 其他标签和聚合指标
) ENGINE=OLAP
AGGREGATE KEY(date, province, city, user_id)
PARTITION BY RANGE(date) (...)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

物化视图创建示例：

sql

-- 为按省和日期聚合创建物化视图，加速上卷查询
CREATE MATERIALIZED VIEW province_date_agg AS
SELECT`date`,`province`,COUNT(DISTINCT user_id) AS total_users,SUM(CAST(is_high_risk_cardio AS INT)) AS high_risk_users,SUM(favorited_vc_product_count) AS total_vc_favorites
FROM dws_user_profile_wide
GROUP BY `date`, `province`;-- 为按市、性别和日期聚合创建物化视图，支持下钻查询
CREATE MATERIALIZED VIEW city_gender_date_agg AS
SELECT`date`,`province`,`city`,`gender`,COUNT(DISTINCT user_id) AS total_users,...
FROM dws_user_profile_wide
GROUP BY `date`, `province`, `city`, `gender`;

Doris 的查询优化器会自动选择最优的物化视图来响应查询。

四、 ETL 数据处理流程

数据采集：用户行为数据通过埋点实时写入 Kafka。体检报告和用户信息通过业务数据库的 Binlog 或每日全量/增量导出文件到 HDFS。
数据导入：
- 实时：使用 Routine Load 任务持续消费 Kafka 中的行为数据，导入到 ods_user_behavior 表。
- 批量：每天凌晨，使用 Broker Load 从 HDFS 拉取体检报告和用户信息的增量文件，导入到对应的 ODS 表。
数据加工：
* 通过INSERT INTO SELECT语句，定期（如每小时或每天）将 ODS 层数据清洗、转换、聚合到 DWD 层事实表和维度表。
* 同样通过 INSERT INTO SELECT，将 DWD 层数据与维度表 JOIN 后，聚合到 DWS 宽表 dws_user_profile_wide。这个过程会计算用户标签（如 is_high_risk_cardio）和聚合指标。
数据服务：应用层直接通过 MySQL 协议查询 DWS 层的宽表及其物化视图，快速获取分析结果。

五、示例查询

下钻分析：”2023年Q4，江苏省南京市，高血糖用户中最关注维生素C保健品的人群分布？“

sql

SELECTcity,gender,age_group,COUNT(DISTINCT user_id) AS user_count
FROM dws_user_profile_wide
WHERE`date` >= '2023-10-01' AND `date` <= '2023-12-31'AND province = '江苏省'AND city = '南京市'AND latest_blood_sugar_status = 'high' -- 高血糖标签AND favorited_vc_product_count > 0     -- 关注过VC产品
GROUP BYcity, gender, age_group -- 下钻到市、性别、年龄
ORDER BY user_count DESC;

Doris 会尝试使用 province_date_agg 等物化视图快速定位到江苏省2023Q4的数据，然后再进行下钻计算。

上卷分析：”近一年，全国各季度，心脑血管高风险用户的比例趋势？“

sql

SELECTYEAR(date) AS year,QUARTER(date) AS quarter,SUM(CAST(is_high_risk_cardio AS INT)) / COUNT(DISTINCT user_id) AS risk_ratio
FROM dws_user_profile_wide
WHERE `date` >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
GROUP BY year, quarter
ORDER BY year, quarter;

此查询可能会直接命中按日期聚合的物化视图，效率极高。

六、总结与优化建议

优势：本方案充分利用了 Doris 的聚合模型、物化视图、分区分桶等核心特性，完美支撑了用户画像的多维分析需求，预计能达到极快的查询响应速度。
监控与调优：
- 监控 dws_user_profile_wide 表的物化视图命中率，根据业务查询模式调整或增加物化视图。
- 监控集群磁盘、内存、BE节点状态，随着数据增长水平扩展 Doris 集群。
- 合理设置分桶数量，通常在10-100个之间，避免 Tablet 过多导致元数据压力过大。
未来扩展：
- 可以引入更复杂的用户标签算法（如机器学习模型打分），通过 ETL 过程将结果写入用户宽表。
- 可以探索使用 Doris 的 Array 类型存储用户的行为标签序列，进行用户行为路径分析。