Let me show you what the requirements are like:
Display the data of each node according to the time interval to form a curve.
My first method is to use mysql functions:
SELECT @a :=@a + 1 AS `index`, DATE_FORMAT(ADDDATE( #{startDate} ,INTERVAL @a DAY),'%Y-%m-%d') AS date FROM mysql.help_topic,(SELECT @a:=-1) temp WHERE @a < (YEAR( #{endDate} )-YEAR( #{startDate} ))*365 + (MONTH( #{endDate} )- MONTH( #{startDate} ))*30 + (DAY( #{endDate} )- DAY( #{startDate} ))
The first is to make statistics on a daily basis. First, use the help of mysql_ Topic table to generate list data with sequence number as key and time as value.
Perform the following:
Then connect our own data table (t1 is the data table alias generated above):
LEFT JOIN ( SELECT date_format(sel.login_time, '%Y-%m-%d') AS dateDay, count(*) as count FROM xxx_log sel WHERE sel.login_time > #{startDate} AND sel.login_time < #{endDate} group by date_format(sel.login_time, '%Y-%m-%d')) AS t2 ON t1.date = t2.dateDay
Finally, use select to merge the whole sql, and judge whether it is empty in the query field count. If it is empty, fill in 0, and finally sort according to the field.
SELECT c.date AS dateMonth, IFNULL(c.count,0) AS countMonth FROM ( SELECT * FROM ( {First paragraph sql} ) AS t1 LEFT JOIN ({The second paragraph sql}) AS t2 ON t1.date = t2.dateMonth) AS c ORDER BY DATEMONTH desc
Perform the following:
result:
Perfect ~ ~ from September 1, 20 to October 11, 21~~~~~
If it is done at monthly or annual intervals, modify the sql and let me write a reference.
Year:
SELECT @a :=@a + 1 AS `index`, DATE_FORMAT(ADDDATE( #{startTime} ,INTERVAL @a MONTH),'%Y-%m') AS date FROM mysql.help_topic,(SELECT @a:=-1) temp WHERE @a < (YEAR( #{endTime} )-YEAR( #{startTime} ))*12+ MONTH( #{endTime} )- MONTH( #{startTime} )
The second code changes the time format to yyyy mm, SQL:
result:
It's even simpler in:
Although this method seems to be completed by operating the database, it is also restrictive. For example, the first paragraph of sql is actually mysql.help for query_ Topic table. The data in this table is limited
Therefore, the sequence number generated by the first paragraph of sql is limited. If the number exceeds this number, 0 cannot be supplemented. The solution is to change a table and make a special serial number table for association, and then process it in the code. I use the second method here.
The second method is to slice the time by year / month / day:
/** * Date range - slice * @param startDate Start date * @param endDate End date * @param type Slice type 1-year 2-month 3-day 4-hour * @return Slice date */ public static List<String> sliceUpDateRange(String startDate, String endDate ,int type ) { List<String> rs = new ArrayList<>(); try { int dt = Calendar.DATE; String pattern = "yyyy-MM-dd"; if (type==1) { pattern = "yyyy"; dt = Calendar.YEAR; } else if (type==2) { pattern = "yyyy-MM"; dt = Calendar.MONTH; } else if (type==3) { pattern = "yyyy-MM-dd"; dt = Calendar.DATE; }else if (type==4) { pattern = "yyyy-MM-dd HH"; dt = Calendar.HOUR_OF_DAY; } SimpleDateFormat sd = new SimpleDateFormat(pattern); Calendar sc = Calendar.getInstance(); Calendar ec = Calendar.getInstance(); sc.setTime(sd.parse(startDate)); ec.setTime(sd.parse(endDate)); while (sc.compareTo(ec) < 1) { rs.add(sd.format(sc.getTime())); sc.add(dt, 1); } } catch (ParseException e) { e.printStackTrace(); } return rs; }
Use the above method to put the cut date into the sql union. Hey hey, look at the code (take every day as an example):
select od.date,od.count from (SELECT DATE_FORMAT(SIGN_TIME, '%Y-%m-%d') as date,COUNT(*) as count FROM xxxx_log <where> STATE = #{state} AND SIGN_TIME <![CDATA[ > ]]> #{param.createStartTime, jdbcType = VARCHAR} AND SIGN_TIME <![CDATA[ < ]]> #{param.createEndTime, jdbcType = VARCHAR} </where> group by DATE_FORMAT(SIGN_TIME, '%Y-%m-%d') union <foreach collection="list" item="item" index="index" separator="union"> (select #{item},0) </foreach> )as od GROUP BY od.date ORDER BY od.date asc
The problem is that sql will be very long by the union. The last method is to supplement data in the code, but it can also be implemented by loop traversal (regardless of performance)
The second advantage of time slicing is that union can achieve year / month / day / hour compatibility, and the code is relatively neat