Maison >base de données >tutoriel mysql >Revenus de commande statistiques élégants (2)
Revenus de commande statistiques élégants (2)
- coldplay.xixiavant
- 2020-10-22 18:45:562058parcourir
La colonne
tutoriel mysql présente aujourd'hui comment compter avec élégance les revenus des commandes et réduire les soucis.
Introduction
L'article précédent expliquait en détail le différence Construisez un plan pour le rapport sur les revenus quotidiens. Ensuite, nous résoudrons le problème de l'optimisation de l'agrégation SQL lorsqu'il existe de nombreuses exigences d'agrégation.
Exigences
Cela a été expliqué en détail dans Comment compter élégamment les revenus des commandes (1), qui sont probablement les statistiques de revenus de certains jours/mois/années.
Réflexion
Objectif
Minimiser le nombre de requêtes SQL d'agrégation Donnez aux données de l'API frontale un affichage pratique, ce qui signifie que si les données d'un certain jour sont null, le back-end Traite les données avec un revenu de 0 et les envoie au front-end -
La fonction de la méthode doit être la plus générale possible pour améliorer la qualité du code
Idée
Mise en œuvre initiale
Construit à la date des revenus obtenus par isomérisation du canal Dans le cas d'un tableau statistique :
Les statistiques sur une seule journée (comme aujourd'hui, hier, date précise) peuvent être directement renvoyées en verrouillant une donnée par date Statistiques mensuelles. peut également être filtré par temps. Les données du mois en cours sont agrégées et comptées Les statistiques annuelles sont également mises en œuvre en interrogeant la plage de dates pour obtenir les statistiques de l'année. Les gains peuvent également être effectués séparément.
Il semble que l'hétérogénéité des tableaux statistiques quotidiens soit précieuse, et au moins elle peut tout résoudre besoins actuels.
Si vous avez besoin de statistiques de revenus pour aujourd'hui/hier/le mois dernier/ce mois-ci et que vous utilisez SQL pour agréger directement la requête, vous devez interroger séparément aujourd'hui, hier et l'ensemble de données couvrant tout le mois, puis les mettre en œuvre via 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='用户收益日统计'Si l'interface doit renvoyer les statistiques de revenus d'aujourd'hui/d'hier/du mois dernier/de ce mois-ci avec cette méthode d'écriture, elle devra interroger
pour y parvenir. La méthode d'écriture est bonne, mais ce n'est pas le cas. la solution optimale ? Peut-on utiliser moins de requêtes SQL ? 4次SQL
Observation
Par l'observation et l'analyse, aujourd'hui/hier /Le mois dernier/les statistiques de ce mois existent, Elles sont toutes dans le même intervalle de temps (le premier du mois dernier - la fin de ce mois), alors on peut directement connaître les données de ces deux mois via SQL, et puis utilisez l'agrégation de programmes pour obtenir facilement les données souhaitées.共同的交集
优化实现
补充一下收益日统计表设计
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教程(视 频)
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!
Déclaration：
Cet article est reproduit dans:. en cas de violation, veuillez contacter admin@php.cn Supprimer
Article précédent：Ma compréhension de MySQL Partie 3 : Plan d'exécutionArticle suivant：Ma compréhension de MySQL Partie 3 : Plan d'exécution
Articles Liés
Voir plus- Que dois-je faire si la connexion à Navicat pour MySQL échoue ?
- Comment modifier les autorisations de connexion MySQL
- Ma compréhension de MySQL Partie 2 : Index
- Statistiques pratiques sur les revenus des commandes (1)
- Comment définir la valeur de la ligne pour qu'elle soit unique dans MySQL ?
- Revenus de commande statistiques élégants (2)