月亮的变化.jpg

• 介绍
• • 概念
• • 应用场景
• • 处理方式
• • 使用
• • 存在的问题
• 实践应用
• • 基础数据
• • 处理方式a
• • 处理方式b
• • 优劣对比
• • 总结

介绍

概念

​ Slowly Changing Dimensions

​ 在现实世界中，维度的属性并不是静态的，它会随着时间的流失发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维，并且把处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，有时也简称为处理SCD的问题。

应用场景

​ 支持对于历史数据的追溯，Inmon对于数仓的定义已成为公认。而数仓是这么考虑的：在企业管理和决策中面向主题的、集成的、与时间相关的、不可修改的数据集合。

处理方式

缓慢变化维.png

如何使用

-- 通过对开始和结束时间的限定，获取指定时间点数据情况
select name
from tableA 
where begin_time<='2019-06-02' and end_time>='2019-06-02'

存在的问题

1. 数据重复

   处理数据时，总会有各种各样的情况导致数据更新失败或错误。这个时候，需要对应的脚本支持数据重跑。在缓慢变化维中，由于保留历史数据，如何保证重跑数据时保障数据正确性、不会大的变化。

2. 存储与性能

   缓慢变化维记录整个历史数据，数据会以增量的方式进行扩张。随着时间推移，数据就会越来越多。查询的效率也会随之面临挑战。

3. 应用的边界

   数据的变化是不可避免的，在处理数据时哪些是必须要使用，那么可用可不用。如何选择

实践应用

基础数据

变化-基础数据.png

处理方式a

​ 目标 记录数据变化轨迹，支持数据重跑

-- 更新已有数据
insert overwrite table temp_db.temp_scd_checking
select 
 a.id
,a.name
,a.money
,a.status
,a.begin_time
,case when b.id is not null and a.end_time='9999-09-09' then date_sub('${dealDate}',1) else a.end_time end as end_time
from temp_db.temp_scd_checking as a
left join (
  select 
   id
  from temp_db.temp_scd_source_data
  where p_dt='${dealDate}'
) as b on a.id=b.id
;

-- 插入新数据
insert overwrite table temp_db.temp_scd_checking
select 
 case when a.id is not null then a.id else b.id end as id
,case when a.id is not null then a.name else b.name end as name
,case when a.id is not null then a.money else b.money end as money
,case when a.id is not null then a.status else b.status end as status
,case when a.id is not null then '${dealDate}' else b.begin_time end as begin_time
,case when a.id is not null then '9999-09-09' else b.end_time end as end_time
from (
select 
   id
  ,name
  ,money
  ,status
  ,p_dt
  from temp_db.temp_scd_source_data
  where p_dt='${dealDate}'
) as a
full join (
  select 
   *
  from temp_db.temp_scd_checking 
  where end_time='9999-09-09'
) as b on a.id=b.id

-- 历史去除重复(by: sunhj)
union all
select 
 id
,name
,money
,status
,begin_time
,end_time
from temp_db.temp_scd_checking 
where end_time<>'9999-09-09'
group by 
  id
 ,name
 ,money
 ,status
 ,begin_time
 ,end_time
 ;

结果

方式a执行结果.png

重跑结果(0602-0604)

方式a重跑.png

处理方式b

目标 方式a中处理效果太差，时间、脚本都太长。进行优化

-- 支持历史重刷 (by sunhj)
insert overwrite table temp_db.temp_scd_checking
select 
 a.id
,a.name
,a.money
,a.status
,a.begin_time
,case when b.id is not null and a.end_time='9999-09-09' then date_sub('${dealDate}',1) else a.end_time end as end_time
from 
(
  select 
   id
  ,name
  ,money
  ,status
  ,begin_time
  ,case when begin_time=date_sub('${dealDate}',1) then '9999-09-09' else end_time end as end_time
  from temp_db.temp_scd_checking
  where begin_time<'${dealDate}'
) as a 
left join (
  select
   id
  from temp_db.temp_scd_source_data
  where p_dt='${dealDate}'
) as b on a.id=b.id
;

-- 更新数据 (by sunhj)
insert overwrite table temp_db.temp_scd_checking
select 
 case when a.id is not null then a.id else b.id end as id
,case when a.id is not null then a.name else b.name end as name
,case when a.id is not null then a.money else b.money end as money
,case when a.id is not null then a.status else b.status end as status
,case when a.id is not null then a.begin_time else b.begin_time end as begin_time
,case when a.id is not null then a.end_time else b.end_time end as end_time
from (
select 
   id
  ,name
  ,money
  ,status
  ,'${dealDate}' as begin_time
  ,'9999-09-09' as end_time
  from temp_db.temp_scd_source_data
  where p_dt='${dealDate}'
) as a
full join temp_db.temp_scd_checking as b on a.id=b.id and b.end_time='9999-09-09'
;

结果略.

优劣对比

总结: 建议方式b处理