关于统计时间切片标签的一些sql

------当天付费明细表
DROP TABLE IF EXISTS rpt.tmp_mm_rb_daily_ffmx;
create table rpt.tmp_mm_rb_daily_ffmx as
select a.* FROM
(select
c.feemsisdn,
c.destmsisdn,
c.day,
c.price/1000 fee,
c.contentid,
dc.content_name,
c.ordernumber,
c.cdrtime,
c.createtime,
c.servcode,
 p.province_id,
 p.province as province_name,
case when b.paytype=1 and b.thirdapptype in (14,17) then '应用内计费'
when b.paytype=1 and b.thirdapptype=15 then '网页类计费应用'
ELSE '下载计费' end fftype,
 row_number()over(distribute by c.ordernumber sort by c.cdrtime) as rn
 from ods.ods_mm_cssp_cdr c  LEFT OUTER JOIN  dim.dim_mm_phone_city p ON substring(c.feemsisdn,1,7) =p.prefixnum
 LEFT OUTER JOIN dim.dim_mm_content dc ON  c.contentid=dc.content_id
LEFT OUTER JOIN dim.dim_mm_service_content b ON  c.servcode=b.servicecode
 where c.day =  date_sub('${hiveconf:CURRENT_DATE}',  1)
) a
where a.rn=1;

------180天付费明细表
DROP TABLE IF EXISTS rpt.tmp_mm_rb_daily_180ffmx;
create table rpt.tmp_mm_rb_daily_180ffmx as
select t.day, t.feemsisdn,t.province_id, t.province_name, t.contentid,t.content_name,t.fftype,count(t.ordernumber) ffcs,sum(t.fee) fee
FROM (
select a.* FROM
(select
  c.feemsisdn,
  c.destmsisdn,
  c.day,
  c.price/1000 fee,
  c.contentid,
  dc.content_name,
  c.ordernumber,
  p.province_id,
  p.province as province_name,
  case when b.paytype=1 and b.thirdapptype in (14,17) then '应用内计费'
when b.paytype=1 and b.thirdapptype=15 then '网页类计费应用'
ELSE '下载计费' end fftype,
  row_number()over(distribute by c.ordernumber sort by c.cdrtime) as rn
 from ods.ods_mm_cssp_cdr c LEFT OUTER JOIN  dim.dim_mm_phone_city p ON substring(c.feemsisdn,1,7) =p.prefixnum
  LEFT OUTER JOIN dim.dim_mm_content dc ON  c.contentid=dc.content_id
  LEFT OUTER JOIN dim.dim_mm_service_content b ON  c.servcode=b.servicecode
 where c.day >=  date_sub('${hiveconf:CURRENT_DATE}',  181)
 and  c.day <=  date_sub('${hiveconf:CURRENT_DATE}',  1)
) a where a.rn=1
) t
 group by t.day, t.feemsisdn, t.province_id,t.province_name, t.contentid,t.content_name,t.fftype;

------0-6点的订单量/总订单量（time）
DROP TABLE IF EXISTS rpt.tmp_mm_rb_daily_tb1;
create table rpt.tmp_mm_rb_daily_tb1 as
select a.day,a.province_id,a.province_name,a.contentid,a.content_name,a.fftype,
       sum(a.fee) amount,
       var_pop(a.ordernumber) charging_point_concentration,
       count(a.ordernumber) order_number,
       count(distinct a.feemsisdn) user_number,
       sum(a.fee)/count( a.ordernumber) avg_amount,
       sum(a.fee)/count(distinct a.feemsisdn) amount_per_user,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) in ('00','01','02','03','04','05','06') then a.ordernumber end) /count( a.ordernumber) time,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) = '00' then a.ordernumber end) /count( a.ordernumber) time0,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) = '01' then a.ordernumber end) /count( a.ordernumber) time1,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) = '02' then a.ordernumber end) /count( a.ordernumber) time2,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) = '03' then a.ordernumber end) /count( a.ordernumber) time3,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) = '04' then a.ordernumber end) /count( a.ordernumber) time4,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) = '05' then a.ordernumber end) /count( a.ordernumber) time5,
       count( case when hour(from_unixtime(unix_timestamp(a.cdrtime,'yyyyMMddHHmmss'),'yyyy-MM-dd HH:mm:ss')) = '06' then a.ordernumber end) /count( a.ordernumber) time6
  from rpt.tmp_mm_rb_daily_ffmx a
 group by a.day,a.province_id,a.province_name,a.contentid,a.content_name,a.fftype;

 ------半年内未付费的当日付费用户数/总付费用户数（new_user_6months1）
DROP TABLE IF EXISTS rpt.tmp_mm_rb_daily_tb2;
create table rpt.tmp_mm_rb_daily_tb2 as
select t1.day,
       t1.province_id,
       t1.province_name,
       t1.contentid,
       t1.content_name,
       t1.fftype,
       t2.ffyhs /t1.ffyhs  new_user_6months1
from (
 select  tt.day,tt.province_id, tt.province_name, tt.contentid,tt.content_name,tt.fftype, count(distinct tt.feemsisdn) ffyhs FROM rpt.tmp_mm_rb_daily_ffmx tt
 GROUP BY tt.day,tt.province_id, tt.province_name, tt.contentid,tt.content_name,tt.fftype
 ) t1 LEFT OUTER JOIN (
    select tt.province_id, tt.contentid,tt.content_name, tt.fftype, count(distinct tt.feemsisdn) ffyhs
    from (
      select a.feemsisdn, b.feemsisdn as bfeemsisdn from rpt.tmp_mm_rb_daily_ffmx a LEFT OUTER JOIN  rpt.tmp_mm_rb_daily_180ffmx b ON
      (a.feemsisdn=b.feemsisdn AND  b.day <  date_sub('${hiveconf:CURRENT_DATE}',  1))
      where b.feemsisdn is NULL
     ) t  JOIN rpt.tmp_mm_rb_daily_ffmx tt  ON t.feemsisdn = tt.feemsisdn GROUP BY  tt.province_id,tt.contentid,tt.content_name,tt.fftype
) t2 ON (t1.province_id=t2.province_id and t1.contentid=t2.contentid and t1.fftype=t2.fftype);

------半年内未在该APP付费的当日付费用户数/总付费用户数（new_user_6months2）
DROP TABLE IF EXISTS rpt.tmp_mm_rb_daily_tb3;
create table rpt.tmp_mm_rb_daily_tb3 as
select t1.day,
       t1.province_id,
       t1.province_name,
       t1.contentid,
       t1.content_name,
       t1.fftype,
       t2.ffyhs /t1.ffyhs  new_user_6months2
from (
 select  tt.day,tt.province_id, tt.province_name, tt.contentid,tt.content_name,tt.fftype, count(distinct tt.feemsisdn) ffyhs FROM rpt.tmp_mm_rb_daily_ffmx tt
 GROUP BY tt.day,tt.province_id, tt.province_name, tt.contentid,tt.content_name,tt.fftype
 ) t1 LEFT OUTER JOIN (
    select tt.province_id, tt.contentid, tt.content_name,tt.fftype,count(distinct tt.feemsisdn) ffyhs
    from (
      select a.feemsisdn, b.feemsisdn as bfeemsisdn from rpt.tmp