mysql之订单时间统计---按周---横向地区时间(7天)统计,纵向时间(周一到周日)机柜订单统计

太过爱你忘了你带给我的痛 2021-09-21 09:18 522阅读 0赞

上一篇说了按天统计,这次按照周统计,横向地区时间(7天)统计,就是说这个地区(地区下有多个机柜)每台机柜的周使用量(当前周,本月某周最大使用量,最小使用量),而纵向时间(周一到周日)机柜订单统计,就是说某台机柜从星期一到星期日的订单量(当前周,周一到周日,当月某个周一的最大使用量,某个周二的最大使用量....最小同最大原理).好了解释完毕开始笔记

1按周---横向地区时间(7天)统计----(当前周)

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

注意:YEARWEEK函数是指当前周在今年属于第几周,下面历史统计的时候需要传过来,注意计算方式,有的是按照当前月为基点计算的,这里是按照年来计算的

2按周---横向地区时间(7天)统计----(当月历史最大)

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

注意:beginTime 和 endTime 是按照当前年份计算的周,查询历史最大周的统计,开始时间的周可以按照某月1号是第几周和最后一天是第几周传过来即可.也可以是两个月多个月.笔者使用的是当前月,java代码计算月份分享一下如下

//获取当前日期是今年的第几周  今年开始周 格式 2020-01 第一周...2020-26 第26周日期是从
    if(datetYpe == 7 && startAndEnd == 1){
        //设置这个月1号
        c.add(Calendar.MONTH, 0);
        c.set(Calendar.DAY_OF_MONTH,1);//设置为1号,当前日期既为本月第一天
        //然后获取周
        int week = c.get(c.WEEK_OF_YEAR);
        int year = c.get(c.YEAR);
        String day = year+"-"+week;
        return  day;
    }
    //截止今天所在的当前年份的周
    if(datetYpe == 7 && startAndEnd == 2){
        int week = c.get(c.WEEK_OF_YEAR);
        int year = c.get(c.YEAR);
        String day = year+"-"+week;
        return  day;
    }

3按周---横向地区时间(7天)统计----(当月历史最小)

原理同上面最大的原理只是将上面的count DESC 改为 asc

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

第二部分: 纵向时间(周一到周日)机柜订单统计

1按周---纵向时间(周一到周日)机柜订单统计----(当前周)

<select id="weekTimeByDeviceIdListNow" resultType="java.util.HashMap" parameterType="list">
    	SELECT
    		DATE_FORMAT( cad.datelist, '%Y-%m-%a' ) AS bywek,
    		count(create_time) AS count
    	FROM
    		calendar AS cad
    	LEFT JOIN (
    		SELECT
    			DATE_FORMAT( create_time, '%Y-%m-%d' ) AS wek ,
    			create_time
    		FROM
    			t_order tod
    		WHERE
    			YEARWEEK( DATE_FORMAT( create_time, '%Y-%m-%d' ), 1 ) = YEARWEEK( now( ), 1 )
    			AND tod.USED_DEVICE_CODE IN
    			<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
    				#{id}
    			</foreach>
    	) td  ON cad.datelist = td.wek
    	WHERE
    		YEARWEEK( date_format( datelist, '%Y-%m-%d' ), 1 ) = YEARWEEK( now( ), 1 )
    	GROUP BY cad.datelist
    	ORDER BY datelist
    </select>

2按周---纵向时间(周一到周日)机柜订单统计----(当月历史最大)

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

3按周---纵向时间(周一到周日)机柜订单统计----(当月历史最小)

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

后续跟进按月,按年统计