文章目录

一省份交易额统计接口
- 1 Sugar配置
- - （1）图表配置
  - （2）接口地址
  - （3）数据格式
  - （4）执行SQL
- 2 数据接口实现
- - （1）创建地区交易额统计实体类ProvinceStats
  - （2）Mapper层：创建ProvinceStatsMapper接口
  - （3）Service层：创建ProvinceStatsService接口
  - （4）Service层：创建ProvinceStatsServiceImpl实现类
  - （5）Controller层：在SugarController中增加方法
  - （6）测试
二新老访客对比接口
- 1 Sugar配置
- - （1）图表配置
  - （2）接口地址
  - （3）数据格式
  - （4）执行SQL
- 2 数据接口实现
- - （1）创建访问流量统计实体类VisitorStats
  - （2）Mapper层：创建VisitorStatsMapper
  - （3）Service层：创建VisitorStatsService接口
  - （4）Service层：创建VisitorStatsServiceImpl实现类
  - （5）Controller层：在SugarController中增加方法
  - （6）测试
三访客分时统计接口
- 1 Sugar配置
- - （1）图表配置
  - （2）接口地址
  - （3）数据格式
  - （4）执行SQL
- 2 数据接口实现
- - （1）Mapper
  - （2）Service
  - （3）ServiceIml
  - （4）Controller
  - （5）测试

一省份交易额统计接口

1 Sugar配置

（1）图表配置

在这里插入图片描述

（2）接口地址

https://m23o108551.zicp.fun/api/sugar/province?date=20221215

（3）数据格式

{"status": 0,"data": {"mapData": [{"name": "北京","value": 9350,},{"name": "天津","value": 9317,}],"valueName": "交易额",}
}

（4）执行SQL

province_stats_2022表结构：
在这里插入图片描述

select province_id,province_name,sum(order_amount) order_amount from province_stats_2022 where toYYYYMMDD(stt)=20221215 group by province_id,province_name;

2 数据接口实现

（1）创建地区交易额统计实体类ProvinceStats

package com.hzy.gmall.publisher.beans;
/*** Desc: 地区交易额统计实体类*/
@AllArgsConstructor
@Data
@NoArgsConstructor
public class ProvinceStats {private String stt;private String edt;private String province_id;private String province_name;private BigDecimal order_amount;private String ts;
}

（2）Mapper层：创建ProvinceStatsMapper接口

package com.hzy.gmall.publisher.mapper;
/*** 地区统计Mapper*/
public interface ProvinceStatsMapper {@Select("select province_id,province_name,sum(order_amount) order_amount " +"from province_stats_2022 " +"where toYYYYMMDD(stt)=#{date} " +"group by province_id,province_name;")List<ProvinceStatsMapper> selectProvinceStats(Integer date);
}

（3）Service层：创建ProvinceStatsService接口

package com.hzy.gmall.publisher.service;
/***地区统计service接口*/
public interface ProvinceStatsService {List<ProvinceStats> getProvinceStats(Integer date);
}

（4）Service层：创建ProvinceStatsServiceImpl实现类

package com.hzy.gmall.publisher.service.impl;
/*** 地区统计service实现类*/
@Service
public class ProvinceStatsServiceImpl implements ProvinceStatsService {@Autowiredprivate ProvinceStatsMapper provinceStatsMapper;@Overridepublic List<ProvinceStats> getProvinceStats(Integer date) {return provinceStatsMapper.selectProvinceStats(date);}
}

（5）Controller层：在SugarController中增加方法

@Autowired
private ProvinceStatsService provinceStatsService;@RequestMapping("/province")
public String getProvinnceStats(@RequestParam(value = "date",defaultValue = "0") Integer date){if (date == 0) {date = now();}List<ProvinceStats> provinceStatsList = provinceStatsService.getProvinceStats(date);StringBuilder jsonBuilder = new StringBuilder("{\"status\": 0,\"data\": {\"mapData\": [");for (int i = 0; i < provinceStatsList.size(); i++) {ProvinceStats provinceStats = provinceStatsList.get(i);jsonBuilder.append("{\"name\": \""+provinceStats.getProvince_name()+"\",\"value\": "+provinceStats.getOrder_amount()+"}");if (i < provinceStatsList.size() - 1 ){jsonBuilder.append(",");}}jsonBuilder.append("],\"valueName\": \"交易额\"}}");return jsonBuilder.toString();
}

（6）测试

$API_HOST/api/sugar/province?date=20221215

在这里插入图片描述

二新老访客对比接口

1 Sugar配置

（1）图表配置

在这里插入图片描述

（2）接口地址

https://m23o108551.zicp.fun/api/sugar/visitor?date=20221213

（3）数据格式

{"status": 0,"data": {"columns": [{"name": "类别","id": "type"},{"name": "新用户","id": "new"},{"name": "老用户","id": "old"}],"rows": [{"type": "用户数（人）","new": 200,"old": 500},{"type": "总访问页面（次）","new": 200,"old": 500},{"type": "跳出率（%）","new": 200,"old": 500},{"type": "平均在线时长（秒）","new": 200,"old": 500},{"type": "平均访问页面数（次）","new": 200,"old": 500}]}
}

（4）执行SQL

visitor_stats_2022表结构：

在这里插入图片描述

select is_new,sum(uv_ct) uv_ct,sum(pv_ct) pv_ct,sum(sv_ct) sv_ct,sum(uj_ct) uj_ct,sum(dur_sum) dur_sum from visitor_stats_2022 where toYYYYMMDD(stt)=20221213 group by is_new;

2 数据接口实现

（1）创建访问流量统计实体类VisitorStats

package com.hzy.gmall.publisher.beans;
/*** Desc: 访客流量统计实体类*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class VisitorStats {private String stt;private String edt;private String vc;private String ch;private String ar;private String is_new;private Long uv_ct = 0L;private Long pv_ct = 0L;private Long sv_ct = 0L;private Long uj_ct = 0L;private Long dur_sum = 0L;private Long new_uv = 0L;private Long ts;private int hr;//计算跳出率  = 跳出次数*100/访问次数public BigDecimal getUjRate() {if (sv_ct != 0L) {return BigDecimal.valueOf(uj_ct).multiply(BigDecimal.valueOf(100)).divide(BigDecimal.valueOf(sv_ct), 2, RoundingMode.HALF_UP);} else {return BigDecimal.ZERO;}}//计算每次访问停留时间(秒)  = 当日总停留时间（毫秒)/当日访问次数/1000public BigDecimal getDurPerSv() {if (sv_ct != 0L) {return BigDecimal.valueOf(dur_sum).divide(BigDecimal.valueOf(sv_ct), 0, RoundingMode.HALF_UP).divide(BigDecimal.valueOf(1000), 1, RoundingMode.HALF_UP);} else {return BigDecimal.ZERO;}}//计算每次访问停留页面数 = 当日总访问页面数/当日访问次数public BigDecimal getPvPerSv() {if (sv_ct != 0L) {return BigDecimal.valueOf(pv_ct).divide(BigDecimal.valueOf(sv_ct), 2, RoundingMode.HALF_UP);} else {return BigDecimal.ZERO;}}
}

（2）Mapper层：创建VisitorStatsMapper

package com.hzy.gmall.publisher.mapper;public interface VisitorStatsMapper {@Select("select is_new,sum(uv_ct) uv_ct,sum(pv_ct) pv_ct,sum(sv_ct) sv_ct," +"sum(uj_ct) uj_ct,sum(dur_sum) dur_sum " +"from visitor_stats_2022 " +"where toYYYYMMDD(stt)=#{date} " +"group by is_new")List<VisitorStats> selectVisitorStats(Integer date);
}

（3）Service层：创建VisitorStatsService接口

package com.hzy.gmall.publisher.service;public interface VisitorStatsService {List<VisitorStats> getVisitorStats(Integer date);
}

（4）Service层：创建VisitorStatsServiceImpl实现类

package com.hzy.gmall.publisher.service.impl;@Service
public class VisitorStatsServiceImpl implements VisitorStatsService {@Autowiredprivate VisitorStatsMapper visitorStatsMapper;@Overridepublic List<VisitorStats> getVisitorStats(Integer date) {return visitorStatsMapper.selectVisitorStats(date);}
}

（5）Controller层：在SugarController中增加方法

@Autowired
private VisitorStatsService visitorStatsService;@RequestMapping("visitor")
public String getVisitorStats(@RequestParam(value = "date",defaultValue = "0") Integer date){if (date == 0){date = now();}List<VisitorStats> visitorStatsList = visitorStatsService.getVisitorStats(date);VisitorStats newVisitorStats = new VisitorStats();VisitorStats oldVisitorStats = new VisitorStats();for (VisitorStats visitorStats : visitorStatsList) {if ("1".equals(visitorStats.getIs_new())){// 新访客newVisitorStats = visitorStats;}else{// 老访客oldVisitorStats = visitorStats;}}String json = "{\"status\": 0,\"data\": {\"columns\": [" +"      {\"name\": \"类别\",\"id\": \"type\"}," +"      {\"name\": \"新用户\",\"id\": \"new\"}," +"      {\"name\": \"老用户\",\"id\": \"old\"}],\"rows\": [" +"      {\"type\": \"用户数（人）\",\"new\": "+newVisitorStats.getUv_ct()+",\"old\": "+oldVisitorStats.getUv_ct()+"}," +"      {\"type\": \"总访问页面（次）\",\"new\": "+newVisitorStats.getPv_ct()+",\"old\": "+oldVisitorStats.getPv_ct()+"}," +"      {\"type\": \"跳出率（%）\",\"new\": "+newVisitorStats.getUjRate()+",\"old\": "+oldVisitorStats.getUjRate()+"}," +"      {\"type\": \"平均在线时长（秒）\",\"new\": "+newVisitorStats.getDurPerSv()+",\"old\": "+oldVisitorStats.getDurPerSv()+"}," +"      {\"type\": \"平均访问页面数（次）\",\"new\": "+newVisitorStats.getPvPerSv()+",\"old\": "+oldVisitorStats.getPvPerSv()+"}]}}";return json;
}

（6）测试

https://m23o108551.zicp.fun/api/sugar/visitor?date=20221213

在这里插入图片描述

三访客分时统计接口

统计指标：uv，pv，新访客uv

1 Sugar配置

（1）图表配置

在这里插入图片描述

（2）接口地址

https://m23o108551.zicp.fun/api/sugar/hr?date=20221213

（3）数据格式

{"status": 0,"data": {"categories": ["00","01","02","...","23"],"series": [{"name": "UV","data": [4919,2252,...,2537]},{"name": "PV","data": [7979,10500,...,8428]},{"name": "NewUV","data": [11291,20146,...,6257]}]}
}

（4）执行SQL

select toHour(stt) hr,sum(uv_ct) uv_ct,sum(pv_ct) pv_ct,sum(if(is_new='1',visitor_stats_2022.uv_ct,0)) new_uv from visitor_stats_2022 where toYYYYMMDD(stt)=20221213 group by hr;

2 数据接口实现

（1）Mapper

@Select("select toHour(stt) hr,sum(uv_ct) uv_ct,sum(pv_ct) pv_ct," +"sum(if(is_new='1',visitor_stats_2022.uv_ct,0)) new_uv " +"from visitor_stats_2022 " +"where toYYYYMMDD(stt)=#{date} " +"group by hr;")
List<VisitorStats> selectVisitorStatsByHr(Integer date);

（2）Service

List<VisitorStats> getVisitorStatsByHr(Integer date);

（3）ServiceIml

@Override
public List<VisitorStats> getVisitorStatsByHr(Integer date) {return visitorStatsMapper.selectVisitorStatsByHr();
}

（4）Controller

@RequestMapping("hr")
public String getVisitorStatsByHr(@RequestParam(value = "date",defaultValue = "0") Integer date){if (date == 0){date = now();}// 从service中获取分时访问情况List<VisitorStats> visitorStatsList = visitorStatsService.getVisitorStatsByHr(date);// 定义数组，存放24小时的分时统计情况VisitorStats[] visitorStatsArr = new VisitorStats[24];// 从集合中获取结果，赋值给数组中相对应的小时for (VisitorStats visitorStats : visitorStatsList) {visitorStatsArr[visitorStats.getHr()] = visitorStats;}// 用于存放24小时的集合List hrList = new ArrayList();// 用于存放每个小时uv情况的集合List uvList = new ArrayList();// 用于存放每个小时pv情况的集合List pvList = new ArrayList();// 用于存放新访客每个小时uv情况的集合List newUvList = new ArrayList();// 从数组中获取一天24小时的数据for (int hr = 0; hr < visitorStatsArr.length; hr++) {VisitorStats visitorStats = visitorStatsArr[hr];if (visitorStats != null){uvList.add(visitorStats.getUv_ct());pvList.add(visitorStats.getPv_ct());newUvList.add(visitorStats.getNew_uv());}else{uvList.add(0L);pvList.add(0L);newUvList.add(0L);}hrList.add(String.format("%02d",hr));}String json = "{\"status\": 0,\"data\": {\"categories\": " +"[\""+ StringUtils.join(hrList,"\",\"")+"\"]," +"\"series\": [" +"{\"name\": \"UV\",\"data\":["+StringUtils.join(uvList,",")+"]}," +"{\"name\": \"PV\",\"data\":["+StringUtils.join(pvList,",")+"]}," +"{\"name\": \"NewUV\",\"data\":["+StringUtils.join(newUvList,",")+"]" +"}]}}";return json;
}