dws_coupon_info_daycout

首日拆解

--特定日期中某优惠券的使用次数
select coupon_id,
       date_format(used_time, 'yyyy-MM-dd') dt,
       count(*)                             coupon_used_count
from dwd_coupon_use
where dt = '2020-06-14'
  and used_time is not null
group by coupon_id, date_format(used_time, 'yyyy-MM-dd');

--某种优惠券在某天被下单使用了多少次
select
       coupon_id,
       date_format(using_time,'yyyy-MM-dd') dt,
       count(*)                             order_count
from dwd_coupon_use
where (dt<='2020-06-14' or dt='9999-99-99') and using_timeis not null
group by coupon_id,date_format(using_time,'yyyy-MM-dd')


--某种优惠券在某天被领取了多少次
select
       coupon_id,
       date_format(get_time,'yyyy-MM-dd') dt,
       count(*)                             get_count
from dwd_coupon_use
where dt<='2020-06-14'
    or dt='9999-99-99'
group by coupon_id, date_format(get_time,'yyyy-MM-dd')

--截止到6-14，每天每种优惠券过期多少此
select
       coupon_id,
       dt,
       count(*) expire_count
from dwd_coupon_use
where dt<='2020-06-14'
    and expire_time is not null
group by coupon_id, dt

--一笔订单中，使用优惠券减免了价格的订单的部分信息
select
       order_id,
       sku_id,
       coupon_id,
       split_coupon_amount,
       split_final_amount,
       dt
from dwd_order_detail
where dt<='2020-06-14'
and coupon_id is not null

--所有在14号之前支付的订单
select
       order_id,
       dt
from dwd_payment_info
where dt<='2020-06-14'

dws_area_stats_daycount

首日导入拆解

select --每天对应每个省
       id province_id,
       date_id
from (
         select id
         from dim_base_province
     ) t2
         join (
    select date_id
    from dim_date_info
    where date_id between '2020-06-11' and '2020-06-14'
) t3

select --获取省份和特定时间的退款次数和金额
       province_id,
       dt,
       count(*)           refund_payment_count,
       sum(refund_amount) refund_payment_amout
from dwd_refund_payment
where dt <= '2020-06-14'
group by province_id, dt

select --获取省份和特定时间的退单次数和退单金额
       province_id,
       dt,
       count(*)           refund_order_count,
       sum(refund_amount) refund_order_amout
from dwd_order_refund_info
where dt <= '2020-06-14'
group by province_id, dt


select --按照省份id获取特定时间内支付次数和支付金额
       province_id,
       dt,
       count(*)            payment_count,
       sum(payment_amount) payment_amount
from dwd_payment_info
where dt <= '2020-06-14'
group by province_id, dt



select --根据省份查出某天下单金额（包括应付金额和最终金额）
       province_id,
       date_format(create_time, 'yyyy-MM-dd') dt,
       sum(original_amount)                   order_original_amout,
       sum(final_amount)                      order_final_amout
from dwd_order_info
where dt = '2020-06-14'
   or dt = '9999-99-99'
group by province_id, date_format(create_time, 'yyyy-MM-dd')



select --按照省份id查询出访客访问次数，用户访问次数，访客人数，用户人数
       id province_id,
       visit_count,
       log_count,
       visitor_count,
       user_count
from (
         select area_code,
                sum(`if`(last_page_id is not null, 1, 0))                         visit_count,
                sum(`if`(last_page_id is not null and user_id is not null, 1, 0)) log_count,
                count(distinct mid_id)                                            visitor_count,
                count(distinct user_id)                                           user_count
         from dwd_page_log
         where dt = '2020-06-14'
         group by area_code) t1
         left join dim_base_province
                   on t1.area_code = dim_base_province.area_code