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

我就是我 2021-09-21 09:10 677阅读 0赞

笔者最近做了统计,感觉自己的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 >= #{beginTime} AND cad.datelist <=  #{endTime} ) AS deT
    	LEFT JOIN ( SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) AS hours FROM t_order AS tod WHERE tod.create_time >= #{beginTime} AND tod.create_time <= #{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 >=  #{beginTime}
    					AND cad.datelist  <= #{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' ) >=  #{beginTime}
    					AND DATE_FORMAT( tod.create_time, '%Y-%m-%d-%H' )  <= #{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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70][]

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 1][]

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

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70]: /images/20210920/938b999e353f401384e3a9f45e65a64d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzM4MjU5NTM5_size_16_color_FFFFFF_t_70 1]: /images/20210920/3ecfeb8e29a54e94af4d5ac4d7c73bdc.png