введение
В предыдущей статье было подробно объяснено решение разнородного отчета о ежедневном доходе Теперь давайте решим проблему оптимизации SQL-агрегации при наличии множества требований к агрегации.
нужно
существуетКак элегантно подсчитать доход от заказа (1)Это было подробно объяснено выше, что, вероятно, является статистикой доходов за несколько дней/месяцев/лет.
считать
Цель
- Минимизируйте количество запросов для агрегирования SQL
- Данные API для внешнего интерфейса для удобного отображения, а это означает, что если данные определенного дня пусты, серверная часть обработает их в данные 0 и отправит во внешний интерфейс.
- Методы и функции должны быть как можно более общими для улучшения качества кода.
идеи
Первоначальная реализация
На основании того факта, что таблица статистики доходных дней была неоднородно сгенерирована через канал:
- Статистику за один день (например, сегодня, вчера, точную дату) можно получить напрямую, заблокировав данные по дате.
- Ежемесячная статистика также может фильтровать данные за текущий месяц по времени для сводной статистики.
- Статистика года также реализуется путем запроса статистики года в диапазоне дат.
- Каждый доход также может быть агрегирован и запрошен отдельно.
Кажется, что неоднородность таблицы ежедневной статистики ценна, по крайней мере, для решения всех текущих потребностей.
Если вам нужна статистика доходов за сегодня/вчера/последний месяц/месяц и вы используете SQL для агрегирования и прямого запроса, вам нужно запросить сегодня, вчера и набор данных, охватывающий весь месяц, отдельно, а затем передатьSUM聚合выполнить.
//单日收益
select 分销收益,自购收益,... from t_user_income_daily where day_time='日期' and user_id=xxx
//时间区间收益(周,月,年统计均可使用)
1.select sum(分销收益),sum(自购收益),sum(...) from t_user_income_daily where day_time BETWEEN '起始日期' AND '结束日期' and user_id=xxx
При таком способе написания, если интерфейс должен вернуть статистику доходов за сегодняшний/вчерашний/последний месяц/месяц, вам нужно запросить4次SQLЭто может быть достигнуто.Метод написания хорош, но это не оптимальное решение?Можно ли запросить его с меньшим количеством SQL?
наблюдать
Благодаря наблюдению и анализу существует статистика за сегодня/вчера/последний месяц/месяц.共同的交集, они все в одном временном промежутке (первое число прошлого месяца - конец этого месяца), то мы можем напрямую через SQL узнать данные этих двух месяцев, а потом легко получить нужные нам данные через программу агрегация.
Оптимизированная реализация
Дополнить оформление таблицы статистики доходных дней
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='用户收益日统计'
Узнать доход за два месяца
select * from t_user_income_daily where day_time BETWEEN '上月一号' AND '本月月末' and user_id=xxx
Чтобы уменьшить количество данных в таблице, если в течение дня нет изменения дохода, ежедневные статистические данные текущего дня не будут созданы, поэтому здесь мы можем только запросить статистические данные о доходах пользователя с доходом. меняется через определенный интервал времени.Если данные пустые в определенный день Это тоже требует специальной обработки в программе.Вот небольшая хитрость для генерации файла в базе данных时间辅助表.В днях он хранит различные отформатированные данные о времени, а подробное описание работы вспомогательного запроса можно найти в этом сообщении в блоге.Mysql генерирует вспомогательную таблицу времениС помощью этой таблицы вы можете дополнительно оптимизировать этот SQL.Формат вспомогательной таблицы времени выглядит следующим образом, вы также можете изменить хранимую процедуру и добавить свой собственный персонализированный формат времени.
SELECT
a.DAY_ID day_time,
a.MONTH_ID month_time,
a.DAY_SHORT_DESC day_time_str,
IFNULL(b.user_id ,#{userId}) user_id,
IFNULL(b.self_purchase_income,0) self_purchase_income,
IFNULL(b.member_income,0) member_income,
IFNULL(b.affiliate_member_income,0) affiliate_member_income,
IFNULL(b.share_income,0) share_income,
IFNULL(b.effective_order_num,0) effective_order_num,
IFNULL(b.total_income,0) 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
Идея очень проста.Используйте вспомогательную таблицу времени, чтобы связать слева запрашиваемую таблицу статистики доходов.Связанным полем является время дня.Если нет данных о доходах за день, также будут данные в 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()`
}
Данные о доходах за этот год (агрегированные ежемесячно)
Давайте сначала посмотрим на агрегацию потоков Синтаксический сахар:
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聚合.
Подводя итог, когда объем бизнеса постепенно увеличивается,尽量避免Агрегирование запросов большого количества масштабов много раз может быть выполнено с минимальным количеством запросов агрегирования после анализа и размышлений, а некоторые простые сервисы также могут быть напрямую агрегированы программой.Избегайте накладных расходов на несколько запросов к базе данных.Когда клиент возвращается интерфейс, когда требуется целостность времени, вспомогательная таблица времени может быть рассмотрена для ассоциации, что может уменьшить вычисление нулевого значения и определить нулевую операцию программы, а также оптимизировать качество кода.