需求：可以分别按日，周，月进行统计交易总额。后台接收参数为type,string。其中type为查询类型，string接收的为“yyyy-mm-dd”格式的字符串。按周统计的话是计算一周的交易总额，按月的话是计算一个月的交易总额；按周或者按月都是通过传过来的字符串去解析周数或月份。默认查询：日前推30天，周前推12周，月前推12个月。
注意：1，周计数策略统一为iso标准策略计算；每周一为一周的第一天，第一个自然周最少要4天；
2，数据的补全策略，通过事先运算出该时间范围内的所有应展示的统计数据与实际mysql中查询出来的数据进行对比，如果不存在响应map中则添加一条值为0的记录。
参考代码：
1 public OpenAPiJsonResult shopTradeTrend(AppSession session, GetTradeTrendRequest request) throws Exception {
2 WeekFields weekFields = WeekFields.of(DayOfWeek.MONDAY, 4);
3 // 查询条件
4 String queryData = request.getQueryDate();
5 Integer queryType = request.getType();
6
7 List<String> responseDate = Lists.newArrayList();
8 if (Objects.isNull(queryType) && StringUtils.isBlank(queryData)) {
9 // 默认按日，展示当前日期前推30天的数据
10 request.setType(0);
11 request.setStartDate(LocalDate.now().minusDays(29).toString());
12 request.setEndDate(LocalDate.now().plusDays(1).toString());
13 responseDate = DateUtil.findDates(LocalDate.now().minusDays(30).toString(), LocalDate.now().toString());
14 } else if (Objects.nonNull(queryType)) {
15 if (queryType == 0) {
16 // 按日，默认查30天
17 if (StringUtils.isBlank(queryData)) {
18 request.setStartDate(LocalDate.now().minusDays(29).toString());
19 request.setEndDate(LocalDate.now().plusDays(1).toString());
20 responseDate = DateUtil.findDates(LocalDate.now().minusDays(30).toString(), LocalDate.now().toString());
21 } else {
22 LocalDate queryDate = DateUtil.stringToLocalDate(queryData);
23 request.setStartDate(queryDate.minusDays(29).toString());
24 request.setEndDate(queryDate.plusDays(1).toString());
25 responseDate = DateUtil.findDates(queryDate.minusDays(30).toString(), queryDate.toString());
26 }
27 } else if (queryType == 1) {
28 // 按周，默认查当前周前推12周的数据
29 if (StringUtils.isBlank(queryData)) {
30 int nowWeekNo = LocalDate.now().get(weekFields.weekOfYear());
31 String weekStartDate = DateUtil.getWeekDays(LocalDate.now().getYear(), nowWeekNo, -11);
32 request.setStartDate(weekStartDate);
33 request.setEndDate(LocalDate.now().plusDays(1).toString());
34 responseDate = new ArrayList<>(Objects.requireNonNull(DateUtil.getWeekBetween(weekStartDate, LocalDate.now().toString())));
35 } else {
36 LocalDate date = DateUtil.stringToLocalDate(queryData);
37 int nowWeekNo = date.get(weekFields.weekOfWeekBasedYear());
38 request.setStartDate(DateUtil.getWeekDays(date.get(weekFields.weekBasedYear()), nowWeekNo, -11));
39 request.setEndDate(DateUtil.getDateByYearAndWeekNo(date.get(weekFields.weekBasedYear()), nowWeekNo).plusDays(1).toString());
40 responseDate = new ArrayList<>(Objects.requireNonNull(DateUtil.getWeekBetween(DateUtil.getWeekDays(date.get(weekFields.weekBasedYear()), nowWeekNo, -11), DateUtil.getDateByYearAndWeekNo(date.get(weekFields.weekBasedYear()), nowWeekNo).toString())));
41 }
42 } else {
43 // 按月，默认查12个月
44 if (StringUtils.isBlank(queryData)) {
45 String startMonth = DateUtil.dateMinusMonth(LocalDate.now().getYear() + "" + LocalDate.now().getMonthValue(), -11);
46 request.setStartDate(startMonth.substring(0, 4) + "-" + startMonth.substring(4, 6) + "-01");
47 request.setEndDate(LocalDate.now().plusDays(1).toString());
48
49 responseDate = DateUtil.getMonthBetween(startMonth.substring(0, 4) + "-" + startMonth.substring(4, 6), LocalDate.now().getYear() + "-" + LocalDate.now().getMonthValue());
50 } else {
51 LocalDate date = DateUtil.stringToLocalDate(queryData);
52 String startMonth = DateUtil.dateMinusMonth(date.getYear() + "" + date.getMonthValue(), -11);
53 request.setStartDate(startMonth.substring(0, 4) + "-" + startMonth.substring(4, 6) + "-01");
54 String endDate = DateUtil.getLastDay(date.getYear() + "-" + date.getMonthValue() + "-01");
55 request.setEndDate(DateUtil.stringToLocalDate(endDate).plusDays(1).toString());
56 responseDate = DateUtil.getMonthBetween(startMonth.substring(0, 4) + "-" + startMonth.substring(4, 6), date.getYear() + "-" + date.getMonthValue());
57 }
58 }
59 }
60 List<TradeTrendResponse> responseList = orderService.selectCountTradeTrend(session.getOrgId(), session.getStoreId(), request.getType(), request.getStartDate(), request.getEndDate());
61
62 // 因为mysql month,week,day函数返回的数据在1-9内的都不带0，需要拼装成规范月，周，日数据
63 List<String> convertData = Stream.of("1", "2", "3", "4", "5", "6", "7", "8", "9").collect(toList());
64 if (Objects.isNull(request.getType()) || request.getType() == 0) {
65 List<String> hasData = Lists.newArrayList();
66 TreeMap<LocalDate, Double> responseMap = Maps.newTreeMap();
67 // 按日
68 responseList.forEach(x -> {
69 if (convertData.contains(x.getMonthNo())) {
70 x.setMonthNo("0" + x.getMonthNo());
71 }
72 if (convertData.contains(x.getDayNo())) {
73 x.setDayNo("0" + x.getDayNo());
74 }
75 String str = x.getYearNo() + "-" + x.getMonthNo() + "-" + x.getDayNo();
76 responseMap.put(DateUtil.stringToLocalDate(str), x.getAmount());
77 hasData.add(str);
78 });
79 responseDate.removeAll(hasData);
80 responseDate.forEach(noData -> {
81 responseMap.put(DateUtil.stringToLocalDate(noData), 0.00);
82 });
83 return OpenAPiJsonResult.success(responseMap);
84 } else if (request.getType() == 1) {
85 // 按周
86 List<String> hasData = Lists.newArrayList();
87 TreeMap<String, Double> map = Maps.newTreeMap();
88 responseList.forEach(x -> {
89 map.put(x.getWeekNo().substring(0, 4) + "第" + x.getWeekNo().substring(4, 6) + "周", x.getAmount());
90 hasData.add(x.getWeekNo());
91 });
92 responseDate.removeAll(hasData);
93 responseDate.forEach(noData -> {
94 map.put(noData.substring(0, 4) + "第" + noData.substring(4, 6) + "周", 0.00);
95 });
96 if (map.size() > 12) {
97 map.remove(map.firstKey());
98 }
99 return OpenAPiJsonResult.success(map);
100 } else {
101 // 按月
102 List<String> hasData = Lists.newArrayList();
103 TreeMap<String, Double> map = Maps.newTreeMap();
104 responseList.forEach(x -> {
105 if (convertData.contains(x.getMonthNo())) {
106 x.setMonthNo("0" + x.getMonthNo());
107 }
108 String str = x.getYearNo() + "-" + x.getMonthNo();
109 map.put(str + "月", x.getAmount());
110 hasData.add(str);
111 });
112 responseDate.removeAll(hasData);
113 responseDate.forEach(noData -> {
114 map.put(noData + "月", 0.00);
115 });
116 return OpenAPiJsonResult.success(map);
117 }
118 }业务层参考代码
1 /**
2 * 根据当前周号，计算n周前或后的周一日期
3 *
4 * @param year
5 * @param week
6 * @param targetNum
7 * @return
8 */
9 public static String getWeekDays(int year, int week, int targetNum) {
10 // 计算目标周数
11 if (week + targetNum > 52) {
12 year++;
13 } else if (week + targetNum <= 0) {
14 year--;
15 week += targetNum + 52;
16 } else {
17 week += targetNum;
18 }
19
20 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
21 Calendar cal = Calendar.getInstance();
22
23 // 设置每周的开始日期
24 cal.setFirstDayOfWeek(Calendar.MONDAY);
25
26 cal.set(Calendar.YEAR, year);
27 cal.set(Calendar.WEEK_OF_YEAR, week);
28 cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek());
29
30 // 该周周一
31 String beginDate = sdf.format(cal.getTime());
32
33 cal.add(Calendar.DAY_OF_WEEK, 6);
34 // 该周周日
35 String endDate = sdf.format(cal.getTime());
36
37 return beginDate;
38 }
39
40 /**
41 * 根据年，周号，获取该周周日
42 *
43 * @return
44 */
45 public static LocalDate getDateByYearAndWeekNo(int year, int week) {
46 WeekFields weekFields2 = WeekFields.of(DayOfWeek.MONDAY, 4);
47 LocalDate sunday = LocalDate.now()
48 .withYear(year)
49 .with(weekFields2.weekOfYear(), week)
50 .with(weekFields2.dayOfWeek(), 7L);
51
52 return sunday;
53 }
54
55 /**
56 * 日期加、减几月
57 *
58 * @param str
59 * @param amount 正数加，负数减
60 * @return
61 * @throws Exception
62 */
63 public static String dateMinusMonth(String str, int amount) throws Exception {
64 SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
65 Date dt = sdf.parse(str);//将字符串生成Date
66 Calendar rightNow = Calendar.getInstance();
67 rightNow.setTime(dt);//使用给定的 Date 设置此 Calendar 的时间。
68 rightNow.add(Calendar.MONTH, amount);// 日期减1个月
69 Date dt1 = rightNow.getTime();//返回一个表示此 Calendar 时间值的 Date 对象。
70 String reStr = sdf.format(dt1);//将给定的 Date 格式化为日期/时间字符串，并将结果添加到给定的 StringBuffer。
71 return reStr;
72 }
73
74 /**
75 * 获取某个月最后一天
76 *
77 * @param datadate
78 * @return
79 * @throws Exception
80 */
81 public static String getLastDay(String datadate) throws Exception {
82 Date date = null;
83 String day_last = null;
84 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
85 date = format.parse(datadate);
86
87 //创建日历
88 Calendar calendar = Calendar.getInstance();
89 calendar.setTime(date);
90 calendar.add(Calendar.MONTH, 1); //加一个月
91 calendar.set(Calendar.DATE, 1);//设置为该月第一天
92 calendar.add(Calendar.DATE, -1);//再减一天即为上个月最后一天
93 day_last = format.format(calendar.getTime());
94 return day_last;
95 }
96
97 /**
98 * 获取时间段内，所有天
99 *
100 * @param dBegin
101 * @param dEnd
102 * @return
103 * @throws ParseException
104 */
105 public static List<String> findDates(String dBegin, String dEnd) throws ParseException {
106 //日期工具类准备
107 DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
108 //设置开始时间
109 Calendar calBegin = Calendar.getInstance();
110 calBegin.setTime(format.parse(dBegin));
111 //设置结束时间
112 Calendar calEnd = Calendar.getInstance();
113 calEnd.setTime(format.parse(dEnd));
114 //装返回的日期集合容器
115 List<String> datelist = new ArrayList<String>();
116 // 每次循环给calBegin日期加一天，直到calBegin.getTime()时间等于dEnd
117 while (format.parse(dEnd).after(calBegin.getTime())) {
118 // 根据日历的规则，为给定的日历字段添加或减去指定的时间量
119 calBegin.add(Calendar.DAY_OF_MONTH, 1);
120 datelist.add(format.format(calBegin.getTime()));
121 }
122 return datelist;
123 }
124
125 /**
126 * 获取一段时间内，所有月份
127 *
128 * @param minDate
129 * @param maxDate
130 * @return
131 * @throws ParseException
132 */
133 public static List<String> getMonthBetween(String minDate, String maxDate) throws ParseException {
134 ArrayList<String> result = new ArrayList<String>();
135 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");//格式化为年月
136
137 Calendar min = Calendar.getInstance();
138 Calendar max = Calendar.getInstance();
139
140 min.setTime(sdf.parse(minDate));
141 min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);
142
143 max.setTime(sdf.parse(maxDate));
144 max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);
145
146 Calendar curr = min;
147 while (curr.before(max)) {
148 result.add(sdf.format(curr.getTime()));
149 curr.add(Calendar.MONTH, 1);
150 }
151
152 return result;
153 }
154
155 /**
156 * 获取时间段内所有的周，注意：需要指定周计数策略
157 *
158 * @param startStr
159 * @param endStr
160 * @return
161 */
162 @SuppressWarnings("all")
163 public static Set<String> getWeekBetween(String startStr, String endStr) {
164 WeekFields weekFields = WeekFields.of(DayOfWeek.MONDAY, 4);
165 LocalDate start = DateUtil.stringToLocalDate(startStr);
166 LocalDate end = DateUtil.stringToLocalDate(endStr);
167 int startY = start.get(weekFields.weekBasedYear());
168 int startW = start.get(weekFields.weekOfWeekBasedYear());
169 int endY = end.get(weekFields.weekBasedYear());
170 int endW = end.get(weekFields.weekOfWeekBasedYear());
171 List<Integer> convertData = Stream.of(1, 2, 3, 4, 5, 6, 7, 8, 9).collect(toList());
172 Set<String> response = Sets.newTreeSet();
173 if (endY - startY == 0) {// 同一年
174 for (int i = startW; i <= endW; i++) {
175 if (convertData.contains(i)) {
176 response.add(endY + "0" + i);
177 } else {
178 response.add(endY + "" + i);
179 }
180 }
181 return response;
182 } else if (endY - startY == 1) { // 这里因业务只统计跨度为12周的数据，所以只考虑年差距在一年的情况
183 String lastDay = startY + "-12-31"; // 获取起始年最大周数
184 int maxWeek = DateUtil.stringToLocalDate(lastDay).get(weekFields.weekOfYear());
185 for (int i = startW; i <= maxWeek; i++) {
186 if (convertData.contains(i)) {
187 response.add(startY + "0" + i);
188 } else {
189 response.add(startY + "" + i);
190 }
191 }
192 for (int j = 1; j <= endW; j++) {
193 if (convertData.contains(j)) {
194 response.add(endY + "0" + j);
195 } else {
196 response.add(endY + "" + j);
197 }
198 }
199 return response;
200 } else {// 暂不考虑
201 return null;
202 }
203 }工具类参考代码
1 <select id="selectCountTradeTrend" resultMap="CountPayTrend">
2 select
3 <if test="queryType == 0">
4 MONTH(pay_time) as month_no,
5 DAY(pay_time) as day_no,
6 </if>
7 <if test="queryType == 1">
8 yearweek(pay_time,3) as week_no,
9 </if>
10 <if test="queryType == 2">
11 MONTH(pay_time) as month_no,
12 </if>
13 YEAR(pay_time) as year_no,
14 SUM(practical_deal_price) as amount
15 from
16 <include refid="tableName"/>
17 <where>
18 org_id = #{orgId}
19 and store_id = #{storeId}
20 and is_del = 0
21 <if test="startDate != null and startDate != ''">
22 and pay_time >= #{startDate}
23 </if>
24 <if test="endDate != null and endDate != ''">
25 and pay_time <= #{endDate}
26 </if>
27 </where>
28 GROUP BY
29 <if test="queryType == 0">
30 DAY(pay_time)
31 </if>
32 <if test="queryType == 1">
33 yearweek(pay_time,3)
34 </if>
35 <if test="queryType == 2">
36 MONTH(pay_time)
37 </if>
38 </select>持久层参考代码
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
