mysql之订单时间统计---按月---横向地区时间(每月的天数)统计,纵向时间(每月1号到最后那天)机柜订单统计

淡淡的烟草味﹌ 2021-09-21 09:20 443阅读 0赞

上一篇说了按照周进行统计,这篇说一下按照月统计,横向地区时间(每月的天数)统计,也就是说这个地区下的所有机柜在当前月产生了多少次订单,历史最大订单是按照当前年份计算(当前年下,某一个月的订单最大值获取出来),最小订单同理.

纵向时间(每月1号到最后那天)机柜订单统计,也就是说某台机柜从1号到当月最后那日的使用量统计,历史最大使用,也是按照年份进行为基准,统计今年所有1号的某台机器的最大值,一直到当月的最后那日.最小值同理.

1按月---横向地区时间(每月的天数)统计---当前月

<select id="monthRegionsByDeviceIdListNow" resultType="java.lang.String" parameterType="list">
    	SELECT
    	count(1)
    	FROM t_order  tod
    	WHERE
    		DATE_FORMAT( tod.create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' )
    	AND  tod.USED_DEVICE_CODE IN
    	<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
    		#{id}
    	</foreach>
    </select>

2按月---横向地区时间(每月的天数)统计---(本年历史最大值)

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

注意:这里的开始时间和结束时间都是指本月1号到最后那日(也可以是从1号到当前日期),其它注意事项见前几篇

3按月---横向地区时间(每月的天数)统计---(本年历史最小值)

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

第二部分:纵向时间(每月1号到最后那天)机柜订单统计

1纵向时间(每月1号到最后那天)机柜订单统计---(当前月)

<select id="monthTimeByDeviceIdListNow" resultType="java.util.HashMap" parameterType="list">
    	SELECT
    		DATE_FORMAT( cad.datelist, '%d' ) AS by_month_day,
    		count(ot.create_time) AS count
    	FROM
    		calendar AS cad
    		LEFT JOIN (
    			SELECT  DATE_FORMAT( tod.create_time, '%Y-%m-%d' ) AS tod_month,  tod.create_time
    		FROM  t_order tod
    		WHERE
    			DATE_FORMAT( tod.create_time, '%Y-%m' ) = DATE_FORMAT( CURDATE( ), '%Y-%m' )
    		AND tod.USED_DEVICE_CODE IN
    		<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
    			#{id}
    		</foreach>
    		) AS ot ON cad.datelist = ot.tod_month
    	WHERE
    		DATE_FORMAT( cad.datelist, '%Y-%m' ) = DATE_FORMAT( CURDATE( ), '%Y-%m' )
    	GROUP BY cad.datelist
    </select>

2纵向时间(每月1号到最后那天)机柜订单统计---(本年历史最大值)

<select id="monthTimeByDeviceIdListMax" resultType="java.util.HashMap" parameterType="list">
    	SELECT
    	DATE_FORMAT( t.datelist, '%d' ) AS by_month_day,
    	MAX( count ) AS count
    	FROM
    	(
    	SELECT  datelist, DATE_FORMAT( cad.datelist, '%Y-%d' ) AS by_month_day, count( create_time ) AS count
    	FROM  calendar AS cad
    	LEFT JOIN (
    	SELECT  DATE_FORMAT( create_time, '%Y-%m-%d' ) AS tod_month,  create_time
    	FROM  t_order tod
    	WHERE   DATE_FORMAT( create_time, '%Y-%m-%d' )  >=  #{beginTime}
    	AND DATE_FORMAT( create_time, '%Y-%m' ) <= #{endTime}
    	AND tod.USED_DEVICE_CODE IN
    	<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
    		#{id}
    	</foreach>
    	) AS od ON od.tod_month = cad.datelist
    	WHERE   DATE_FORMAT( cad.datelist, '%Y-%m' )  >=  #{beginTime}
    	AND DATE_FORMAT( cad.datelist, '%Y-%m' ) <= #{endTime}
    	GROUP BY  datelist
    	) AS t
    	GROUP BY
    	t.by_month_day
    </select>

3纵向时间(每月1号到最后那天)机柜订单统计---(本年历史最小值)

<select id="monthTimeByDeviceIdListMin" resultType="java.util.HashMap" parameterType="list">
    	SELECT
    		DATE_FORMAT( t.datelist, '%d' ) AS by_month_day,
    		MIN( count ) AS count
    	FROM
    	(
    		SELECT  datelist, DATE_FORMAT( cad.datelist, '%Y-%d' ) AS by_month_day, count( create_time ) AS count
    		FROM  calendar AS cad
    		LEFT JOIN (
    			SELECT  DATE_FORMAT( create_time, '%Y-%m-%d' ) AS tod_month,  create_time
    			FROM  t_order tod
    			WHERE   DATE_FORMAT( create_time, '%Y-%m-%d' )  >=  #{beginTime}
    			AND DATE_FORMAT( create_time, '%Y-%m' ) <= #{endTime}
    			AND tod.USED_DEVICE_CODE IN
    			<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
    				#{id}
    			</foreach>
    		) AS od ON od.tod_month = cad.datelist
    		WHERE   DATE_FORMAT( cad.datelist, '%Y-%m' )  >=  #{beginTime}
    		AND DATE_FORMAT( cad.datelist, '%Y-%m' ) <= #{endTime}
    		GROUP BY  datelist
    	) AS t
    	GROUP BY
    	t.by_month_day
    </select>