Elegantes statistisches Auftragseinkommen (2)

In der heutigen Kolumne „MySQL-Tutorial“ wird vorgestellt, wie Sie Auftragseinnahmen elegant zählen und Sorgen reduzieren können.

Einführung

Im vorherigen Artikel wurde die Lösung für heterogene tägliche Einkommensberichte detailliert beschrieben. Als Nächstes lösen wir das Problem, wie Aggregations-SQL optimiert werden kann, wenn viele Aggregationsanforderungen bestehen.

Anforderungen

wurden im Detail unter Wie man Auftragserlöse elegant zählt (1) erläutert, wobei es sich wahrscheinlich um die Umsatzstatistik einiger Tage/Monate/Jahre handelt.

Denken

Ziel

• Minimieren Sie die Anzahl der Aggregations-SQL-Abfragen.
• Geben Sie die Front-End-API-Daten an, die einfach angezeigt werden können bedeutet, wenn die Daten auf Ein bestimmter Tag ist null. Wenn das Back-End die Daten mit einem Umsatz von 0 verarbeitet und an das Front-End sendet, sollten die Methoden und Funktionen so allgemein wie möglich sein, um die Codequalität zu verbessern Tägliche Statistiktabelle mit heterogenem Einkommen:
  1. Einzeltägige Statistiken (z. B. heute, gestern, genaues Datum) können Daten direkt nach Datum sperren und zurückgeben.
  2. Monatliche Statistiken können auch die Daten des aktuellen Monats nach Zeit herausfiltern Statistiken.
  3. Jahresstatistiken können auch die statistische Umsetzung des Jahres über das Datumsintervall abfragen.
  4. Verdienste können auch separat aggregiert und abgefragt werden. Es scheint, dass die Heterogenität der täglichen statistischen Tabelle besteht wertvoll, und zumindest kann es alle aktuellen Bedürfnisse lösen. Wenn Sie Einkommensstatistiken für heute/gestern/letzten Monat/diesen Monat benötigen und SQL verwenden, um die Abfrage direkt zu aggregieren, müssen Sie heute, gestern und den Datensatz, der sich über den gesamten Monat erstreckt, separat abfragen und dann SUM Aggregation-Implementierung.

     CREATE TABLE `t_user_income_daily` (
       `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
       `user_id` int(11) NOT NULL COMMENT '用户id',
       `day_time` date NOT NULL COMMENT '日期',
       `self_purchase_income` int(11) DEFAULT '0' COMMENT '自购收益',
       `member_income` int(11) DEFAULT '0' COMMENT '一级分销收益',
       `affiliate_member_income` int(11) DEFAULT '0' COMMENT '二级分销收益',
       `share_income` int(11) DEFAULT '0' COMMENT '分享收益',
       `effective_order_num` int(11) DEFAULT '0' COMMENT '有效订单数',
       `total_income` int(11) DEFAULT '0' COMMENT '总收益',
       `update_time` datetime DEFAULT NULL COMMENT '更新时间',
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='用户收益日统计'

  Wenn die Schnittstelle heute zurückkehren muss/ gestern/letzter Monat/ Bei der Berechnung der Umsatzstatistik dieses Monats müssen Sie 4-faches SQL kann erreicht werden. Es gibt kein Problem mit dem Schreiben, aber es ist nicht die optimale Lösung? Können Sie weniger SQL-Abfragen verwenden?

  Beobachten

  Durch Beobachtung und Analyse, Statistiken für heute/gestern/letzten Monat/diesen Monat existCommon interface, sie sind alle in der Wenn wir das gleiche Zeitintervall (Erster des letzten Monats – Monatsende dieses Monats) verwenden, können wir die Daten dieser beiden Monate direkt über SQL überprüfen und dann die Programmaggregation verwenden, um auf einfache Weise die gewünschten Daten zu erhalten.SUM聚合实现.

  select * from t_user_income_daily where day_time BETWEEN '上月一号' AND '本月月末' and user_id=xxx

  这种写法如果接口需要返回今日/昨日/上月/本月的收益统计时,就需要查询4次SQL才可以实现.写法没问题,但是不是最优解?可以用更少的SQL查询么?

  观察

  通过观察分析,今日/昨日/上月/本月统计存在共同的交集

  优化实现

  补充一下收益日统计表设计

  select * from t_user_income_daily where day_time BETWEEN '上月一号' AND '本月月末' and user_id=xxx

  查询出两个月的收益

  select * from t_user_income

  为了减少表的数据量,如果当日没有收益变动是不会创建当日的日统计数据的,所以这里只能查询出某时间区间用户有收益变动的收益统计数据.如果处理某一天数据为空的情况则还需要再程序中特殊处理.此处有小妙招,在数据库中生成一张时间辅助表.以天为单位,存放各种格式化后的时间数据,辅助查询详细操作可见这篇博文Mysql生成时间辅助表.有了这张表就可以进一步优化这条SQL.时间辅助表的格式如下,也可修改存储过程,加入自己个性化的时间格式.

   SELECT
          a.DAY_ID day_time,
          a.MONTH_ID month_time,
          a.DAY_SHORT_DESC day_time_str,
          CASE when b.user_id is null then #{userId} else b.user_id end user_id,
          CASE when b.self_purchase_income is null then 0 else b.self_purchase_income end self_purchase_income,
          CASE when b.member_income is null then 0 else b.member_income end member_income,
          CASE when b.affiliate_member_income is null then 0 else b.affiliate_member_income end affiliate_member_income,
          CASE when b.share_income is null then 0 else b.share_income end share_income,
          CASE when b.effective_order_num is null then 0 else b.effective_order_num end effective_order_num,
          CASE when b.total_income is null then 0 else b.total_income end total_income
          FROM
          t_day_assist a
          LEFT JOIN t_user_income_daily b ON b.user_id = #{userId}
          AND a.DAY_SHORT_DESC = b.day_time
          WHERE
          STR_TO_DATE( a.DAY_SHORT_DESC, '%Y-%m-%d' ) BETWEEN #{startTime} AND #{endTime}
          ORDER BY
          a.DAY_ID DESC

  思路很简单,用时间辅助表左关联需要查询的收益日统计表,关联字段就是day_time时间,如果没有当天的收益数据,SQL中也会有日期为那一天但是统计数据为空的数据,用casewhen判空赋值给0,最后通过时间倒序,便可以查询出一套完整时间区间统计.

  最终实现

  以SQL查询出的数据为基础.在程序中用stream进行聚合. 举例说明一些例子,先从简单的开始

  常用静态方法封装

  /**
       * @description: 本月的第一天
       * @author: chenyunxuan
       */
      public static LocalDate getThisMonthFirstDay() {
          return LocalDate.of(LocalDate.now().getYear(), LocalDate.now().getMonthValue(), 1);
      }
  
      /**
       * @description: 本月的最后一天
       * @author: chenyunxuan
       */
      public static LocalDate getThisMonthLastDay() {
          return LocalDate.now().with(TemporalAdjusters.lastDayOfMonth());
      }
  
      /**
       * @description: 上个月第一天
       * @author: chenyunxuan
       */
      public static LocalDate getLastMonthFirstDay() {
          return LocalDate.of(LocalDate.now().getYear(), LocalDate.now().getMonthValue() - 1, 1);
      }
  
      /**
       * @description: 上个月的最后一天
       * @author: chenyunxuan
       */
      public static LocalDate getLastMonthLastDay() {
          return getLastMonthFirstDay().with(TemporalAdjusters.lastDayOfMonth());
      }
      
      /**
       * @description: 今年的第一天
       * @author: chenyunxuan
       */
      public static LocalDate getThisYearFirstDay() {
          return LocalDate.of(LocalDate.now().getYear(), 1, 1);
      }
      
      /**
       * @description: 分转元，不支持负数
       * @author: chenyunxuan
       */
      public static String fenToYuan(Integer money) {
          if (money == null) {
              return "0.00";
          }
          String s = money.toString();
          int len = s.length();
          StringBuilder sb = new StringBuilder();
          if (s != null && s.trim().length() > 0) {
              if (len == 1) {
                  sb.append("0.0").append(s);
              } else if (len == 2) {
                  sb.append("0.").append(s);
              } else {
                  sb.append(s.substring(0, len - 2)).append(".").append(s.substring(len - 2));
              }
          } else {
              sb.append("0.00");
          }
          return sb.toString();
      }

  指定月份收益列表(按时间倒序)

  public ResponseResult selectIncomeDetailThisMonth(int userId, Integer year, Integer month) {
          ResponseResult responseResult = ResponseResult.newSingleData();
          String startTime;
          String endTime;
          //不是指定月份
          if (null == year && null == month) {
              //如果时间为当月则只显示今日到当月一号
              startTime = DateUtil.getThisMonthFirstDay().toString();
              endTime = LocalDate.now().toString();
          } else {
              //如果是指定年份月份,用LocalDate.of构建出需要查询的月份的一号日期和最后一天的日期
              LocalDate localDate = LocalDate.of(year, month, 1);
              startTime = localDate.toString();
              endTime = localDate.with(TemporalAdjusters.lastDayOfMonth()).toString();
          }
          //查询用通用的SQL传入用户id和开始结束时间
          List<UserIncomeDailyVO> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime);
          /给前端的数据需要把数据库存的分转为字符串,如果没有相关需求可跳过直接返回
          List<UserIncomeStatisticalVO> userIncomeStatisticalList = userIncomeDailyList.stream()
                  .map(item -> UserIncomeStatisticalVO.builder()
                          .affiliateMemberIncome(Tools.fenToYuan(item.getAffiliateMemberIncome()))
                          .memberIncome(Tools.fenToYuan(item.getMemberIncome()))
                          .effectiveOrderNum(item.getEffectiveOrderNum())
                          .shareIncome(Tools.fenToYuan(item.getShareIncome()))
                          .totalIncome(Tools.fenToYuan(item.getTotalIncome()))
                          .dayTimeStr(item.getDayTimeStr())
                          .selfPurchaseIncome(Tools.fenToYuan(item.getSelfPurchaseIncome())).build()).collect(Collectors.toList());
          responseResult.setData(userIncomeStatisticalList);
          return responseResult;
      }

  今日/昨日/上月/本月收益

      public Map<String, String> getPersonalIncomeMap(int userId) {
          Map<String, String> resultMap = new HashMap<>(4);
          LocalDate localDate = LocalDate.now();
          //取出上个月第一天和这个月最后一天
          String startTime = DateUtil.getLastMonthFirstDay().toString();
          String endTime = DateUtil.getThisMonthLastDay().toString();
          //这条查询就是上面优化过的SQL.传入开始和结束时间获得这个时间区间用户的收益日统计数据
          List<UserIncomeDailyVO> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime);
          //因为这里需要取的都是总收益,所以封装了returnTotalIncomeSum方法,用于传入条件返回总收益聚合
          //第二个参数就是筛选条件,只保留符合条件的部分.(此处都是用的LocalDate的API)
          int today = returnTotalIncomeSum(userIncomeDailyList, n -> localDate.toString().equals(n.getDayTimeStr()));
          int yesterday = returnTotalIncomeSum(userIncomeDailyList, n -> localDate.minusDays(1).toString().equals(n.getDayTimeStr()));
          int thisMonth = returnTotalIncomeSum(userIncomeDailyList, n ->
                  n.getDayTime() >= Integer.parseInt(DateUtil.getThisMonthFirstDay().toString().replace("-", ""))
                          && n.getDayTime() <= Integer.parseInt(DateUtil.getThisMonthLastDay().toString().replace("-", "")));
          int lastMonth = returnTotalIncomeSum(userIncomeDailyList, n ->
                  n.getDayTime() >= Integer.parseInt(DateUtil.getLastMonthFirstDay().toString().replace("-", ""))
                          && n.getDayTime() <= Integer.parseInt(DateUtil.getLastMonthLastDay().toString().replace("-", "")));
          //因为客户端显示的是两位小数的字符串,所以需要用Tools.fenToYuan把数值金额转换成字符串
          resultMap.put("today", Tools.fenToYuan(today));
          resultMap.put("yesterday", Tools.fenToYuan(yesterday));
          resultMap.put("thisMonth", Tools.fenToYuan(thisMonth));
          resultMap.put("lastMonth", Tools.fenToYuan(lastMonth));
          return resultMap;
      }
      
      //传入收益集合以及过滤接口,返回对应集合数据,Predicate接口是返回一个boolean类型的值,用于筛选
      private int returnTotalIncomeSum(List<UserIncomeDailyVO> userIncomeDailyList, Predicate<UserIncomeDailyVO> predicate) {
          return userIncomeDailyList.stream()
                  //过滤掉不符合条件的数据
                  .filter(predicate)
                  //把流中对应的总收益字段取出
                  .mapToInt(UserIncomeDailyVO::getTotalIncome)
                  //聚合总收益
                  .sum();
      }

  扩展returnTotalIncomeSum函数,mapToInt支持传入ToIntFunction参数的值.

       private int returnTotalIncomeSum(List<UserIncomeDailyVO> userIncomeDailyList, Predicate<UserIncomeDailyVO> predicate,ToIntFunction<UserIncomeDailyVO> function) {
          return userIncomeDailyList.stream()
                  //过滤掉不符合条件的数据
                  .filter(predicate)
                  //把流中对应的字段取出
                  .mapToInt(function)
                  //聚合收益
                  .sum();
  例如:
      今日分享的金额,function参数传入`UserIncomeDailyVO::getShareIncome`
      今日自购和分享的金额,funciton参数传入`userIncomeDailyVO->userIncomeDailyVO.getShareIncome()+userIncomeDailyVO.getSelfPurchaseIncome()`
  }

  今年的收益数据(聚合按月展示)

  我们先来了解一下stream的聚合 语法糖:

        list.stream().collect(
              Collectors.groupingBy(分组字段,
                       Collectors.collectingAndThen(Collectors.toList(), 
                       list -> {分组后的操作})
              ));

  流程图:代码实例:

   public ResponseResult selectIncomeDetailThisYear(int userId) {
          ResponseResult responseResult = ResponseResult.newSingleData();
          List<UserIncomeStatisticalVO> incomeStatisticalList = new LinkedList<>();
          //开始时间为今年的第一天
          String startTime = DateUtil.getThisYearFirstDay.toString();
          //区间最大时间为今日
          String endTime = LocalDate.now().toString();
          //通用SQL
          List<UserIncomeDailyVO> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime);
          //运用了stream的聚合,以月份进行分组,分组后用LinkedHashMap接收防止分组后月份顺序错乱,完毕后再把得到的每个月的收益集合流进行聚合并组装成最终的实体返回
          Map<Integer, UserIncomeStatisticalVO> resultMap = userIncomeDailyList.parallelStream()
                  .collect(Collectors.groupingBy(UserIncomeDailyVO::getMonthTime, LinkedHashMap::new,
                          Collectors.collectingAndThen(Collectors.toList(), item -> UserIncomeStatisticalVO.builder()
                                  .affiliateMemberIncome(Tools.fenToYuan(item.stream().mapToInt(UserIncomeDailyVO::getAffiliateMemberIncome).sum()))
                                  .memberIncome(Tools.fenToYuan(item.stream().mapToInt(UserIncomeDailyVO::getMemberIncome).sum()))
                                  .effectiveOrderNum(item.stream().mapToInt(UserIncomeDailyVO::getEffectiveOrderNum).sum())
                                  .shareIncome(Tools.fenToYuan(item.stream().mapToInt(UserIncomeDailyVO::getShareIncome).sum()))
                                  .totalIncome(Tools.fenToYuan(item.stream().mapToInt(UserIncomeDailyVO::getTotalIncome).sum()))
                                  .monthTimeStr(item.stream().map(time -> {
                                      String timeStr = time.getMonthTime().toString();
                                      return timeStr.substring(0, timeStr.length() - 2).concat("-").concat(timeStr.substring(timeStr.length() - 2));
                                  }).findFirst().get())
                                  .selfPurchaseIncome(Tools.fenToYuan(item.stream().mapToInt(UserIncomeDailyVO::getSelfPurchaseIncome).sum())).build()))
                  );
          resultMap.forEach((k, v) -> incomeStatisticalList.add(v));
          responseResult.setData(incomeStatisticalList);
          return responseResult;
      }

  总结

  本文主要介绍了在统计收益时,一些SQL的优化小技巧和JDK中stream聚合. 总结下来就是在业务量逐渐增大时,尽量避免多次大数量量表的查询聚合,可以分析思考后用尽量少的聚合查询完成,一些简单的业务也可以直接程序聚合.避免多次数据库查询的开销.在客户端返回接口需要时间完整性时,可以考虑时间辅助表进行关联,可以减少程序计算空值判空操作,优化代码的质量.

  相关免费学习推荐：mysql教程(视 频)

Das obige ist der detaillierte Inhalt vonElegantes statistisches Auftragseinkommen (2). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!