ccms bug记录1

20130202

周期表结果中有重复记录：查到是t_latitude_period_exuid的重复记录：

重新跑：

insert overwrite  table t_latitude_period_exuid partition(stat_time='{STAT_DATE}',latitude_type)
select * from(
    select user_name,1,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp 
    where send_time='{STAT_DATE}'
    group by user_name,latitude_id,latitude_type
  union all
    select user_name,2,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp
    where send_time>date_sub('{STAT_DATE}',7)
    group by user_name,latitude_id,latitude_type
  union all
    select user_name,3,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp
    where send_time>date_sub('{STAT_DATE}',15)
    group by user_name,latitude_id,latitude_type
  union all
    select user_name,4,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp
    where send_time>date_sub('{STAT_DATE}',30)
    group by user_name,latitude_id,latitude_type
  union all
    select user_name,5,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp
    where send_time>date_sub('{STAT_DATE}',90)
    group by user_name,latitude_id,latitude_type
  union all
    select user_name,6,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp
    where (weekofyear(send_time)=weekofyear('{STAT_DATE}') and year(send_time)=year('{STAT_DATE}'))
    or (weekofyear(send_time)=IF(weekofyear('{STAT_DATE}')=1,)
    group by user_name,latitude_id,latitude_type
  union all
    select user_name,7,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp 
    where month(send_time)=month('{STAT_DATE}') and year(send_time)=year('{STAT_DATE}')
    group by user_name,latitude_id,latitude_type
union all
    select user_name,8,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp
    where (weekofyear(send_time)=weekofyear(date_sub('{STAT_DATE}',7)) and year(send_time)=year(date_sub('{STAT_DATE}',7)))
    or (weekofyear(send_time)=(IF(weekofyear(date_sub('{STAT_DATE}',7))=1, and year(send_time)=year(date_sub('{STAT_DATE}',7)))
    group by user_name,latitude_id,latitude_type
  union all
    select user_name,9,latitude_id,sum(sms_send_count),sum(sms_bill_count),sum(edm_send_count),
    sum(wap_send_count),sum(mms_send_count),sum(coupon_send_count),sum(ump_send_count),latitude_type
    from (select * from t_fx_latitude_daily where stat_time<='{STAT_DATE}' and stat_time>date_sub('{STAT_DATE}',90))tmp
    where month(send_time)=IF(month('{STAT_DATE}')=1,12,month('{STAT_DATE}')-1) and year(send_time)=IF(month('{STAT_DATE}')=1,year('{STAT_DATE}')-1,year('{STAT_DATE}'))
    group by user_name,latitude_id,latitude_type
)tmp;