总是觉得柱图中带气泡显示当前值的echarts显得稍微高端一些,于是乎研究一下为同学的贷款网站做下渠道推广点击访问率的统计分析。
首先去echarts官网demo下找了一个模板(当然是柱图的),改了改里面的参数的示例,大概出来一个雏形:
js代码如下:
option = {
title : {
text: '某地区蒸发量和降水量',
subtext: '纯属虚构'
},
tooltip : {
trigger: 'axis'
},
legend: {
data:['降水量']
},
toolbox: {
show : true,
feature : {
mark : {show: true},
dataView : {show: true, readOnly: false},
magicType : {show: true, type: ['line', 'bar']},
restore : {show: true},
saveAsImage : {show: true}
}
},
calculable : true,
xAxis : [
{
type : 'category',
data : ['aaaaa渠道','bbbbb渠道','ccccc渠道','4月','5月','6月','7月','8月','9月','10月','11月','12月'],
axisLabel:{
interval:0,//x轴显示全部
rotate:-30//倾斜-30度
}
}
],
yAxis : [
{
type : 'value'
}
],
series : [
{
name:'降水量',
type:'bar',
data:[2.6, 5.9, 9.0, 26.4, 28.7, 70.7, 175.6, 182.2, 48.7, 18.8, 6.0, 2.3],
markPoint : {
data : [
{name : '访问量', value : 2.6, xAxis: 0, yAxis: 2.6, symbolSize:18},
{name : '访问量', value : 5.9, xAxis: 1, yAxis: 5.9, symbolSize:18},
{name : '访问量', value : 9.0, xAxis: 2, yAxis: 9.0, symbolSize:18},
{name : '访问量', value : 26.4, xAxis: 3, yAxis: 26.4, symbolSize:18},
{name : '访问量', value : 28.7, xAxis: 4, yAxis: 28.7, symbolSize:18},
{name : '访问量', value : 70.7, xAxis: 5, yAxis: 70.7, symbolSize:18},
{name : '访问量', value : 175.6, xAxis: 6, yAxis: 175.6, symbolSize:18},
{name : '访问量', value : 182.2, xAxis: 7, yAxis: 182.2, symbolSize:18},
{name : '访问量', value : 48.7, xAxis: 8, yAxis: 48.7, symbolSize:18},
{name : '访问量', value : 18.8, xAxis: 9, yAxis: 18.8, symbolSize:18},
{name : '访问量', value : 6.0, xAxis: 10, yAxis: 6.0, symbolSize:18},
{name : '访问量', value : 2.3, xAxis: 11, yAxis: 2.3,symbolSize:18}
]
},
markLine : {
data : [
{type : 'average', name : '平均值'}
]
}
}
]
};
开始建表:
1:渠道伪表
CREATE TABLE `bd_ad_channel_info` (
`channel_code` varchar(30) DEFAULT NULL COMMENT '渠道编号',
`channel_name` varchar(50) DEFAULT NULL COMMENT '渠道名称'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='渠道伪表';
2:访问日志表
CREATE TABLE `bd_channel_ad_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`channel_code` varchar(30) DEFAULT NULL COMMENT '渠道编号',
`visit_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '访问时间',
`visit_page` varchar(50) DEFAULT NULL COMMENT '访问页面',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='渠道推广访问日志表';
然后乎开始写关联sql:
select IFNULL(a.channel_name, b.channel_code) channel_name, count(1) as visitnum
from bd_ad_channel_info a right join bd_channel_ad_log b on a.channel_code = b.channel_code
where 1=1 group by channel_name;
控制层代码:
@RequestMapping("/chartLog")
public ModelAndView chartLog(HttpServletRequest request){
ModelAndView mv = new ModelAndView("cms/channelAdLog");
System.out.println("访问统计...");
List<String> nameList = new ArrayList<String>();
List<Integer> dataList = new ArrayList<Integer>();
List<ChannelAdVisit> vList = new ArrayList<ChannelAdVisit>();
String sql = "select IFNULL(a.channel_name, b.channel_code) channel_name, count(1) as visitnum " +
" from bd_ad_channel_info a right join bd_channel_ad_log b on a.channel_code = b.channel_code " +
" where 1=1 group by channel_name ";
List<Map<String, Object>> visitlist = this.jdbcTemplate.queryForList(sql);
if(visitlist!=null && visitlist.size()>0){
for (int i = 0; i < visitlist.size(); i++) {
Map<String, Object> map = visitlist.get(i);
ChannelAdVisit channelAdVisit = new ChannelAdVisit();
String channelName = map.get("channel_name")+"";
Integer visitNum = Integer.parseInt(map.get("visitnum")+"");
channelAdVisit.setName(channelName);
channelAdVisit.setValue(visitNum);
channelAdVisit.setxAxis(i);
channelAdVisit.setyAxis(visitNum);
channelAdVisit.setSymbolSize(50);
vList.add(channelAdVisit);
nameList.add(channelName);
dataList.add(visitNum);
}
}
JSONArray namejsonArray = JSONArray.fromObject(nameList);
JSONArray datajsonArray = JSONArray.fromObject(dataList);
JSONArray tipjsonArray = JSONArray.fromObject(vList);
String logSql= "select IFNULL(a.channel_name, b.channel_code) channel_name, b.visit_time" +
" from bd_ad_channel_info a right join bd_channel_ad_log b on a.channel_code = b.channel_code " +
" where 1=1 order by b.visit_time desc ";
List<Map<String, Object>> logList = this.jdbcTemplate.queryForList(logSql);
mv.addObject("channelName", namejsonArray.toString());
mv.addObject("channelData", datajsonArray.toString());
mv.addObject("channelTip", tipjsonArray.toString());
mv.addObject("logList", logList);
return mv;
}
效果如图: