数据仓库设计：公会每日工作量统计 - ODS & ADS层设计详解

数据仓库设计：公会每日工作量统计 - ODS & ADS层设计详解

本文将详细介绍如何将来自Kafka或API接口的用户答题情况和用户关系数据，设计ODS层和ADS层数据表，并最终实现公会每日工作量统计的功能。

数据源:

• 用户答题情况：
  • task_id - 任务id
  • user_id - 用户id
  • page_id - 页id
  • count - 工作量
• 用户关系：
  • user_id - 用户id
  • union_id - 公会id
  • alliance_id - 代理商id

需求： 统计每个公会每一天的工作量。

设计思路：

我们将采用两层数据模型：ODS层和ADS层。ODS层主要用于存储原始数据，而ADS层则用于存储聚合后的数据。

ODS层表结构和生成逻辑：

1. ODS_USER_ANSWER表：

   • 结构：task_id, user_id, page_id, count, source
   • 生成逻辑：从Kafka或API接口获取用户答题情况数据，将数据按照字段结构插入到ODS_USER_ANSWER表中。字段source标识数据来源，可以是'kafka'或'api'。

2. ODS_USER_RELATION表：

   • 结构：user_id, union_id, alliance_id, agent_id, source
   • 生成逻辑：从Kafka或API接口获取用户关系数据，将数据按照字段结构插入到ODS_USER_RELATION表中。字段source标识数据来源，可以是'kafka'或'api'。

3. ODS_ALLIANCE_DAILY_WORKLOAD表：

   • 结构：date, alliance_id, user_id, page_id, count, source
   • 生成逻辑：根据ODS_USER_ANSWER和ODS_USER_RELATION表的数据，通过联合查询计算每个公会每一天的工作量。将计算结果按照字段结构插入到ODS_ALLIANCE_DAILY_WORKLOAD表中。字段source标识数据来源，可以是'kafka'或'api'。

ADS层表结构和生成逻辑：

4. ADS_ALLIANCE_DAILY_WORKLOAD表（聚合表）：
   • 结构：date, alliance_id, total_workload
   • 生成逻辑：根据ODS_ALLIANCE_DAILY_WORKLOAD表的数据，按照公会和日期进行分组，计算每个公会每一天的总工作量。将计算结果按照字段结构插入到ADS_ALLIANCE_DAILY_WORKLOAD表中。

表命名规范：

• ODS：ODS_表名
• ADS：ADS_表名

表的复用性：

• ODS层的表可以根据需要保留历史数据，以支持数据溯源和分析。
• ADS层的表可以根据需要保留一段时间的数据，以支持近期的工作量统计和分析。根据存储空间和性能需求，可以定期清理旧数据。

总结：

本文详细介绍了如何设计ODS层和ADS层数据表，并最终实现公会每日工作量统计的功能。通过合理的表结构设计、生成逻辑和数据复用性策略，可以有效地管理和利用数据，为业务分析和决策提供支持。