粉丝关系表-浅究

最新推荐文章于 2021-09-01 00:06:04 发布

ohoy

最新推荐文章于 2021-09-01 00:06:04 发布

阅读量850

点赞数

分类专栏：案例应用

本文链接：https://blog.csdn.net/weixin_40598838/article/details/111599852

版权

案例应用专栏收录该内容

1 篇文章

订阅专栏

需求

在这里插入图片描述
及

有这样一个需求，需要展示用户昨日的粉丝数据，包括用户的取关数、关注数、用户活跃数、粉丝总数，并且还能按时间查询用户每日的粉丝数据

建表

在这里插入图片描述

很纠结的一个问题是用户的昨日粉丝数据如何计算的问题，如果说用户状态为取关、关注、活跃三种状态，用户取关我取更新表，那我怎么知道昨日用户有多少个取关数和多少个关注数，这个问题纠结了很久，在参考网上一些资料后，发现有人增加了一个UPDATE_TIME字段，顺着往下想

用户如果更新了状态就记录状态的更新时间，那么这样我在查昨日取关数更新时间查就好了，

那么带来这样一个问题，原来是粉丝，取关了，再关注，算不算涨粉，认为是不算涨粉，因为你原来就是粉丝，取关了再关注也还是我的粉丝，粉丝数没有变，那么就不应当算做涨粉，

这样昨日关注数按创建时间查就是涨粉数了

昨日粉丝数据

在这里插入图片描述

考虑这样一种情况,如果当天的这条数据为0呐，那么在前端也就是返回空了，这种

在这里插入图片描述

这样只需要在返回实体类中置0就可以了，这样当数据库中返回值为空时，它的默认也是0，不为空时修改一下就可以了
在这里插入图片描述
将一些由共性的代码进行了抽取和复用

public YesterDayFansChange yesTerDayFansData3(FansDetailFront fansDetailFront) {

        LocalDate localNow = LocalDate.now();
        localNow = localNow.minusDays(1);

        YesterDayFansChange yesterDayFansChange = new YesterDayFansChange();

        YesterDayFansChangeDto yesterDayFansChangeDto = new YesterDayFansChangeDto();

        //计算掉粉数
        fansDetailFront = fansDetailFrontChange("0", localNow, null,fansDetailFront);

        yesterDayFansChangeDto.setUnfollow(fansDetailMapper.yesTerDayFansData2(fansDetailFront).getTotalNum());

        //计算涨粉数
        fansDetailFront = fansDetailFrontChange("1", null, localNow,fansDetailFront);

        yesterDayFansChange.setFollow(fansDetailMapper.yesTerDayFansData2(fansDetailFront).getTotalNum());

        //计算活跃人数
        fansDetailFront = fansDetailFrontChange("2", null, localNow,fansDetailFront);

        yesterDayFansChange.setLineFans(fansDetailMapper.yesTerDayFansData2(fansDetailFront).getTotalNum());

        //计算粉丝数
        fansDetailFront = fansDetailFrontChange("2", null, null,fansDetailFront);

        YesterDayFansData yesterDayFollowTotal = fansDetailMapper.yesTerDayFansData2(fansDetailFront);
        yesterDayFansChange.setFans(yesterDayFollowTotal.getTotalNum());

        return yesterDayFansChange;
    }

    private FansDetailFront fansDetailFrontChange(String status,LocalDate updateTime,LocalDate createTime,FansDetailFront fansDetailFront){

        fansDetailFront.setUpdateTime(updateTime);
        fansDetailFront.setCreateTime(createTime);
        fansDetailFront.setFollowStatusCode(status);
        return  fansDetailFront;

    }

sql:

   <select id="yesTerDayFansData2" parameterType="com.bill.entity.FansDetailFront" resultType="com.bill.entity.YesterDayFansData">
     SELECT
     MAX(FOLLOWER_STATUS_CODE) AS FOLLOWER_STATUS_CODE,
     MAX(FOLLOWER_STATUS_NAME) AS FOLLOWER_STATUS_NAME,
     COUNT(1) AS TOTAL_NUM
     FROM FANS_DETAIL
     <where>
         USER_ID =#{userId}
         AND FOLLOWER_STATUS_CODE=#{followStatusCode}
         <if test="createTime != null ">
             AND TO_CHAR(CREATE_TIME,'yyyy-MM-dd')=TO_CHAR(#{createTime},'yyyy-MM-dd')
         </if>
         <if test="updateTime != null ">
             AND TO_CHAR(UPDATE_TIME,'yyyy-MM-dd')=TO_CHAR(#{updateTime},'yyyy-MM-dd')
         </if>
     </where>
    </select>

粉丝数据列表

大概逻辑和昨日粉丝数据差不多，只是在SQL端按时期做了数据的补全，

在这里插入图片描述
JAVA:

 LocalDate localNow = LocalDate.now();

        List<YesterDayFansChange> yesterDayFansChanges = new ArrayList<>();

        //计算掉粉数
        fansDetailFront.setUpdateTime(localNow);
        fansDetailFront.setFollowStatusCode("0");

        List<YesterDayFansData> yesterDayUnfollow = fansDetailMapper.fansChart(fansDetailFront);

        //计算涨粉数
        fansDetailFront.setUpdateTime(null);
        fansDetailFront.setCreateTime(localNow);
        fansDetailFront.setFollowStatusCode("1");

        List<YesterDayFansData> yesterDayFollow = fansDetailMapper.fansChart(fansDetailFront);

        //计算活跃人数
        fansDetailFront.setCreateTime(localNow);
        fansDetailFront.setFollowStatusCode("2");

        List<YesterDayFansData> yesterDayActive = fansDetailMapper.fansChart(fansDetailFront);

        //计算粉丝数
        fansDetailFront.setCreateTime(null);
        fansDetailFront.setUpdateTime(null);
        fansDetailFront.setFollowStatusCode("1");
        YesterDayFansData yesterDayFollowTotal = fansDetailMapper.yesTerDayFansData2(fansDetailFront);

        YesterDayFansChange yesterDayFansChange = null;
        for (int i = 0; i < yesterDayUnfollow.size(); i++) {

            yesterDayFansChange = new YesterDayFansChange();
            YesterDayFansData unfollow = yesterDayUnfollow.get(i);
            YesterDayFansData follow = yesterDayFollow.get(i);
            YesterDayFansData active = yesterDayActive.get(i);

            yesterDayFansChange.setFans(yesterDayFollowTotal.getTotalNum());
            yesterDayFansChange.setUnfollow(unfollow.getTotalNum());
            yesterDayFansChange.setFollow(follow.getTotalNum());
            yesterDayFansChange.setLineFans(active.getTotalNum());
            yesterDayFansChange.setDate(follow.getCreateTime());

            yesterDayFansChanges.add(yesterDayFansChange);


        }

        return yesterDayFansChanges;

SQL:

SELECT NVL(a.FOLLOWER_STATUS_CODE, 0) AS FOLLOWER_STATUS_CODE,NVL(a.FOLLOWER_STATUS_NAME, DECODE(#{followStatusCode}, '0', '取关','1','关注','2','活跃')) AS FOLLOWER_STATUS_NAME,NVL(a.TOTAL_NUM, 0) AS TOTAL_NUM,b.CREATE_TIME
        FROM
        (
        SELECT
        MAX( FOLLOWER_STATUS_CODE ) AS FOLLOWER_STATUS_CODE,
        MAX( FOLLOWER_STATUS_NAME ) AS FOLLOWER_STATUS_NAME,
        <if test="createTime != null ">
            MAX(TO_CHAR(CREATE_TIME,'yyyy-MM-dd')) AS CREATE_TIME,
        </if>
        <if test="updateTime != null ">
            MAX(TO_CHAR(UPDATE_TIME,'yyyy-MM-dd')) AS CREATE_TIME,
        </if>
        COUNT( 1 ) AS TOTAL_NUM
        FROM FANS_DETAIL
        <where>
            USER_ID =#{userId}
            AND FOLLOWER_STATUS_CODE =#{followStatusCode}
            <if test="createTime != null ">
                AND TO_CHAR(CREATE_TIME,'yyyy-MM-dd') BETWEEN #{startTimeFront} AND  #{endTimeFront}
            </if>
            <if test="updateTime != null ">
                AND TO_CHAR(UPDATE_TIME,'yyyy-MM-dd') BETWEEN #{startTimeFront} AND  #{endTimeFront}
            </if>
        </where>

        )a

        RIGHT JOIN

        (

        SELECT
        TO_CHAR(TO_DATE(#{startTimeFront}, 'yyyy-MM-dd') + ROWNUM -1 ,'yyyy-MM-dd') AS CREATE_TIME
        FROM DUAL
        CONNECT BY ROWNUM <![CDATA[<=]]>  TO_DATE(#{endTimeFront}, 'yyyy-MM-dd')+1 - TO_DATE(#{startTimeFront}, 'yyyy-MM-dd')

        )b ON a.CREATE_TIME=b.CREATE_TIME
        ORDER BY b.CREATE_TIME

查粉丝列表

--在粉丝表中根据ID查出用户关联的粉丝,用粉丝ID查出在用户表中查出粉丝数据
SELECT
u.USER_ID,
u.USER_NAME,
u.USER_TX_URL
FROM USER_INFO u,FANS_DETAIL f
WHERE f.USER_ID='1' AND f.FOLLOWER_USER_ID = u.USER_ID

SELECT
USER_ID,
USER_NAME
FROM USER_INFO
WHERE USER_ID IN
(
SELECT
FOLLOWER_USER_ID
FROM FANS_DETAIL
WHERE USER_ID='1'
)