短链管理之今日历史访问统计

# 汇总统计

public interface ShortLinkMapper extends BaseMapper<ShortLinkDO> {

    /**
     * 短链接访问统计自增
     */
    @Update("update t_link set total_pv = total_pv + #{totalPv}, total_uv = total_uv + #{totalUv}, total_uip = total_uip + #{totalUip} where gid = #{gid} and full_short_url = #{fullShortUrl}")
    void incrementStats(
            @Param("gid") String gid,
            @Param("fullShortUrl") String fullShortUrl,
            @Param("totalPv") Integer totalPv,
            @Param("totalUv") Integer totalUv,
            @Param("totalUip") Integer totalUip
    );

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ShortLinkServiceImpl#shortLinkStats：

baseMapper.incrementStats(gid, fullShortUrl, 1, uvFirstFlag.get() ? 1 : 0, uipFirstFlag ? 1 : 0);

# 今日统计

public interface LinkStatsTodayMapper extends BaseMapper<LinkStatsTodayDO> {

    /**
     * 记录今日统计监控数据
     */
    @Insert("INSERT INTO t_link_stats_today (full_short_url, gid, date,  today_uv, today_pv, today_uip, create_time, update_time, del_flag) " +
            "VALUES( #{linkTodayStats.fullShortUrl}, #{linkTodayStats.gid}, #{linkTodayStats.date}, #{linkTodayStats.todayUv}, #{linkTodayStats.todayPv}, #{linkTodayStats.todayUip}, NOW(), NOW(), 0) " +
            "ON DUPLICATE KEY UPDATE today_uv = today_uv +  #{linkTodayStats.todayUv}, today_pv = today_pv +  #{linkTodayStats.todayPv}, today_uip = today_uip +  #{linkTodayStats.todayUip};")
    void shortLinkTodayState(@Param("linkTodayStats") LinkStatsTodayDO linkStatsTodayDO);

}

1
2
3
4
5
6
7
8
9
10
11

ShortLinkServiceImpl#shortLinkStats：

LinkStatsTodayDO linkStatsTodayDO = LinkStatsTodayDO.builder()
        .todayPv(1)
        .todayUv(uvFirstFlag.get() ? 1 : 0)
        .todayUip(uipFirstFlag ? 1 : 0)
        .gid(gid)
        .fullShortUrl(fullShortUrl)
        .date(new Date())
        .build();
linkStatsTodayMapper.shortLinkTodayState(linkStatsTodayDO);

1
2
3
4
5
6
7
8
9

# 监控数据排序

mybatis-plus配置：

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:mapper/*.xml

1
2
3
4

分页查询短链接：LinkMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.Nreal.shortlink.project.dao.mapper.ShortLinkMapper">

    <!-- 分页查询短链接 -->
    <select id="pageLink" parameterType="com.Nreal.shortlink.project.dto.req.ShortLinkPageReqDTO"
            resultType="com.Nreal.shortlink.project.dao.entity.ShortLinkDO">
        SELECT t.*,
        COALESCE(s.today_pv, 0) AS todayPv,
        COALESCE(s.today_uv, 0) AS todayUv,
        COALESCE(s.today_uip, 0) AS todayUip
        FROM t_link t
        LEFT JOIN t_link_stats_today s ON t.gid = s.gid
        AND t.full_short_url = s.full_short_url
        AND s.date = CURDATE()
        WHERE t.gid = #{gid}
        AND t.enable_status = 0
        AND t.del_flag = 0
        <choose>
            <when test="orderTag == 'todayPv'">
                ORDER BY todayPv DESC
            </when>
            <when test="orderTag == 'todayUv'">
                ORDER BY todayUv DESC
            </when>
            <when test="orderTag == 'todayUip'">
                ORDER BY todayUip DESC
            </when>
            <when test="orderTag == 'totalPv'">
                ORDER BY t.total_uv DESC
            </when>
            <when test="orderTag == 'totalUv'">
                ORDER BY t.total_pv DESC
            </when>
            <when test="orderTag == 'totalUip'">
                ORDER BY t.total_uip DESC
            </when>
            <otherwise>
                ORDER BY t.create_time DESC
            </otherwise>
        </choose>
    </select>
</mapper>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

public interface ShortLinkMapper extends BaseMapper<ShortLinkDO> {
    /**
     * 分页统计短链接
     */
    IPage<ShortLinkDO> pageLink(ShortLinkPageReqDTO requestParam);

}

1
2
3
4
5
6
7

ShortLinkServiceImpl#pageShortLink：

@Override
public IPage<ShortLinkPageRespDTO> pageShortLink(ShortLinkPageReqDTO requestParam) {
    IPage<ShortLinkDO> resultPage = baseMapper.pageLink(requestParam);
    return resultPage.convert(each -> {
        ShortLinkPageRespDTO result = BeanUtil.toBean(each, ShortLinkPageRespDTO.class);
        result.setDomain("http://" + result.getDomain());
        return result;
    });
}

1
2
3
4
5
6
7
8
9

绑定表解决ShardingSphere笛卡尔积：

bindingTables:
  - t_link, t_link_stats_today

1
2

← 短链接跳转回收站管理→