Shulou(Shulou.com)06/01 Report--

This article mainly introduces the case of mysql statistics order revenue, the article is very detailed, has a certain reference value, interested friends must read it!

Demand

How to elegantly count order revenue (1) has been described in detail, which is probably some day / month / year income statistics.

The goal of thinking is to reduce the number of SQL queries to the front end to display API data conveniently. If the data on a certain day is null, the back end processes it into zero data and gives the front end method function as universal as possible to improve the code quality.

Based on the fact that the daily income statistics have been isomerized through canal:

One-day statistics (for example, today, yesterday, exact date) can be returned by locking a piece of data directly through the date. Monthly statistics can also filter out the data of the current month through time for aggregate statistics. The annual statistics are also realized by querying the statistics of the year through the date interval. Each income can also be aggregated separately.

In this way, the heterogeneity of daily statistics is valuable, at least to address all current needs. If you need today's / yesterday's / last month / month's earnings statistics, use SQL direct aggregation query, you need to query today, yesterday and the entire month of data sets and then through SUM aggregation.

CREATE TABLE `troomuserroomincomedaily` (`id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key', `affiliate_member_ id` int (11) NOT NULL COMMENT 'user id', `day_ time`date NOT NULL COMMENT' date', `self_purchase_ income` int (11) DEFAULT'0' COMMENT 'self-purchase revenue', `member_ income` int (11) DEFAULT'0' COMMENT 'primary distribution revenue', `affiliate_member_ income` int (11) DEFAULT'0' COMMENT 'secondary distribution revenue' `total_ income` int (11) DEFAULT'0' COMMENT 'share revenue', `effective_order_ num` int (11) DEFAULT'0' COMMENT 'valid orders', `total_ income` int'0' COMMENT 'total revenue', `update_ time`datetime DEFAULT NULL COMMENT 'update time', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT=' user revenue daily statistics'

In this way, if the API needs to return the revenue statistics of today / yesterday / last month / this month, it needs to query SQL 4 times before it can be implemented. It's okay to write it, but it's not the best solution? Can I use fewer SQL queries?

Observe

Through observation and analysis, today / yesterday / last month / this month statistics have a common overlap, they are all in the same time range (from the 1st of last month to the end of this month), then we can find out the data of these two months directly through SQL, and then we can easily get the data we want through program aggregation.

Optimization and realization

Supplement the design of daily income statistics

Select * from t_user_income_daily where day_time BETWEEN 'the first of last month' AND'at the end of this month 'and user_id=xxx

Find out the income of two months.

Select * from t_user_income

In order to reduce the amount of data in the table, the daily statistics of the day will not be created if there is no income change on that day, so we can only query the income statistics of users with income changes in a certain time interval. If you deal with the situation that the data is empty on a certain day, you need to do something special in the program. Here is a trick to generate a time assist table in the database. All kinds of formatted time data are stored in days. The detailed operation of the auxiliary query can be seen in the Mysql generation time auxiliary table of this blog post. With this table, you can further optimize the SQL. The format of the time assist table is as follows, or you can modify the stored procedure to add your own personalized time format.

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 '% YMel% mMel% d') BETWEEN # {startTime} AND # {endTime} ORDER BY a.DAY_ID DESC

The idea is very simple, use the time auxiliary table left to correlate the income daily statistical table that needs to be queried, and the associated field is the day_time time. If there is no income data for that day, there will also be data in SQL that date is that day but the statistical data is empty. Use casewhen to determine a null assignment to 0, and finally, through the reverse order of time, you can query a complete set of time interval statistics.

Final realization

Based on the data queried by SQL. Stream is used for aggregation in the program. Give some examples, starting with a simple one.

Commonly used static method encapsulation / * * @ description: first day of this month * @ author: chenyunxuan * / public static LocalDate getThisMonthFirstDay () {return LocalDate.of (LocalDate.now () .getYear (), LocalDate.now () .getMonthValue (), 1) } / * * @ description: last day of this month * @ author: chenyunxuan * / public static LocalDate getThisMonthLastDay () {return LocalDate.now () .with (TemporalAdjusters.lastDayOfMonth ()) } / * * @ description: first day of last month * @ author: chenyunxuan * / public static LocalDate getLastMonthFirstDay () {return LocalDate.of (LocalDate.now () .getYear (), LocalDate.now () .getMonthValue ()-1,1) } / * * @ description: the last day of last month * @ author: chenyunxuan * / public static LocalDate getLastMonthLastDay () {return getLastMonthFirstDay () .with (TemporalAdjusters.lastDayOfMonth ()) } / * * @ description: the first day of this year * @ author: chenyunxuan * / public static LocalDate getThisYearFirstDay () {return LocalDate.of (LocalDate.now (). GetYear (), 1,1) } / * * @ description: divider. Negative numbers * @ author are not supported: 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 () } list of monthly earnings (in reverse chronological order) public ResponseResult selectIncomeDetailThisMonth (int userId, Integer year, Integer month) {ResponseResult responseResult = ResponseResult.newSingleData (); String startTime; String endTime / / not the specified month if (null = = year & & null = = month) {/ / if the time is the current month, only today to the 1st of the current month will be displayed startTime = DateUtil.getThisMonthFirstDay () .toString (); endTime = LocalDate.now () .toString () } else {/ / if it is a specified year month, use LocalDate.of to build the date of the first and last day of the month to be queried LocalDate localDate = LocalDate.of (year, month, 1); startTime = localDate.toString (); endTime = localDate.with (TemporalAdjusters.lastDayOfMonth ()). ToString () } / / query uses general SQL to pass in user id and start and end time List userIncomeDailyList = selectIncomeByTimeInterval (userId, startTime, endTime) / the data given to the front end needs to change the score of the data inventory into a string If there are no related requirements, you can skip returning List userIncomeStatisticalList = userIncomeDailyList.stream () .map (item-> UserIncomeStatisticalVO.builder () .recipateMemberIncome (Tools.fenToYuan (item.getAffiliateMemberIncome () .memberIncome (Tools.fenToYuan (item.getMemberIncome () .returtiveOrderNum (item.getEffectiveOrderNum () .shareIncome (Tools.fenToYuan (item.getShareIncome () .totalIncome (Tools.fenToYuan (item.getTotalIncome () .dayTimeStr (item.getDayTimeStr ()) .selfPurchaseIncome (Tools.fenToYuan (item.getSelfPurchaseIncome () .totalIncome (Collectors.toList ()) ResponseResult.setData (userIncomeStatisticalList); return responseResult;} Today / yesterday / month / month earnings public Map getPersonalIncomeMap (int userId) {Map resultMap = new HashMap (4); LocalDate localDate = LocalDate.now (); / / take out the first day of the previous month and the last day of the month String startTime = DateUtil.getLastMonthFirstDay (). ToString (); String endTime = DateUtil.getThisMonthLastDay (). ToString () / / this query is the optimized SQL above. Enter the start and end time to get the daily revenue statistics of users in this time interval List userIncomeDailyList = selectIncomeByTimeInterval (userId, startTime, endTime); / / because what you need to take here is the total revenue, so the returnTotalIncomeSum method is encapsulated for the input condition to return the total revenue aggregation / / the second parameter is the filter condition, leaving only those that meet the criteria. (LocalDate's API is used here) 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 () n.getDayTime () > = Integer.parseInt (DateUtil.getLastMonthFirstDay (). ToString (). Replace ("-") "") & & n.getDayTime () userIncomeDailyVO.getShareIncome () + userIncomeDailyVO.getSelfPurchaseIncome () `} earnings data for this year (aggregated monthly presentation)

Let's first take a look at stream's aggregate grammatical candy:

List.stream () .collect (Collectors.groupingBy (grouping field, Collectors.collectingAndThen (Collectors.toList (), list-> {operation after grouping})

Flowchart: code example:

Public ResponseResult selectIncomeDetailThisYear (int userId) {ResponseResult responseResult = ResponseResult.newSingleData (); List incomeStatisticalList = new LinkedList (); / / the start time is the first day of this year String startTime = DateUtil.getThisYearFirstDay.toString (); / / the maximum time in the range is today's String endTime = LocalDate.now (). ToString (); / / General SQL List userIncomeDailyList = selectIncomeByTimeInterval (userId, startTime, endTime) / / the aggregation of stream is used to group by month. After the packet is received by LinkedHashMap, the month sequence of the packet is prevented from being out of order. After that, the monthly revenue collection stream is aggregated and assembled into the final entity Map resultMap = userIncomeDailyList.parallelStream (). Collectors.groupingBy (UserIncomeDailyVO::getMonthTime, LinkedHashMap::new, Collectors.collectingAndThen (Collectors.toList ()) Item-> UserIncomeStatisticalVO.builder (). OriginateMemberIncome (Tools.fenToYuan (item.stream (). MapToInt (UserIncomeDailyVO::getAffiliateMemberIncome). Sum ()) .memberIncome (Tools.fenToYuan (item.stream (). MapToInt (UserIncomeDailyVO::getMemberIncome). Sum ()). MapToInt (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 } the above are all the contents of the mysql order revenue statistics case, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.