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進行聚合. 舉例說明一些例子,先從簡單的開始
常用靜態方法封裝
/** ?????*?@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; ????}</userincomestatisticalvo></userincomedailyvo>
今日/昨日/上月/本月收益
????public?Map<string>?getPersonalIncomeMap(int?userId)?{ ????????Map<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()? ????????????????n.getDayTime()?>=?Integer.parseInt(DateUtil.getLastMonthFirstDay().toString().replace("-",?"")) ????????????????????????&&?n.getDayTime()??userIncomeDailyList,?Predicate<userincomedailyvo>?predicate)?{ ????????return?userIncomeDailyList.stream() ????????????????//過濾掉不符合條件的數據 ????????????????.filter(predicate) ????????????????//把流中對應的總收益字段取出 ????????????????.mapToInt(UserIncomeDailyVO::getTotalIncome) ????????????????//聚合總收益 ????????????????.sum(); ????}</userincomedailyvo></userincomedailyvo></string></string>
擴展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()` }</userincomedailyvo></userincomedailyvo></userincomedailyvo>
今年的收益數據(聚合按月展示)
我們先來了解一下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>?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; ????}</integer></userincomedailyvo></userincomestatisticalvo>
總結
本文主要介紹了在統計收益時,一些SQL的優化小技巧和JDK中stream聚合. 總結下來就是在業務量逐漸增大時,盡量避免多次大數量量表的查詢聚合,可以分析思考后用盡量少的聚合查詢完成,一些簡單的業務也可以直接程序聚合.避免多次數據庫查詢的開銷.在客戶端返回接口需要時間完整性時,可以考慮時間輔助表進行關聯,可以減少程序計算空值判空操作,優化代碼的質量.
相關免費學習推薦：mysql教程(視 頻)
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
