创新实训（29）——有关管理员端博客和文章信息统计的接口

最新推荐文章于 2022-04-18 18:18:12 发布

日暮途远.

最新推荐文章于 2022-04-18 18:18:12 发布

阅读量202

点赞数

文章标签：项目实训

本文链接：https://blog.csdn.net/baidu_41871794/article/details/106879495

版权

项目实训专栏收录该内容

50 篇文章 4 订阅

订阅专栏

前言

系统的管理员端需要对系统中一些情况，进行数据的分析，所以需要设计一些接口，为前端绘制统计图做准备。这部分的内容，主要没有什么逻辑上的难度，只是需要设计sql语句，使用sql本身的聚合功能，实现对于数据的统计，然后将数据返回前台即可

被浏览文章最多的博主以及文章浏览的数量降序排序

在这里插入图片描述

 /**
     *被浏览文章最多的博主 以及文章浏览的数量 降序排序
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(value = "/getBlogArticleBrowsingCountRecently", method = RequestMethod.GET, produces = "application/json")
    public Response getBlogArticleBrowsingCountRecently(
            @RequestParam(value = "day", required = false, defaultValue = "7") Integer day,
            @RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
            @RequestParam(value = "size", required = false, defaultValue = "20") Integer size) {
        if (page < 0) {
            page = 1;
        }
        if (size < 0) {
            size = 20;
        }
        List<Blog> userRecords = userDataRecordService.selectBlogArticleBrowsingCountRecently(page,size,day);
        return Response.success("获取博客浏览量成功", userRecords);
    }

 /**
     * 被浏览文章最多的博主 以及文章浏览的数量   最近几天
     * @param page
     * @param size
     * @param day
     * @return
     */
    public List<Blog> selectBlogArticleBrowsingCountRecently(int page ,int size,int day)
    {
        int start = (page - 1) * size;
        if (start < 0) {
            start = 0;
        }
        if (size < 0 || size > 100) {
            size = 10;
        }
        return userDataRecordDao.selectBlogArticleBrowsingCountRecently(size,start,day);
    }

sql语句中，需要对用户浏览记录表，博客表，文章表进行自然链接，count出每个文章属于的博客，获取浏览最多的博客，最后根据计数排序即可

where DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(browse_time) 可以返回几天之内的day为参数

    /**
     * 被浏览文章最多的博主 以及文章浏览的数量 最近几天
     * @param limit
     * @param offset
     * @param day
     * @return
     */
    @Select("select C.id , C.title, C.url ,C.feed ,count(C.id) as articleCount " +
            "from user_browsing_history as A ,article as B , blog as C " +
            "where DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(browse_time)  " +
            "and A.article_id = B.id and B.blog_id = C.id group by C.id " +
            "order by articleCount desc " +
            "limit #{limit} offset #{offset} ")
    List<Blog> selectBlogArticleBrowsingCountRecently(@Param("limit") int limit, @Param("offset") int offset,@Param("day") int day);

获取最近七天给文章点赞最多的用户及点赞数量降序排列

在这里插入图片描述

    /**
     * 获取最近七天 给文章点赞最多的用户 及点赞数量  降序排列
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(value = "/getUserLikingCountRecently", method = RequestMethod.GET, produces = "application/json")
    public Response getUserLikingCountRecently(
            @RequestParam(value = "day", required = false, defaultValue = "7") Integer day,
            @RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
            @RequestParam(value = "size", required = false, defaultValue = "20") Integer size) {
        if (page < 0) {
            page = 1;
        }
        if (size < 0) {
            size = 20;
        }
        List<UserRecord> userRecords = userDataRecordService.selectUserLikingCountRecently(page,size,day);
        return Response.success("获取用户点赞量", userRecords);
    }


    /**
     * 获取最近day天 给文章点赞最多的用户 及点赞数量  降序排列
     * @param page
     * @param size
     * @param day
     * @return
     */
    public  List<UserRecord> selectUserLikingCountRecently(int page ,int size,int day)
    {
        int start = (page - 1) * size;
        if (start < 0) {
            start = 0;
        }
        if (size < 0 || size > 100) {
            size = 10;
        }
        return userDataRecordDao.selectUserLikingCountRecently(size,start,day);
    }

sql语句的设计也是做自然连接，然后count计数，最后根据计数排序即可

   /**
     * 获取最近day天 给文章点赞最多的用户 及点赞数量  降序排列
     * @param limit
     * @param offset
     * @param day
     * @return
     */
    @Select("select  B.id , B.name ,B.email ,count(B.id) as count" +
            " from  user_liking as  A, user as B " +
            "where  DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(click_liking_time) " +
            "and A.user_id = B.id  " +
            "group by A.user_id  " +
            "order by count desc " +
            "limit #{limit} offset #{offset}")
    List<UserRecord> selectUserLikingCountRecently(@Param("limit") int limit, @Param("offset") int offset,@Param("day") int day);

获取最近七天浏览文章最多的用户及浏览数量降序排列

在这里插入图片描述

 /**
     * 获取最近七天 浏览文章最多的用户及浏览数量 降序排列
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(value = "/getUserBrowsingCountRecently", method = RequestMethod.GET, produces = "application/json")
    public Response getUserBrowsingCountRecently(
            @RequestParam(value = "day", required = false, defaultValue = "7") Integer day,
            @RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
            @RequestParam(value = "size", required = false, defaultValue = "20") Integer size) {
        if (page < 0) {
            page = 1;
        }
        if (size < 0) {
            size = 20;
        }
        List<UserRecord> userRecords = userDataRecordService.selectUserBrowsingCountRecently(page,size,day);
        return Response.success("获取用户浏览量成功", userRecords);
    }

   /**
     * 获取最近day天 浏览文章最多的用户及浏览数量
     * @param page
     * @param size
     * @param day
     * @return
     */
    public  List<UserRecord> selectUserBrowsingCountRecently(int page ,int size,int day)
    { int start = (page - 1) * size;
        if (start < 0) {
            start = 0;
        }
        if (size < 0 || size > 100) {
            size = 10;
        }
        return userDataRecordDao.selectUserBrowsingCountRecently(size,start,day);
    }

    /**
     * 获取最近day 浏览文章最多的用户及浏览数量
     * @param limit
     * @param offset
     * @param day
     * @return
     */
    @Select("select  B.id , B.name ,B.email ,count(B.id) as count" +
            " from  user_browsing_history as  A, user as B " +
            "where  DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(browse_time) " +
            "and A.user_id = B.id  " +
            "group by A.user_id  " +
            "order by count desc " +
            "limit #{limit} offset #{offset}")
    List<UserRecord> selectUserBrowsingCountRecently(@Param("limit") int limit, @Param("offset") int offset, @Param("day") int day);

获取最近七天某个标签被浏览的次数列表倒序返回

在这里插入图片描述

   /**
     * 获取最近七天某个标签被浏览的次数
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(value = "/getTagBrowsingCountRecently", method = RequestMethod.GET, produces = "application/json")
    public Response getTagBrowsingCountRecently(
            @RequestParam(value = "day", required = false, defaultValue = "7") Integer day,
            @RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
            @RequestParam(value = "size", required = false, defaultValue = "20") Integer size) {
        if (page < 0) {
            page = 1;
        }
        if (size < 0) {
            size = 20;
        }
        List<Tag> tagList = articleTagService.selectTagBrowsingCountRecently(page,size,day);
        return Response.success("获取标签浏览量成功", tagList);
    }

   /**
     * 获取最近几天某个标签被浏览的次数
     * @param page
     * @param size
     * @param day
     * @return
     */
    public  List<Tag> selectTagBrowsingCountRecently(int page, int size,int day)
    {
        int start = (page - 1) * size;
        if (start < 0) {
            start = 0;
        }
        if (size < 0 || size > 100) {
            size = 20;
        }
        return articleTagDao.selectTagBrowsingCountRecently(size,start,day);
    }


    /**
     * 获取最近七天某个标签被浏览的次数
     * @param limit
     * @param offset
     * @return
     */
    @Select("select C.id , C.name,C.description , count(*) as count  " +
            "from article_tag as A , user_browsing_history as B ,tag as C " +
            "where DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(browse_time)  " +
            "and A.article_id = B.article_id and A.tag_id = C.id " +
            "group by A.tag_id " +
            "order by count(*) desc " +
            "limit #{limit} offset #{offset}")

    List<Tag> selectTagBrowsingCountRecently(@Param("limit") int limit,@Param("offset") int offset,@Param("day") int day);

获取文章最近七天的浏览数量降序排序

在这里插入图片描述

    /**
     *获取文章最近七天的浏览数量  降序排序
     * @param page
     * @param size
     * @return
     */
    @RequestMapping(value = "/getArticleBrowsingCountRecently", method = RequestMethod.GET, produces = "application/json")
    public Response getArticleBrowsingCountRecently(
            @RequestParam(value = "day", required = false, defaultValue = "7") Integer day,
            @RequestParam(value = "page", required = false, defaultValue = "1") Integer page,
            @RequestParam(value = "size", required = false, defaultValue = "20") Integer size) {
        if (page < 0) {
            page = 1;
        }
        if (size < 0) {
            size = 20;
        }
        List<ArticleBrowsingCount> articleBrowsingCounts = userDataRecordService.selectArticleBrowsingCountRecently(page,size,day);
        for(ArticleBrowsingCount articleBrowsingCount :articleBrowsingCounts)
        {
            Article article  = getArticleInfo(articleBrowsingCount.getArticleId());
            articleBrowsingCount.setArticle(article);
        }
        return Response.success("获取文章最近点赞数量列表成功", articleBrowsingCounts);
    }

 /**
     * 查询最近day天的博客文章浏览数量列表
     * @param page
     * @param size
     * @param day
     * @return
     */
    public  List<ArticleBrowsingCount> selectArticleBrowsingCountRecently(int page ,int size,int day)
    {
        int start = (page - 1) * size;
        if (start < 0) {
            start = 0;
        }
        if (size < 0 || size > 100) {
            size = 10;
        }
        return userDataRecordDao.selectArticleBrowsingCountRecently(size,start,day);
    }


    /**
     * 查询最近day天的博客文章浏览数量列表
     * @param limit
     * @param offset
     * @param day
     * @return
     */
    @Select("select article_id , count(*) as browsing_count " +
            "from user_browsing_history where " +
            " DATE_SUB(CURDATE(), INTERVAL #{day} DAY) <= date(browse_time) " +
            " group by article_id " +
            " order by browsing_count desc " +
            " limit #{limit} offset #{offset}")
    @Results(id = "ArticleBrowsingCount", value = {
            @Result(property = "articleId", column = "article_id"),
            @Result(property = "browsingCount", column = "browsing_count")})
    List<ArticleBrowsingCount> selectArticleBrowsingCountRecently(@Param("limit") int limit, @Param("offset") int offset,@Param("day") int day);

日暮途远.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
创新实训（29）——有关管理员端博客和文章信息统计的接口

前言系统的管理员端需要对系统中一些情况，进行数据的分析，所以需要设计一些接口，为前端绘制统计图做准备。这部分的内容，主要没有什么逻辑上的难度，只是需要设计sql语句，使用sql本身的聚合功能，实现对于数据的统计，然后将数据返回前台即可被浏览文章最多的博主以及文章浏览的数量降序排序 /** *被浏览文章最多的博主以及文章浏览的数量降序排序 * @param page * @param size * @return */ @RequestM
复制链接

扫一扫