信件分析实战（四）——数据分析以及部分可视化

　　今天看着文件按照其上面给的三个需求，做了相应的数据处理。需求如下。

　　　　需求1：分析每年的信件数量

　　　　需求2：分析信件是属于投诉还是属于咨询，及投诉和咨询的数量

　　　　需求3：分析对信件进行回答的政府部门

　　按照这三个需求我做了其相应的数据处理。我使用的mysql建立的试图来进行处理的，这种方法很简单用于处理不是恨多的数据，如果涉及数据很多那就需要导入到hive数据仓库来进行处理，我现在只是使用MySQL来进行了简单的处理，处理结果如下。

需求一：

需求二：

需求三：

做完了这三个数据清洗我还使用echarts和javaweb做了需求一的相关可视化界面。其中使用了一些的框架。

相关的源代码：

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>北京百姓信件分析</title>
    <link rel="stylesheet" href="./layui/css/layui.css">
    <script src="./layui/layui.js"></script>
</head>
<body>
<body class="layui-layout-body">
<div class="layui-layout layui-layout-admin">
  <div class="layui-header">
    <div class="layui-logo">北京百姓信件分析</div>
  </div>
  
  <div class="layui-side layui-bg-black">
    <div class="layui-side-scroll">
      <!-- 左侧导航区域（可配合layui已有的垂直导航） -->
      <ul class="layui-nav layui-nav-tree"  lay-filter="test">
        <li class="layui-nav-item">
          <a class="" href="LetterServlet?method=year" target="center">每年数量</a>
          
        </li>
        <li class="layui-nav-item">
          <a href="javascript:;">信件类型</a>
        </li>
        <li class="layui-nav-item"><a href="">部门信件</a></li>
      </ul>
    </div>
  </div>
  
  <div class="layui-body">
    <!-- 内容主体区域 -->
    <div style="padding: 15px;">
    <iframe name="center" id="center" style="100%;height:490px;border:0;padding:0;"></iframe>
    </div>
  </div>
  
  <div class="layui-footer">
    <!-- 底部固定区域 -->
    ©陈欢
  </div>
</div>

<script>
//JavaScript代码区域
layui.use('element', function(){
  var element = layui.element;
  
});
</script>
</body>
</body>
</html>

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<% String path=request.getContextPath(); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>按年分析</title>
    <script type="text/javascript" src="<%=path %>/js/echarts.min.js"></script>
</head>
<body>
<%
    String year=(String)request.getAttribute("year");
    String num=(String)request.getAttribute("num");
%> 
<div id="container" style="height: 490px;weight:100%;"></div>
<script type="text/javascript">
var dom = document.getElementById("container");
var myChart = echarts.init(dom);
var app = {};
option = null;
var year=<%=year%>
var num=<%=num%>
option = {
    xAxis: {
        type: 'category',
        //data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
        data: year
    },
    yAxis: {
        type: 'value'
    },
    series: [{
        //data: [820, 932, 901, 934, 1290, 1330, 1320],
        data : num,
        type: 'line'
    }]
};
;
if (option && typeof option === "object") {
    myChart.setOption(option, true);
}
       </script>
</body>
</html>

ByYear.jsp

package com.servlet;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dao.LetterDAO;

/**
 * Servlet implementation class LetterServlet
 */
@WebServlet("/LetterServlet")
public class LetterServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    LetterDAO dao=new LetterDAO();
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LetterServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
     */
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        String method=request.getParameter("method");
        if(method.equals("year")) {
            NumByYear(request,response);
        }
    }

    private void NumByYear(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {
        String [] data =new String [2];
        data=dao.getYear();
        request.setAttribute("year", data[0]);
        request.setAttribute("num", data[1]);
        System.out.print(data[0]);
        System.out.print(data[1]);
        request.getRequestDispatcher("ByYear.jsp").forward(request, response);
        
    }

}

LetterServlet.java

package com.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.DBUtil.DBUtil;
import com.bean.LetterBean;

public class LetterDAO {

    public String[] getYear() {
        String[] num =new String [2];
        List<LetterBean> data =new ArrayList<LetterBean>();
        data=GetNumByYear();
        num[0]="[";
        num[1]="[";
        for(int i=0;i<data.size();i++) {
            num[0]+="'"+data.get(i).getCols()+"'";
            num[1]+="'"+data.get(i).getValue()+"'";
            if(i<data.size()-1) {
                num[0]+=",";
                num[1]+=",";
            }else {
                num[0]+="]";
                num[1]+="]";
            }
        }
        
        return num;
    }

    private List<LetterBean> GetNumByYear() {
        String sql="select * from num_by_year";
        List<LetterBean> data =new ArrayList<LetterBean>();
        Connection conn = DBUtil.getConn();
        Statement state = null;
        ResultSet rs = null;
        LetterBean bean=null;
        try {
            state = conn.createStatement();
            rs = state.executeQuery(sql);
            while (rs.next()) {
                    String cols=rs.getString("dt");
                    String value=rs.getString("num");
                    bean=new LetterBean(cols,value);
                    data.add(bean);
                }
            }catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBUtil.close(rs, state, conn);
            }
        return data;
    }

}

LetterDAO.java

package com.bean;

public class LetterBean {
    private String cols;
    private String value;
    public LetterBean(String cols,String value) {
        this.cols=cols;
        this.value=value;
    }
    public String getCols() {
        return cols;
    }
    public void setCols(String cols) {
        this.cols = cols;
    }
    public String getValue() {
        return value;
    }
    public void setValue(String value) {
        this.value = value;
    }
}

LetterBean

明天将完善相关的界面，之后将对系统进行进一步的完善。

新人编程，有错误请大家指正。