【Mysql】设备首页统计展示

来源：互联网发布：淘宝卖家寄快递怎么做编辑：程序博客网时间：2024/06/09 17:39

最近分组，老大对dm平台组做了新的规划，对于平台的首页，想要展示设备的一系列统计。

总共包括6个部分，实现类似于下图中的首页，老大从网上的找的图，这是初始需求。6个部分包括：
1、总设备数
2、设备已连接数（缓存）
3、按类型分类
4、按版本分类（版本）
5、一周内，活跃设备数统计
6、一周内，异常设备数统计

首页需求

1. 总设备数

单表查询，Mytabis自动生成表mapper的时候，xml里会有统计count的方法。

2. 设备已连接数

设备已连接数是从ppcp缓存中拿出来的数据，调用getConnectMapCache（）方法，可得到Map，取其size，即为已连接数量。由于用的是平台封装项目，暂不详述。

3. 按类型分类

Mysql：

多表查询，sql语句如下：

select t.type device_type ,COUNT(d.model_id) count        from t_device_model t , t_device d        where 1=1        and t.id = d.model_id        group by t.type

查询结果如下：
按类型分类设备查询结果

Dao层：

定义了VO类，接收查询结果的一条记录：

public class ReportVO implements Serializable{    private String deviceType;//设备类型    private Integer count;//统计的数量    public Integer getCount() {        return count;    }    public void setCount(Integer count) {        this.count = count;    }    public String getDeviceType() {        return deviceType;    }    public void setDeviceType(String deviceType) {        this.deviceType = deviceType;    }}

返回List列表，List内是实体类VO，接口方法如下：

public interface ReportDeviceClassMapper {    List<ReportVO> reportDeviceByType();}

Mapper.xml如下：

<mapper namespace="com.payment.dm.model.mapper.ext.ReportDeviceClassMapper">    <resultMap id="BaseResultMap" type="com.payment.dm.model.vo.ReportVO">        <result column="device_type" jdbcType="VARCHAR" property="deviceType"/>        <result column="count" jdbcType="INTEGER" property="count"/>    </resultMap>    <select id="reportDeviceByType" resultMap="BaseResultMap">        select t.type device_type ,COUNT(d.model_id) count        from t_device_model t , t_device d        where 1=1        and t.id = d.model_id        group by t.type    </select></mapper>

对应的service

public List<ReportVO> reportDeviceByType() {        List<ReportVO> reportVOs = reportDeviceClassMapper.reportDeviceByType();        logger.info("[ReportDeviceService] [reportDeviceByType] query device by type, response = {}", reportVOs);        return reportVOs;    }

Service层：
调用dao层的service方法，返回Response类型的结果，方便前台解析

  @Override    public Response<List<ReportVO>> reportDeviceByType() {        List<ReportVO> reportVOs = reportDeviceService.reportDeviceByType();        logger.info("[ReportServiceImpl] [reportDeviceByType] query device by type, reportVOs = {}", reportVOs);        return Response.OK(reportVOs);    }

Controller层：
Controller：

@RequestMapping("/device/report")public class ReportController extends BaseController{@Autowired    private DeviceReportComponent deviceReportComponent;   /**     * query device report by type     *     * @return     */    @RequestMapping(value = "/type", method = RequestMethod.GET)    public String reportDeviceByType() {        logger.info("[ReportController] [reportDeviceByType] report divice by type!");        return deviceReportComponent.reportDeviceByType();        }    }

component：

   public String reportDeviceByType() {        logger.info("[QueryController] [reportDeviceByType] report divice by type!");        Response<List<ReportVO>> response = reportService.reportDeviceByType();        return ResponseUtil.returnResponse(response.getEntity());    }

前端：
前端链接到后台接口：/device/report/type

{"code":200,"data":    [{"count":16601,"deviceType":"1"},     {"count":6433,"deviceType":"2"},     {"count":2183,"deviceType":"3"},     {"count":29046,"deviceType":"4"}],"message":"SUCCESS"}

再将type的1234解析成对应的类型名称，放到对应饼图，即可。页面如下：
按类型统计设备数-饼图

4. 按版本分类

按软件版本分类的设备数，也是从ppcp服务器缓存中取数据。

Map<String, Map<String, Object>> versionCache = ppcpDeviceVersionCache.getAllDeviceVersionCache();

从缓存取出数据格式为Map：Map<设备号，Map<设备大类型，设备类型版本>>

{12345={system=1.7, ppbutler=2.14, uboot=1.0}, 98765={system=1.7, ppbutler=2.13, uboot=1.0}}

最后想要传给前端的格式为：Map<设备大类型，Map<设备类型版本，设备数量>>

{system={1.7=2}, ppbutler={2.14=1, 2.13=1}, uboot={1.0=2}}

在这里的格式转换，纠结了很长的时间，要感谢我的给力同事zhen，轻而易举解决了我的困扰，膜拜~~
问题解决过程及解析请移步博文：
【Map】Map类型和格式设计（工作需要）

http://blog.csdn.net/xiaoxiaolove613/article/details/73289513

代码如下：

Controller层：

component：

    public String reportDeviceByVersion() {        logger.info("[QueryController] [reportDeviceByVersion] report divice by version!");        Map<String, Map<String, Object>> versionCache = ppcpDeviceVersionCache.getAllDeviceVersionCache();        logger.info("[QueryController] [getAllDeviceVersionCache] get version cache,versionCache={}",versionCache);        Map<String,Map<String,Integer>> result = new HashMap<>();        for (Map<String,Object> map : versionCache.values()) {            for (String key : map.keySet()) {                if (!result.containsKey(key)) {                    result.put(key,new HashMap<>());                }                Map<String, Integer> map1 = result.get(key);                String k = String.valueOf(map.get(key));                if (!map1.containsKey(k)) {                    map1.put(k,1);                } else {                    map1.put(k,map1.get(k)+1);                }            }        }        return ResponseUtil.returnResponse(result);    }

前端：
前端链接到后台接口：/device/report/version

{"code":200,"data":{"system":{"1.7":2},        "ppbutler":{"2.13\n":1,"2.14\n":1},        "uboot":{"1.0\n":2}},"message":"SUCCESS"}

前端解析数据，按设备类型分类，并放到对应饼图。页面如下：
按版本统计设备

5. 一周内，活跃设备数统计

从数据库查询出数据，到后台代码，再到前端页面，中间的日期格式转换，以及7天数据中有缺失日期时应该怎么处理，着实为难了我好一阵子。中间也请教了好多人，还有网上的各种资料查询，好在有给力zhen的帮助，最后终于在一个乌七八黑的夜晚解决了这个“大”bug。具体问题解决过程及解析请移步博文：
【mysql】查询前7天的数据统计（解决日期不连续问题）

http://blog.csdn.net/xiaoxiaolove613/article/details/73251181

实现代码如下：

MySQL：

 SELECT        COUNT(DISTINCT device_serial_num) device_count,        DATE_FORMAT(created_time,'%Y-%m-%d') date,        DAYOFWEEK(created_time)-1 week_day        FROM        sp_user_order        WHERE        pay_fee > 0        and created_time < CURDATE() and created_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)        GROUP BY        date

活跃设备数统计

Dao层：
VO：

public class ReportWeekVO implements Serializable{    private String date;//日期    private Integer week;//周几    private Integer count;//设备数    public Integer getCount() {        return count;    }    public void setCount(Integer count) {        this.count = count;    }    public Integer getWeek() {        return week;    }    public void setWeek(Integer week) {        this.week = week;    }    public String getDate() {        return date;    }    public void setDate(String date) {        this.date = date;    }}

Mapper：

public interface ReportDeviceWeekMapper {    List<ReportWeekVO> activeDeviceWeek();}

Mapper.xml

<mapper namespace="com.payment.dm.model.mapper.ext.ReportDeviceWeekMapper">    <resultMap id="BaseResultMap" type="com.payment.dm.model.vo.ReportWeekVO">        <result column="date" jdbcType="VARCHAR" property="date"/>        <result column="device_count" jdbcType="INTEGER" property="count"/>        <result column="week_day" jdbcType="INTEGER" property="week"/>    </resultMap>    <select id="activeDeviceWeek" resultMap="BaseResultMap">        SELECT        COUNT(DISTINCT device_serial_num) device_count,        DATE_FORMAT(created_time,'%Y-%m-%d') date,        DAYOFWEEK(created_time)-1 week_day        FROM        sp_user_order        WHERE        pay_fee > 0        and created_time &lt; CURDATE() and created_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)        GROUP BY        date    </select></mapper>

service:

public List<ReportWeekVO> activeDeviceWeek() {        List<ReportWeekVO> reportWeekVOs = reportDeviceWeekMapper.activeDeviceWeek();        logger.info("[ReportDeviceService] [activeDeviceWeek] query active device in week, response = {}", JSON.toJSONString(reportWeekVOs));        List<ReportWeekVO> newReportWeekVOs = new ArrayList<>();        //获取当天日期        SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy-MM-dd");        for (int i = 1; i < 8; i++) {            ReportWeekVO newReportWeekVO = new ReportWeekVO();            Calendar calendar = Calendar.getInstance();            calendar.add(Calendar.DATE, -i);            Date monday = calendar.getTime();            String preMonday = dateFormater.format(monday);            newReportWeekVO.setDate(preMonday);            int intWeek = calendar.get(Calendar.DAY_OF_WEEK) - 1;            newReportWeekVO.setWeek(intWeek);            newReportWeekVO.setCount(0);            newReportWeekVOs.add(newReportWeekVO);        }        logger.info("[ReportDeviceService] [activeDeviceWeek]  newReportWeekVOs = {}", JSON.toJSONString(newReportWeekVOs));        for (int i = 0; i < newReportWeekVOs.size(); i++) {            for (int j = 0; j < reportWeekVOs.size(); j++) {                if (newReportWeekVOs.get(i).getDate().equals(reportWeekVOs.get(j).getDate())) {                    newReportWeekVOs.get(i).setCount(reportWeekVOs.get(j).getCount());                }            }        }        logger.info("[ReportDeviceService] [activeDeviceWeek]  newReportWeekVOs = {}", JSON.toJSONString(newReportWeekVOs));        return newReportWeekVOs;    }

Service层：

  @Override    public Response<List<ReportWeekVO>> activeDeviceWeek() {        List<ReportWeekVO> reportWeekVOs = reportDeviceService.activeDeviceWeek();        logger.info("[ReportServiceImpl] [activeDeviceWeek] query active device in week, reportWeekVOs = {}", reportWeekVOs);        return Response.OK(reportWeekVOs);    }

Controller层：

Controller：

    /**     * query device active num in a week     *     * @return     */    @RequestMapping(value = "/active", method = RequestMethod.GET)    public String activeDeviceWeek() {        logger.info("[ReportController] [reportDeviceByVersion] query active device in week!");        return deviceReportComponent.activeDeviceWeek();    }

component：

 public String activeDeviceWeek() {        logger.info("[QueryController] [activeDeviceWeek] report active divice in weeks!");        Response<List<ReportWeekVO>> response = reportService.activeDeviceWeek();        return ResponseUtil.returnResponse(response.getEntity());    }

前端：

{"code":200,"data":[{"count":4,"date":"2017-06-14","week":3},        {"count":8,"date":"2017-06-13","week":2},        {"count":4,"date":"2017-06-12","week":1},        {"count":0,"date":"2017-06-11","week":0},        {"count":0,"date":"2017-06-10","week":6},        {"count":4,"date":"2017-06-09","week":5},        {"count":8,"date":"2017-06-08","week":4}],"message":"SUCCESS"}

一周内活跃设备数

6. 一周内，异常设备数统计

类似于活跃设备数统计，除了sql有不同。

MySQL：

SELECT        a.dateconn date,        count(a.deviceid) device_count,        DAYOFWEEK(a.dateconn) - 1 week_day        FROM        (        SELECT        COUNT(1) concount,        DATE_FORMAT(connect_time, '%Y-%m-%d') dateconn,        device_id deviceid        FROM        t_connect_history        WHERE        connect_time < CURDATE()        AND connect_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)        GROUP BY        dateconn,        device_id        ) a        WHERE        a.concount >= 5        GROUP BY        date

异常设备查询

Mapper：

List<ReportWeekVO> excepDeviceWeek();

Mapper.xml：

<select id="excepDeviceWeek" resultMap="BaseResultMap">        SELECT        a.dateconn date,        count(a.deviceid) device_count,        DAYOFWEEK(a.dateconn) - 1 week_day        FROM        (        SELECT        COUNT(1) concount,        DATE_FORMAT(connect_time, '%Y-%m-%d') dateconn,        device_id deviceid        FROM        t_connect_history        WHERE        connect_time &lt; CURDATE()        AND connect_time >= DATE_SUB(CURDATE(), INTERVAL + 7 DAY)        GROUP BY        dateconn,        device_id        ) a        WHERE        a.concount >= 5        GROUP BY        date    </select>

前端：

{"code":200,"data":[{"count":26,"date":"2017-06-14","week":3},        {"count":20,"date":"2017-06-13","week":2},        {"count":19,"date":"2017-06-12","week":1},        {"count":0,"date":"2017-06-11","week":0},        {"count":0,"date":"2017-06-10","week":6},        {"count":22,"date":"2017-06-09","week":5},        {"count":37,"date":"2017-06-08","week":4}],"message":"SUCCESS"}

一周异常设备查询

7、首页展示

至此，设备首页大功告成，页面如下。（由于首页页面太大，只截取上半部分）后续的细节工作就是，从首页点击可以进到设备管理页面。

8、总结

总结来看，设备首页统计，有几个需要注意的小点：
格式转换
1、时间格式（Date型和String型）
2、List和Map类型转换
3、后台数据和前台数据转换

阅读全文

0 0