star tree的oracle实现

两个层次的代码，后面的层次同理实现，基本上计算一个层次2分钟，后面因为数据剪枝条，可以更快。
我这里少写了一步是从tree中将剪出来的枝append到结果中


drop table t_tree_lv2;
create table t_tree_lv2 as
select tree_o,lv2,pv,convert,click,cnt,
CASE
WHEN (pv 1000
or convert > 2
or click > 10;

drop table t_tree_lv2_left;
create table t_tree_lv2_left
as
select a.* from t_tree_lv2 a join t_tree_lv2_merge b on (a.tree = b.tree);

drop table t_detail_lv2;
create table t_detail_lv2 as
select /*+ MAPJOIN(b) */
a.lv2,
a.lv3,
a.lv4,
a.lv5,
a.lv6,
a.lv7,
a.lv8,
a.lv9,
a.pv,
a.click,
a.convert,
b.tree
from t_detail_lv1 a
join t_tree_lv2_left b on (a.tree = b.tree_o and a.lv2 = b.lv2);


--lv2 lv3
--lv1 lv2
drop table t_tree_lv3;
create table t_tree_lv3 as
select tree_o,lv3,pv,convert,click,cnt,
CASE
WHEN (pv 1000
or convert > 2
or click > 10;

drop table t_tree_lv3_left;
create table t_tree_lv3_left
as
select a.* from t_tree_lv3 a join t_tree_lv3_merge b on (a.tree = b.tree);

drop table t_detail_lv3;
create table t_detail_lv3 as
select /*+ MAPJOIN(b) */
a.lv2,
a.lv3,
a.lv4,
a.lv5,
a.lv6,
a.lv7,
a.lv8,
a.lv9,
a.pv,
a.click,
a.convert,
b.tree
from t_detail_lv2 a
join t_tree_lv3_left b on (a.tree = b.tree_o and a.lv3 = b.lv3);