The introduction

The previous article detailed a scheme for heterogeneous income reporting daily. Next, let’s solve the problem of how to optimize aggregate SQL when there are many aggregate requirements.

demand

This is explained in how to elegantly tally order revenue (I), which is roughly a daily/monthly/annual revenue tally.

thinking

The target

  • Minimize the number of aggregated SQL queries
  • API data for the front-end to easily display, showing that if the data is null on a given day, the backend will process the data into a revenue of 0 data for the front-end
  • Method functions are as generic as possible to improve code quality

Train of thought

The preliminary implementation

Based on the fact that the revenue day statistics have been generated by canal:

  1. One-day statistics (for example, today, yesterday, exact date) can be returned by locking a data item directly by date.
  2. Monthly statistics Can also be filtered out by time for aggregated statistics of the current month.
  3. Annual statistics are also achieved by querying the year of the date interval.
  4. Each income can also be separately aggregated query

It seems that the heterogeneity of daily statistics is valuable, at least for all current needs. If you need today’s/yesterday’s/last month’s/this month’s revenue statistics, you need to query today’s, yesterday’s and the span of the whole month’s data sets separately and then aggregate them through SUM.

// One-day returnsSelect distribution revenue, self-purchase revenue,... from t_user_income_dailywhere day_time='date' and user_id=xxx
// Time interval returns (weekly, monthly, annual statistics can be used)1. Select sum(sales revenue),sum(self-purchase revenue),sum(... from t_user_income_dailywhere day_time BETWEEN 'Start date' AND 'End Date' and user_id=xxx
Copy the code

If the interface needs to return today’s/yesterday’s/last month’s/current month’s revenue statistics, it needs to query SQL 4 times. It’s all right, but it’s not optimal, right? Can you use fewer SQL queries?

To observe the

Through observation and analysis, the statistics of today/yesterday/last month/this month have common intersection, they are all in the same time interval (the first of last month – the end of this month), then we can directly find the data of these two months through SQL, and then through program aggregation can easily get the data we want.

To optimize the implementation

Supplement the revenue day spreadsheet design

CREATE TABLE `t_user_income_daily` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key'.  `user_id` int(11) NOT NULL COMMENT 'user id'.  `day_time` date NOT NULL COMMENT 'date'.  `self_purchase_income` int(11) DEFAULT '0' COMMENT 'Self-purchase proceeds'. `member_income` int(11) DEFAULT '0' COMMENT 'Primary Distribution Proceeds'. `affiliate_member_income` int(11) DEFAULT '0' COMMENT 'Secondary Distribution Proceeds'. `share_income` int(11) DEFAULT '0' COMMENT 'Share the proceeds'. `effective_order_num` int(11) DEFAULT '0' COMMENT 'Valid order number'. `total_income` int(11) DEFAULT '0' COMMENT 'Gross revenue'. `update_time` datetime DEFAULT NULL COMMENT 'Update Time'. PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='Daily User Revenue Statistics' Copy the code

Query returns for two months

select * from t_user_income_daily where day_time BETWEEN 'First of last month' AND 'End of the month' and user_id=xxx
Copy the code

In order to reduce the amount of data in the table, the daily statistics of the current day will not be created if there is no revenue change on the current day. Therefore, the revenue statistics of users with revenue changes in a certain period can only be queried here. If the data is empty on a certain day, special processing is required in the program. Here’s the trick: generate one in the databaseAuxiliary time tableIn days as a unit, store a variety of formatted time data, auxiliary query detailed operation can be seen in this blogMysql generates secondary tables for timeWith this table you can further optimize this SQL. The format of the time helper table is as follows, and you can also modify the stored procedure to add your own personalized time format.

 	SELECT
          a.DAY_ID day_time,
          a.MONTH_ID month_time,
          a.DAY_SHORT_DESC day_time_str,
          IFNULL(b.user_id ,#{userId}) user_id,
          IFNULL(b.self_purchase_income,0) self_purchase_income,
          IFNULL(b.member_income,0) member_income,
          IFNULL(b.affiliate_member_income,0) affiliate_member_income,
          IFNULL(b.share_income,0) share_income,
          IFNULL(b.effective_order_num,0) effective_order_num,
          IFNULL(b.total_income,0) 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 Copy the code

Auxiliary table left thinking is very simple, using time related to the benefits of query, statistics, related fields is day_time time, if not the day’s earnings data, also can have the date in SQL for that day but the statistics data is empty, use casewhen sentenced to empty assigned to 0, finally through the reverse chronological order, you can check out a set of complete time interval Statistics.

Finally realize

Based on the data queried in SQL. Use stream for aggregation in the program. Give some examples, starting with the easy ones

Generally, static encapsulation is used

/ * ** @Description: First day of the month     * @author: chenyunxuan
* /    public static LocalDate getThisMonthFirstDay() {
 return LocalDate.of(LocalDate.now().getYear(), LocalDate.now().getMonthValue(), 1);  }  / * ** @Description: Last day of the month * @author: chenyunxuan * / public static LocalDate getThisMonthLastDay() {  return LocalDate.now().with(TemporalAdjusters.lastDayOfMonth());  }  / * ** @Description: First day last month * @author: chenyunxuan * / public static LocalDate getLastMonthFirstDay() {  return LocalDate.of(LocalDate.now().getYear(), LocalDate.now().getMonthValue() - 1, 1);  }  / * ** @Description: Last day of last month * @author: chenyunxuan * / public static LocalDate getLastMonthLastDay() {  return getLastMonthFirstDay().with(TemporalAdjusters.lastDayOfMonth());  }  / * ** @Description: First day of the year * @author: chenyunxuan * / public static LocalDate getThisYearFirstDay() {  return LocalDate.of(LocalDate.now().getYear(), 1, 1);  }  / * ** @description: transfer element. Negative numbers are not supported * @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();  } Copy the code

List of earnings for specified months (in reverse chronological order)

public ResponseResult selectIncomeDetailThisMonth(int userId, Integer year, Integer month) {
        ResponseResult responseResult = ResponseResult.newSingleData();
        String startTime;
        String endTime;
// Not a specified month if (null == year && null == month) { // If the time is in the current month, only the date from today to 1st of the current month is displayed startTime = DateUtil.getThisMonthFirstDay().toString();  endTime = LocalDate.now().toString();  } else { // If it is a specified year and month, use localdate. of to build the first and last date of the month to be queried LocalDate localDate = LocalDate.of(year, month, 1);  startTime = localDate.toString();  endTime = localDate.with(TemporalAdjusters.lastDayOfMonth()).toString();  } // The query uses generic SQL to pass in the user ID and start/end time List<UserIncomeDailyVO> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime); / The data to the front-end needs to be converted into a string of data inventory, if there is no relevant requirements can be skipped directly return 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;  } Copy the code

Today/yesterday/last month/this month’s earnings

    public Map<String, String> getPersonalIncomeMap(int userId) {
        Map<String, String> resultMap = new HashMap<>(4);
        LocalDate localDate = LocalDate.now();
// Retrieve the first day of last month and the last day of this month        String startTime = DateUtil.getLastMonthFirstDay().toString();
 String endTime = DateUtil.getThisMonthLastDay().toString(); // This query is the SQL optimized above. Pass in the start and end times to get the daily revenue statistics of the users within this time range List<UserIncomeDailyVO> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime); ReturnTotalIncomeSum (); returnTotalIncomeSum (); returnTotalIncomeSum ()// The second parameter is the filter condition, only the part that matches the condition is retained. 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("-".""))); // Since the client is displaying a two-digit decimal string, tools. fenToYuan is required to convert numeric amounts to strings 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;  }  The Predicate interface returns a Boolean value for filtering. The Predicate interface returns a Boolean value for filtering private int returnTotalIncomeSum(List<UserIncomeDailyVO> userIncomeDailyList, Predicate<UserIncomeDailyVO> predicate) {  return userIncomeDailyList.stream() // Filter out the unqualified data .filter(predicate) // Fetch the corresponding total revenue field from the stream .mapToInt(UserIncomeDailyVO::getTotalIncome) // Aggregate total revenue .sum();  } Copy the code

Extending the returnTotalIncomeSum function,mapToInt supports passing in the value of the ToIntFunction argument.

    private int returnTotalIncomeSum(List<UserIncomeDailyVO> userIncomeDailyList, Predicate<UserIncomeDailyVO> predicate,ToIntFunction<UserIncomeDailyVO> function) {
        return userIncomeDailyList.stream()
// Filter out the unqualified data                .filter(predicate)
// Fetch the corresponding field from the stream .mapToInt(function) // Aggregate revenue .sum(); Such as:The amount of money shared today,functionThe parameters passed in ` UserIncomeDailyVO: : getShareIncome `Today since the purchase and the amount of the share, funciton parameters into ` userIncomeDailyVO - > userIncomeDailyVO. GetShareIncome () + userIncomeDailyVO. GetSelfPurchaseIncome ` ()} Copy the code

Revenue figures for this year (aggregated monthly)

Let’s look at the syntactic sugar of stream:

    list.stream().collect(
GroupingBy (Grouping,                     Collectors.collectingAndThen(Collectors.toList(), 
List -> {after grouping})            ));
Copy the code

Flow chart:Example code:

 public ResponseResult selectIncomeDetailThisYear(int userId) {
        ResponseResult responseResult = ResponseResult.newSingleData();
        List<UserIncomeStatisticalVO> incomeStatisticalList = new LinkedList<>();
// Start on the first day of the year        String startTime = DateUtil.getThisYearFirstDay.toString();
// The maximum time of the interval is today String endTime = LocalDate.now().toString(); / / general SQL List<UserIncomeDailyVO> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime); After grouping, use LinkedHashMap to receive to prevent the month order disorder after grouping. After grouping, aggregate and assemble the stream of each month into the final entity 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;  } Copy the code

conclusion

This article focuses on some SQL optimization tips and STREAM aggregation in the JDK when counting revenue. To sum up, when the business volume is gradually increasing, it should try to avoid the query aggregation of large number of scales for many times. It can be completed with as few query aggregation as possible after analyzing and thinking. Some simple business can also be directly program aggregation. Avoid the overhead of multiple database queries. When the client return interface needs time integrity, time auxiliary table can be considered for association, which can reduce the null-judgment operation of program calculation and optimize the quality of code.