以半累加事实为主的建模场景：酒店评分平台维度建模咨询

Hey there! 很高兴看到你在学习维度建模，并且要为酒店评分社交平台构建数据模型——这个场景很典型，我来给你梳理一套落地性强的方案：

维度表是建模的基础，用来描述业务中的实体，这里我们需要三个核心维度：

• 酒店维度表（dim_hotel）：
  主键：hotel_id（唯一标识酒店）
  核心字段：hotel_name（酒店名称）、address（酒店地址），还可以根据业务扩展city（所属城市）、star_level（星级）等字段，方便后续按地域、星级做分析。
• 用户维度表（dim_user）：
  主键：user_id（唯一标识用户）
  核心字段：user_name（用户名）、register_date（注册日期），如果有用户画像数据，还可以加age_group（年龄组）、preferred_city（偏好城市）等，用来分析不同用户群体的评分行为。
• 时间维度表（dim_date）：
  主键：date_key（比如YYYYMMDD格式的数字）
  核心字段：full_date（日期）、year（年）、quarter（季度）、month（月）、day_of_week（星期几），用来关联评论日期、回复日期，支持按日/周/月/季度等时间维度做统计。

事实表用来记录业务事件，这里分两个核心事务型事实表，再加一个可选的汇总事实表：

• 用户评分评论事实表（fact_hotel_review）：
  这是最核心的事务表，记录用户的每一次评分+评论行为
  主键：review_id（唯一标识一条评论）
  关联字段：hotel_id（关联酒店维度）、user_id（关联用户维度）、review_date_key（关联时间维度）
  事实字段：rating（1-5分）、review_content（评论内容），还可以加is_anonymous（是否匿名评论）、is_verified（是否是住客评论）这类标识字段。
• 酒店回复事实表（fact_hotel_reply）：
  记录酒店对评论的回复行为
  主键：reply_id（唯一标识一条回复）
  关联字段：review_id（关联评论事实表）、hotel_id（关联酒店维度）、reply_date_key（关联时间维度）
  事实字段：reply_content（回复内容）

你提到需要存储各评分等级的累计总数，这里有两种实用方案，按需选择：

• 实时计算方案：如果平台数据量不大，或者对实时性要求极高，可以直接通过SQL查询评论事实表得到统计结果，比如：

  SELECT 
      hotel_id,
      rating,
      COUNT(*) AS rating_total
  FROM fact_hotel_review
  GROUP BY hotel_id, rating;
  

• 预计算汇总方案：如果数据量较大、查询频繁，建议建一个酒店评分汇总事实表（fact_hotel_rating_summary），通过定时任务（比如每天凌晨）预计算并更新统计数据：
  主键：hotel_id + stat_date_key（按酒店+统计日期唯一标识）
  核心字段：rating_1_count（1分累计数）、rating_2_count、rating_3_count、rating_4_count、rating_5_count、total_reviews（总评论数）、avg_rating（平均评分）
  这个方案能大幅提升前端查询统计数据的速度，避免每次查询都做全表聚合。

• 优先采用星型模型：维度表直接关联事实表，不要用多层关联的雪花模型，这样查询效率更高，也更易维护。
• 给维度表加缓慢变化维（SCD）：比如酒店地址可能变更，用SCD Type 2来记录历史版本，这样可以追溯不同时期的酒店信息，分析地址变更前后的评分变化。
• 索引优化：在事实表的关联字段（hotel_id、user_id、date_key）上建立联合索引，维度表的主键建立主键索引，提升查询速度。
• 数据校验：在ETL过程中加入校验逻辑，比如确保rating字段的值只能是1-5，避免脏数据进入模型。

内容的提问来源于stack exchange，提问作者yellow days