上一篇说了按天统计,这次按照周统计,横向地区时间(7天)统计,就是说这个地区(地区下有多个机柜)每台机柜的周使用量(当前周,本月某周最大使用量,最小使用量),而纵向时间(周一到周日)机柜订单统计,就是说某台机柜从星期一到星期日的订单量(当前周,周一到周日,当月某个周一的最大使用量,某个周二的最大使用量....最小同最大原理).好了解释完毕开始笔记
1按周---横向地区时间(7天)统计----(当前周)
<select id="weekRegionsByDeviceIdListNow" resultType="java.lang.String" parameterType="list">SELECTcount(1)FROM t_order todWHEREYEARWEEK( 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">SELECTcount( tod.create_time ) AS countFROM( SELECT DATE_FORMAT( datelist, '%Y-%u' ) AS cad_weeks FROM calendar GROUP BY cad_weeks HAVING cad_weeks >= #{beginTime} AND cad_weeks <= #{endTime} ) AS cadLEFT JOIN( SELECT DATE_FORMAT( create_time, '%Y-%u' ) AS tod_weeks, create_time, USED_DEVICE_CODEFROMt_orderWHEREUSED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>HAVING tod_weeks >= #{beginTime} AND tod_weeks <= #{endTime}) AS todON cad.cad_weeks = tod.tod_weeksGROUP BYcad.cad_weeksORDER BYcount DESCLIMIT 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">SELECTcount( tod.create_time ) AS countFROM( SELECT DATE_FORMAT( datelist, '%Y-%u' ) AS cad_weeks FROM calendar GROUP BY cad_weeks HAVING cad_weeks >= #{beginTime} AND cad_weeks <= #{endTime} ) AS cadLEFT JOIN( SELECT DATE_FORMAT( create_time, '%Y-%u' ) AS tod_weeks, create_time, USED_DEVICE_CODEFROMt_orderWHEREUSED_DEVICE_CODE IN<foreach collection="list" index="index" item="id" open="(" separator="," close=")">#{id}</foreach>HAVING tod_weeks >= #{beginTime} AND tod_weeks <= #{endTime}) AS todON cad.cad_weeks = tod.tod_weeksGROUP BYcad.cad_weeksORDER BYcount ASCLIMIT 1
</select>
第二部分: 纵向时间(周一到周日)机柜订单统计
1按周---纵向时间(周一到周日)机柜订单统计----(当前周)
<select id="weekTimeByDeviceIdListNow" resultType="java.util.HashMap" parameterType="list">SELECTDATE_FORMAT( cad.datelist, '%Y-%m-%a' ) AS bywek,count(create_time) AS countFROMcalendar AS cadLEFT JOIN (SELECTDATE_FORMAT( create_time, '%Y-%m-%d' ) AS wek ,create_timeFROMt_order todWHEREYEARWEEK( 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.wekWHEREYEARWEEK( date_format( datelist, '%Y-%m-%d' ), 1 ) = YEARWEEK( now( ), 1 )GROUP BY cad.datelistORDER BY datelist
</select>
2按周---纵向时间(周一到周日)机柜订单统计----(当月历史最大)
<select id="weekTimeByDeviceIdListMax" resultType="java.util.HashMap" parameterType="list">SELECTal.bywek,MAX( al.count ) AS countFROM(SELECT DATE_FORMAT( datelist, '%Y-%m-%a' ) AS bywek, datelist AS cad_wek, count( td.create_time ) AS countFROM calendar AS cadLEFT JOIN (SELECTDATE_FORMAT( tod.create_time, '%Y-%m-%d' ) AS wek, create_timeFROM t_order todWHERE 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.wekWHERE date_format( cad.datelist, '%Y-%m-%d' ) >= #{beginTime}AND date_format( cad.datelist, '%Y-%m-%d' ) <= #{endTime}GROUP BY cad_wek) AS alGROUP BYal.bywek
</select>
3按周---纵向时间(周一到周日)机柜订单统计----(当月历史最小)
<select id="weekTimeByDeviceIdListMin" resultType="java.util.HashMap" parameterType="list">SELECTal.bywek,MIN( al.count ) AS countFROM(SELECT DATE_FORMAT( datelist, '%Y-%m-%a' ) AS bywek, datelist AS cad_wek, count( td.create_time ) AS countFROMcalendar AS cadLEFT JOIN (SELECTDATE_FORMAT( tod.create_time, '%Y-%m-%d' ) AS wek, create_timeFROM t_order todWHERE 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.wekWHERE date_format( cad.datelist, '%Y-%m-%d' ) >= #{beginTime}AND date_format( cad.datelist, '%Y-%m-%d' ) <= #{endTime}GROUP BY cad_wek) AS alGROUP BYal.bywek
</select>
后续跟进按月,按年统计