mysql教程欄目今天介紹如何優雅的統計訂單收益，減少煩惱。
引言
上篇文章詳細說明了異構出收益日報表的方案.接下來我們來解決聚合需求多的情況下如何優化聚合SQL的問題.
需求
在如何優雅統計訂單收益(一)中已經詳細說明,大概就是些日/月/年的收益統計.
思考
目标盡量減少聚合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進行聚合.
舉例說明一些例子,先從簡單的開始
常用靜态方法封裝
public static LocalDate getThisMonthFirstDay() {
return LocalDate.of(LocalDate.now().getYear(), LocalDate.now().getMonthValue(), 1);
}
public static LocalDate getThisMonthLastDay() {
return LocalDate.now().with(TemporalAdjusters.lastDayOfMonth());
}
public static LocalDate getLastMonthFirstDay() {
return LocalDate.of(LocalDate.now().getYear(), LocalDate.now().getMonthValue() - 1, 1);
}
public static LocalDate getLastMonthLastDay() {
return getLastMonthFirstDay().with(TemporalAdjusters.lastDayOfMonth());
}
public static LocalDate getThisYearFirstDay() {
return LocalDate.of(LocalDate.now().getYear(), 1, 1);
}
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;
}
今日/昨日/上月/本月收益public Map getPersonalIncomeMap(int userId) {
Map resultMap = new HashMap<>(4);
LocalDate localDate = LocalDate.now();
//取出上個月第一天和這個月最後一天
String startTime = DateUtil.getLastMonthFirstDay().toString();
String endTime = DateUtil.getThisMonthLastDay().toString();
//這條查詢就是上面優化過的SQL.傳入開始和結束時間獲得這個時間區間使用者的收益日統計資料
List 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 userIncomeDailyList, Predicate predicate) {
return userIncomeDailyList.stream()
//過濾掉不符合條件的資料
.filter(predicate)
//把流中對應的總收益字段取出
.mapToInt(UserIncomeDailyVO::getTotalIncome)
//聚合總收益
.sum();
}
擴充returnTotalIncomeSum函數,mapToInt支援傳入ToIntFunction參數的值.private int returnTotalIncomeSum(List userIncomeDailyList, Predicate predicate,ToIntFunction 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 incomeStatisticalList = new LinkedList<>();
//開始時間為今年的第一天
String startTime = DateUtil.getThisYearFirstDay.toString();
//區間最大時間為今日
String endTime = LocalDate.now().toString();
//通用SQL
List userIncomeDailyList = selectIncomeByTimeInterval(userId, startTime, endTime);
//運用了stream的聚合,以月份進行分組,分組後用LinkedHashMap接收防止分組後月份順序錯亂,完畢後再把得到的每個月的收益集合流進行聚合并組裝成最終的實體傳回
Map 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教程(視
頻)