mysql之订单时间统计---按天---横向地区时间(一天)统计,纵向时间(按24小时)机柜统计

笔者最近做了统计,感觉自己的sql语句明显不够用.脑袋也转的慢了(哎,老了).总结如下

1按照点击这当前时间统计地区今天的下单数据(横向统计-这个地区所有柜子今天的订单数)

<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>

2按照点击当前时间统计地区这个月最大的下单数据(横向统计-这个地区当前这个月每天最大数据挑选出来),时间格式需要传过来

<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上面试历史最大数据(这个月哪天最大下单量),这个最小下单量将数量排序改一下即COUNT ASC

第二部分:纵向时间机柜统计

因为是纵向统计,一个机柜需要统计出24小时中每个小时的下单量.总结如下

1当前时间从凌晨到现在每个机柜的下单量

<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选取这个月,某台机柜的最小值(某台机柜在这个月中,从凌晨到23点每个小时最小值挑选出来)

<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>

注意:

1这里的calendar是自己新建的一个时间表,时间数据是从2016年到2289年,此表就一个字段datelist

2这里的num也是自己新建的一个数字表,数据只有0到23,为的是从0点到23点

3选取这个月,某台机柜的最大值(某台机柜在这个月中,从凌晨到23点每个小时最大值挑选出来)

这个只需要将上面的MIN(con) 改成 MAX(con)就可以了

剩下的部分(按周统计,按月统计,按年统计)继续关注后续部分