mapper及sql

public interface SafetyReportMapper extends BaseMapper {
@Select(“select sum(mileage) as mileage ,start_statistic_time from vehicle_mileage_statistic where start_statistic_time>=#{startDate} and start_statistic_time<=#{endDate} GROUP BY start_statistic_time”)
List queryMileage(Date startDate, Date endDate);

//风险数数据 图3
@Select("SELECT a.totalnums riskTotals, CASE WHEN a.mileageums !=0 AND a.mileageums!=NULL THEN a.totalnums/a.mileageums *100  ELSE 0 END riskAvg, create_time startTime from (SELECT to_char(create_time,'YYYY-MM-DD') create_time,count(id) totalnums,sum(mileage) mileageums from vehicle_gps_info where alarm_warning_level>0 and  create_time>=to_timestamp(#{endDate},'yyyy-MM-dd HH24:MI:SS')and create_time<=to_timestamp(#{startDate},'yyyy-MM-dd HH24:MI:SS') group by to_char(create_time ,'YYYY-MM-DD') ORDER BY to_char(create_time ,'YYYY-MM-DD')) a")
List<SafetyReportInfo> queryRiskNums(@Param("startDate") Date startDate, @Param("endDate")Date endDate);

//百公里风险数数据对比 图4
@Select("SELECT b.alarm_warning_type warningType,count(id) weekNums FROM ( SELECT  a.alarm_warning_type, a.alarm_warning_level,( CASE WHEN alarm_warning_type = 'adas_alarm_1' THEN '前向碰撞报警' END ) qianxiang, ( CASE WHEN alarm_warning_type = 'adas_alarm_2' THEN '车道偏离报警' END ) chedao,( CASE WHEN alarm_warning_type = 'adas_alarm_3' THEN '车距过近报警' END ) cheju,( CASE WHEN alarm_warning_type = 'dsm_alarm_4' THEN '分神驾驶报警' END ) fenshen  FROM ( SELECT alarm_warning_type,alarm_warning_level FROM vehicle_gps_info WHERE alarm_warning_level > 0 AND create_time >=to_timestamp(#{endDate},'yyyy-MM-dd HH24:MI:SS') AND create_time <= to_timestamp(#{startDate},'yyyy-MM-dd HH24:MI:SS') and alarm_warning_type in('adas_alarm_1','adas_alarm_2','adas_alarm_3','dsm_alarm_4') GROUP BY alarm_warning_level, alarm_warning_type,create_time ORDER BY create_time ) A )b GROUP BY alarm_warning_type")
/*@Select("select alarm_warning_type warningType,count(id) weekNums from vehicle_gps_info\n" +
        "where create_time >=to_timestamp(#{endDate},'yyyy-MM-dd HH24:MI:SS') AND create_time <= to_timestamp(#{startDate},'yyyy-MM-dd HH24:MI:SS') \n" +
        "and alarm_warning_type is not null and alarm_warning_type<>''\n" +
        "and alarm_warning_type in('adas_alarm_1','adas_alarm_2','adas_alarm_3','dsm_alarm_4')\n" +
        "and alarm_warning_level>0\n" +
        "group by alarm_warning_type")*/
List<SafetyReportInfo> queryRiskNumsCompare(@Param("startDate") Date startDate, @Param("endDate")Date endDate);

//司机安全排名
@Select("SELECT c.driver_name driver_name , count(pilao) pilao, count (fenshen) fenshen , count(shouji) shouji, count (qianxiang) qianxiang , count(chedao) chedao, count(cheju) cheju \n" +
        "FROM(SELECT a.driver_name,\n" +
        "(CASE WHEN alarm_warning_type = 'dsm_alarm_2' THEN '疲劳驾驶' END ) pilao,\n" +
        "(CASE WHEN alarm_warning_type = 'dsm_alarm_4' THEN '分神驾驶报警' END ) fenshen,\n" +
        "(CASE WHEN alarm_warning_type = 'dsm_alarm_1' THEN '使用手机' END  ) shouji,\n" +
        "(CASE WHEN alarm_warning_type = 'adas_alarm_1' THEN '前向碰撞报警' END ) qianxiang,\n" +
        "( CASE WHEN alarm_warning_type = 'adas_alarm_2' THEN '车道偏离报警' END ) chedao,\n" +
        "(CASE WHEN alarm_warning_type = 'adas_alarm_3' THEN '车距过近报警' END ) cheju\n" +
        "FROM(SELECT alarm_warning_type,driver_name FROM vehicle_gps_info WHERE alarm_warning_level > 0 \n" +
        "AND create_time >=to_timestamp(#{endDate},'yyyy-MM-dd HH24:MI:SS') AND create_time <=to_timestamp(#{startDate},'yyyy-MM-dd HH24:MI:SS') and alarm_warning_type in('adas_alarm_1','adas_alarm_2','adas_alarm_3','dsm_alarm_4','dsm_alarm_2','dsm_alarm_1') GROUP BY driver_name, alarm_warning_type,create_time ORDER BY create_time ) A ) c GROUP BY driver_name")
List<SafetyReportInfo> queryDriverRank(@Param("startDate") Date startDate, @Param("endDate")Date endDate);

//车辆安全排名
@Select("SELECT c.plat_number plat_number , count(pilao) pilao, count (fenshen) fenshen , count(shouji) shouji, count (qianxiang) qianxiang , count(chedao) chedao, count(cheju) cheju \n" +
        "FROM(SELECT a.plat_number,\n" +
        "(CASE WHEN alarm_warning_type = 'dsm_alarm_2' THEN '疲劳驾驶' END ) pilao,\n" +
        "(CASE WHEN alarm_warning_type = 'dsm_alarm_4' THEN '分神驾驶报警' END ) fenshen,\n" +
        "(CASE WHEN alarm_warning_type = 'dsm_alarm_1' THEN '使用手机' END  ) shouji,\n" +
        "(CASE WHEN alarm_warning_type = 'adas_alarm_1' THEN '前向碰撞报警' END ) qianxiang,\n" +
        "( CASE WHEN alarm_warning_type = 'adas_alarm_2' THEN '车道偏离报警' END ) chedao,\n" +
        "(CASE WHEN alarm_warning_type = 'adas_alarm_3' THEN '车距过近报警' END ) cheju\n" +
        "FROM(SELECT alarm_warning_type,plat_number FROM vehicle_gps_info WHERE alarm_warning_level > 0 \n" +
        "AND create_time >=to_timestamp(#{endDate},'yyyy-MM-dd HH24:MI:SS') AND create_time <=to_timestamp(#{startDate},'yyyy-MM-dd HH24:MI:SS') and alarm_warning_type in('adas_alarm_1','adas_alarm_2','adas_alarm_3','dsm_alarm_4','dsm_alarm_2','dsm_alarm_1') GROUP BY plat_number, alarm_warning_type,create_time ORDER BY create_time ) A ) c GROUP BY plat_number")
List<SafetyReportInfo> queryCarSafetyRank(@Param("startDate") Date startDate, @Param("endDate")Date endDate);

List<SafetyReportInfo> queryCarNums(@Param("startDate") Date startDate, @Param("endDate")Date endDate);