大数据

dwd层建模思考02

Posted by

问题：关于，联系人表和联系方式表dwd层：一个企业有多个联系人，一个联系人有多个联系方式，一个联系方式有多个采集来源，以及多个联系方式标签标注这个联系方式有效、空号、非本公司等。按照上面的理论，这样该如何做dwd层

🎯 一、业务关系梳理（实体与粒度）

你描述的结构：

企业 (1) ——> 联系人 (N)
联系人 (1) ——> 联系方式 (N)
联系方式 (1) ——> 来源 (N)        ← 通常一个联系方式一个来源，除非多源采集
联系方式 (1) ——> 标签 (N)        ← 一个联系方式可打多个标签（如“有效”、“非本公司”）

💡 注意：

  • “联系方式-来源” 通常是 1:1（一个号码从某渠道采集）
  • “联系方式-标签” 是 1:N（一个号码可被多人打标或系统自动打多个标签）

✅ 二、DWD 层设计原则回顾

  • DWD 是原子事实层，不聚合、不汇总
  • 维度可退化（把常用维度属性冗余进来，减少 JOIN）
  • 保持原始业务粒度
  • 标准化字段、统一码值、清洗脏数据
  • 为 DWS/ADS 提供可关联、可下钻的干净明细

✅ 三、推荐 DWD 层表结构设计（3张核心表）

📌 1. dwd_contact_base_inc —— 联系人基础信息表（粒度：每个联系人）

退化企业维度 + 联系人自身属性

contact_id                -- 联系人唯一ID（业务主键）
company_id                -- 所属企业ID
company_name              -- 退化维度：避免下游反复关联企业表
company_credit_code       -- 退化维度：统一社会信用代码（用于关联/筛选）

contact_name
position_code             -- 职位编码（标准化：如 01=法人, 02=技术负责人）
position_name             -- 退化维度：职位名称
department                -- 部门（如“财务部”、“研发中心”）
is_key_person             -- 是否关键人（0/1，预标注意图，可选）

-- 时间 & 元数据
first_appear_date         -- 首次出现日期（采集时间）
last_update_time
data_date                 -- 分区字段（快照日）
source_system
etl_time

✅ 为什么退化 company_name, credit_code？
→ 90% 的下游分析都要“按企业名称筛选”或“关联企业资质”，避免反复 JOIN 企业表，极大提升性能。

📌 2. dwd_contact_channel_inc —— 联系方式明细表（粒度：每个联系方式）

一个联系人有多个联系方式 → 每个联系方式一行

channel_id                -- 联系方式唯一ID（如 phone_id, email_id）
contact_id                -- 所属联系人ID
company_id                -- 冗余企业ID（便于直接按企业聚合，避免JOIN联系人表）

channel_type              -- 方式类型：phone / email / wechat / qq
channel_value             -- 具体值：138****1234, zhang@company.com

-- 退化来源维度（一个联系方式通常一个来源）
source_code               -- 来源编码：1=爬虫, 2=客户录入, 3=展会, 4=转介绍
source_name               -- 退化维度：来源名称

-- 标签处理（关键！见下方说明）
tag_codes                 -- 字符串数组或逗号分隔：'valid,not_company,verified'
tag_names                 -- 对应名称：'有效,非本公司,已验证'

-- 状态 & 时间
is_valid                  -- 是否有效（根据标签计算：如果含'valid'则为1，否则0）
is_primary                -- 是否主联系方式（业务标记或系统计算）
first_collected_time
last_verified_time
data_date                 -- 分区
etl_time

✅ 标签处理方案（重点）：

由于一个联系方式有多个标签，我们不拆分成多行（会破坏联系方式粒度），而是：

  • 在 ETL 阶段，将多个标签聚合为 逗号分隔字符串 或 Array 类型（如 Hive/Spark/Doris 支持）
  • 同时计算一个 is_valid 逻辑字段，方便下游快速筛选
  • 下游如需按标签分析，可用 LIKE '%not_company%' 或 array_contains(tag_codes, 'not_company')

示例：

tag_codes = 'valid,not_company,high_intent'
tag_names = '有效,非本公司,高意向'
is_valid = 1 （因为包含 'valid'）

⚠️ 注意：

  • 不要在 DWD 层为每个标签建一个字段（如 is_valid_flag, is_not_company_flag），会导致字段爆炸且不灵活。
  • 标签是动态的，未来可能新增“已加微信”、“拒接三次”等，用字符串/数组更易扩展

📌 3. （可选）dwd_contact_channel_tag_inc —— 联系方式标签明细表（粒度：每个标签）

如果你必须保留原始标签打标记录（如审计、追溯谁在何时打的标），可单独建此表

channel_id
tag_code
tag_name
tagged_by               -- 打标人（系统/人工）
tagged_time
data_date

✅ 适用场景：
需要分析“哪些销售喜欢打‘高意向’标签”
需要追溯“某个号码何时被标记为‘空号’”
标签有生效时间范围（SCD）

❗ 否则，推荐使用 dwd_contact_channel_inc 中的 tag_codes 字段，更简洁高效。

✅ 四、为什么不把“来源”和“标签”拆成独立维度表？

理论上可以，但工程上不推荐：

方案优点缺点适用场景
独立维度表 + 事实表关联符合范式、节省存储查询需多层 JOIN，性能差，复杂度高超大规模、标签频繁变更、需要 SCD2
退化到联系方式表（当前方案）查询快、易理解、开发简单冗余存储、更新成本略高你当前场景：推荐！

💡 在千万级数据规模下，“退化常用维度”是性价比最高的选择，尤其当来源和标签种类有限（<50种）。

✅ 五、DWS 层聚合表示例（后续构建）

DWD 层建好后，DWS 可轻松构建：

1. 企业联系人有效性统计

-- dws_company_contact_effectiveness_1d
company_id,
data_date,
total_contact_count,            -- 联系人总数
total_channel_count,            -- 联系方式总数
valid_channel_count,            -- 有效联系方式数（is_valid=1）
primary_channel_coverage_rate,  -- 有主联系方式的联系人占比
wechat_coverage_rate,           -- 有微信的联系人占比
...

联系方式标签分布

-- dws_company_channel_tag_dist_1d
company_id,
tag_code,
tag_name,
channel_count,
contact_count,
...

联系人-企业关联宽表（用于BI拖拽）

-- dws_contact_company_wide
contact_id,
contact_name,
company_name,
industry_name,
province_name,
position_name,
main_phone,
main_email,
has_wechat,
is_valid_contact,
last_verified_days_ago,  -- 距离上次验证天数
...

✅ 六、ETL 处理伪代码示例（联系方式表）

-- 步骤1：关联来源 + 聚合标签
WITH channel_with_tags AS (
  SELECT
    c.channel_id,
    c.contact_id,
    c.channel_type,
    c.channel_value,
    s.source_code,
    s.source_name,
    -- 聚合标签
    CONCAT_WS(',', COLLECT_SET(t.tag_code)) AS tag_codes,
    CONCAT_WS(',', COLLECT_SET(t.tag_name)) AS tag_names,
    -- 计算是否有效
    MAX(CASE WHEN t.tag_code = 'valid' THEN 1 ELSE 0 END) AS is_valid_flag
  FROM ods_contact_channel c
  LEFT JOIN ods_channel_source s ON c.source_id = s.source_id
  LEFT JOIN ods_channel_tag_relation r ON c.channel_id = r.channel_id
  LEFT JOIN ods_contact_tag t ON r.tag_id = t.tag_id
  WHERE c.is_deleted = 0
  GROUP BY c.channel_id, c.contact_id, c.channel_type, c.channel_value, s.source_code, s.source_name
)

-- 步骤2：插入 DWD
INSERT OVERWRITE dwd_contact_channel_inc PARTITION (data_date='2025-09-22')
SELECT
  channel_id,
  contact_id,
  -- 冗余 company_id（从联系人表获取）
  ct.company_id,
  channel_type,
  channel_value,
  source_code,
  source_name,
  tag_codes,
  tag_names,
  is_valid_flag AS is_valid,
  -- 判断是否主联系方式（示例逻辑）
  CASE WHEN channel_type = 'phone' AND is_valid_flag = 1 THEN 1 ELSE 0 END AS is_primary,
  CURRENT_DATE() AS first_collected_time, -- 实际应取最小采集时间
  CURRENT_TIMESTAMP() AS last_verified_time,
  '2025-09-22' AS data_date,
  CURRENT_TIMESTAMP() AS etl_time
FROM channel_with_tags c
JOIN dwd_contact_base_inc ct ON c.contact_id = ct.contact_id AND ct.data_date = '2025-09-22';

✅ 七、性能 & 扩展性建议

  1. 分区策略：
    • dwd_contact_base_inc 按 data_date + province_code 分区
    • dwd_contact_channel_inc 按 data_date + company_id 分区（或哈希）
  2. 索引/排序键（如使用 Doris/ClickHouse）：
    • company_id, contact_id, is_valid, channel_type
  3. 冷热分离：
    • 近6个月活跃联系人全量更新
    • 历史数据按需查询或归档
  4. 标签扩展：
    • 未来新增标签，只需更新 ETL 中的 COLLECT_SET 逻辑，无需改表结构

🧭 总结：你的 DWD 层应该这样建

表名粒度核心内容是否聚合
dwd_contact_base_inc每个联系人联系人属性 + 退化企业维度❌
dwd_contact_channel_inc每个联系方式方式值 + 退化来源 + 聚合标签 + 有效性标记❌（标签聚合是维度属性，非指标聚合）
dws_xxx企业/联系人/标签各种COUNT、覆盖率、趋势✅

✅ 这样设计：

  • 保持了原子粒度
  • 通过退化维度提升查询性能
  • 通过标签聚合兼顾灵活性与简洁性
  • 为 DWS 层打下坚实基础

问题：看上去联系人基本没变还是维度退化的工作，联系方式将来源、和标签放到了一起，以字符串的形式存储，现在有个问题：字符串存储还是array存储比较好，当前使用doris支持数组类型。另外后面dws聚合是基于冗余了来源和标签的dwd表吗？是不是明细表直接出dws根简单

✅ 一、标签/来源存储：Array vs 字符串 —— 用 Array！

既然你使用的是 Doris，且支持 Array 类型，强烈推荐使用 Array 存储标签和来源，理由如下：

：

维度字符串（逗号分隔）ARRAY类型推荐
✅ 查询便利性tag_codes LIKE '%valid%'→ 易误匹配（如 'invalid'）ARRAY_CONTAINS(tag_codes, 'valid')→ 精准、语义清晰⭐ Array
✅ 性能（Doris）需全字段扫描 + 字符串匹配Doris 对 Array 有优化，支持倒排索引（0.16+）⭐ Array
✅ 可读性'valid,not_company'→ 需人工解析['valid', 'not_company']→ 结构清晰⭐ Array
✅ 扩展性新增标签需字符串拼接，易出错ARRAY_PUSH/ARRAY_DISTINCT原生支持⭐ Array
✅ 数据质量无法约束格式（可能多逗号、空格）类型安全，ETL 可校验⭐ Array
❗ 存储空间略小（无括号、引号）略大（JSON格式存储）字符串

📌 结论：在 Doris 中，优先使用 Array 类型存储多值标签/来源
→ 更安全、更易用、性能更好（尤其 Doris 2.0+ 对复杂类型优化极佳

🛠️ Doris Array 使用示例：

-- 建表
CREATE TABLE dwd_contact_channel_inc (
  channel_id BIGINT,
  contact_id BIGINT,
  company_id BIGINT,
  channel_type VARCHAR(20),
  channel_value VARCHAR(100),
  source_codes ARRAY<VARCHAR(50)>,   -- 来源数组（通常1个，但支持多源）
  tag_codes ARRAY<VARCHAR(50)>,      -- 标签数组
  is_valid BOOLEAN AS ARRAY_CONTAINS(tag_codes, 'valid'), -- 虚拟列 or ETL计算
  ...
) PARTITION BY RANGE(data_date) (...);

-- 查询：筛选“有效”且“非本公司”的联系方式
SELECT *
FROM dwd_contact_channel_inc
WHERE ARRAY_CONTAINS(tag_codes, 'valid')
  AND ARRAY_CONTAINS(tag_codes, 'not_company');

-- 聚合：统计各标签分布
SELECT tag, COUNT(*) as cnt
FROM dwd_contact_channel_inc
LATERAL VIEW EXPLODE(tag_codes) tmp AS tag
GROUP BY tag;

💡 提示：Doris 2.1+ 支持 ARRAY_DISTINCT, ARRAY_JOIN, ARRAY_SORT 等函数，处理更灵活。

✅ 二、DWS 聚合：是基于“冗余了来源和标签的 DWD 表”，还是更原子的明细表？

先说结论：

✅ DWS 聚合应基于“冗余了来源和标签的 DWD 表”（即你当前设计的 dwd_contact_channel_inc）
❌ 不要跳过 DWD 直接从 ODS 或更原子的“无退化表”出 DWS

理由如下：

🎯 1. 为什么基于“冗余退化后的 DWD 表”？

✅DWD 是唯一可信的干净数据源ODS 有脏数据、未标准化、未过滤逻辑删除，不能直接用于聚合
✅避免重复清洗 & 退化逻辑如果 DWS 直接关联原始表，每个 DWS 表都要重复写“关联企业表、映射来源码值、聚合标签”逻辑 → 维护灾难
✅性能更优DWD 表已退化常用维度，DWS 聚合时无需再 JOIN 企业表、来源维表等，减少计算开销
✅口径统一所有 DWS 表基于同一份 DWD 数据，确保“企业名称”、“是否有效”等口径一致

📌 举个例子：

如果你在 DWS 层直接写：

-- ❌ 错误做法：跳过 DWD，直接从 ODS 聚合
SELECT 
  c.company_id,
  s.source_name,
  t.tag_name,
  COUNT(*) 
FROM ods_contact_channel c
JOIN ods_company_raw comp ON c.company_id = comp.id  -- 未标准化！
JOIN ods_source s ON c.source_id = s.id
JOIN ods_tag_relation r ON c.channel_id = r.channel_id
JOIN ods_tag t ON r.tag_id = t.id
WHERE comp.is_deleted = 0 
  AND c.status = 'active'  -- 口径不统一！
GROUP BY ...

→ 每个 DWS 表都要重复这段逻辑，一旦“有效”定义变更（如新增标签 verified），所有 DWS 表都要改！

而如果基于 DWD：

-- ✅ 正确做法：基于 DWD 聚合
SELECT 
  company_id,
  source_codes[1] AS source_name,  -- 假设第一个来源
  tag,
  COUNT(*) 
FROM dwd_contact_channel_inc
LATERAL VIEW EXPLODE(tag_codes) tmp AS tag
WHERE data_date = '2025-09-22'
  AND is_valid = 1  -- 口径已在 DWD 统一定义！
GROUP BY company_id, source_codes[1], tag;

→ 口径统一、逻辑简单、性能更好。

🎯 2. “明细表直接出 DWS 更简单”？—— 看似简单，实则埋雷

表面简单实际代价
“少一层，代码少”→ 重复逻辑遍布各处，修改成本高
“直接 JOIN 原始表”→ 每次都要处理脏数据、码值映射、逻辑删除，易出错
“灵活，想怎么算就怎么算”→ 口径不一致，报表对不齐，业务骂你

📌 数仓分层的核心价值就是“复用”和“解耦”：

  • DWD 负责“清洗、标准化、建模”
  • DWS 负责“按主题聚合”
    各司其职，才能长期可维护。

✅ 三、推荐架构：清晰、可维护、高性能

ODS（原始数据，含脏数据、多表分散）
  ↓ ETL：清洗、标准化、退化维度、聚合标签（Array）、计算 is_valid
DWD_contact_base_inc          → 联系人粒度，退化企业维度
DWD_contact_channel_inc       → 联系方式粒度，Array存储标签/来源，冗余company_id
  ↓ 聚合、关联、按主题汇总
DWS_company_contact_stat      → 企业联系人统计
DWS_channel_tag_analysis      → 标签分布分析
DWS_contact_effectiveness     → 联系有效性分析
  ↓ 应用
ADS（报表、API、BI、模型）

✅ 四、Doris 特别优化建议

对 Array 字段建倒排索引（Doris 2.0+）：

CREATE INDEX idx_tag_codes ON dwd_contact_channel_inc (tag_codes) USING INVERTED;

→ 加速 ARRAY_CONTAINS 查询

使用物化视图预聚合（Doris 2.0+）：

CREATE MATERIALIZED VIEW mv_company_tag_count AS
SELECT 
  company_id,
  tag,
  COUNT(*) as cnt
FROM dwd_contact_channel_inc
LATERAL VIEW EXPLODE(tag_codes) tmp AS tag
GROUP BY company_id, tag;

→ 查询自动路由，性能提升10倍+

分区 + 分桶：

PARTITION BY RANGE(data_date) (...)
DISTRIBUTED BY HASH(company_id) BUCKETS 10;

🧭 总结：你的两个问题，最优解是

问题最优解
标签/来源存储✅用 Array 类型（Doris 支持，更安全、高效、易用）
DWS 聚合来源✅基于冗余退化后的 DWD 表（非原始表），确保口径统一、逻辑复用、性能更优

💡 你当前的设计（联系人维度退化 + 联系方式 Array 存储标签/来源）完全正确，且符合现代数仓最佳实践。
继续坚持这个方向，后续 DWS/ADS 层会非常顺滑。

如果需要，我可以帮你：

  • 设计完整的 Doris 建表语句（含分区、分桶、索引）
  • 编写标签聚合的 ETL SQL
  • 设计 DWS 层物化视图

下一篇
dwd层建模思考03
上一篇 dwd层建模思考01