Elegant statistical order income (2)

mysql tutorial column today introduces how to elegantly count order income and reduce worries.

##Introduction

The previous article explained in detail the difference Construct a plan for the daily income report. Next, we will solve the problem of how to optimize the aggregation SQL when there are many aggregation requirements.

Requirements

It has been explained in detail in How to Elegantly Count Order Revenues (1), which is probably the revenue statistics of some days/months/years.

Thinking

##Goal

• Minimize the number of queries for aggregation SQL
• Provide API data for the front end to display conveniently, which means that if the data on a certain day is null, the back end Process the data with a revenue of 0 and send it to the front end
• The method function should be as general as possible to improve the code quality

Ideas

Initial implementation

is established on the day when income has been generated through canal isomerization In the case of statistical tables:

1. Single-day statistics (such as today, yesterday, precise date) can be directly returned by locking a piece of data by date.
2. Monthly statistics can also be filtered out by time The data of the current month are aggregated and statistics are performed.
3. Year statistics are also implemented by querying the date interval to obtain the statistics of the year.
4. Various income can also be Perform aggregation queries separately

It seems that the heterogeneity of daily statistical tables is valuable, and at least it can solve all current needs. If you need income statistics for today/yesterday/last month/this month, and use SQL to directly aggregate the query, you need to query today, yesterday and the data set spanning the entire month separately and then implement it through SUM aggregation.

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='用户收益日统计'

If the interface needs to return today's/yesterday's/last month's/this month's income statistics with this writing method, it will need to query 4 SQL times to achieve it. The writing method is okay, but it is not optimal. Solution? Can you query with less SQL?

##Observation##Through observation and analysis, today/yesterday/ There is a

common intersection

in the statistics of last month/this month. They are all in the same time interval (the first of the previous month - the end of this month). Then we can directly find out the data of these two months through SQL, Then through program aggregation, we can easily get the data we want.<h3 style="margin-top: 30px; margin-bottom: 15px; padding: 0px; font-weight: bold; color: black; font-size: 20px; margin: 20px auto 5px; border-top: 1px solid rgb(221, 221, 221); box-sizing: border-box;" data-id="heading-7"> <span class="prefix" style="display: none;"></span><span class="content" style="margin-top: -1px; padding-top: 6px; padding-right: 5px; padding-left: 5px; font-size: 17px; border-top: 2px solid rgb(33, 33, 34); display: inline-block; line-height: 1.1;">优化实现</span><span class="suffix" style="display: none;"></span> </h3> <p style="padding-top: 8px; padding-bottom: 8px; margin: 0; color: black; box-sizing: border-box; margin-bottom: 16px; font-family: 'Helvetica Neue', Helvetica, 'Segoe UI', Arial, freesans, sans-serif; font-size: 15px; text-align: start; white-space: normal; text-size-adjust: auto; line-height: 1.75em;">补充一下收益日统计表设计</p><pre class="brush:php;toolbar:false;" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">select * from t_user_income_daily where day_time BETWEEN &amp;#39;上月一号&amp;#39; AND &amp;#39;本月月末&amp;#39; and user_id=xxx</pre><p style="padding-top: 8px; padding-bottom: 8px; margin: 0; color: black; box-sizing: border-box; margin-bottom: 16px; font-family: 'Helvetica Neue', Helvetica, 'Segoe UI', Arial, freesans, sans-serif; font-size: 15px; text-align: start; white-space: normal; text-size-adjust: auto; line-height: 1.75em;">查询出两个月的收益</p><pre class="brush:php;toolbar:false;" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;">select * from t_user_income</pre><p style="padding-top: 8px; padding-bottom: 8px; margin: 0; color: black; box-sizing: border-box; margin-bottom: 16px; font-family: 'Helvetica Neue', Helvetica, 'Segoe UI', Arial, freesans, sans-serif; font-size: 15px; text-align: start; white-space: normal; text-size-adjust: auto; line-height: 1.75em;">为了减少表的数据量,如果当日没有收益变动是不会创建当日的日统计数据的,所以这里只能查询出某时间区间用户有收益变动的收益统计数据.如果处理某一天数据为空的情况则还需要再程序中特殊处理.此处有小妙招,在数据库中生成一张<code style="font-size: 14px; word-wrap: break-word; padding: 2px 4px; border-radius: 4px; margin: 0 2px; background-color: rgba(27,31,35,.05); font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; word-break: break-all; color: rgb(239, 112, 96);">时间辅助表.以天为单位,存放各种格式化后的时间数据,辅助查询详细操作可见这篇博文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教程(视 频)

The above is the detailed content of Elegant statistical order income (2). For more information, please follow other related articles on the PHP Chinese website!