如何基于Type 2 SCD维度表的最新映射进行数据聚合？

场景说明 #

维度表采用Type 2缓慢变化维度（SCD）维护物料信息：

• day_1：物料编码M-01的名称为mat_1_old，当日交易关联维度ID dim_mat_master_id=1
• day_2：M-01的名称更新为mat_1_new，该新记录的is_current_rec设为true，当日交易关联维度ID dim_mat_master_id=2

直接关联的问题 #

若直接关联事实表与维度表，按material_name聚合，会得到分属新旧名称的统计结果：

select dmm.material_name, sum(qty) as quantity
from fct_material_transaction fmt
join dim_material_master dmm on 
fmt.dim_mat_master_id = dmm.dim_mat_master_id
group by dmm.material_name

统计结果：

mat_1_new, 60
mat_1_old, 100

需求 #

需要基于物料的最新维度映射（即当前生效的名称）聚合所有交易数据，得到该物料的总数量。

给定方案的可行性分析 #

你提供的方案是可行的，方案代码如下：

with transaction_det as (
  select dmm.material_code, fmt.qty from fct_material_transaction fmt
  join dim_material_master dmm on 
  fmt.dim_mat_master_id = dmm.dim_mat_master_id
)
select dmm.material_name, sum(qty) from transaction_det td join dim_mat_master dmm on 
td.material_code = dmm.material_code where dmm.is_current_rec
group by dmm.material_name

统计结果：

mat_1_new, 160

方案逻辑 #

1. 通过CTEtransaction_det将每笔交易关联到对应维度记录，提取出不变的物料编码material_code和交易数量qty——material_code是物料的唯一标识，不会随名称等属性变化而改变
2. 用物料编码关联维度表中is_current_rec=true的当前生效记录，拿到最新物料名称，最后按最新名称聚合该物料的所有交易数量

优化写法 #

可以简化掉CTE，让逻辑更紧凑：

select current_dmm.material_name, sum(fmt.qty) as quantity
from fct_material_transaction fmt
-- 关联历史维度记录，获取物料编码
join dim_material_master hist_dmm 
  on fmt.dim_mat_master_id = hist_dmm.dim_mat_master_id
-- 关联当前生效维度记录，获取最新名称
join dim_material_master current_dmm 
  on hist_dmm.material_code = current_dmm.material_code
where current_dmm.is_current_rec = true
group by current_dmm.material_name

注意事项 #

• 确保material_code是物料的全局唯一且不变的标识，同一物料的所有历史维度记录必须对应同一个material_code
• 维度表中每个material_code只能存在一条is_current_rec=true的记录，否则会出现一对多关联，导致交易数量被重复统计

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