The author has done statistics recently, and I feel that my sql statement is obviously not enough. My head is also slow (hey, old). The summary is as follows

1According to the click on the current time to count the order data of the region today (horizontal statistics - the number of orders for all cabinets in this region today)

<select id="dayRegionsByDeviceIdListNow" resultType="java.lang.String" parameterType="list">
		SELECT
			count(1)
		FROM t_order  tod
		WHERE
		DATEDIFF( tod.create_time, NOW( ) ) = 0
		AND  tod.USED_DEVICE_CODE IN
		<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
	</select>

2According to the click on the current time to count the region's largest order data this month (horizontal statistics - this region's current month's largest daily data is selected), the time format needs to be passed over

<select id="dayRegionsByDeviceIdListMax" resultType="java.lang.String" parameterType="list">
	SELECT
		count( od.hours ) AS count
	FROM
	( SELECT datelist FROM calendar cad WHERE cad.datelist &gt;= #{beginTime} AND cad.datelist &lt;=  #{endTime} ) AS deT
	LEFT JOIN ( SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) AS hours FROM t_order AS tod WHERE tod.create_time &gt;= #{beginTime} AND tod.create_time &lt;= #{endTime} AND  tod.USED_DEVICE_CODE IN
		<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
		) AS od
	ON deT.datelist = od.hours
	GROUP BY
	deT.datelist
	ORDER BY
	count DESC
	LIMIT 1
</select>

3. The largest data in the interview history (which day of the month is the largest order quantity), this minimum order quantity will be sorted and changed to COUNT ASC

Part II: Longitudinal Time Cabinet Statistics

Because it is vertical statistics, a cabinet needs to count the number of orders placed for each hour in 24 hours. The summary is as follows

1 The order quantity of each cabinet from the early morning to the present time

<select id="dayTimeByDeviceIdListNow" resultType="java.util.HashMap" parameterType="list">
	SELECT
		nu.t_hour,
		count( tod.hours ) count
	FROM
		num AS nu
	LEFT JOIN (
		SELECT  DATE_FORMAT( create_time, '%H' ) hours
		FROM  t_order
		WHERE  DATEDIFF( create_time, NOW( ) ) = 0
		AND USED_DEVICE_CODE IN
		<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
			#{id}
		</foreach>
		) AS tod
	ON nu.t_hour = tod.hours
	GROUP BY  nu.t_hour
</select>

2 Select this month, the minimum value of a certain cabinet (a certain cabinet is selected from the minimum value of each hour from the early morning to 23:00 in this month)

<select id="dayTimeByDeviceIdListMin" resultType="java.util.HashMap" parameterType="list">
	SELECT
		al.t_hour,
		MIN( con ) AS count
	FROM
	(
			SELECT *, count( odt.create_time ) AS con
			FROM
			(
				SELECT cad.datelist, nu.t_hour, CONCAT( cad.datelist, '-', nu.t_hour ) AS c
				FROM calendar cad
				LEFT JOIN num AS nu ON 1 = 1
				WHERE
					cad.datelist &gt;=  #{beginTime}
					AND cad.datelist  &lt;= #{endTime}
			) AS dat
			LEFT JOIN
			(
				SELECT  DATE_FORMAT( create_time, '%Y-%m-%d-%H' ) AS hours, tod.create_time
				FROM  t_order AS tod
				WHERE
					DATE_FORMAT( tod.create_time, '%Y-%m-%d-%H' ) &gt;=  #{beginTime}
					AND DATE_FORMAT( tod.create_time, '%Y-%m-%d-%H' )  &lt;= #{endTime}
					AND tod.USED_DEVICE_CODE IN
					<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
						#{id}
					</foreach>
			) AS odt ON dat.c = odt.hours
			GROUP BY
			dat.c
	) AS al
	GROUP BY  al.t_hour
</select>

Notice:

1 The calendar here is a newly created timetable, the time data is from 2016 to 2289, this table has a field datelist

2 The num here is also a new digital table, the data is only 0 to 23, in order to go from 0 to 23

3 Select the maximum value of a certain cabinet in this month (a certain cabinet is selected from the maximum value of each hour from the early morning to 23:00 in this month)

This only needs to change the above MIN(con) to MAX(con)

The remaining parts (by week, by month, by year) continue to focus on subsequent parts