优惠券核销SQL怎么写

---------------------优惠券领取核销信息

create table if not exists ads_traffic.ab_test_getquan_use

(

activity_no string comment '活动号',

goods_rule_no string comment '商品规则',

goods_serial_no string comment '商品规则编号',

unionid string comment '用户unionid',

memberid string comment '用户memberid',

goods_no string comment 'goods_no',

goods_name string comment 'goods_name',

dispatch_date date comment '领取时间',

start_time TIMESTAMP comment '生效时间',

end_time TIMESTAMP comment '失效时间',

couponamount DECIMAL(14, 2) comment '满金额',

smallvalidamount DECIMAL(14, 2) comment '减金额',

serialid string comment '订单号',

use_date date comment '核销时间'

)

;
 

insert overwrite table ads_traffic.ab_test_getquan_use

select

a.activity_no,

a.goods_rule_no,

a.goods_serial_no,

a.unionid,

a.memberid,

a.goods_no,

a.goods_name,

a.dispatch_date,

a.start_time,

a.end_time,

a.couponamount,

a.smallvalidamount,

b.serialid,

b.use_date

from ads_traffic.ab_test_lingqu_mingxi a

left join

ads_traffic.ab_test_use_mingxi b on a.create_date=b.dispatch_date and a.goods_rule_no=b.goods_rule_no

and a.goods_serial_no=b.goods_serial_no and a.unionid=b.unionid and a.activity_no=b.activity_no

----------------------------------------优惠券领取核销最终表

create table if not exists ads_traffic.ab_test_getquan_use_summary

(

create_date date comment '日期',

    activity_no string comment '活动号',

    goods_rule_no string comment '商品规则',

    unionid_num bigint comment '发放优惠券UV',

    quan_num bigint comment '发放优惠券数量',

    anyget_dayuse_user bigint comment '当日使用会员数（不限制领券时间）',

    anyget_dayuse_quan bigint comment '当日使用券数量（不限制领券时间）',

    

    dayget_dayuse_uid bigint comment '当天用户数（核销）',

    dayget_dayuse_revbefore decimal(14,2) comment '当天促前营收（核销）',

    dayget_dayuse_revafter decimal(14,2) comment '当天促后营收（核销）',

    dayget_dayuse_legcount bigint comment '当天票量数（核销）',

    dayget_dayuse_ordernum bigint comment '当天订单数（核销）',

    dayget_dayuse_hongbao decimal(14,2) comment '当天红包补贴（核销）',

 

    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 bigint comment '7天红包补贴（核销）'

)

;

 

insert overwrite table ads_traffic.ab_test_getquan_use_summary

select a.create_date,

a.activity_no,

a.goods_rule_no,

a.unionid_num,--发放优惠券UV

a.quan_num,--发放优惠券数量

d.unionid_num as anyget_dayuse_user,--当日使用会员数（不限制领券时间）

d.quan_num as anyget_dayuse_quan,--当日使用券数量（不限制领券时间）

 

c.dayget_dayuse_uid,--当日领取券已使用数

c.dayget_dayuse_revbefore,

c.dayget_dayuse_revafter,

c.dayget_dayuse_legcount,

c.dayget_dayuse_ordernum,

c.dayget_dayuse_hongbao,

 

c.dayget_7dayuse_uid,

c.dayget_7dayuse_revbefore,

c.dayget_7dayuse_revafter,

c.dayget_7dayuse_legcount,

c.dayget_7dayuse_ordernum,

c.dayget_7dayuse_hongbao 

from

(

    select create_date,

    activity_no,

    goods_rule_no,

    count(distinct unionid) unionid_num,--发放优惠券UV

    count(distinct goods_serial_no) quan_num --发放优惠券数量

    from ads_traffic.ab_test_lingqu_mingxi

    group by create_date,

    activity_no,

    goods_rule_no

)a --当天领取&当天领取券数量

left join

(

    select

    a.dispatch_date,

    a.activity_no,

    a.goods_rule_no,

    count(distinct case when a.dispatch_date=a.use_date then b.unionid else null end) as dayget_dayuse_uid,

    sum(case when a.dispatch_date=a.use_date then b.rev_before else null end) as dayget_dayuse_revbefore,

    sum(case when a.dispatch_date=a.use_date then b.rev_after else null end) as dayget_dayuse_revafter,

    sum(case when a.dispatch_date=a.use_date then b.legcount else null end) as dayget_dayuse_legcount,

    count(distinct case when a.dispatch_date=a.use_date then b.serialid else null end) as dayget_dayuse_ordernum,

    sum(case when a.dispatch_date=a.use_date then b.redpacketprice else null end) as dayget_dayuse_hongbao,

    

 

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

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

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

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

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

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

    from ads_traffic.ab_test_getquan_use a

    left join

    (

        select distinct

        pdate

        ,unionid

        ,serialid

        ,legcount

        ,rev_before

        ,redpacketprice

        ,rev_after

        FROM dws_traffic.order

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

        AND isvalid = 1

        AND istest = 0

        and issuccess=1

    )b on a.serialid=b.serialid--领取后核销数据

    group by a.dispatch_date,

    a.activity_no,

    a.goods_rule_no

 

)c on a.create_date=c.dispatch_date and a.goods_rule_no=c.goods_rule_no and a.activity_no=c.activity_no--领取日期和券号

left join

(

    select use_date,

    activity_no,

    goods_rule_no,

    count(distinct unionid) unionid_num,--当日使用会员数（不限制领券时间）

    count(distinct goods_serial_no) quan_num----当日使用券数量（不限制领券时间）

    from ads_traffic.ab_test_use_mingxi

    group by use_date,

    activity_no,

    goods_rule_no

)d on a.create_date=d.use_date and a.goods_rule_no=d.goods_rule_no and a.activity_no=d.activity_no