共计 9106 个字符，预计需要花费 23 分钟才能阅读完成。
自动写代码机器人，免费开通
这篇文章主要介绍 mysql 统计订单收益的案例，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！
需求
在如何优雅统计订单收益 (一) 中已经详细说明, 大概就是些日 / 月 / 年的收益统计.
思考目标尽量减少聚合 SQL 的查询次数给前端方便展示的 API 数据, 表现在如果某一天的数据为空值时, 后端处理成收益为 0 数据给前端方法函数尽量通用提高代码质量思路初步实现
建立在已经通过 canal 异构出收益日统计表的情况下:
单日统计 (例如今日, 昨日, 精确日期) 可以直接通过日期锁定一条数据返回. 月统计也可以通过时间过滤出当月的数据进行聚合统计. 年统计也通过日期区间查询出所在年份的统计实现. 各项收益也可以分别进行聚合查询
这样看来日统计表的异构是有价值的, 至少可以解决当前的所有需求.
如果需要今日 / 昨日 / 上月 / 本月的收益统计, 用 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;
}
以上是 mysql 统计订单收益的案例的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！
向 AI 问一下细节
丸趣 TV 网 – 提供最优质的资源集合！
正文完
