背景:
数据库格式如下图所示
现在要统计出在一段时间内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>