深入前端研发效能治理：数据化运营思路及其实践

/** 效能工具内核类声明（与埋点上报监控平台相关的部分） */class HornetCore {  /** 监控单实例声明 */  private static traceInstance: TraceSdkType = null;
  /** HornetCore 监控初始化 */  public static initTrace = (): void => {    HornetCore.traceInstance = new TraceLiteSdk({ pid: '<研发效能工具PID>' });  };
  /** 上报参数给监控平台 */  public static sendTraceLog = async (    /**     * 上报类型     */    type: string,    /**     * 上报的参数     */    params?: TraceLogParamType,  ): Promise<void> => {    try {      // 操作人工号      const uid = DYNAMIC_NAMES.ADMIN_WORK_ID;
      // 操作人      const c1 = DYNAMIC_NAMES.ADMIN_NAME;
      // 应用名      const c2 = DYNAMIC_NAMES.PROJECT_NAME;
      // 分支名      const c3 = DYNAMIC_NAMES.BRANCH_NAME;
      let c4: any;      let c5: any;      let c6: any;      let c7: any;      let c8: any;
      // 工具是否最新版      const c9 = HornetCore.isLatestVersion.toString();
      // 工具内核版本号      const c10 = HornetCore.version;
      switch (type) {        case TRACE_LOG_TYPE.CR: {          const { targetBranch, crAdmins, crAdminCnt, currentReqName, crSequence } = params || {};          [c4, c5, c6, c7, c8] = [targetBranch, crAdmins, crAdminCnt, currentReqName, crSequence];          break;        }        case TRACE_LOG_TYPE.CR_HANDLING: {          const { targetBranch, currentReqName, crCount, crStartTime, crEndTime } = params || {};          [c4, c5, c6, c7, c8] = [targetBranch, currentReqName, crCount, crStartTime, crEndTime];          break;        }        default:          [c4, c5, c6, c7, c8] = [null, null, null, null, null];      }
      const logParams = { type, uid, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 };
      return HornetCore.traceInstance.log(logParams);    } catch (e) {      BasicMessage.error(`上报数据错误: ${SPLIT_VALUE.BREAK}${e}`);      return Promise.reject();    }  };}

/*  代码评审数据明细分析表*/SELECT  a.stat_date,  a.ds,  a.log_day,  substr(a.log_hour, 9) as log_hour,  substr(a.log_time, 9) as log_time,  substr(a.log_second, 9) as log_second,  a.c1 as admin_name,  b.c2 as project_name, -- 项目中文名信息由维表提供  a.c2 as project_en_name,  a.c3 as branch_name,  a.c4 as target_branch,  a.c5 as cr_admins,  substring_index(c5, ',', 1) as cr_first_admin, -- 取得第一个评审人为主要评审人信息  a.c6 as cr_admin_cnt,  a.c7 as cur_req_name,  concat(c4, ' ', c7) as cur_branch_and_req_name,  a.c8 as cr_sequence,  a.c9 as latest_version_flag,  a.c10 as cur_versionFROM  source_table a  LEFT OUTER JOIN (    SELECT      code,      c1,      c2    FROM      source_table_dim -- 维表存储不经常更改的维度信息  ) b ON a.c2 = b.c1WHERE  a.code = 'code_review'  AND b.code = 'project_name'  AND a.c4 != 'develop' -- 过滤掉提交到主发布分支的 CR，因为其不是关键信息  AND a.ds > 20210630; -- 限定有数据的 ds 范围，增加查询性能
/*  代码评审处理数据明细分析表*/SELECT  a.stat_date,  a.ds,  a.log_day,  substr(a.log_hour, 9) as log_hour,  substr(a.log_time, 9) as log_time,  substr(a.log_second, 9) as log_second,  a.c1 as admin_name,  b.c2 as project_name,  a.c2 as project_en_name,  a.c3 as branch_name,  a.c4 as target_branch,  a.c5 as cur_req_name,  concat(a.c4, ' ', a.c5) as cur_branch_and_req_name,  concat(b.c2, ' ', a.c5) as cur_project_and_req_name,  c.c1 as cur_req_admin_name,  a.c6 as cr_count,  a.c7 as cr_start_time,  a.c8 as cr_end_time,  datediff(    to_date(a.c8, 'yyyy-mm-dd hh:mi:ss'),    to_date(a.c7, 'yyyy-mm-dd hh:mi:ss'),    'mi'  ) as cr_duration, -- 使用 datediff 函数来计算CR处理时长  a.c9 as latest_version_flag,  a.c10 as cur_versionfrom  source_table a  LEFT OUTER JOIN (    SELECT      code,      c1,      c2    FROM      source_table_dim    WHERE      code = 'project_name' -- 关联项目名称信息  ) b ON a.c2 = b.c1  LEFT OUTER JOIN (    SELECT      distinct code,      c1,      c2,      c3,      c4    FROM      source_table    WHERE      code = 'requirements' -- 关联需求信息  ) c ON a.c5 = c.c4WHERE  a.code = 'code_review_handling'  AND a.c4 != 'develop'  AND a.c8 IS NOT NULL -- 没有CR结束时间的定义为废弃CR，此处过滤掉  AND a.c8 != 'null'  AND a.ds > 20210720;
/*  代码评审汇总分析表*/SELECT  stat_date,  project_name,  project_name_and_req_name,  cur_req_name,  admin_name,  COUNT(admin_name) as cr_cnt -- 统计CR提交次数FROM  (    SELECT      a.stat_date,      a.ds,      a.log_day,      substr(a.log_hour, 9) as log_hour,      substr(a.log_time, 9) as log_time,      substr(a.log_second, 9) as log_second,      a.c1 as admin_name,      b.c2 as project_name,      a.c2 as project_en_name,      a.c3 as branch_name,      a.c4 as target_branch,      a.c5 as cr_admins,      substring_index(a.c5, ',', 1) as cr_first_admin,      a.c6 as cr_admin_cnt,      a.c7 as cur_req_name,      concat(b.c2, ' ', a.c7) as project_name_and_req_name,      a.c8 as cr_sequence,      a.c9 as latest_version_flag,      a.c10 as cur_version    FROM      source_table a      LEFT OUTER JOIN (        SELECT          code,          c1,          c2        FROM          source_table_dim      ) b ON a.c2 = b.c1    WHERE      a.code = 'code_review'      AND a.c4 != 'develop'      AND a.ds > 20210715      AND b.code = 'project_name'  )GROUP BY -- 选择日期、项目名称、需求名称、开发者等维度  stat_date,  project_name,  cur_req_name,  admin_name,  project_name_and_req_name;