如何优雅统计订单收益(二)

10/22 15:13

思考

目标

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

思路

初步实现

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

//单日收益
select 分销收益,自购收益,... from t_user_income_daily where day_time='日期' and user_id=xxx
//时间区间收益(周,月,年统计均可使用)
1.select sum(分销收益),sum(自购收益),sum(...) from t_user_income_daily where day_time BETWEEN '起始日期' AND '结束日期' and user_id=xxx

优化实现

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

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

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

最终实现

常用静态方法封装

/**
* @description: 本月的第一天
* @author: chenyunxuan
*/
public static LocalDate getThisMonthFirstDay() {
return LocalDate.of(LocalDate.now().getYear(), LocalDate.now().getMonthValue(), 1);
}

/**
* @description: 本月的最后一天
* @author: chenyunxuan
*/
public static LocalDate getThisMonthLastDay() {
}

/**
* @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() {
}

/**
* @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 &amp;&amp; s.trim().length() &gt; 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 &amp;&amp; null == month) {
//如果时间为当月则只显示今日到当月一号
startTime = DateUtil.getThisMonthFirstDay().toString();
endTime = LocalDate.now().toString();
} else {
//如果是指定年份月份,用LocalDate.of构建出需要查询的月份的一号日期和最后一天的日期
LocalDate localDate = LocalDate.of(year, month, 1);
startTime = localDate.toString();
}
//查询用通用的SQL传入用户id和开始结束时间
List<userincomedailyvo> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime);
/给前端的数据需要把数据库存的分转为字符串,如果没有相关需求可跳过直接返回
List<userincomestatisticalvo> userIncomeStatisticalList = userIncomeDailyList.stream()
.map(item -&gt; 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&lt;&gt;(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 -&gt; localDate.toString().equals(n.getDayTimeStr()));
int yesterday = returnTotalIncomeSum(userIncomeDailyList, n -&gt; localDate.minusDays(1).toString().equals(n.getDayTimeStr()));
int thisMonth = returnTotalIncomeSum(userIncomeDailyList, n -&gt;
n.getDayTime() &gt;= Integer.parseInt(DateUtil.getThisMonthFirstDay().toString().replace("-", ""))
&amp;&amp; n.getDayTime() &lt;= Integer.parseInt(DateUtil.getThisMonthLastDay().toString().replace("-", "")));
int lastMonth = returnTotalIncomeSum(userIncomeDailyList, n -&gt;
n.getDayTime() &gt;= Integer.parseInt(DateUtil.getLastMonthFirstDay().toString().replace("-", ""))
&amp;&amp; n.getDayTime() &lt;= 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();
}

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-&gt;userIncomeDailyVO.getShareIncome()+userIncomeDailyVO.getSelfPurchaseIncome()`
}

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

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

public ResponseResult selectIncomeDetailThisYear(int userId) {
ResponseResult responseResult = ResponseResult.newSingleData();
//开始时间为今年的第一天
String startTime = DateUtil.getThisYearFirstDay.toString();
//区间最大时间为今日
String endTime = LocalDate.now().toString();
//通用SQL
List<userincomedailyvo> userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime);
Map<integer, userincomestatisticalvo> resultMap = userIncomeDailyList.parallelStream()
Collectors.collectingAndThen(Collectors.toList(), item -&gt; 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 -&gt; {
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()))
);
responseResult.setData(incomeStatisticalList);
return responseResult;
}

3
8 收藏

10/22 18:04

10/22 22:05

2 评论
8 收藏
3