需求 #23291
晨会数据看板-工单类型调整
PRD时间:
移交时间:
提测时间:
承诺上线时间:
2021-03-16
实际上线时间:
2021-03-16
截稿时间:
开发时间:
2021-03-16
联调时间:
需求方:
供应链中心
风险评估:
未知
优先级:
10
开发优先级:
10
前端开发:
需求方改动次数:
0
数据组完成时间:
描述
| select |
| to_char(coalesce(so.ext_agree_time,so.add_time),'yyyymmdd') 工单时间 |
| ,case when service_order_type = 3 ----无忧退（持有额度的退货，购卡、赠送） |
| then '无忧退换货工单' |
| when w.rec_id is not null then '平台化无理由退换货工单' |
| else '普通工单' end as service_type |
| ,count(distinct so.os_sn)工单数 |
| from yishou_data.dwd_as_service_order_detail so |
| left outer join |
| ( |
| SELECT DISTINCT c.user_id |
| ,d.rec_id |
| FROM ( |
| SELECT DISTINCT c.user_id |
| ,c.rec_id |
| ,c.supply_id |
| ,c.add_time |
| FROM yishou_data.dwd_sale_order_info_dt c |
| WHERE c.pay_status = 1 |
| AND c.dt >= to_char(dateadd(to_date(${bdp.system.bizdate},'yyyymmdd'), - 90,'dd'),'yyyymmdd') |
| AND to_char(dateadd(c.add_time, - 7,'hh'),'yyyymmdd') BETWEEN to_char(dateadd(to_date(${bdp.system.bizdate},'yyyymmdd'), - 90,'dd'),'yyyymmdd') |
| AND ${bdp.system.bizdate} |
| ) c |
| JOIN ( |
| SELECT DISTINCT rec_id |
| FROM yishou_data.all_fmys_no_reason_order |
| WHERE TYPE = 1 |
| ) d |
| ON c.rec_id = d.rec_id |
| JOIN yishou_data.dim_supply_info ee |
| ON c.supply_id = ee.supply_id |
| JOIN ( |
| SELECT a.supply_id |
| ,CASE WHEN from_unixtime(max(b.audit_time-25200)) <= to_date('20210219', 'yyyymmdd') THEN to_date('20210219', 'yyyymmdd') |
| ELSE datetrunc(from_unixtime(max(b.audit_time-2500)),'dd') |
| END AS join_time --加盟时间，取max(20210219,time) |
| FROM yishou_data.dim_supply_info a |
| JOIN yishou_data.all_fmys_supply_cooperation b |
| ON a.supply_id = b.supply_id |
| AND to_char(from_unixtime(b.audit_time - 25200),'yyyymmdd') <= ${bdp.system.bizdate} |
| WHERE a.is_platform = 1 |
| GROUP BY a.supply_id |
| ) f |
| ON c.supply_id = f.supply_id |
| WHERE d.rec_id IS NOT NULL |
| AND ee.is_platform = 1 ---平台化脚本 |
| AND to_char(dateadd(c.add_time, - 7,'hh'),'yyyymmdd') >= to_char(f.join_time,'yyyymmdd') ---下单时间大于加盟时间 |
| )w on so.rec_id=w.rec_id and so.user_id=w.user_id |
| left join yishou_data.all_fmys_refund_card_log a |
| on so.os_sn = a.os_sn |
| where to_char(coalesce(so.ext_agree_time,so.add_time),'yyyymmdd') = ${bdp.system.bizdate} |
| group by to_char(coalesce(so.ext_agree_time,so.add_time),'yyyymmdd') |
| ,case when service_order_type = 3 ----无忧退（持有额度的退货，购卡、赠送） |
| then '无忧退换货工单' |
| when w.rec_id is not null then '平台化无理由退换货工单' |
| else '普通工单' end |
| ; |
由 
