jyz-base-start/src/main/java/com/tievd/jyz/Timer/SystemSchedule.java
@@ -279,7 +279,13 @@ */ public String infer() { try { if (CollectionUtils.isEmpty(rules.getClientConfigs())) { return null; } for (ClientConfig rule : rules.getClientConfigs()) { if (rule == null || rule.getTimeValue() == null || StringUtils.isBlank(rule.getTimeUnit())) { return null; } ChronoUnit unit = ChronoUnit.valueOf(rule.getTimeUnit().toUpperCase()); LocalDate timeLimit = LocalDate.now().minus(rule.getTimeValue(), unit); switch(unit) { @@ -288,30 +294,53 @@ } LocalDate finalTimeLimit = timeLimit; List<T> filterData = dataList.stream().filter(t -> !timeVal.apply(t).isBefore(finalTimeLimit)).collect(Collectors.toList()); int limit = rule.getCountNum(); int ref = rule.getCountRef(); if (rule.getCountType() == 1) { int sum = filterData.size(); if (Integer.compare(sum, limit) != ref) { // 根据规则类型选择判断逻辑 Byte ruleType = rule.getRuleType(); if (ruleType == null) { return null; } if (ruleType == 1) { if (rule.getCountType() == null || rule.getCountRef() == null || rule.getCountNum() == null) { return null; } } else if((rule.getCountType() == 2)) { Map<Integer, Integer> monthCount = new HashMap<>(); int nowMonth = LocalDate.now().getMonthValue(); for (int month = timeLimit.getMonthValue(); month != nowMonth ; month++) { monthCount.put(month, 0); } for (T tmp : filterData) { int month = timeVal.apply(tmp).getMonthValue(); if (monthCount.containsKey(month)) { monthCount.put(month, monthCount.get(month) + 1); // 加油频次（原有逻辑） int limit = rule.getCountNum(); int ref = rule.getCountRef(); if (rule.getCountType() == 1) { int sum = filterData.size(); if (Integer.compare(sum, limit) != ref) { return null; } } Collection<Integer> countNums = monthCount.values(); if (countNums.stream().anyMatch(n -> Integer.compare(n, limit) != ref)) { } else if((rule.getCountType() == 2)) { Map<Integer, Integer> monthCount = new HashMap<>(); int nowMonth = LocalDate.now().getMonthValue(); for (int month = timeLimit.getMonthValue(); month != nowMonth ; month++) { monthCount.put(month, 0); } for (T tmp : filterData) { int month = timeVal.apply(tmp).getMonthValue(); if (monthCount.containsKey(month)) { monthCount.put(month, monthCount.get(month) + 1); } } Collection<Integer> countNums = monthCount.values(); if (countNums.stream().anyMatch(n -> Integer.compare(n, limit) != ref)) { return null; } } else { return null; } ///////////////////////////// } else if (ruleType == 2) { // 加油趋势（新逻辑） if (rule.getCountTrend() == null || rule.getCountNum() == null || rule.getHistoryMonths() == null || rule.getRecentMonths() == null) { return null; } if (!checkTrend(rule, filterData)) { return null; } } else { return null; } } } catch (Exception e) { @@ -321,6 +350,69 @@ return rules.getClientName(); } /** * 趋势判断（使用配置的月数） * @param rule 规则 * @param dataList 数据列表 * @return */ private boolean checkTrend(ClientConfig rule, List<T> dataList) { if (dataList == null || dataList.isEmpty()) { return false; } Byte countTrend = rule.getCountTrend(); Integer countNum = rule.getCountNum(); Integer historyMonths = rule.getHistoryMonths(); Integer recentMonths = rule.getRecentMonths(); if (countTrend == null || countNum == null || historyMonths == null || recentMonths == null) { return false; } if (historyMonths <= 0 || recentMonths <= 0 || countNum <= 0) { return false; } LocalDate now = LocalDate.now(); LocalDate currentMonth = now.withDayOfMonth(1); LocalDate recentStart = currentMonth.minusMonths(recentMonths - 1L); LocalDate historyStart = recentStart.minusMonths(historyMonths); LocalDate historyEnd = recentStart; Map<LocalDate, Integer> recentMonthCount = new LinkedHashMap<>(); for (int i = 0; i < recentMonths; i++) { LocalDate month = recentStart.plusMonths(i); recentMonthCount.put(month, 0); } Map<LocalDate, Integer> historyMonthCount = new LinkedHashMap<>(); for (int i = 0; i < historyMonths; i++) { LocalDate month = historyStart.plusMonths(i); historyMonthCount.put(month, 0); } for (T tmp : dataList) { LocalDate month = timeVal.apply(tmp).withDayOfMonth(1); if (recentMonthCount.containsKey(month)) { recentMonthCount.put(month, recentMonthCount.get(month) + 1); } if (historyMonthCount.containsKey(month) && !month.isBefore(historyStart) && month.isBefore(historyEnd)) { historyMonthCount.put(month, historyMonthCount.get(month) + 1); } } if (countTrend == 1) { boolean historyStable = historyMonthCount.values().stream().allMatch(n -> n >= countNum); boolean recentStable = recentMonthCount.values().stream().allMatch(n -> n >= countNum); return historyStable && recentStable; } else if (countTrend == 2) { boolean historyStable = historyMonthCount.values().stream().allMatch(n -> n >= countNum); boolean recentDecrease = recentMonthCount.values().stream().allMatch(n -> n < countNum); return historyStable && recentDecrease; } return false; } } } jyz-base-start/src/main/java/com/tievd/jyz/controller/ClientConfigController.java
@@ -5,21 +5,31 @@ import com.tievd.cube.commons.annotations.DictApi; import com.tievd.cube.commons.base.CubeController; import com.tievd.cube.commons.base.Result; import com.tievd.cube.commons.constant.CacheConst; import com.tievd.cube.modules.system.entity.SysDictItem; import com.tievd.cube.modules.system.service.ISysDictItemService; import com.tievd.jyz.entity.Client; import com.tievd.jyz.entity.ClientConfig; import com.tievd.jyz.entity.vo.ClientVo; import com.tievd.jyz.service.IClientConfigService; import com.tievd.jyz.service.IClientService; import cn.dev33.satoken.stp.StpUtil; import io.swagger.v3.oas.annotations.Operation; import io.swagger.v3.oas.annotations.tags.Tag; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.cache.annotation.CacheEvict; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.*; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.Arrays; import java.util.Collections; import java.util.List; import java.util.UUID; /** * ClientConfig @@ -35,11 +45,16 @@ @Tag(name = "客户规则配置") public class ClientConfigController extends CubeController<ClientConfig, IClientConfigService> { private static final String DICT_ID = "1631109859116990466"; @Autowired private IClientConfigService clientConfigService; @Autowired IClientService clientService; @Autowired private ISysDictItemService sysDictItemService; /** * 分页列表查询 @@ -54,10 +69,13 @@ @AutoLog("ClientConfig-添加") @PostMapping("/add") @Transactional(rollbackFor = Exception.class) @CacheEvict(value = CacheConst.SYS_DICT_CACHE, allEntries = true) @Operation(summary = "添加客户规则") public Result<?> add(@RequestBody ClientVo clientVo) { clientService.save(clientVo); clientConfigService.saveBatch(getConfig(clientVo)); syncDictItem(clientVo.getClientName(), clientVo.getId()); return Result.ok(); } @@ -66,23 +84,73 @@ */ @AutoLog("ClientConfig-编辑") @PutMapping("/edit") @Transactional(rollbackFor = Exception.class) @CacheEvict(value = CacheConst.SYS_DICT_CACHE, allEntries = true) @Operation(summary = "修改客户规则") public Result<?> edit(@RequestBody ClientVo clientVo) { clientService.updateById(clientVo); clientConfigService.remove(new LambdaQueryWrapper<ClientConfig>().eq(ClientConfig::getClientId, clientVo.getId())); clientConfigService.saveBatch(getConfig(clientVo)); updateDictItem(clientVo.getClientName(), clientVo.getId()); return Result.ok(); } List<ClientConfig> getConfig(ClientVo clientVo) { List<ClientConfig> clientConfigs = clientVo.getClientConfigs(); if (clientConfigs == null || clientConfigs.isEmpty()) { return Collections.emptyList(); } clientConfigs.forEach(c -> { String[] param = c.getTimeStr().split(","); if (StringUtils.hasText(c.getTimeStr())) { String[] param = c.getTimeStr().split(","); c.setTimeValue(Integer.valueOf(param[0])).setTimeUnit(param[1]); } if (c.getRuleType() == null) { c.setRuleType((byte) 1); } if (c.getRuleType() == 2) { if (c.getCountType() == null) { c.setCountType((byte) 2); } if (c.getCountRef() == null) { c.setCountRef((byte) 1); } } c.setClientId(clientVo.getId()) .setClientName(clientVo.getClientName()) .setTimeValue(Integer.valueOf(param[0])).setTimeUnit(param[1]); .setClientName(clientVo.getClientName()); }); return clientConfigs; } private void syncDictItem(String clientName, Integer clientId) { SysDictItem dictItem = new SysDictItem(); dictItem.setId(UUID.randomUUID().toString().replace("-", "")); dictItem.setDictId(DICT_ID); dictItem.setItemText(clientName); dictItem.setItemValue(String.valueOf(clientId)); dictItem.setSortOrder(clientId); dictItem.setStatus(1); dictItem.setCreateBy(StpUtil.getLoginIdAsString()); dictItem.setCreateTime(new java.util.Date()); sysDictItemService.save(dictItem); } private void updateDictItem(String clientName, Integer clientId) { LambdaQueryWrapper<SysDictItem> wrapper = new LambdaQueryWrapper<>(); wrapper.eq(SysDictItem::getDictId, DICT_ID).eq(SysDictItem::getItemValue, String.valueOf(clientId)); SysDictItem dictItem = sysDictItemService.getOne(wrapper); if (dictItem != null) { dictItem.setItemText(clientName); sysDictItemService.updateById(dictItem); } else { syncDictItem(clientName, clientId); } } private void deleteDictItem(String clientName) { LambdaQueryWrapper<SysDictItem> wrapper = new LambdaQueryWrapper<>(); wrapper.eq(SysDictItem::getDictId, DICT_ID).eq(SysDictItem::getItemText, clientName); sysDictItemService.remove(wrapper); } /** @@ -90,10 +158,18 @@ */ @AutoLog("ClientConfig-通过id删除") @DeleteMapping("/delete") @Transactional(rollbackFor = Exception.class) @CacheEvict(value = CacheConst.SYS_DICT_CACHE, allEntries = true) @Operation(summary = "删除客户规则") public Result<?> delete(@RequestParam String id) { Client client = clientService.getById(id); if (client == null) { return Result.error("记录不存在"); } String clientName = client.getClientName(); clientService.removeById(id); clientConfigService.remove(new LambdaQueryWrapper<ClientConfig>().eq(ClientConfig::getClientId, id)); deleteDictItem(clientName); return Result.ok(); }
New file @@ -0,0 +1,42 @@ package com.tievd.jyz.controller; import com.tievd.cube.commons.base.CubeController; import com.tievd.cube.commons.base.Result; import com.tievd.jyz.entity.Client; import com.tievd.jyz.service.IClientService; import io.swagger.v3.oas.annotations.Operation; import io.swagger.v3.oas.annotations.tags.Tag; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; /** * Client * * @author cube * @since 2023-08-18 * @version V2.0.0 */ @Slf4j @RestController @RequestMapping("/jyz/client") @Tag(name = "客户类型接口") public class ClientController extends CubeController<Client, IClientService> { @Autowired private IClientService clientService; /** * 获取客户类型列表 */ @GetMapping("/listClientType") @Operation(summary = "获取客户类型列表") public Result<List<Client>> listClientType() { List<Client> list = clientService.list(); return Result.ok(list); } } jyz-base-start/src/main/java/com/tievd/jyz/controller/DataTableController.java
@@ -66,6 +66,15 @@ StatDataTableVo total = oilRecordService.statisTotal(param); return Result.ok(total); } @PostMapping("/statisOilFreqCompare") @Operation(summary = "活动前后车辆加油频次统计") public Result<?> statisOilFreqCompare(@RequestBody DataStatisReqVo param) { LoginUser user = SystemContextUtil.currentLoginUser(); param.setOrgCodeIfnull(user.getOrgCode()); List<Map> list = oilRecordService.statisOilFreqCompare(param); return Result.ok(list); } @GetMapping("/clientTop") @Operation(summary = "客户类型top10") @@ -118,6 +127,8 @@ @PostMapping("/statTrend") @Operation(summary = "加油站统计-顶部条形图") public Result<?> statTrend(@RequestBody DataStatisReqVo param) { LoginUser user = SystemContextUtil.currentLoginUser(); param.setOrgCodeIfnull(user.getOrgCode()); JSONObject statFan; if (param.getTrendType() == DataStatisReqVo.TrendType.CLIENT || param.getTrendType() == DataStatisReqVo.TrendType.LOSE_CLIENT) { statFan = oilRecordService.statTrendClient(param); @@ -126,6 +137,15 @@ } return Result.ok(statFan); } @PostMapping("/statTrendDetail") @Operation(summary = "加油站统计-趋势分析明细") public Result<?> statTrendDetail(@RequestBody DataStatisReqVo param) { LoginUser user = SystemContextUtil.currentLoginUser(); param.setOrgCodeIfnull(user.getOrgCode()); List<Map<String, Object>> list = oilRecordService.statTrendDetail(param); return Result.ok(list); } } jyz-base-start/src/main/java/com/tievd/jyz/entity/Client.java
@@ -37,6 +37,9 @@ @TableField("create_time") private Timestamp createTime; @TableField("history_oil_count") private Integer historyOilCount; @Override public Serializable pkVal() { return this.id; jyz-base-start/src/main/java/com/tievd/jyz/entity/ClientConfig.java
@@ -61,6 +61,22 @@ @TableField("count_num") private Integer countNum; @Schema(description = "加油趋势: 0-无 1-稳定 2-减少") @TableField("count_trend") private Byte countTrend; @Schema(description = "规则类型: 1-加油频次 2-加油趋势") @TableField("rule_type") private Byte ruleType; @Schema(description = "历史月数") @TableField("history_months") private Integer historyMonths; @Schema(description = "近期月数") @TableField("recent_months") private Integer recentMonths; @Override public Serializable pkVal() { return this.id; jyz-base-start/src/main/java/com/tievd/jyz/entity/vo/DataStatisReqVo.java
@@ -28,6 +28,18 @@ @Schema(description = "结束时间") private String endTime; @Schema(description = "活动前开始时间") private String beforeStartTime; @Schema(description = "活动前结束时间") private String beforeEndTime; @Schema(description = "活动后开始时间") private String afterStartTime; @Schema(description = "活动后结束时间") private String afterEndTime; public void setOrgCodeIfnull(String orgCode) { if (this.orgCode == null || this.orgCode.equals("")){ @@ -46,6 +58,12 @@ @Schema(description = "HOURS, DAYS, MONTHS") private StatUnit timeUnit = StatUnit.HOURS; @Schema(description = "趋势图点位时间") private String statTime; @Schema(description = "趋势图系列名称") private String seriesName; private DataStatisReqVo setTimeUnit(String timeUnit){ this.timeUnit = StatUnit.valueOf(timeUnit); jyz-base-start/src/main/java/com/tievd/jyz/mapper/OilRecordMapper.java
@@ -100,6 +100,8 @@ List<Map> statisOrgTopOil(DataStatisReqVo param); List<Map> statisOrgTopVolume(DataStatisReqVo param); List<Map> statisOilFreqCompare(DataStatisReqVo param); List<StatDataTableVo> statFanByModel(DataStatisReqVo param);
@@ -40,6 +40,8 @@ List<Map> statisOrgTopOil(DataStatisReqVo param); List<Map> statisOrgTopVolume(DataStatisReqVo param); List<Map> statisOilFreqCompare(DataStatisReqVo param); JSONObject statFan(DataStatisReqVo param); @@ -48,4 +50,6 @@ JSONObject statTrend(DataStatisReqVo param); JSONObject statTrendClient(DataStatisReqVo param); List<Map<String, Object>> statTrendDetail(DataStatisReqVo param); } jyz-base-start/src/main/java/com/tievd/jyz/service/impl/OilRecordServiceImpl.java
@@ -154,6 +154,11 @@ public List<Map> statisOrgTopVolume(DataStatisReqVo param) { return oilRecordMapper.statisOrgTopVolume(param); } @Override public List<Map> statisOilFreqCompare(DataStatisReqVo param) { return oilRecordMapper.statisOilFreqCompare(param); } @Override public JSONObject statFan(DataStatisReqVo param) { @@ -363,6 +368,131 @@ res.put("lineData", lineDatas); return res; } @Override public List<Map<String, Object>> statTrendDetail(DataStatisReqVo param) { if (param.getTrendType() == null || param.getTimeUnit() == null || param.getStatTime() == null) { return new ArrayList<>(); } DataStatisReqVo.TrendType trendType = param.getTrendType(); DataStatisReqVo.StatUnit timeUnit = param.getTimeUnit(); LocalDateTime[] timeRange = getTrendTimeRange(timeUnit); LocalDateTime timeLimit = timeRange[0]; LocalDateTime timeLimitRight = timeRange[1]; DateTimeFormatter formatter = DateTimeFormatter.ofPattern(timeUnit.statFormat()); String statTime = param.getStatTime(); List<Map<String, Object>> detailList = new ArrayList<>(); if (trendType == DataStatisReqVo.TrendType.TRAFFIC) { LambdaQueryWrapper<TrafficFlow> wrapper = new LambdaQueryWrapper<TrafficFlow>() .ge(TrafficFlow::getCaptureTime, timeLimit.toString()) .le(TrafficFlow::getCaptureTime, timeLimitRight.toString()) .likeRight(TrafficFlow::getOrgCode, param.getOrgCode()) .orderByDesc(TrafficFlow::getCaptureTime); List<TrafficFlow> list = trafficFlowMapper.selectList(wrapper); for (TrafficFlow trafficFlow : list) { if (!matchStatTime(trafficFlow.getCaptureTime().toLocalDateTime(), formatter, statTime)) { continue; } Map<String, Object> row = new LinkedHashMap<>(); row.put("id", trafficFlow.getId()); row.put("captureTime", trafficFlow.getCaptureTime()); row.put("carCount", trafficFlow.getCarCount()); row.put("modelCode", trafficFlow.getModelCode()); row.put("cameraCode", trafficFlow.getCameraCode()); detailList.add(row); } return detailList; } if (trendType == DataStatisReqVo.TrendType.CLIENT || trendType == DataStatisReqVo.TrendType.LOSE_CLIENT) { LambdaQueryWrapper<OilStatis> wrapper = new LambdaQueryWrapper<OilStatis>() .ge(OilStatis::getUpdateTimeSelf, timeLimit.toString()) .le(OilStatis::getUpdateTimeSelf, timeLimitRight.toString()) .likeRight(OilStatis::getOrgCode, param.getOrgCode()) .orderByDesc(OilStatis::getUpdateTimeSelf); List<OilStatis> list = oilStaticMapper.selectList(wrapper); for (OilStatis oilStatis : list) { if (!matchStatTime(oilStatis.getUpdateTimeSelf().toLocalDateTime(), formatter, statTime)) { continue; } if (trendType == DataStatisReqVo.TrendType.CLIENT && param.getSeriesName() != null && !param.getSeriesName().equals(oilStatis.getClientName())) { continue; } if (trendType == DataStatisReqVo.TrendType.LOSE_CLIENT && (oilStatis.getClientId() == null || oilStatis.getClientId() != SystemConstant.LOSE_CLIENT_ID)) { continue; } Map<String, Object> row = new LinkedHashMap<>(); row.put("id", oilStatis.getId()); row.put("updateTimeSelf", oilStatis.getUpdateTimeSelf()); row.put("licenseNum", oilStatis.getLicenseNum()); row.put("clientName", oilStatis.getClientName()); row.put("oilCount", oilStatis.getOilCount()); row.put("oilSum", oilStatis.getOilSum()); detailList.add(row); } return detailList; } LambdaQueryWrapper<OilRecord> wrapper = new LambdaQueryWrapper<OilRecord>() .ge(OilRecord::getStartTime, timeLimit.toString()) .le(OilRecord::getStartTime, timeLimitRight.toString()) .likeRight(OilRecord::getOrgCode, param.getOrgCode()) .orderByDesc(OilRecord::getStartTime); List<OilRecord> records = this.list(wrapper); for (OilRecord oilRecord : records) { if (!matchStatTime(oilRecord.getStartTime().toLocalDateTime(), formatter, statTime)) { continue; } if (trendType == DataStatisReqVo.TrendType.OIL && (oilRecord.getBehavior() == null || oilRecord.getBehavior().intValue() != SystemConstant.BEHAVIOR_TYPE_OIL)) { continue; } if (trendType == DataStatisReqVo.TrendType.OIL_vOLUME && (oilRecord.getBehavior() == null || oilRecord.getBehavior().intValue() != SystemConstant.BEHAVIOR_TYPE_OIL)) { continue; } Map<String, Object> row = new LinkedHashMap<>(); row.put("id", oilRecord.getId()); row.put("startTime", oilRecord.getStartTime()); row.put("licenseNum", oilRecord.getLicenseNum()); row.put("behavior", oilRecord.getBehavior()); row.put("behaviorText", behaviorText(oilRecord.getBehavior())); row.put("oilPosition", oilRecord.getOilPosition()); row.put("oilVolume", oilRecord.getOilVolume()); row.put("spandTime", oilRecord.getSpandTime()); detailList.add(row); } return detailList; } private LocalDateTime[] getTrendTimeRange(DataStatisReqVo.StatUnit timeUnit) { LocalDateTime timeLimit = LocalDateTime.now() .minus(timeUnit.value(), ChronoUnit.valueOf(timeUnit.name())) .with(timeUnit.initField(), 1); LocalDateTime timeLimitRight = LocalDateTime.now().with(timeUnit.initField(), 1); return new LocalDateTime[]{timeLimit, timeLimitRight}; } private boolean matchStatTime(LocalDateTime time, DateTimeFormatter formatter, String statTime) { return formatter.format(time).equals(statTime); } private String behaviorText(Byte behavior) { if (behavior == null) { return ""; } if (behavior.intValue() == SystemConstant.BEHAVIOR_TYPE_OIL) { return "加油"; } if (behavior.intValue() == SystemConstant.BEHAVIOR_TYPE_TMP) { return "停靠"; } return behavior.toString(); } private <E> JSONObject dataTransLists( List<E> dataList, jyz-base-start/src/main/resources/xml/OilRecordMapper.xml
@@ -90,6 +90,22 @@ <if test="orgCode != null and orgCode != ''">and org_code like concat(#{orgCode}, '%')</if> </where> </sql> <sql id="statBeforeOilFreqCondition"> <where> and behavior = 1 <if test="beforeStartTime != null and beforeStartTime != ''">and start_time >= #{beforeStartTime}</if> <if test="beforeEndTime != null and beforeEndTime != ''">and start_time < #{beforeEndTime}</if> <if test="orgCode != null and orgCode != ''">and org_code like concat(#{orgCode}, '%')</if> </where> </sql> <sql id="statAfterOilFreqCondition"> <where> and behavior = 1 <if test="afterStartTime != null and afterStartTime != ''">and start_time >= #{afterStartTime}</if> <if test="afterEndTime != null and afterEndTime != ''">and start_time < #{afterEndTime}</if> <if test="orgCode != null and orgCode != ''">and org_code like concat(#{orgCode}, '%')</if> </where> </sql> <select id="statisTotal" parameterType="com.tievd.jyz.entity.vo.DataStatisReqVo" resultType="com.tievd.jyz.entity.vo.StatDataTableVo"> select *, @@ -183,6 +199,38 @@ GROUP BY org_code limit 10 </select> <select id="statisOilFreqCompare" resultType="java.util.Map" parameterType="com.tievd.jyz.entity.vo.DataStatisReqVo"> select l.licenseNum licenseNum, ifnull(b.beforeOilCount, 0) beforeOilCount, ifnull(a.afterOilCount, 0) afterOilCount, ifnull(a.afterOilCount, 0) - ifnull(b.beforeOilCount, 0) diffOilCount, case when ifnull(b.beforeOilCount, 0) = 0 then null else round((ifnull(a.afterOilCount, 0) - ifnull(b.beforeOilCount, 0)) * 100 / ifnull(b.beforeOilCount, 0), 2) end diffRate from ( select license_num licenseNum from t_oil_record <include refid="statBeforeOilFreqCondition"/> union select license_num licenseNum from t_oil_record <include refid="statAfterOilFreqCondition"/> ) l left join ( select license_num licenseNum, count(1) beforeOilCount from t_oil_record <include refid="statBeforeOilFreqCondition"/> group by license_num ) b on l.licenseNum = b.licenseNum left join ( select license_num licenseNum, count(1) afterOilCount from t_oil_record <include refid="statAfterOilFreqCondition"/> group by license_num ) a on l.licenseNum = a.licenseNum order by afterOilCount desc, beforeOilCount desc, licenseNum asc limit 200 </select> <select id="statFanByModel" resultType="com.tievd.jyz.entity.vo.StatDataTableVo" parameterType="com.tievd.jyz.entity.vo.DataStatisReqVo"> select count(if(behavior=1,1,null)) oilCount,
