【Mysql】设备首页统计展示

最新推荐文章于 2023-05-30 21:30:00 发布

酸奶不酸

最新推荐文章于 2023-05-30 21:30:00 发布

阅读量513

点赞数

分类专栏：给工作留有一片清静之地

本文链接：https://blog.csdn.net/xiaoxiaolove613/article/details/73286040

版权

给工作留有一片清静之地专栏收录该内容

1 篇文章 0 订阅

订阅专栏

最近分组，老大对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

收藏

觉得还不错? 一键收藏
0
评论
【Mysql】设备首页统计展示

最近分组，老大对dm平台组做了新的规划，对于平台的首页，想要展示设备的一系列统计。 1、总设备数 2、设备已连接数 3、按类型分类 4、按版本分类（版本） 5、一周内，活跃设备数统计 6、一周内，异常设备数统计
复制链接

扫一扫