ab实验sql怎么写

----------------ab实验代码

create table if not exists dws.ab_test_result 

(

new_date date comment '日期',

sceneid string comment '分流id',

name string comment '分流名字',

quanxuan_uid bigint comment '圈选用户数',

jinzhan_uid bigint comment '圈选且登录用户数',

dayget_1dayuse_uid bigint comment '当天下单用户数',

dayget_1dayuse_revbefore decimal(14,2) comment '当天订单优惠前营收',

dayget_1dayuse_revafter decimal(14,2) comment '当天订单优惠后营收',

dayget_1dayuse_legcount bigint comment '当天票量',

dayget_1dayuse_ordernum bigint comment '当天订单数',

dayget_1dayuse_hongbao decimal(14,2) comment '当天红包补贴',

dayget_3dayuse_uid bigint comment '3天下单用户数',

dayget_3dayuse_revbefore decimal(14,2) comment '3天订单优惠前营收',

dayget_3dayuse_revafter decimal(14,2) comment '3天订单优惠后营收',

dayget_3dayuse_legcount bigint comment '3天票量',

dayget_3dayuse_ordernum bigint comment '3天订单数',

dayget_3dayuse_hongbao decimal(14,2) comment '3天红包补贴',

dayget_7dayuse_uid bigint comment '7天下单用户数',

dayget_7dayuse_revbefore decimal(14,2) comment '7天订单优惠前营收',

dayget_7dayuse_revafter decimal(14,2) comment '7天订单优惠后营收',

dayget_7dayuse_legcount bigint comment '7天票量',

dayget_7dayuse_ordernum bigint comment '7天订单数',

dayget_7dayuse_hongbao decimal(14,2) comment '7天红包补贴',

dayget_14dayuse_uid bigint comment '14天下单用户数',

dayget_14dayuse_revbefore decimal(14,2) comment '14天订单优惠前营收',

dayget_14dayuse_revafter decimal(14,2) comment '14天订单优惠后营收',

dayget_14dayuse_legcount bigint comment '14天票量',

dayget_14dayuse_ordernum bigint comment '14天订单数',

dayget_14dayuse_hongbao decimal(14,2) comment '14天红包补贴'

dayget_30dayuse_uid bigint comment '30天下单用户数',

dayget_30dayuse_revbefore decimal(14,2) comment '30天订单优惠前营收',

dayget_30dayuse_revafter decimal(14,2) comment '30天订单优惠后营收',

dayget_30dayuse_legcount bigint comment '30天票量',

dayget_30dayuse_ordernum bigint comment '30天订单数',

dayget_30dayuse_hongbao decimal(14,2) comment '30天红包补贴'

);

 

insert overwrite table ads_traffic.ab_test_result 

select

a.new_date,

a.sceneid,

a.name,

count(distinct a.primarykey) as quanxuan_uid,

count(distinct b.unionid) as jinzhan_uid,

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<1 then c.unionid else null end) as dayget_1dayuse_uid,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<1 then c.rev_before else null end) as dayget_1dayuse_revbefore,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<1 then c.rev_after else null end) as dayget_1dayuse_revafter,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<1 then c.legcount else null end) as dayget_1dayuse_legcount,

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<1 then c.serialid else null end) as dayget_1dayuse_ordernum,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<1 then c.redpacketprice else null end) as dayget_1dayuse_hongbao,

 

 

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<3 then c.unionid else null end) as dayget_3dayuse_uid,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<3 then c.rev_before else null end) as dayget_3dayuse_revbefore,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<3 then c.rev_after else null end) as dayget_3dayuse_revafter,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<3 then c.legcount else null end) as dayget_3dayuse_legcount,

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<3 then c.serialid else null end) as dayget_3dayuse_ordernum,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<3 then c.redpacketprice else null end) as dayget_3dayuse_hongbao,

 

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<7 then c.unionid else null end) as dayget_7dayuse_uid,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<7 then c.rev_before else null end) as dayget_7dayuse_revbefore,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<7 then c.rev_after else null end) as dayget_7dayuse_revafter,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<7 then c.legcount else null end) as dayget_7dayuse_legcount,

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<7 then c.serialid else null end) as dayget_7dayuse_ordernum,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<7 then c.redpacketprice else null end) as dayget_7dayuse_hongbao,

 

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<14 then c.unionid else null end) as dayget_14dayuse_uid,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<14 then c.rev_before else null end) as dayget_14dayuse_revbefore,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<14 then c.rev_after else null end) as dayget_14dayuse_revafter,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<14 then c.legcount else null end) as dayget_14dayuse_legcount,

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<14 then c.serialid else null end) as dayget_14dayuse_ordernum,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<14 then c.redpacketprice else null end) as dayget_14dayuse_hongbao,

 

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<30 then c.unionid else null end) as dayget_30dayuse_uid,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<30 then c.rev_before else null end) as dayget_30dayuse_revbefore,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<30 then c.rev_after else null end) as dayget_30dayuse_revafter,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<30 then c.legcount else null end) as dayget_30dayuse_legcount,

count(distinct case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<30 then c.serialid else null end) as dayget_30dayuse_ordernum,

sum(case when c.pdate>=b.pdate and datediff(c.pdate,b.pdate)<30 then c.redpacketprice else null end) as dayget_30dayuse_hongbao

from

(

    select

    new_date,

    sceneid,

    name,

    unionid 

    from ads_traffic.ab_test_xianzhi_sys 

    group by

    new_date,

    sceneid,

    name,

    unionid 

) a 

left join

(

    select

    pdate

    ,unionid

    from dws.guiji 

    where pdate>='{today-30,yyyy-MM-dd}' and pdate<='{today-1,yyyy-MM-dd}'

    and pageid in (54)

    group by

    pdate

    ,unionid

)b on a.unionid=b.unionid and a.new_date=b.pdate

left join

(

    select pdate

    ,unionid --创单UV

    ,serialid

    ,legcount

    ,rev_before 

    ,rev_after

    ,redpacketprice 

        from dw.order

        where TO_DATE(createdate)>='{today-30,yyyy-MM-dd}' and TO_DATE(createdate)<='{today-1,yyyy-MM-dd}'

        and toeseatupflag = 0

        and changeticketid = 0

        group by pdate,

    unionid,

    serialid,

    passengernum,

    totalrev_yw,

    revenueafterpromotion,

    redpackge_useamt,

    toedeductibleamount

)c on b.unionid=c.unionid

group by a.new_date,

a.sceneid,

a.name

 

union all

select * from ads_traffic.ab_test_xianzhi_sys_result_train where (new_date<'{today-30,yyyy-MM-dd}' or new_date>'{today-1,yyyy-MM-dd}')