mysql之订单时间统计---按年---横向地区时间(本年天数)统计,纵向时间(1月到12月)机柜订单统计

野性酷女 2021-09-21 09:22 445阅读 0赞

横向地区时间(本年天数)统计,也就是根据本地区从本年1号到现在查看的日期或者是本年最后一天统计机柜的订单量,历史最大值是按照从产生订单之日起到现在为止,某年最大值和最小值.

纵向时间(1月到12月)机柜订单统计,也就是某台机柜按照1月到12月每个月进行统计,历史最大值是指从产生订单开始,每个1月2月...的订单最大值和最小值.

1按年---横向地区时间(本年天数)统计----(本年)

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

2按年---横向地区时间(本年天数)统计----(历史最大值,年份.产生订单日到现在)

<select id="yearRegionsByDeviceIdListMax" resultType="java.lang.String" parameterType="list">
    	SELECT
    		count( tod.create_time ) AS count
    	FROM
    		t_order AS tod
    	WHERE
    		DATE_FORMAT( tod.create_time, '%Y' ) > 2018
    		AND USED_DEVICE_CODE IN
    		<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
    			#{id}
    		</foreach>
    	GROUP BY  DATE_FORMAT( tod.create_time, '%Y' )
    	ORDER BY  count DESC
    	LIMIT 1
    </select>

3按年---横向地区时间(本年天数)统计----(历史最小值,年份.产生订单日到现在)

<select id="yearRegionsByDeviceIdListMin" resultType="java.lang.String" parameterType="list">
    	SELECT
    	count( tod.create_time ) AS count
    	FROM
    	t_order AS tod
    	WHERE
    	DATE_FORMAT( tod.create_time, '%Y' ) > 2018
    	AND USED_DEVICE_CODE IN
    	<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
    		#{id}
    	</foreach>
    	GROUP BY  DATE_FORMAT( tod.create_time, '%Y' )
    	ORDER BY  count ASC
    	LIMIT 1
    </select>

第二部分 纵向时间(1月到12月)机柜订单统计

1纵向时间(1月到12月)机柜订单统计 ---(本年)

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

2纵向时间(1月到12月)机柜订单统计 ---(历史最大值,从有订单开始,每个1月2月....12月的最大值)

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

3纵向时间(1月到12月)机柜订单统计 ---(历史最大值,从有订单开始,每个1月2月....12月的最小值)

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

后期在做一个总结吧.目前日,周,月,年的统计已经差不多了.这些虽然有些不是那么完美的sql,但是也是笔者总结了1周的劳动成果,绞尽脑汁的总结,以后有类似的情况,就一这个为根本进行演变了.