记一道sql题

有一张运单表：dwd_biz_waybill_td，该表的主键是way_bill_id，并且有如下字段：

way_bill_id（运单表主键），shiping_date（下单日期，时间格式为yyyy-MM-dd），payment_customer_id（付款客户），damaged_degree_type（是否严重破损，为枚举值。1为是，0为否），is_throw_away（是否丢失，为枚举值。1为是，0为否），is_pickup_overtime（是否取超时，为枚举值。1为是，0为否）。需求为：

求连续俩周 严重破损的客户，丢失的客户、取超时的客户。

思路：

步骤一：

求每天严重破损、取超时的客户

1.  
   with v_customer_votes_base_data as (
2.  
   select
3.  
   waybill_td.payment_customer_id,
4.  
   waybill_td.shiping_date,
5.  
   CASE
6.  
   when shiping_date>= date_format(date_trunc('week',current_date),'%Y-%m-%d') then 'cur_week'
7.  
   when cast(date_add('day',-7,cast(date_format(date_trunc('week',current_date),'%Y-%m-%d') as date )) as varchar)<=shiping_date
8.  
   and shiping_date<cast(date_format(date_trunc('week',current_date),'%Y-%m-%d') as varchar) then 'last_week'
9.  
   else null
10.  
    end as shiping_date_week,
11.  
    sum(if(damaged_degree_type = 1,1,0)) as payment_serious_damage_votes , /**严重破损**/
12.  
    sum(if(is_throw_away = 1,1,0)) as payment_throw_away_votes , /**丢失票(排除退栏单)**/
13.  
    sum(if(is_pickup_overtime = 1,1,0)) as payment_order_pickup_overdue_votes ,/*订单取超时数*/
14.  
    from dwd_biz_waybill_td as waybill_td
15.  
    where waybill_td.shiping_date >= cast(date_add('day',-7,cast(date_format(date_trunc('week',current_date),'%Y-%m-%d') as date )) as varchar)
16.  
    and waybill_td.shiping_date<= date_format(current_date,'%Y-%m-%d')
17.  
    group by waybill_td.payment_customer_id,waybill_td.shiping_date
18.  
    ),

第二步，求本周与上周的相关指标

1.  
   /*客户连续俩周的相关指标*/
2.  
   v_customer_votes_result as (
3.  
   select payment_customer_id,
4.  
   shiping_date_week, /*本周还是上周*/
5.  
   sum(payment_serious_damage_votes) as payment_serious_damage_votes , /*严重破损*/
6.  
   sum(payment_throw_away_votes) as payment_throw_away_votes, /*丢失票(排除退栏单)*/
7.  
   sum(payment_order_pickup_overdue_votes) as payment_order_pickup_overdue_votes, /*订单取超时数*/
8.  
   sum(payment_inner_overdue_votes) as payment_inner_overdue_votes /*内因超时票（内因）*/
9.  
   from v_customer_votes_base_data
10.  
    where shiping_date_week is not null
11.  
    group by payment_customer_id,shiping_date_week
12.  
    )

第三部，将本周与上周的指标打平成一行

1.  
   /*客户连续俩周的相关指标*/
2.  
   v_customer_votes_result_two_week as (
3.  
   select payment_customer_id,
4.  
   sum(if(shiping_date_week='cur_week' and payment_serious_damage_votes>0,
5.  
   1,
6.  
   0)
7.  
   ) as payment_serious_damage_votes,
8.  
   sum(if(shiping_date_week='last_week' and payment_serious_damage_votes>0,
9.  
   1,
10.  
    0)
11.  
    ) as payment_serious_damage_votes_last_week,
12.  
    sum(if(shiping_date_week='cur_week' and payment_throw_away_votes>0,
13.  
    1,
14.  
    0)
15.  
    ) as payment_throw_away_votes,
16.  
    sum(if(shiping_date_week='last_week' and payment_throw_away_votes>0,
17.  
    1,
18.  
    0)
19.  
    ) as payment_throw_away_votes_last_week,
20.  
    sum(if(shiping_date_week='cur_week' and payment_order_pickup_overdue_votes>0,
21.  
    1,
22.  
    0)
23.  
    ) as payment_order_pickup_overdue_votes,
24.  
    sum(if(shiping_date_week='last_week' and payment_order_pickup_overdue_votes>0,
25.  
    1,
26.  
    0)
27.  
    ) as payment_order_pickup_overdue_votes_last_week
28.  
    from v_customer_votes_result
29.  
    group by payment_customer_id
30.  
    )

第四步：计算结果：

1.  
   select
2.  
   if(payment_serious_damage_votes>0 and payment_serious_damage_votes_last_week>0,1,0) as is_serious_damage_tw, /*是否【连续2周】严重破损*/
3.  
   if(payment_throw_away_votes>0 and payment_throw_away_votes_last_week>0,1,0) as is_serious_damage_tw /*是否【连续2周】丢失*/
4.  
   if(payment_order_pickup_overdue_votes>0 and payment_order_pickup_overdue_votes_last_week>0,1,0) as is_serious_damage_tw, /*是否【连续2周】取超时*/
5.  
   from v_customer_votes_result_two_week

这篇好文章是转载于：学新通技术网