创新实训（36）——有关用户画像部分基础信息统计的后端接口

前言

准备实现一下统计意义上的用户画像，所以需要一些后端进行数据的统计
（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);