一、环境搭建
1、数据库
CREATE TABLE ` statistics_daily` (
` id` char ( 19 ) NOT NULL COMMENT '主键' ,
` date_calculated` varchar ( 20 ) NOT NULL COMMENT '统计日期' ,
` register_num` int ( 11 ) NOT NULL DEFAULT '0' COMMENT '注册人数' ,
` login_num` int ( 11 ) NOT NULL DEFAULT '0' COMMENT '登录人数' ,
` video_view_num` int ( 11 ) NOT NULL DEFAULT '0' COMMENT '每日播放视频数' ,
` course_num` int ( 11 ) NOT NULL DEFAULT '0' COMMENT '每日新增课程数' ,
` gmt_create` datetime NOT NULL COMMENT '创建时间' ,
` gmt_modified` datetime NOT NULL COMMENT '更新时间' ,
PRIMARY KEY ( ` id` ) ,
KEY ` statistics_day` ( ` date_calculated` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT = '网站统计日数据' ;
INSERT INTO ` statistics_daily` VALUES ( '1078490017163833345' , '2018-12-28' , 0 , 0 , 154 , 170 , '2018-12-28 11:17:12' , '2019-09-09 12:12:12' ) , ( '1087142127818768386' , '2019-01-02' , 2 , 0 , 167 , 177 , '2019-01-21 08:17:36' , '2019-09-09 12:12:12' ) , ( '1087198321809457153' , '2019-01-01' , 1 , 0 , 130 , 189 , '2019-01-21 12:00:54' , '2019-09-09 12:12:12' ) , ( '1087198383973236738' , '2019-01-03' , 0 , 0 , 114 , 130 , '2019-01-21 12:01:09' , '2019-09-09 12:12:12' ) , ( '1087451681764982785' , '2019-01-04' , 0 , 0 , 118 , 155 , '2019-01-22 04:47:39' , '2019-09-09 12:12:12' ) , ( '1087455336471785473' , '2019-01-05' , 0 , 0 , 184 , 186 , '2019-01-22 05:02:11' , '2019-09-09 12:12:12' ) , ( '1105339962460491777' , '2019-03-01' , 0 , 143 , 168 , 136 , '2019-03-12 13:29:18' , '2019-03-12 13:29:18' ) , ( '1105339977027309569' , '2019-03-02' , 0 , 165 , 171 , 158 , '2019-03-12 13:29:21' , '2019-03-12 13:29:21' ) , ( '1105339990738489346' , '2019-03-03' , 0 , 143 , 147 , 194 , '2019-03-12 13:29:25' , '2019-03-12 13:29:25' ) , ( '1105340000544772098' , '2019-03-04' , 0 , 155 , 106 , 153 , '2019-03-12 13:29:27' , '2019-03-12 13:29:27' ) , ( '1105340011244441602' , '2019-03-05' , 0 , 186 , 102 , 155 , '2019-03-12 13:29:30' , '2019-03-12 13:29:30' ) , ( '1105340020929089538' , '2019-03-06' , 0 , 140 , 192 , 129 , '2019-03-12 13:29:32' , '2019-03-12 13:29:32' ) , ( '1105340029800042497' , '2019-03-07' , 0 , 186 , 139 , 116 , '2019-03-12 13:29:34' , '2019-03-12 13:29:34' ) , ( '1105340038696161282' , '2019-03-08' , 0 , 120 , 166 , 112 , '2019-03-12 13:29:36' , '2019-03-12 13:29:36' ) , ( '1105340049441968129' , '2019-03-09' , 0 , 182 , 147 , 119 , '2019-03-12 13:29:39' , '2019-03-12 13:29:39' ) , ( '1105340059738984449' , '2019-03-10' , 0 , 199 , 141 , 103 , '2019-03-12 13:29:41' , '2019-03-12 13:29:41' ) , ( '1105340070438653953' , '2019-03-11' , 0 , 127 , 137 , 156 , '2019-03-12 13:29:44' , '2019-03-12 13:29:44' ) ;
2、搭建后端微服务
1、application.yml
server :
port : 8007
spring :
application :
name : service- statistics
profiles :
active : dev
datasource :
driver-class-name : com.mysql.cj.jdbc.Driver
url : jdbc: mysql: //localhost: 3306/gulischool? serverTimeZone=UTC&useUnicode =true&characterEncoding =utf- 8&useSSL =false&allowPublicKeyRetrieval =true
username : root
password : 123456
jackson :
date-format : yyyy- MM- DD HH: mm: ss
time-zone : GMT+8
cloud :
nacos :
discovery :
server-addr : 127.0.0.1: 8848
feign :
client :
config :
default :
connect-timeout : 10000
read-timeout : 10000
hystrix :
enabled : true
hystrix :
command :
default :
execution :
isolation :
thread :
timeoutInMilliseconds : 6000
mybatis-plus :
mapper-locations : classpath: com/yzpnb/statistics_service/mapper/xml/*.xml
2、代码生成器,生成MVC结构
3、启动类
package com. yzpnb. statistics_service;
import org. mybatis. spring. annotation. MapperScan;
import org. springframework. boot. SpringApplication;
import org. springframework. boot. autoconfigure. SpringBootApplication;
import org. springframework. cloud. client. discovery. EnableDiscoveryClient;
import org. springframework. cloud. openfeign. EnableFeignClients;
import org. springframework. context. annotation. ComponentScan;
@SpringBootApplication
@EnableFeignClients
@EnableDiscoveryClient
@MapperScan ( "com.yzpnb.statistics_service.mapper" )
@ComponentScan ( "com.yzpnb" )
public class StatisticsApplication {
public static void main ( String[ ] args) {
SpringApplication. run ( StatisticsApplication. class , args) ;
}
}
二、后端
1、统计某一天的注册人数
统计注册人数,这肯定是会员模块的东西,所以需要Feign远程调用
1、sql讲解
1、controller
@ApiOperation ( "统计某一天的注册人数" )
@GetMapping ( value = "countregister" )
public Integer registerCount ( @ApiParam ( name = "day" , value = "天数" )
@RequestParam ( value = "day" ) String day) {
Integer count = ucenterMemberService. countRegisterByDay ( day) ;
return count;
}
2、service
@Override
public Integer countRegisterByDay ( String day) {
return baseMapper. countRegisterByDay ( day) ;
}
3、mapper
Integer countRegisterByDay ( String day) ;
< ! -- 查询2019 年3 月12 号的注册用户-- >
< select id= "countRegisterByDay" resultType= "java.lang.Integer" >
select
count ( 1 )
from
ucenter_member as uc
where
Date ( uc. gmt_create) = #{ day}
< / select>
4、测试
2、统计分析功能
1、远程调用统计注册人数接口
2、controller
package com. yzpnb. statistics_service. controller;
import com. yzpnb. common_utils. Result;
import com. yzpnb. statistics_service. service. StatisticsDailyService;
import io. swagger. annotations. Api;
import io. swagger. annotations. ApiOperation;
import org. springframework. beans. factory. annotation. Autowired;
import org. springframework. web. bind. annotation. PathVariable;
import org. springframework. web. bind. annotation. PostMapping;
import org. springframework. web. bind. annotation. RequestMapping;
import org. springframework. web. bind. annotation. RestController;
@RestController
@RequestMapping ( "/statistics_service/statistics-daily" )
public class StatisticsDailyController {
@Autowired
private StatisticsDailyService statisticsDailyService;
@ApiOperation ( "根据日期创建统计对象" )
@PostMapping ( "{day}" )
public Result createStatisticsByDate ( @PathVariable String day) {
statisticsDailyService. createStatisticsByDay ( day) ;
return Result. ok ( ) ;
}
}
3、service
package com. yzpnb. statistics_service. service. impl;
import com. baomidou. mybatisplus. core. conditions. query. QueryWrapper;
import com. yzpnb. statistics_service. entity. StatisticsDaily;
import com. yzpnb. statistics_service. feign. FeignToUcenterClient;
import com. yzpnb. statistics_service. mapper. StatisticsDailyMapper;
import com. yzpnb. statistics_service. service. StatisticsDailyService;
import com. baomidou. mybatisplus. extension. service. impl. ServiceImpl;
import org. apache. commons. lang3. RandomUtils;
import org. springframework. beans. factory. annotation. Autowired;
import org. springframework. stereotype. Service;
@Service
public class StatisticsDailyServiceImpl extends ServiceImpl < StatisticsDailyMapper, StatisticsDaily> implements StatisticsDailyService {
@Autowired
private FeignToUcenterClient feignToUcenterClient;
@Override
public void createStatisticsByDay ( String day) {
QueryWrapper< StatisticsDaily> dayQueryWrapper = new QueryWrapper < > ( ) ;
dayQueryWrapper. eq ( "date_calculated" , day) ;
baseMapper. delete ( dayQueryWrapper) ;
Integer registerNum = feignToUcenterClient. registerCount ( day) ;
Integer loginNum = RandomUtils. nextInt ( 100 , 200 ) ;
Integer videoViewNum = RandomUtils. nextInt ( 100 , 200 ) ;
Integer courseNum = RandomUtils. nextInt ( 100 , 200 ) ;
StatisticsDaily daily = new StatisticsDaily ( ) ;
daily. setDateCalculated ( day) ;
daily. setRegisterNum ( registerNum) ;
daily. setLoginNum ( loginNum) ;
daily. setVideoViewNum ( videoViewNum) ;
daily. setCourseNum ( courseNum) ;
baseMapper. insert ( daily) ;
}
}
4、测试
3、定时任务功能
cron 计划任务,是任务在约定的时间执行已经计划好的工作,这是表面的意思。 在Linux中,我们经常用到 cron 服务器来完成这项工作。cron服务器可以根据配置文件约定的时间来执行特定的任务。
Cron表达式 在线生成Cron表达式网址:http://cron.qqe2.com/ cron表达式用来表示时间,比如0 0 0 * * * 就表示每天的晚上12:00 注意:我们在生成时,不可以带年,也就是生成的表达式只能为6位,因为spring默认是每年,我们不能指定年,记得生成后数一下,是不是6位
1、启动类加注解@EnableScheduling
2、创建定时任务类,在类中使用cron表达式指定方法执行规则
3、每天凌晨1点统计前一天的数据
package com. yzpnb. statistics_service. schedule;
import com. yzpnb. statistics_service. service. StatisticsDailyService;
import org. springframework. beans. factory. annotation. Autowired;
import org. springframework. scheduling. annotation. Scheduled;
import org. springframework. stereotype. Component;
import java. text. SimpleDateFormat;
import java. util. Calendar;
import java. util. Date;
@Component
public class ScheduledTask {
@Autowired
private StatisticsDailyService statisticsDailyService;
@Scheduled ( cron = "0 0 1 * * ?" )
public void task1 ( ) {
Calendar now = Calendar. getInstance ( ) ;
now. setTime ( new Date ( ) ) ;
now. set ( Calendar. DATE, ( now. get ( Calendar. DATE) ) - 1 ) ;
Date time = now. getTime ( ) ;
SimpleDateFormat sdf= new SimpleDateFormat ( "yyyy-MM-dd" ) ;
String day = sdf. format ( time) ;
statisticsDailyService. createStatisticsByDay ( day) ;
}
}
4、统计数据条件获取
1、controller
@ApiOperation ( "根据起始日期结束日期和查询数据类型得到统计数据" )
@GetMapping ( "show-chart/{begin}/{end}/{type}" )
public Result showChart ( @ApiParam ( name = "begin" , value = "起始时间" )
@PathVariable String begin,
@ApiParam ( name = "end" , value = "结束时间" )
@PathVariable String end,
@ApiParam ( name = "type" , value = "查询数据类型" )
@PathVariable String type) {
Map< String, Object> map = statisticsDailyService. getChartData ( begin, end, type) ;
return Result. ok ( ) . data ( map) ;
}
2、service
@Override
public Map< String, Object> getChartData ( String begin, String end, String type) {
QueryWrapper< StatisticsDaily> dayQueryWrapper = new QueryWrapper < > ( ) ;
dayQueryWrapper. select ( type, "date_calculated" ) ;
dayQueryWrapper. between ( "date_calculated" , begin, end) ;
List< StatisticsDaily> dayList = baseMapper. selectList ( dayQueryWrapper) ;
Map< String, Object> map = new HashMap < > ( ) ;
List< Integer> dataList = new ArrayList < Integer> ( ) ;
List< String> timeList = new ArrayList < String> ( ) ;
map. put ( "dataList" , dataList) ;
map. put ( "timeList" , timeList) ;
for ( int i = 0 ; i < dayList. size ( ) ; i++ ) {
StatisticsDaily daily = dayList. get ( i) ;
timeList. add ( daily. getDateCalculated ( ) ) ;
switch ( type) {
case "register_num" :
dataList. add ( daily. getRegisterNum ( ) ) ;
break ;
case "login_num" :
dataList. add ( daily. getLoginNum ( ) ) ;
break ;
case "video_view_num" :
dataList. add ( daily. getVideoViewNum ( ) ) ;
break ;
case "course_num" :
dataList. add ( daily. getCourseNum ( ) ) ;
break ;
default :
break ;
}
}
return map;
}
3、测试
三、前端(后台管理系统)
1、统计分析功能
1、nginx配置
2、api接口
3、组件
< ! -- 统计表单开始-- >
< div class = "app-container" >
< ! -- 表单-- >
< el- form : inline= "true" class = "demo-form-inline" >
< el- form- item label= "日期" >
< el- date- picker
v- model= "day"
type= "date"
placeholder= "选择要统计的日期"
value- format= "yyyy-MM-dd" / >
< / el- form- item>
< el- button
: disabled= "btnDisabled"
type= "primary"
@click = "create()" > 生成< / el- button>
< / el- form>
< / div>
< ! -- 统计表单结束-- >
4、代码实现
2、生成统计图表(使用ECharts组件生成图表)
安装:npm install echarts --save 安装完成后,在需要的地方使用即可
1、api接口
2、组件
< ! -- 图表表单开始 -- >
< div class = "app-container" >
< ! -- 表单-- >
< el- form : inline= "true" class = "demo-form-inline" >
< el- form- item>
< el- select v- model= "searchObj.type" clearable placeholder= "请选择" >
< el- option label= "学员登录数统计" value= "login_num" / >
< el- option label= "学员注册数统计" value= "register_num" / >
< el- option label= "课程播放数统计" value= "video_view_num" / >
< el- option label= "每日课程数统计" value= "course_num" / >
< / el- select>
< / el- form- item>
< el- form- item>
< el- date- picker
v- model= "searchObj.begin"
type= "date"
placeholder= "选择开始日期"
value- format= "yyyy-MM-dd" / >
< / el- form- item>
< el- form- item>
< el- date- picker
v- model= "searchObj.end"
type= "date"
placeholder= "选择截止日期"
value- format= "yyyy-MM-dd" / >
< / el- form- item>
< el- button
: disabled= "btnDisabled"
type= "primary"
icon= "el-icon-search"
@click= "showChart()" > 查询< / el- button>
< / el- form>
< div class = "chart-container" >
< div id= "chart" class = "chart" style= "height:500px;width:100%" / >
< / div>
< / div>
< ! -- 图表表单结束-- >
3、代码
searchObj: {
type: '' ,
begin: '' ,
end: ''
} ,
btnDisabled2: false ,
chart: null ,
title: '' ,
xData: [ ] ,
yData: [ ]
}
showChart ( ) {
this . initChartData ( )
} ,
initChartData ( ) {
daily. showChart ( this . searchObj) . then ( response => {
this . yData = response. data. dataList
this . xData = response. data. timeList
switch ( this . searchObj. type) {
case 'register_num' :
this . title = '学员注册数统计'
break
case 'login_num' :
this . title = '学员登录数统计'
break
case 'video_view_num' :
this . title = '课程播放数统计'
break
case 'course_num' :
this . title = '每日课程数统计'
break
}
this . setChart ( )
} )
} ,
setChart ( ) {
this . chart = echarts. init ( document. getElementById ( 'chart' ) )
var option = {
xAxis: {
type: 'category' ,
data: this . xData
} ,
yAxis: {
type: 'value'
} ,
series: [ {
data: this . yData,
type: 'line'
} ] ,
}
this . chart. setOption ( option)
} ,
4、测试
因为我的数据是字符串,所以没有办法区分顺序,因为是测试数据,所以2号排在了1号前面,以后我们正常存入数据,不会有这种问题
5、添加样式
title: {
text: this . title
} ,
tooltip: {
trigger: 'axis'
} ,
dataZoom: [ {
show: true ,
height: 30 ,
xAxisIndex: [
0
] ,
bottom: 30 ,
start: 10 ,
end: 80 ,
handleIcon: 'path://M306.1,413c0,2.2-1.8,4-4,4h-59.8c-2.2,0-4-1.8-4-4V200.8c0-2.2,1.8-4,4-4h59.8c2.2,0,4,1.8,4,4V413z' ,
handleSize: '110%' ,
handleStyle: {
color: '#d3dee5'
} ,
textStyle: {
color: '#fff'
} ,
borderColor: '#90979c'
} ,
{
type: 'inside' ,
show: true ,
height: 15 ,
start: 1 ,
end: 35
} ] ,