前言
准备实现一下统计意义上的用户画像,所以需要一些后端进行数据的统计
(1)用户基本活动的数据统计
包括过去一段时间的点赞量、浏览量、阅读量的数据统计
(2)用户最喜欢的标签,以及标签的得分
用户阅读的文章,或者点过赞的文章,都属于一定的标签,该用户给那个标签下的文章点赞多,或者阅读那个标签下文章比较多
(3) 用户最喜欢的博主排名
就是用户阅读的文章属于的博客,然后统计每个博客被阅读过的数量,就是喜欢的博主的排名
(4) 用户最喜欢的文章排名
就是用户最喜欢的文章,通过浏览次数和点赞次数害获得一个综合的评分
获取用户最近一段时间的点赞量 浏览量
Controller层:
分别调用service层的方法,计算浏览量和点赞量,然后返回即可
/**
*获取用户最近一段时间的点赞量 浏览量
* @param username
* @param day
* @return
*/
@PermitAll
@RequestMapping(value = "/statistics", method = RequestMethod.GET, produces = "application/json")
public Response getStatistics(
@RequestAttribute String username,
@RequestParam(value = "day", required = false, defaultValue = "15") Integer day)
{
User user = userService.selectUserByName(username);
List<ArticleTimeCount> userLikingCounts =getUserLikingCountRecently(day,user.getId());
List<ArticleTimeCount> userBrowsingCounts =getUserBrowsingCountRecently(day,user.getId());
Statistic statistic = new Statistic();
statistic.setUserBrowsingCounts(userBrowsingCounts);
statistic.setUserLikingCounts(userLikingCounts);
return Response.success("获取统计信息成功",statistic);
}
@Data
private class Statistic
{
List<ArticleTimeCount> userLikingCounts;
List<ArticleTimeCount> userBrowsingCounts;
}
/**
* 获取用户近期 每天的点赞量
* @param day
* @param userId
* @return
*/
public List<ArticleTimeCount> getUserLikingCountRecently(int day,int userId)
{
return userPortraitService.selectUserLikingCountRecently(day,userId);
}
/**
* 获取用户近期的浏览量
* @param day
* @param userId
* @return
*/
public List<ArticleTimeCount> getUserBrowsingCountRecently(int day,int userId)
{
return userPortraitService.selectUserBrowsingCountRecently(day,userId);
}
Service层:
统计用户每一天的阅读量,然后返回,统计用的是数据库的count,但是有的用户可能某些天没有阅读文章,所以没有这一天的阅读记录,数据库不会给一个count为0的记录,而前端展示折线图时,必须要每一天的数据都要有,count为0的天数需要我们自己去补充,所以需要在后台写一个方法,把统计出来缺少的天数,补上,将count置为0。
思想:先从今天开始,获取到day天的所有时间(如2020-06-08~2020-06_24),然后遍历数据库的查询得到的结果,看缺少那一天(上述结果缺少6月8日 6月10日 6月12日等等),将缺少的补充到结果中即可,count置为0,最后按时间进行排序。
/**
* 对时间进行补充 填上count为0的时间
* @param articleTimeCounts
* @return
*/
public static List<ArticleTimeCount> sortList(List<ArticleTimeCount> articleTimeCounts)
{
String startTime;
if(articleTimeCounts.size() >=2)
{
startTime = articleTimeCounts.get(0).getTime();
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
LocalDateTime startDate = LocalDateTime.of(LocalDate.parse(startTime,df),LocalTime.parse("00:00:00"));
LocalDateTime endDate = LocalDateTime.now();
List<String> dateList = completionDate(startDate,endDate);
for(String str :dateList)
{
if(!dateInList(str, articleTimeCounts))
{
ArticleTimeCount articleTimeCount = new ArticleTimeCount();
articleTimeCount.setCount(0);
articleTimeCount.setTime(str);
articleTimeCounts.add(articleTimeCount);
}
}
}
sortClass sort = new sortClass();
Collections.sort(articleTimeCounts,sort);
return articleTimeCounts;
}
/**
* 比较日期大小
*/
public static class sortClass implements Comparator {
@Override
public int compare(Object arg0, Object arg1){
ArticleTimeCount item0 = (ArticleTimeCount)arg0;
ArticleTimeCount item1 = (ArticleTimeCount)arg1;
int flag = item0.getTime().compareTo(item1.getTime());
return flag;
}
}
/**
* 此日期是否在时间内
* @param date
* @param dateList
* @return
*/
private static boolean dateInList(String date,List<ArticleTimeCount> dateList)
{
for(ArticleTimeCount str :dateList)
{
if(str.getTime().equals(date) )
{
return true;
}
}
return false;
}
/**
* 补全时间
* @param startTime
* @param endTime
* @return
*/
private static List<String> completionDate(
LocalDateTime startTime,
LocalDateTime endTime) {
//日期格式化
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
List<String> dateList = new ArrayList<>();
//遍历给定的日期期间的每一天
for (int i = 0; !Duration.between(startTime.plusDays(i), endTime).isNegative(); i++) {
//添加日期
dateList.add(startTime.plusDays(i).format(formatter));
}
return dateList;
}
按上述的方法进行时间的格式化,最终输出到前端的就是完整的时间阅读量的列表了
/**
* 获取用户近期 每天的点赞量
* @param day
* @param userId
* @return
*/
public List<ArticleTimeCount> selectUserLikingCountRecently(int day,int userId)
{
List<ArticleTimeCount> articleTimeCounts = userPortraitDao.selectUserLikingCountRecently(day,userId);
articleTimeCounts = sortList(articleTimeCounts);
return articleTimeCounts;
}
/**
* 获取用户近期的阅读量
* @param day
* @param userId
* @return
*/
public List<ArticleTimeCount> selectUserBrowsingCountRecently(int day,int userId)
{
List<ArticleTimeCount> articleTimeCounts = userPortraitDao.selectUserBrowsingCountRecently(day,userId);
articleTimeCounts = sortList(articleTimeCounts);
return articleTimeCounts;
}
Dao层:
使用DATE_FORMAT对datetime格式的时间进行格式化 去掉时 分 秒
使用DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(click_liking_time) 获取day天内的所有数据
结果格式大概是这样的:
/**
* 获取用户近期 每天的点赞量
* @param day
* @return
*/
@Select("select count(*) as count , DATE_FORMAT( click_liking_time, '%Y-%m-%d' ) AS time " +
"from user_liking " +
" where user_id = #{userId} and" +
" browse_time < CURDATE( ) + 1 and" +
" DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(click_liking_time) " +
" group by DATE_FORMAT( click_liking_time, '%Y-%m-%d') " +
" order by click_liking_time asc ")
List<ArticleTimeCount> selectUserLikingCountRecently(@Param("day") int day,@Param("userId") int userId);
/**
* 获取用户近期的阅读量
* @param day
* @return
*/
@Select("select count(*) as count , DATE_FORMAT( browse_time, '%Y-%m-%d' ) AS time " +
"from user_browsing_history where user_id = #{userId} and" +
" browse_time < CURDATE( ) + 1 and" +
" DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(browse_time) " +
" group by DATE_FORMAT( browse_time, '%Y-%m-%d') " +
" order by browse_time desc ")
List<ArticleTimeCount> selectUserBrowsingCountRecently( @Param("day") int day,@Param("userId") int userId);
获取用户喜爱的标签
Controller层:
进行简单的数据传输
/**
* 获取用户喜爱的标签
* @param username
* @return
*/
@PermitAll
@RequestMapping(value = "/tags", method = RequestMethod.GET, produces = "application/json")
public Response getMostLikingTags(
@RequestAttribute String username,
@RequestParam(value = "size", required = false, defaultValue = "15") Integer size)
{
User user = userService.selectUserByName(username);
List<TagScore> tagScoreList = userPortraitService.userMostLikingTag(user.getId(),size);
return Response.success("获取喜爱的标称成功",tagScoreList);
}
Service层:
根据用户阅读过的文章,每个文章都会对应一个或几个标签,通过对文章的阅读,得到每个用户对标签的阅读次数,然后返回统计结果。
根据用户点赞过的文章,每个文章都会对应一个或几个标签,通过对文章的点赞,得到每个用户对标签的点赞次数,然后返回统计结果。
计算分数,我们假设,每个tag被点赞一次的权重是2,被阅读一次的权重是1,然后综合统计一个分数,选出排名前size个。
/**
* 根据点赞量和浏览量 统计用户最喜欢的标签
* @param userId
* @return
*/
public List<TagScore> userMostLikingTag(int userId,int size)
{
List<Tag> tagListBrowsing = userPortraitDao.selectUserTagBrowsingCount(userId);
List<Tag> tagListLiking = userPortraitDao.selectUserTagLikingCount(userId);
Map<Integer,Integer> score = new HashMap<>();
for(Tag tag :tagListBrowsing)
{
int tagId = tag.getId();
int count = tag.getCount();
if(!score.containsKey(tagId))
{
score.put(tagId,0);
}
score.put(tagId,score.get(tagId)+count);
}
//每一次点赞 权重为2
for(Tag tag :tagListLiking)
{
int tagId = tag.getId();
int count = tag.getCount();
if(!score.containsKey(tagId))
{
score.put(tagId,0);
}
score.put(tagId,score.get(tagId)+(count*2));
}
List<Map.Entry<Integer,Integer>> list = new ArrayList<>(score.entrySet());
list.sort((o1, o2) -> o2.getValue().compareTo(o1.getValue()));
int index = 0;
List<TagScore> tagScoreList = new ArrayList<>();
for (Map.Entry<Integer,Integer> t : list) {
TagScore tagScore = new TagScore();
tagScore.setScore(t.getValue());
tagScore.setTag(tagDao.getTagById(t.getKey()));
//选10个
if (++index > size) {
break;
}
}
return tagScoreList;
}
Dao层:
使用sql语句进行简单的统计
/**
* 查询用户对于每个标签的阅读量
* @param userId
* @return
*/
@Select("select A.id , A.name , A.description ,count(*) as count " +
"from tag as A , user_browsing_history as B , article_tag as C " +
"where A.id = C.tag_id and B.article_id = C.article_id " +
"and B.user_id = #{userId} " +
"group by A.id " +
"order by count desc")
List<Tag> selectUserTagBrowsingCount(@Param("userId") int userId);
/**
* 查询用户对每个标签的点赞量
* @param userId
* @return
*/
@Select("select A.id , A.name , A.description ,count(*) as count " +
"from tag as A , user_liking as B , article_tag as C " +
"where A.id = C.tag_id " +
"and B.article_id = C.article_id " +
"and B.user_id = #{userId} " +
"group by A.id " +
"order by count desc;")
List<Tag> selectUserTagLikingCount(@Param("userId") int userId);
用户最喜欢的博主排名
Controller层:
/**
* 获取用户浏览最多的博客
* @param username
* @param size
* @return
*/
@PermitAll
@RequestMapping(value = "/blogs", method = RequestMethod.GET, produces = "application/json")
public Response getMostBrowsingBlogs(@RequestAttribute String username,
@RequestParam(value = "size", required = false,defaultValue = "15") Integer size)
{
User user = userService.selectUserByName(username);
List<BlogScore> blogScores = userPortraitService.selectUserBrowsingBlog(user.getId(),size);
return Response.success("获取浏览最多的博客成功",blogScores);
}
Service层:
/**
* 获取用户浏览最多的博客
* @param userId
* @param size
* @return
*/
public List<BlogScore> selectUserBrowsingBlog(int userId,int size)
{
List<Blog> blogs =userPortraitDao.selectUserBrowsingBlog(userId,size);
List<BlogScore> blogScores = new ArrayList<>();
for(Blog blog :blogs)
{
BlogScore blogScore = new BlogScore();
blogScore.setBlog(blog);
blogScore.setScore(blog.getArticleCount());
}
return blogScores;
}
Dao层:
根据用户浏览文章的记录,寻找用户最喜欢的一些博客,每个文章都属于一篇博客的,根据文章的点击量统计博客点击量。
/**
* 获取用户对博客的浏览量 用户浏览最多的几篇博主
* @param userId
* @param limit
* @return
*/
@Select("select A.id , A.title , A.url , A.feed , count(*) as articleCount " +
"from blog as A , article ad B ,user_browsing_history as C " +
"where C.user_id = #{userId} " +
"and C.article_id = B.id " +
"and B.blog_id = A.id group by A.id " +
"order by browsing_count desc" +
" limit #{limit}")
List<Blog> selectUserBrowsingBlog(@Param("userId") int userId,@Param("limit") int limit);
用户浏览最多的文章
Controller:
/**
* 用户浏览最多的文章
* @param username
* @param size
* @return
*/
@PermitAll
@RequestMapping(value = "/articles", method = RequestMethod.GET, produces = "application/json")
public Response getUserBrowsingArticle(@RequestAttribute String username,
@RequestParam(value = "size", required = false, defaultValue = "15") Integer size)
{
User user = userService.selectUserByName(username);
List<ArticleBrowsingCount> articleBrowsingCounts = userPortraitService.selectUserBrowsingArticle(user.getId(),size);
for(ArticleBrowsingCount articleBrowsingCount:articleBrowsingCounts)
{
articleBrowsingCount.setArticle(articleService.getArticleById(articleBrowsingCount.getArticleId()));
}
return Response.success("获取浏览最多的博客成功",articleBrowsingCounts);
}
Service层:
/**
* 获取用户浏览最多的文章
* @param userId
* @param size
* @return
*/
public List<ArticleBrowsingCount> selectUserBrowsingArticle(int userId,int size)
{
return userPortraitDao.selectUserBrowsingArticle(userId,size);
}
Dao层:
/**
* 用户浏览最多的几篇文章
* @param userId
* @param limit
* @return
*/
@Select("select article_id , count(*) as browsing_count " +
"from user_browsing_history " +
"where user_id = #{userId} " +
"group by article_id " +
"order by browsing_count desc " +
"limit #{limit}")
@Results(id = "ArticleBrowsingCount", value = {
@Result(property = "articleId", column = "article_id"),
@Result(property = "browsingCount", column = "browsing_count")})
List<ArticleBrowsingCount> selectUserBrowsingArticle(@Param("userId") int userId,@Param("limit") int limit);