Used Query

---  查询 look back 6 days logic period_key.

select s.store_id, i.upc, sf.period_key from JNJ_CASINO_HF.spd_fact_pivot sf 
inner join JNJ_CASINO_HF.olap_store s on s.store_key=sf.store_key
inner join JNJ_CASINO_HF.olap_item i on i.item_key=sf.item_key
inner join (select s1.store_id, i1.upc from JNJ_CASINO_HF.spd_fact_pivot sf1
inner join JNJ_CASINO_HF.olap_store s1 on s1.store_key=sf1.store_key
inner join JNJ_CASINO_HF.olap_item i1 on i1.item_key=sf1.item_key
group by s1.store_id, i1.upc having (Sum("POG_IND")>0 or Sum("Ranged Indicator")>0)) a on a.store_id=s.store_id and a.upc=i.upc
where ("Ranged Indicator" is null or "POG_IND" is null)
and sf.period_key >= (select min(period_key) from JNJ_CASINO_HF.spd_fact_pivot where "Ranged Indicator" is not null or "POG_IND" is not null) 
and sf.period_key <= (select max(period_key) from JNJ_CASINO_HF.spd_fact_pivot where "Ranged Indicator" is not null or "POG_IND" is not null) 
group by s.store_id, i.upc, sf.period_key 
order by s.store_id, i.upc, sf.period_key
limit 30

转载于:https://www.cnblogs.com/keepSmile/p/5942420.html