上篇文章详细说明了异构出收益日报表的方案.接下来我们来解决聚合需求多的情况下如何优化聚合SQL的问题.
在如何优雅统计订单收益(一)中已经详细说明,大概就是些日/月/年的收益统计.
建立在已经通过canal异构出收益日统计表的情况下:
这样看来日统计表的异构是有价值的,至少可以解决当前的所有需求.
如果需要今日/昨日/上月/本月的收益统计,用SQL直接聚合查询,则需要分别查询今日,昨日以及跨度为整月的数据集然后通过SUM聚合实现.
CREATE TABLE `t_user_income_daily` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &＃39;主键&＃39;, `user_id` int(11) NOT NULL COMMENT &＃39;用户id&＃39;, `day_time` date NOT NULL COMMENT &＃39;日期&＃39;, `self_purchase_income` int(11) DEFAULT &＃39;0&＃39; COMMENT &＃39;自购收益&＃39;, `member_income` int(11) DEFAULT &＃39;0&＃39; COMMENT &＃39;一级分销收益&＃39;, `affiliate_member_income` int(11) DEFAULT &＃39;0&＃39; COMMENT &＃39;二级分销收益&＃39;, `share_income` int(11) DEFAULT &＃39;0&＃39; COMMENT &＃39;分享收益&＃39;, `effective_order_num` int(11) DEFAULT &＃39;0&＃39; COMMENT &＃39;有效订单数&＃39;, `total_income` int(11) DEFAULT &＃39;0&＃39; COMMENT &＃39;总收益&＃39;, `update_time` datetime DEFAULT NULL COMMENT &＃39;更新时间&＃39;, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT=&＃39;用户收益日统计&＃39;
这种写法如果接口需要返回今日/昨日/上月/本月的收益统计时,就需要查询4次SQL才可以实现.写法没问题,但是不是最优解?可以用更少的SQL查询么?
通过观察分析,今日/昨日/上月/本月统计存在共同的交集,它们都处于同一个时间区间(上月一号-本月月末),那我们可以通过SQL直接查出这两个月的数据,再通过程序聚合就可以轻松得出我们想要的数据.
补充一下收益日统计表设计
select * from t_user_income_daily where day_time BETWEEN &＃39;上月一号&＃39; AND &＃39;本月月末&＃39; 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, &＃39;%Y-%m-%d&＃39; ) 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 userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime);
/给前端的数据需要把数据库存的分转为字符串,如果没有相关需求可跳过直接返回
List 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;
} 
京公网安备 11010802041100号