09：服务域：安装主题分析实现

• 目标：掌握安装主题的需求分析及实现

• 路径

  • step1：需求
  • step2：分析
  • step3：实现

• 实施

  • 需求：统计不同维度下的安装主题指标的结果

    

  • 分析

    • 指标：安装数量、支付金额

    • 维度

      • 安装方式
      • 日期维度：天、周、月
      • 油站维度：类型、省份、城市、地区
      • 客户维度：类型、省份

    • 数据

      • 事实表

        • fact_srv_install：安装事务事实表

          selectinst_id, --安装单idinst_type_id, --安装方式id 1-设备安装，2-设备联调exp_device_money, --安装费用dt,--日期
          os_id --油站id
          from one_make_dwb.fact_srv_install;
          

    • 维度表

      • dim_oilstation：油站维度表

        selectid,--油站idcompany_name,--公司名称province_name,--省份名称city_name,--城市名称county_name,--区域名称customer_classify_name,--客户名称customer_province_name--客户省份
        from dim_oilstation;
        

        • dim_date：时间维度表

          selectdate_id,--天week_in_year_id,--周year_month_id --月
          from dim_date;
          

          

  • 实现

    • 建表

      create table if not exists one_make_st.subj_install(install_way string comment '安装方式',install_sum bigint comment '安装数量',sum_money int comment '支付费用',dws_day string comment '日期维度-按天',dws_week string comment '日期维度-按周',dws_month string comment '日期维度-按月',oil_type string comment '油站维度-油站类型',oil_province string comment '油站维度-油站所属省',oil_city string comment '油站维度-油站所属市',oil_county string comment '油站维度-油站所属区',customer_classify string comment '客户维度-客户类型',customer_province string comment '客户维度-客户所属省'
      ) comment '安装主题表'
      partitioned by (month string, week string, day string)
      stored as orc
      location '/data/dw/st/one_make/subj_install';
      

• 构建

  insert overwrite table one_make_st.subj_install partition(month = '202101', week='2021W1', day='20210101')select--安装方式max(case when install.inst_type_id = 1 then '设备安装' when install.inst_type_id = 2 then '设备联调' else '未知' end) install_way, count(install.inst_id) install_sum                                     --安装数量, sum(install.exp_device_money) sum_money            					 --支付金额, dd.date_id dws_day                                                     --日期天, dd.week_in_year_id dws_week                                            --日期周, dd.year_month_id dws_month                                             --日期月, dimoil.company_name oil_type                                           --油站类型, dimoil.province_name oil_province                                      --油站省份, dimoil.city_name oil_city                                              --油站城市, dimoil.county_name oil_county                                          --油站地区, dimoil.customer_classify_name customer_classify                        --客户类型, dimoil.customer_province_name customer_province                        --客户省份--安装事务事实表from one_make_dwb.fact_srv_install install--关联日期维度表left join one_make_dws.dim_date dd on install.dt = dd.date_id--关联油站维度表left join one_make_dws.dim_oilstation dimoil on install.os_id = dimoil.idwhere dd.year_month_id = '202101' and dd.week_in_year_id = '2021W1' and dd.date_id = '20210101'--按照维度分组group byinst_type_id,dd.date_id, dd.week_in_year_id, dd.year_month_id,dimoil.company_name, dimoil.province_name, dimoil.city_name, dimoil.county_name,dimoil.customer_classify_name, dimoil.customer_province_name;
  

• 小结

  • 掌握安装主题的需求分析及实现

10：服务域：维修主题分析实现

• 目标：掌握维修主题的需求分析及实现

• 路径

  • step1：需求
  • step2：分析
  • step3：实现

• 实施

  • 需求：统计不同维度下的维修主题指标的结果

    

  • 分析

    • 指标

      • 支付费用、工时费用、零部件费用、交通费用
      • 故障总数、最大数量、平均数量

    • 维度

      • 日期维度：天、周、月
      • 油站维度：类型、省份、城市、地区
      • 客户维度：类型、省份
      • 物流公司

    • 数据

      • 事实表

        • fact_srv_repair：维修事务事实表

          selecthour_money,--工时费用parts_money,--配件物料费用fars_money,--交通费用fault_type_ids, --故障id集合dt,--日期os_id,--油站idss_id --服务网点id
          from fact_srv_repair;
          

      • fact_srv_stn_ma：网点物料事务事实表

        selectss_id,--服务网点idlogi_cmp_id --物流公司id
        from fact_srv_stn_ma;
        

    • 维度表

      • dim_oilstation：油站维度表

        selectid,--油站idcompany_name,--公司名称province_name,--省份名称city_name,--城市名称county_name,--区域名称customer_classify_name,--客户名称customer_province_name--客户省份
        from dim_oilstation;
        

      • dim_date：时间维度表

        selectdate_id,--天week_in_year_id,--周year_month_id --月
        from dim_date;
        

      • dim_logistics：物流维度表

        select type_id,  --物流公司id
        type_name --物流公司名称
        from one_make_dws.dim_logistics where prop_name = '物流公司';
        

  • 实现

    • 建表

      drop table if exists one_make_st.subj_repair;
      create table if not exists one_make_st.subj_repair(sum_pay_money decimal(20,1) comment '支付费用',sum_hour_money decimal(20,1) comment '小时费用',sum_parts_money decimal(20,1) comment '零部件费用',sum_fars_money decimal(20,1) comment '交通费用',sum_faulttype_num bigint comment '故障类型总数',max_faulttype_num int comment '故障类型最大数量',avg_faulttype_num int comment '故障类型平均数量',dws_day string comment '日期维度-按天',dws_week string comment '日期维度-按周',dws_month string comment '日期维度-按月',oil_type string comment '油站维度-油站类型',oil_province string comment '油站维度-油站所属省',oil_city string comment '油站维度-油站所属市',oil_county string comment '油站维度-油站所属区',customer_classify string comment '客户维度-客户类型',customer_province string comment '客户维度-客户所属省',logi_company string comment '物流公司维度-物流公司名称'
      ) comment '维修主题表'
      partitioned by (month String, week String, day String)
      stored as orc
      location '/data/dw/st/one_make/subj_repair';
      

• 构建

  insert overwrite table one_make_st.subj_repair partition(month = '202101', week='2021W1', day='20210101')selectsum(pay_money) sum_pay_money, 				--支付费用sum(hour_money) sum_hour_money,             --工时费用sum(parts_money) sum_parts_money,           --物料费用sum(fars_money) sum_fars_money,             --交通费用sum(fault_type_num) sum_faulttype_num,      --故障类型总数max(fault_type_num) max_faulttype_num,      --最大故障数量avg(fault_type_num) avg_faulttype_num,      --平均故障数量dws_day,                                    --日期天dws_week,                                   --日期周dws_month,                                  --日期月oil_type,                                   --油站类型oil_province,                               --油站省份oil_city,                                   --油站城市oil_county,                                 --油站区域customer_classify,                          --客户类型customer_province,                          --客户省份logi_company                                --物流公司from (select(hour_money + parts_money+fars_money) pay_money,hour_money,parts_money,fars_money,case when (size(split(fault_type_ids, ','))) <= 0 then 0 else (size(split(fault_type_ids, ','))) end fault_type_num,dd.date_id dws_day,dd.week_in_year_id dws_week,dd.year_month_id dws_month,dimoil.company_name oil_type,dimoil.province_name oil_province,dimoil.city_name oil_city,dimoil.county_name oil_county,dimoil.customer_classify_name customer_classify,dimoil.customer_province_name customer_province,type_name logi_company--维修事务事实表from one_make_dwb.fact_srv_repair repair--关联日期维度表left join one_make_dws.dim_date dd on repair.dt = dd.date_id--关联油站维度表left join one_make_dws.dim_oilstation dimoil on repair.os_id = dimoil.id--关联网点物料事实表：获取物流公司idleft join one_make_dwb.fact_srv_stn_ma fssm on repair.ss_id = fssm.ss_id--关联物流维度表：获取物流公司名称left join (select type_id, type_name from one_make_dws.dim_logistics where prop_name = '物流公司') dl on fssm.logi_cmp_id = dl.type_idwhere dd.year_month_id = '202101'and dd.week_in_year_id = '2021W1' and  dd.date_id = '20210101' and exp_rpr_num = 1) repair_tmpgroup by dws_day, dws_week, dws_month, oil_type, oil_province, oil_city, oil_county,customer_classify, customer_province,logi_company;
  

• 小结

  • 掌握维修主题的需求分析与实现