Make statistics according to the annual / monthly / daily data, and fill in 0 for the empty data

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
count(*) as count
FROM
xxx_log sel
WHERE
group by
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) {
}
} 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

Tags: Java MySQL

Posted on Wed, 20 Oct 2021 16:49:36 -0400 by Trafalger