sql 使CPU使用100%，棘手！

askgyliu

这 个 SQL 的 DRIVING TABLE 应 当 是 CUST_CHURN_INFO_200710，  所 以 我 的 版 本 的 SQL 写 成 如 下 可 能 会 更 清 楚 些 ：

from CUST_CHURN_INFO_200710 aa inner join CUST_CCP_INFO_200710 cc
    on aa.cust_id = cc.cust_id
  inner join PAR_CUST_CUST_GRP_ASSOC_200710 b
    on aa.cust_id = b.cust_id
  left outer join V_PRE_EXP_GROUP_CUST1 a
    on aa.cust_id = a.cust_id
where (a.cust_id is not null or
       aa.on_net_time=4 or
       aa.LEAVE_DATE <= '3000-11-30 00:00:00')

在 任 何 情 况 下 ， 觉 得 PAR_CUST_CUST_GRP_ASSOC_200710 都 应 当 是 最 后 JOIN 的 TABLE， 因 为 CUST_ID NOT UNIQUE， 在 JOIN 这 个 TABLE 后 INTERMEDIATE RESULT TABLE 可 能 会 长 得 很 大  。

askgyliu

原帖由 myfriend2010 于 2007-12-12 09:42 发表
exfmt.txt为9月份的表做的sql,执行正常,也就是不会报CPU使用100%
exfmt1.txt为10月份的表做的sql,执行不正常,也就是会报CPU使用100%
exfmt2.txt为10月份的表做的sql,但是把临时表session.V_PRE_EXP_GROUP_CUST1换成fact表,执行正常,也就是不会报CPU使用100%

刚 刚 才 去 看 一 下 EXFMT2， 然 后 就 发 现 EXFMT1跟 EXFMT2的 区 别 不 只 是 TEMP 跟 FACT 的 区 别 而 已 ， it is much, much more than that...

myfriend2010

原帖由 askgyliu 于 2007-12-13 20:43 发表
我 现 在 尝 试 把 我 从 LZ 的 SQL 中 理  解 的 整 理 一 下 ， 看 是 不 是 LZ 想 要 的 ：

1) LZ有 三 个 TABLES： CUST_CHURN_INFO_200710，  CUST_CCP_INFO_200710，  AR_CUST_CUST_GRP_ASSOC_200710， 是 1： 1： M 的 关 系 。

2) LZ 要 的 数 据 是 CUST_CHURN_INFO_200710 中 ， LEAVE_DATE<='3000-11-30 00:00:00'， 或 是 on_net_time=4， 或 是 CUST_ID 已 经 在 V_PRE_EXP_GROUP_CUST1中 的 。 这 三 个 条 件 可 能 会 有 OVERLAPPING(互 相 覆 盖 )。

3) 从 CUST_CHURN_INFO_200710 拿 到 所 要  的 CUST_ID 后 ， 再 通 过 CUST_ID 到 CUST_CCP_INFO_200710 中 拿 到  其 他 的 数 据 (m/CLV/)。

4) 再 从 PAR_CUST_CUST_GRP_ASSOC_200710 中 拿 到 每 个 CUST_ID 所 属 的 CUST_GROUP_ID。

5) 最 后 把 这 些 INFORMATION 在 GROUP_ID 和 PRE_M_ARPU_D 这 个 层 次 上 做 AGGREGATION。

有 所 异 同 吗 ？

1>没有问题!
2>CUST_CHURN_INFO_200710取LEAVE_DATE<='3000-11-30 00:00:00'是一部分业务逻辑!来计算离网客户的;
CUST_CHURN_INFO_200710 取on_net_time=4是第二部分逻辑来计算新入网客户的;
关联PAR_CUST_CUST_GRP_ASSOC_200709取的是客户存信息的,因为PAR_CUST_CUST_GRP_ASSOC_200709中cust_ID不是唯一记录,所以做了一个临时表V_HML_EAL_TEM来取唯一的cust_ID,只要能inner join V_HML_EAL_TEM就表示存在!
3.4.5>没有问题

myfriend2010

原帖由 askgyliu 于 2007-12-13 20:45 发表


问 题 是 ， 这 是 LZ 想 要 的 吗 ？ 若 是 同 组 GROUP_ID+PRE_M_ARPU_D 在 V_HML_EAL_tem 中 出 现 多 次 ， LZ 会 怎 么 处 理 ？还 是 在 你 现 在 的 数 据 中 还 没 出 现 这 种 情 况 ？

V_HML_EAL_tem 只记录上各月的PAR_CUST_CUST_GRP_ASSOC_200709,只要按照cust_id能关联上PAR_CUST_CUST_GRP_ASSOC_200709就表示cust存在,就符合逻辑,之所以不直接那PAR_CUST_CUST_GRP_ASSOC_200710 来inner join PAR_CUST_CUST_GRP_ASSOC_200709,是因为CUST_ID在PAR_CUST_CUST_GRP_ASSOC_200709中不是唯一的!所以建了一张临时表来生成唯一CUST_ID!

我感觉是符合逻辑的!

myfriend2010

确实,你终于看到了啊!这也是问题所在!换成临时表就出问题,换成fact表就没有问题!

原帖由 askgyliu 于 2007-12-13 22:00 发表


刚 刚 才 去 看 一 下 EXFMT2， 然 后 就 发 现 EXFMT1跟 EXFMT2的 区 别 不 只 是 TEMP 跟 FACT 的 区 别 而 已 ， it is much, much more than that...

myfriend2010

我说过V_HML_EAL_tem只是过程中下一步计算的一个铺垫!

我把过程中下面的代码发上来

1. 
   
2. declare global temporary table V_HML_EAL_new(group_id decimal(20,0), EXIST_CUST_C decimal(10,0),EXIST_CUST_R decimal(6,2), ADD_CUST_C decimal(10,0),ADD_CUST_R decimal(6,2), LOSS_CUST_C decimal(10,0),LOSS_CUST_R decimal(6,2),HIGH_EXIST_CUST_C decimal(10,0),HIGH_ADD_CUST_C decimal(10,0), HIGH_LOSS_CUST_C decimal(10,0),MIDD_EXIST_CUST_C decimal(10,0), MIDD_ADD_CUST_C decimal(10,0), MIDD_LOSS_CUST_C decimal(10,0), LOW_EXIST_CUST_C decimal(10,0),LOW_ADD_CUST_C decimal(10,0), LOW_LOSS_CUST_C decimal(10,0),H_EXIST_ADD_LOSS_STRU varchar(50), M_EXIST_ADD_LOSS_STRU varchar(50),L_EXIST_ADD_LOSS_STRU varchar(50), EXIST_H_M_L_STRU varchar(50),ADD_H_M_L_STRU varchar(50), LOSS_H_M_L_STRU varchar(50))  on commit preserve rows not logged with replace;
   
3.                 insert into session.V_HML_EAL_new (
   
4.                         group_id , EXIST_CUST_C ,
   
5.                 EXIST_CUST_R , ADD_CUST_C ,
   
6.                 ADD_CUST_R, LOSS_CUST_C,
   
7.                 LOSS_CUST_R,HIGH_EXIST_CUST_C ,
   
8.                 HIGH_ADD_CUST_C, HIGH_LOSS_CUST_C,
   
9.                 MIDD_EXIST_CUST_C, MIDD_ADD_CUST_C,
   
10.                 MIDD_LOSS_CUST_C, LOW_EXIST_CUST_C,
    
11.                 LOW_ADD_CUST_C, LOW_LOSS_CUST_C,
    
12.                 H_EXIST_ADD_LOSS_STRU, M_EXIST_ADD_LOSS_STRU,
    
13.                 L_EXIST_ADD_LOSS_STRU, EXIST_H_M_L_STRU,
    
14.                 ADD_H_M_L_STRU, LOSS_H_M_L_STRU               
    
15.                         )
    
16.             select v.group_id,
    
17.                 sum(EXIST_CUST_C) as EXIST_CUST_C ,cast(sum(EXIST_CUST_C) as decimal(20,2))*100/sum(v.cust_count) as EXIST_CUST_R
    
18.                 ,sum(ADD_CUST_C) as ADD_CUST_C,cast(sum(ADD_CUST_C) as decimal(20,2))*100/sum(v.cust_count) as ADD_CUST_R
    
19.                 ,sum(LOSS_CUST_C) as LOSS_CUST_C,cast(sum(LOSS_CUST_C) as decimal(20,2))*100/sum(v.cust_count) as LOSS_CUST_R,
    
20.                 sum(case when x.pre_m_arpu_d ='高' then EXIST_CUST_C else 0 end) as HIGH_EXIST_CUST_C ,--高存
    
21.                 sum(case when x.pre_m_arpu_d ='高' then ADD_CUST_C else 0 end) as HIGH_ADD_CUST_C,--高增
    
22.                 sum(case when x.pre_m_arpu_d ='高' then LOSS_CUST_C else 0 end) as HIGH_LOSS_CUST_C,--高流
    
23.                 sum(case when x.pre_m_arpu_d ='中' then EXIST_CUST_C else 0 end) as MIDD_EXIST_CUST_C ,--中存
    
24.                 sum(case when x.pre_m_arpu_d ='中' then ADD_CUST_C else 0 end) as MIDD_ADD_CUST_C,--中增
    
25.                 sum(case when x.pre_m_arpu_d ='中' then LOSS_CUST_C else 0 end) as MIDD_LOSS_CUST_C,--中留
    
26.                 sum(case when x.pre_m_arpu_d ='低' then EXIST_CUST_C else 0 end) as LOW_EXIST_CUST_C ,--低存
    
27.                 sum(case when x.pre_m_arpu_d ='低' then ADD_CUST_C else 0 end) as LOW_ADD_CUST_C,--低增
    
28.                 sum(case when x.pre_m_arpu_d ='低' then LOSS_CUST_C else 0 end) as LOW_LOSS_CUST_C,--低留
    
29.                 case when integer(round(sum(case when x.pre_m_arpu_d ='高' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0) else 0 end),0))=0 then null else
    
30.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='高' then EXIST_CUST_C else 0  end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='高' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0) else 0  end),0))  as char(3)) ||':'||
    
31.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='高' then ADD_CUST_C else 0 end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='高' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0)  else 0 end),0)) as char(3)) ||':'||
    
32.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='高' then LOSS_CUST_C else 0  end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='高' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0)  else 0 end),0)) as char(3)) end as H_EXIST_ADD_LOSS_STRU--高价值客户存/增/流结构
    
33.                 ,case when integer(round(sum(case when x.pre_m_arpu_d ='中' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0) else 0 end),0))=0 then null else
    
34.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='中' then EXIST_CUST_C else 0 end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='中' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0) else 0 end),0))  as char(3)) ||':'||
    
35.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='中' then ADD_CUST_C else 0 end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='中' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0) else 0 end),0)) as char(3)) ||':'||
    
36.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='中' then LOSS_CUST_C else 0 end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='中' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0) else 0 end),0)) as char(3)) end as M_EXIST_ADD_LOSS_STRU--中价值客户存/增/流结构
    
37.                 ,case when integer(round(sum(case when x.pre_m_arpu_d ='低' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0) else 0  end),0))=0 then null else
    
38.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='低' then EXIST_CUST_C else 0  end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='低' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0)  else 0 end),0))  as char(3)) ||':'||
    
39.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='低' then ADD_CUST_C else 0 end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='低' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0)  else 0 end),0)) as char(3)) ||':'||
    
40.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='低' then LOSS_CUST_C else 0 end) as decimal(20,2))*100/sum(case when x.pre_m_arpu_d ='低' then coalesce(EXIST_CUST_C,0)+coalesce(ADD_CUST_C,0)+coalesce(LOSS_CUST_C,0)  else 0 end),0)) as char(3)) end as L_EXIST_ADD_LOSS_STRU--低价值客户存/增/流结构
    
41.                 ,cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='高' then EXIST_CUST_C else 0 end) as decimal(20,2))*100/sum(EXIST_CUST_C),0)) as char(3)) ||':'||
    
42.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='中' then EXIST_CUST_C else 0 end) as decimal(20,2))*100/sum(EXIST_CUST_C),0)) as char(3)) ||':'||
    
43.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='低' then EXIST_CUST_C else 0 end) as decimal(20,2))*100/sum(EXIST_CUST_C),0)) as char(3)) as EXIST_H_M_L_STRU--存量客户高/中/低价值结构
    
44.                 ,cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='高' then ADD_CUST_C else 0 end) as decimal(20,2))*100/sum(ADD_CUST_C),0)) as char(3)) ||':'||
    
45.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='中' then ADD_CUST_C else 0 end) as decimal(20,2))*100/sum(ADD_CUST_C),0)) as char(3)) ||':'||
    
46.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='低' then ADD_CUST_C else 0 end) as decimal(20,2))*100/sum(ADD_CUST_C),0)) as char(3)) as ADD_H_M_L_STRU--增量客户高/中/低价值结构
    
47.                 ,cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='高' then LOSS_CUST_C else 0 end) as decimal(20,2))*100/sum(LOSS_CUST_C),0)) as char(3)) ||':'||
    
48.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='中' then LOSS_CUST_C else 0 end) as decimal(20,2))*100/sum(LOSS_CUST_C),0)) as char(3)) ||':'||
    
49.                 cast(integer(round(cast(sum(case when x.pre_m_arpu_d ='低' then LOSS_CUST_C else 0 end) as decimal(20,2))*100/sum(LOSS_CUST_C),0)) as char(3)) as LOSS_H_M_L_STRU--流失客户高/中/低价值结构
    
50.                 from session.V_HML_EAL_tem x inner join session.V_GROUP_COUNT V
    
51.                 on x.group_id=V.group_id
    
52.                 group by v.group_id with ur;
    
53. 
    

复制代码

myfriend2010

可以看到在上面的处理中,按照group_id进行了分组!

myfriend2010

declare global temporary table V_GROUP_COUNT(group_id decimal(20,0), cust_count decimal(20,0),MODEL_ID DECIMAL(8,0)) on commit preserve rows not logged with replace;
                insert into session.V_GROUP_COUNT(group_id, cust_count,MODEL_ID) (select b.cust_group_id as group_id, count(b.cust_id) as cust_count,MODEL_ID from PAR_CUST_CUST_GRP_ASSOC_200710 b group by b.cust_group_id,b.MODEL_ID);
                commit;

这里cust_group_id是唯一的记录,也就是说按照cust_group_id分组和按照cust_group_id,MODEL_ID分组的记录数是一致的!

askgyliu

原帖由 myfriend2010 于 2007-12-14 08:57 发表
确实,你终于看到了啊!这也是问题所在!换成临时表就出问题,换成fact表就没有问题!

嗯，看起来我们看问题的角度很不相同。

我的意思是说EXFMT1中的SQL跟EXFMT2中的SQL的区别不止是TEMP跟FACT那么简单。所以你在#63中的这句话“exfmt2.txt为10月份的表做的sql,但是把临时表session.V_PRE_EXP_GROUP_CUST1换成fact表,执行正常,也就是不会报CPU使用100%”是很难成立的。

askgyliu

原帖由 myfriend2010 于 2007-12-14 08:48 发表


2>CUST_CHURN_INFO_200710取LEAVE_DATE<='3000-11-30 00:00:00'是一部分业务逻辑!来计算离网客户的;
CUST_CHURN_INFO_200710 取on_net_time=4是第二部分逻辑来计算新入网客户的;
关联PAR_CUST_CUST_GRP_ASSOC_200709取的是客户存信息的,因为 PAR_CUST_CUST_GRP_ASSOC_200709中cust_ID不是唯一记录,所以做了一个临时表V_HML_EAL_TEM来取唯一的 cust_ID,只要能inner join V_HML_EAL_TEM就表示存在!

我的版本就是刚好包括了这三种情况：

1) 离网客户：aa.LEAVE_DATE <= '3000-11-30 00:00:00'
2) 新入网客户： aa.on_net_time=4
3) inner join V_HML_EAL_TEM就表示存在：
  left outer join V_PRE_EXP_GROUP_CUST1 a
    on aa.cust_id = a.cust_id
跟 where a.cust_id is not null

这里只是一个小技巧：
  select <something>
  from table_a left outer join table_b on <some conditions>
  where table_b.<a-non-null-field> is not null
跟
  select <something>
  from table_a inner join table_b on <some conditions>
出来的结果是一样的。

这种写法平常不太会用到。但在LZ的这种情况下，恰好可以派上用场。