含重叠时段收视业务的数仓星型schema设计问题咨询

核心选型结论：无需使用桥接表，采用「会话拆分+原子事实表」的单星型结构即可 #

桥接表仅适用于多值维度的复杂关联场景，你的业务本质是原始会话粒度太粗，1个会话对应多档节目，直接将粒度拆到「会话-节目重叠时段」的原子粒度即可，性能和易用性都远优于桥接表方案，完全覆盖所有分析需求。

完整模型结构 #

维度表 #

• DIM_Date：日期维度，粒度1天，字段包含date_key（格式yyyyMMdd，主键）、年、季度、月、月内周数、周内日期（周一至周日标识）、是否节假日等，满足按日、周、月维度的聚合需求
• DIM_Time：时间维度，粒度15分钟，字段包含time_key（格式HHMM，比如0015、0030，主键）、时段标签（夜间/上午/下午/晚间等自定义分类）、15分钟间隔序号，满足分时、分15分钟粒度的统计
• DIM_Channel：频道维度，字段包含channel_key（主键）、频道名称、频道所属分组、频道上线状态等，支撑分频道指标、频道份额计算
• DIM_Program：节目维度，字段包含program_key（主键）、节目名称、节目分类（12种枚举值）、节目所属频道、节目播出日期、节目播出开始时间key、节目播出结束时间key等，支撑分节目、分节目分类的统计
• DIM_Device：设备维度，字段包含device_key（主键）、设备ID、设备类型、用户归属区域等，支撑设备维度的访问类指标统计

事实表：FACT_View_15min（原子粒度：1个会话在单个节目上的15分钟区间收视记录） #

数据加工逻辑

1. 先关联会话表和节目播出表，匹配出和每个会话时间重叠的所有节目，计算单会话在对应节目上的实际观看时长watch_duration：

-- 伪逻辑示例
会话结束时间 = TuneTime + TuneDuration
watch_duration = 最小值(会话结束时间, 节目EndDate) - 最大值(会话TuneTime, 节目StartDate)

2. 再把上述结果按15分钟的最小统计粒度拆分，确保每条事实记录都对应唯一的date_key和time_key，方便后续所有时间维度的聚合。

核心字段

• date_key：关联DIM_Date
• time_key：关联DIM_Time
• device_key：关联DIM_Device
• channel_key：关联DIM_Channel
• program_key：关联DIM_Program
• watch_duration：该条记录对应的实际收视时长（单位秒，核心度量值）
• session_id：原始会话ID，方便回溯原始数据

指标计算方式示例 #

• 设备整体访问量：统计查询时间范围内distinct(device_key)的数量
• 分频道收视份额：单频道总watch_duration / 所有频道总watch_duration * 100%
• 分节目分类收视总时长：按DIM_Program.classification分组求和watch_duration
• 收视最高节目排名：按program_key分组求和watch_duration后倒序取TOP N
• 周内日期收视趋势：按DIM_Date.week_day分组求和watch_duration

方案优势 #

• 完全支持所有自定义时间维度的查询需求，不需要运行时复杂计算，聚合性能高
• 所有维度均为单值关联，没有多值维度的关联歧义，业务用户自助查询门槛低
• 后续扩展指标方便，只需在事实表上新增对应度量值或关联新维度即可

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