背景：
数据库格式如下图所示
现在要统计出在一段时间内dimension_type为op即所有运营商的pv、uv、vv等指标的数组，以便页面显示出每个运营商在该事件段内历史指标曲线图。
分析：
返回的结果格式为："data": [
{
"name": "电信",
"data": [
{
"x": "20170803",
"y": 2
},
{
"x": "20170804",
"y": 1
}
]
},
{
"name": "移动"
"data": [
{
"x": "20170806",
"y": 99
},
{
"x": "20170807",
"y": 1
}
]
},
{
"name": "联通",
"data": [
{
"x": "20170804",
"y": 10
},
{
"x": "20170805",
"y": 2
}
]
}
]
很明显，返回结果是一个name对应多个data中数据的一对多关系。
实现：
首先，构造返回结果对应的java对象DataSeries
public class DataSeries {
private String name;
private List<LineDataVo> data;
public DataSeries(){
}
public DataSeries(String name, List<LineDataVo> data){
this.name = name;
this.data = data;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<LineDataVo> getData() {
return data;
}
public void setData(List<LineDataVo> data) {
this.data = data;
}
@Override
public String toString() {
return "Series [name=" + name + ", data=" + data + "]";
}
}
LineDataVo为
public class LineDataVo {
private String x;
private int y;
public LineDataVo(){
}
public LineDataVo(String x, int y){
this.x = x;
this.y = y;
}
public String getX() {
return x;
}
public void setX(String x) {
this.x = x;
}
public int getY() {
return y;
}
public void setY(int y) {
this.y = y;
}
@Override
public String toString() {
return "LineDataVo [x=" + x + ", y=" + y + "]";
}
}
对应的mapper文件为：
public interface DimStatisticResultMapper {
List<DataSeries> findOperatorFieldTrendDatas(@Param("value")String value, @Param("dimensionType")String dimensionType,
@Param("timeType")String timeType, @Param("startTime")int startTime,
@Param("endTime")int endTime)throws DataAccessException;
}
其中value为要查询的参数，即pv或者vv,或者uv,timeType为统计时间的类型，以天、周或者月为单位
对应的xml文件为：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.dao.DimStatisticResultMapper">
<resultMap id="BaseResultMap" type="com.test.po.DimStatisticResult">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="app_id" jdbcType="VARCHAR" property="appId" />
<result column="cache_key" jdbcType="VARCHAR" property="cacheKey" />
<result column="dimension_type" jdbcType="VARCHAR" property="dimensionType" />
<result column="dimension_value" jdbcType="VARCHAR" property="dimensionValue" />
<result column="time_type" jdbcType="VARCHAR" property="timeType" />
<result column="time_value" jdbcType="INTEGER" property="timeValue" />
<result column="pv" jdbcType="INTEGER" property="pv" />
<result column="uv" jdbcType="INTEGER" property="uv" />
<result column="vv" jdbcType="INTEGER" property="vv" />
<result column="ipv" jdbcType="INTEGER" property="ipv" />
<result column="total_delay" jdbcType="INTEGER" property="totalDelay" />
<result column="total_stay" jdbcType="INTEGER" property="totalStay" />
<result column="bounce_times" jdbcType="INTEGER" property="bounceTimes" />
<result column="created_date" jdbcType="TIMESTAMP" property="createdDate" />
<result column="modified_date" jdbcType="TIMESTAMP" property="modifiedDate" />
</resultMap>
<resultMap id="lineDataVoMap" type="com.test.vo.LineDataVo">
<result column="x" property="x" />
<result column="y" property="y" />
</resultMap> <resultMap id="DataSeriesMap" type="com.test.vo.DataSeries">
<result column="dimensionValue" property="name" />
<collection property="data" javaType="ArrayList" column="{value=count,dimensionValue=dimensionValue}" ofType="com.test.vo.LineDataVo"
select="findOperatorFieldTrendData">
<result property="x" column="time_value"/>
<result property="y" column="count"/>
</collection>
</resultMap> <sql id="Base_Column_List">
id,app_id, cache_key,dimension_type,dimension_value,time_type,time_value, pv, uv,
vv, ipv, total_delay, total_stay, bounce_times, bounce_times, modified_date
</sql> <select id ="findOperatorFieldTrendData" resultMap = "LineDataVoMap">
select time_value, ${value} as count
from bd_dim_statistic_result where dimension_type="op"
and dimension_value=#{dimensionValue}
</select> <select id="findOperatorFieldTrendDatas" resultMap="DataSeriesMap" >
select dimension_value as dimensionValue,time_value ,#{value} as count,pv from bd_dim_statistic_result
where dimension_type = #{dimensionType} and time_value between #{startTime} and #{endTime} group by dimension_value
</select> </mapper>