优雅的统计订单收益(二)

---

mysql教程栏目今天介绍如何优雅的统计订单收益，减少烦恼。

实用的企业订单销售管理cms模板

一套响应式的订单查询，订单收益，订单管理，项目进度管理cms后台模板。适用于手机app订单统计管理后台模板。包含多种ui小组件，和2个主页，总共87个页面html下载。

437 查看详情

引言

上篇文章详细说明了异构出收益日报表的方案.接下来我们来解决聚合需求多的情况下如何优化聚合SQL的问题.

需求

在如何优雅统计订单收益(一)中已经详细说明,大概就是些日/月/年的收益统计.

思考

目标

• 尽量减少聚合SQL的查询次数
• 给前端方便展示的API数据,表现在如果某一天的数据为空值时,后端处理成收益为0数据给前端
• 方法函数尽量通用提高代码质量

思路

初步实现

建立在已经通过canal异构出收益日统计表的情况下:

1. 单日统计(例如今日,昨日,精确日期)可以直接通过日期锁定一条数据返回.
2. 月统计也可以通过时间过滤出当月的数据进行聚合统计.
3. 年统计也通过日期区间查询出所在年份的统计实现.
4. 各项收益也可以分别进行聚合查询

这样看来日统计表的异构是有价值的,至少可以解决当前的所有需求. 如果需要今日/昨日/上月/本月的收益统计,用SQL直接聚合查询,则需要分别查询今日,昨日以及跨度为整月的数据集然后通过SUM聚合实现.

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

这种写法如果接口需要返回今日/昨日/上月/本月的收益统计时,就需要查询4次SQL才可以实现.写法没问题,但是不是最优解?可以用更少的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教程(视 频)

以上就是优雅的统计订单收益(二)的详细内容，更多请关注其它相关文章！

# 上个月  # 广西网站建设成本  # 拼多多店的营销推广方式  # 前端可以做seo吗  # 大流量网站怎么优化软件  # 面试seo技巧  # 市区seo  # 洛阳关键词排名优化厂家  # 威宁seo是什么最贵吗  # 云浮营销网络推广哪个好  # 网站建设geilf  # 统计订单  # 都是  # 时间为  # 当月  # 销售管理  # 镜像  # 昨日  # 执行时间  # 上月  # 今日 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： 《海豚家》注销账号方法  J*aScript调试技巧_性能分析与内存快照  WPS长文档分栏排版不乱方法_WPS分栏+分节符报纸排版教程  CSS如何使用outline-offset与颜色组合突出元素边框  汽水音乐车机版官网5.0 汽水音乐车机版5.0版本下载入口  Mac hosts文件在哪里_Mac修改hosts文件详细教程  抖音火山版注销账号抖音会注销吗 抖音火山版与抖音账号注销关系  C++如何实现矩阵乘法_C++二维数组矩阵运算代码示例  C++ switch case字符串_C++如何实现字符串switch匹配  高德地图导航路线偏差报警频繁怎么办 高德地图路线偏差修复与优化方法  网易云音乐闹钟铃声设置教程  支付宝登录刷脸不是本人如何解决  Lar*el怎么实现全文搜索_Lar*el Scout集成Algolia教程  极兔快递官网查询入口手机版 手机极兔快递登录查询入口官方  画质怪兽120帧安卓和平精英免费版  淘口令快速解析技巧  电脑“无法访问指定设备、路径或文件”怎么办？五种权限设置方法  《花瓣》创建专辑方法  sublime如何处理超大文件不卡顿 _sublime打开大日志文件技巧  《sketchbook》选中部分图案移动方法  MacBook Pro词典使用指南  为什么XML解析器对大小写敏感？ 理解XML规范中的大小写规则与最佳实践  《百度畅听版》关闭兴趣推荐方法  手机雨课堂网页版入口免登录 雨课堂网页版可点击直接进入  j*a中ArrayBlockingQueue的使用  歌词怎么展示在|直播|间视频号？有什么注意事项？  《edge浏览器》关闭翻译功能方法  知音漫客官网首页入口_知音漫客热门漫画推荐  word怎么将图片设置为页面背景并不影响打印_Word图片背景设置方法  C#解析并修改XML后保存 如何确保格式与编码的正确性  qq邮箱格式填写示例 qq邮箱标准填写规范  抖音如何进行蓝V认证 抖音企业号申请所需资料与流程  firefox火狐浏览器最新官网主页_ firefox火狐浏览器平台入口直达官方链接  Django模型动态关联检查：高效管理复杂关系  告别繁琐SEO！如何使用SyliusSitemap插件自动化生成网站地图，提升搜索引擎排名  mysql怎么查询数据_mysql基础查询语句使用教程  青橙手机语音助手怎么唤醒_青橙手机语音助手设置与唤醒方法  蛙漫2(台版)正版官网 2025免费网页版分享  《小黑盒》删除历史浏览方法  汽水音乐网页端访问 汽水音乐官方网页直达  Lar*el 中高效执行多列更新：单次查询实现  动漫之家观看全集库 动漫之家免费资源网地址  快递物流路径揭秘  Lar*el如何创建自定义的辅助函数（Helpers）_Lar*el全局函数定义与加载方法  江苏大剧院会员卡购买步骤  FotoBalloon图片左右镜像教程  视频号视频怎么提取文案?提取的文案如何优化与使用?  中大网校app做题记录清除方法  《虎扑》关闭社区内容推荐方法  在Peewee中处理PostgreSQL记录重复：一站式数据摄取教程 

 2020-10-22