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);