一般维度变更用拉链,度量值变更可以用快照
--获取0-99
WITH a AS (SELECT explode(split('0,1,2,3,4,5,6,7,8,9',',')))
select cast(a1.col + 10*a2.col as int) from a a1 JOIN a a2 ON 1=1
拉链表,做出一段日历表,每天拿去inner join不等关联拉链表获取出当天切片。这样就能获取出每天拉链表的状况,拿这个每天去匹配
create temporary table temp.v_dmn_org_daily as
WITH a AS (SELECT explode(split('0,1,2,3,4,5,6,7,8,9',',')))
SELECT t.*
,regexp_replace(d.dt_date,'-','') as dt_date
FROM dim.v_fin_dmn_org t
INNER JOIN (
SELECT
date_sub('$rptDate', cast(a1.col + 10*a2.col as int)) dt_date,
a1.col + 10*a2.col as num
FROM a a1 JOIN a a2 ON 1=1) d
ON 1=1 and num <20
WHERE regexp_replace(t.start_dt,'-','') <= regexp_r