[关闭]
@JeemyJohn 2020-01-01T13:23:28.000000Z 字数 4868 阅读 938

手稿



  1. SUBSTR(from_unixtime(unix_timestamp()),12,2)
  1. 项目:易购APP UV流量预测
  2. 本周工作:
  3. 1. 天级UV流量预测模型T+2模型离线效果评估(MAPE0.0472)、上线、生成事件并与业务方联调;
  4. 2. 业务方核验过程中天级T+2模型线上效果(平均MAPE:,有一天超过20%)与误差分析,原因定位;
  5. 3. 业务方小时级模型从预测每小时UV改成预测每小时净增UV、到当前小时累计UV以及动态天级UV,小时级模型方案重新设计;
  6. 4. 新的小时模型特征设计、样本与特征跑数据落表、数据验证;
  7. 下周计划:
  8. 1. 新的小时级模型上线、生成事件并与业务方联调;
  9. 2. 对已上线模型天级模型进行效果观察与优化;
  1. USE YYADMIN;
  2. drop table if exists temp_TEST_NYM_D;
  3. create table temp_TEST_NYM_D STORED AS RCFILE AS
  4. SELECT
  5. if(utm_src is null or utm_src = '', 'direct', utm_src) utm_src,
  6. if(utm_src is null or utm_src = '', 'direct', utm_medium) utm_medium,
  7. visitor_id
  8. FROM
  9. ( SELECT
  10. concat(TRMNL_TP_ID, CLNT_ID) AS visitor_id,
  11. case when t.utm_src is not null and trim(t.utm_src)<>'' then t.utm_src
  12. when t5.wap_source is not null then t5.wap_source
  13. else t.utm_src
  14. end as utm_src,
  15. case when t.utm_medium is not null and trim(t.utm_medium)<>'' then t.utm_medium
  16. when t5.wap_medium is not null then t5.wap_medium
  17. else t.utm_medium
  18. end as utm_medium
  19. FROM bi_sor.tsor_br_trmnl_base_pageview_d t
  20. left join (select * from mobdss.tsor_br_trmnl_event_appreport_d a
  21. where a.statis_date = '20191204') t5
  22. on t.clnt_id = t5.client_uid
  23. and t.visit_id = t5.session_id
  24. WHERE t.statis_date = '20191204'
  25. AND t.intfc_tp = 1
  26. )T ;
  27. drop table if exists temp_TEST_NYM_D_1;
  28. create table temp_TEST_NYM_D_1 STORED AS RCFILE AS
  29. select utm_src_cate_id_1,
  30. utm_src_cate_nm_1,
  31. utm_src_cate_id,
  32. utm_src_cate_nm,
  33. utm_src_id,
  34. utm_src_nm
  35. from bi_td.tdm_utm_src_td;
  36. drop table if exists temp_TEST_NYM_D_2;
  37. create table temp_TEST_NYM_D_2 STORED AS RCFILE AS
  38. select CASE WHEN (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) = '60'
  39. THEN '直接流量'
  40. WHEN (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) <> '60' and
  41. (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) <> '99' and
  42. (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) IS NOT NULL
  43. THEN '站外流量'
  44. ELSE '其他' end is_direct,
  45. count(distinct visitor_id) uv
  46. from temp_TEST_NYM_D t
  47. left join temp_TEST_NYM_D_1 t9
  48. on if(t.utm_src = '' or t.utm_src is null or utm_src = 'direct', -- add by 20190103
  49. concat(t.utm_src, rand()), concat(utm_src , '/', utm_medium )) = t9.utm_src_nm
  50. group by CASE WHEN (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) = '60'
  51. THEN '直接流量'
  52. WHEN (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) <> '60' and
  53. (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) <> '99' and
  54. (case when t.utm_src = 'direct' then '60' else nvl(t9.utm_src_cate_id_1, 99) end ) IS NOT NULL
  55. THEN '站外流量'
  56. ELSE '其他' end;

0、线下海信预测使用的表

sfe.tsfe_Hisense_warehouse_sku_sales
sfe.tsfe_Hisense_warehouse_order_base_feature
sfe.tsfe_Hisense_warehouse_order_detail_feature
sfe.tsfe_Hisense_warehouse_sale_detail_feature

  1. SELECT
  2. statis_date
  3. , SUM(sale_cnt) AS sum_cnt
  4. , SUM(IF(sale_cnt >= 1, 1, 0)) AS pos -- 非零销sku
  5. , SUM(IF(sale_cnt < 1, 1, 0)) AS neg -- 零销sku
  6. FROM
  7. (
  8. SELECT
  9. statis_date
  10. , gds_cd
  11. , SUM(sale_cnt) AS sale_cnt
  12. FROM
  13. sfe.tsfe_Hisense_warehouse_sku_sales
  14. WHERE
  15. statis_date < '20190930'
  16. GROUP BY
  17. statis_date
  18. , gds_cd
  19. ) t
  20. GROUP BY statis_date
  21. ORDER BY statis_date
  22. ;

1、重要信息表

  1. 所有店的属性信息表

    brock_dim.t_shp_str_inf_sw_ed
    sfe.tsfe_ar_v0_store_sales_base_d

  2. 订单明细表

    BROCK_DWD.t_ord_retail_grp_ord_dtl_d
    scpdm.TDM_ORDER_DETAIL_D
    sfe.tsfe_mid_total_es_sales_base_info_d

  3. 商品属性信息表

    brock_dim.t_prd_gds_inf_ed

  4. 全渠道库存表

    bi_dpa.tdpa_inv_dtl_d

  5. 海信线上sku查询逻辑

  1. select
  2. *
  3. from
  4. sfe.tsfe_ar_order_detail_org_Hisense_d
  5. where
  6. bill_type='1'
  7. and chnl_cd='50'
  8. and order_source in ('B2C', 'TMALL')
  9. and vendor_tp='1'
  10. and purchase_f in ('0','1')
  11. and depot_id in ('0001', '0002', '0003')
  12. and status = '30'
  13. and statis_date = '${hivevar:statis_date}'
  14. and gds_cd in (select gds_cd from sfe.tsfe_salespredict_v3_gds_info_td_hisense)
  1. he
  1. select
  2. *
  3. from
  4. sfe.tsfe_ods_auto_rep_ie_salespredict_order_detail_offline_d
  5. where
  6. statis_date = 20190920
  7. and instr(brand_nm,'Hisense') != 0
  8. and plant_id != fh_dd

2、指标分析模板

  1. SELECT
  2. sale_cnt
  3. , SUM(IF(val < 0.1, 1, 0)) AS p1
  4. , SUM(IF(val >= 0.1 AND val < 0.2, 1, 0)) AS p2
  5. , SUM(IF(val >= 0.2 AND val < 0.3, 1, 0)) AS p3
  6. , SUM(IF(val >= 0.3 AND val < 0.4, 1, 0)) AS p4
  7. , SUM(IF(val >= 0.4 AND val < 0.5, 1, 0)) AS p5
  8. , SUM(IF(val >= 0.5 AND val < 0.6, 1, 0)) AS p6
  9. , SUM(IF(val >= 0.6 AND val < 0.7, 1, 0)) AS p7
  10. , SUM(IF(val >= 0.7 AND val < 0.8, 1, 0)) AS p8
  11. , SUM(IF(val >= 0.8 AND val < 0.9, 1, 0)) AS p9
  12. , SUM(IF(val >= 0.9 AND val < 1.0, 1, 0)) AS p10
  13. , SUM(IF(val > 1.0, 1, 0)) AS p11
  14. FROM
  15. (
  16. SELECT
  17. if(label <= 10, label, 11) AS sale_cnt
  18. , abs(label - prediction) AS val
  19. FROM
  20. sfe.tsfe_hisense_eshop_sale_predict_results
  21. WHERE
  22. label > 0
  23. ) t
  24. GROUP BY
  25. sale_cnt
  26. ORDER BY
  27. sale_cnt
  28. ;
  29. SELECT
  30. sale_cnt
  31. , SUM(IF(pred_cnt <=1, 1, 0)) AS cnt1
  32. , SUM(IF(pred_cnt = 2, 1, 0)) AS cnt2
  33. , SUM(IF(pred_cnt = 4, 1, 0)) AS cnt3
  34. , SUM(IF(pred_cnt = 5, 1, 0)) AS cnt4
  35. , SUM(IF(pred_cnt = 6, 1, 0)) AS cnt5
  36. , SUM(IF(pred_cnt = 7, 1, 0)) AS cnt7
  37. , SUM(IF(pred_cnt = 8, 1, 0)) AS cnt8
  38. , SUM(IF(pred_cnt = 9, 1, 0)) AS cnt9
  39. , SUM(IF(pred_cnt =10, 1, 0)) AS cnt10
  40. , SUM(IF(pred_cnt =11, 1, 0)) AS cnt11
  41. FROM
  42. (
  43. SELECT
  44. IF(label <= 10, label, 11) AS sale_cnt
  45. , IF(round(prediction) <= 10, round(prediction), 11) AS pred_cnt
  46. FROM
  47. sfe.tsfe_hisense_eshop_sale_predict_results
  48. WHERE
  49. label > 0
  50. ) t
  51. GROUP BY
  52. sale_cnt
  53. ORDER BY
  54. sale_cnt
  55. ;

3、海信

  1. select
  2. statis_date
  3. , count(1) AS cnt
  4. , sum(if(sale_cnt > 0, 1, 0)) AS pos
  5. , sum(if(sale_cnt > 0, 1, 0))/count(1) AS p1
  6. FROM
  7. sfe.tsfe_Hisense_warehouse_sku_sales
  8. --sfe.tsfe_Hisense_warehouse_order_base_feature
  9. WHERE
  10. statis_date >= '20190901'
  11. group by statis_date
  12. order by statis_date
添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注