mysql統計訂單收益的案例

155次閱讀

沒有評論

共計 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 網 – 提供最優質的資源集合！

正文完