오늘
mysql tutorial 칼럼에서는 주문 수입을 우아하게 계산하고 걱정을 줄이는 방법을 소개합니다.
이전 글에서는 집계 요구사항이 많은 경우 집계 SQL을 최적화하는 방법에 대한 문제를 해결해 보겠습니다.
는 주문 수익을 우아하게 계산하는 방법(1)에 자세히 설명되어 있는데, 이는 아마도 며칠/월/년의 수익 통계일 것입니다.
일일 통계표의 이질성은 가치가 있으며 적어도 현재의 모든 요구 사항을 해결할 수 있습니다.
오늘/어제/지난달/이달의 소득 통계가 필요하고 SQL을 사용하여 직접 쿼리를 집계하는 경우 오늘, 어제 및 한 달 전체에 걸친 데이터 세트를 별도로 쿼리한 후 SUM 집계 구현.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查询么?
通过观察分析,今日/昨日/上月/本月统计存在共同的交集
select * from t_user_income_daily where day_time BETWEEN '上月一号' AND '本月月末' and user_id=xxx
4번의 SQL이 가능합니다. 작성에는 문제가 없으나 최적의 해결 방법은 아닙니다. SQL 쿼리를 더 적게 사용할 수 있습니까?🎜공통 교차점은 모두 동일합니다. 시간 간격(지난 달 1일 ~ 이번 달 말)을 입력하면 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;
}本文主要介绍了在统计收益时,一些SQL的优化小技巧和JDK中stream聚合.
总结下来就是在业务量逐渐增大时,尽量避免多次大数量量表的查询聚合,可以分析思考后用尽量少的聚合查询完成,一些简单的业务也可以直接程序聚合.避免多次数据库查询的开销.在客户端返回接口需要时间完整性时,可以考虑时间辅助表进行关联,可以减少程序计算空值判空操作,优化代码的质量.
相关免费学习推荐：mysql教程(视 频)
위 내용은 우아한 통계적 주문수입 (2)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
