Redis-多字段排序-排名设计

约定不等于承诺〃 2022-10-02 01:45 311阅读 0赞

Redis的sorted set集合提供了一种有序的集合，集合中的每个元素都会关联一个double类型的分数。redis正是通过分数来为集合中的成员进行从小到大的排序。  
使用场景通常是作为排名。  
在上一篇说了[选择题型题目表设计][Link 1]，接着这篇说下用户回答题目后的排行设计，这里使用redis的有序集合进行排名，排名条件是正确率高，耗时少。

有序集合添加元素时需设定一个分数，这里两个排序条件不同，一个按照正确率倒序，一个按照耗时升序，所以需要设计一个分数计算算法。

这里通过位运算进行计算分数，将正确率\*100后左移32位，再用Integer.MAX\_VALUE-耗时(毫秒)，将两者结果进行或运算，这样，正确率越高，耗时越少，分数越高，再反向排名即可。

demo使用springboot框架  
redis客户端使用redisson；  
orm使用mybatis

model

package com.example.demo.model;
    
    import lombok.Data;
    
    import java.util.Date;
    
    @Data
    public class Question {
        private Long id;
    
        private String title;
    
        private Date createAt;
    
        private Long createBy;
    
        private Integer status;
    
        private Date updateAt;
    
        private Long updateBy;
    
    }
    
    package com.example.demo.model;
    
    import lombok.Data;
    
    import java.util.Date;
    
    @Data
    public class QuestionAnswer {
        private Long id;
    
        private Long userId;
    
        private Long questionId;
    
        private Long optionId;
    
        private Long historyId;
    
        private Integer status;
    
        private Date updateAt;
    
        private Long updateBy;
    
        private Date createAt;
    
        private Long createBy;
    
    }
    
    package com.example.demo.model;
    
    import lombok.Data;
    
    import java.io.Serializable;
    import java.util.Date;
    
    @Data
    public class QuestionAnswerHistory implements Serializable {
        private Long id;
    
        private Long userId;
    
        private Boolean answerFinish;
    
        private Double correctRate;
    
        private Integer timeConsumed;
    
        private Integer status;
    
        private Date createAt;
    
        private Long createBy;
    
        private Integer createAtMillisecond;
    
        private Date updateAt;
    
        private Long updateBy;
    
        private Integer updateAtMillisecond;
    
        private Integer timeConsumedMillisecond;
    
    }
    
    package com.example.demo.model;
    
    import lombok.Data;
    
    import java.util.Date;
    
    @Data
    public class QuestionOption {
        private Long id;
    
        private Long questionId;
    
        private String content;
    
        private Boolean isAnswer;
    
        private Integer status;
    
        private Date createAt;
    
        private Long createBy;
    
        private Date updateAt;
    
        private Long updateBy;
    
    }

mapper

package com.example.demo.mapper;
    
    import com.example.demo.model.QuestionAnswerHistory;
    import com.example.demo.model.QuestionAnswerHistoryExample;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.ResultMap;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    public interface QuestionAnswerHistoryMapper {
        @Select("select t.id,t.user_id,t.correct_rate,t.time_consumed,t.time_consumed_millisecond from (" +
                "select id,user_id,correct_rate,time_consumed,time_consumed_millisecond from question_answer_history where answer_finish = true and status = #{status}  " +
                " order by correct_rate desc, time_consumed asc,time_consumed_millisecond asc limit 100000 ) t group by t.user_id " +
                " order by correct_rate desc, time_consumed asc,time_consumed_millisecond asc")
        @ResultMap("BaseResultMap")
        List<QuestionAnswerHistory> queryTop(@Param("status") int status);
    }

QuestionAnswerHistoryService

package com.example.demo.service;
    
    import com.example.demo.common.Constant;
    import com.example.demo.mapper.QuestionAnswerHistoryMapper;
    import com.example.demo.model.QuestionAnswerHistory;
    import org.apache.commons.lang3.RandomUtils;
    import org.redisson.api.RScoredSortedSet;
    import org.redisson.api.RedissonClient;
    import org.redisson.client.protocol.ScoredEntry;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.math.BigDecimal;
    import java.util.*;
    
    /**
     * @author wzx
     * @time 2019/6/16
     */
    @Service
    public class QuestionAnswerHistoryService {
    
        @Autowired
        private QuestionAnswerHistoryMapper historyMapper;
    
        @Autowired
        private RedissonClient client;
    
        private static final String QUESTION_TOP_KEY = "QUESTION_TOP_KEY";
    
        private RScoredSortedSet<Object> initRanking() {
            RScoredSortedSet<Object> sortedSet = client.getScoredSortedSet(QUESTION_TOP_KEY);
            if (!sortedSet.isExists()) {
                //初始化排名
                List<QuestionAnswerHistory> histories = historyMapper.queryTop(Constant.GenerictStatus.NORMAL);
                Map<Object, Double> data = new HashMap<>();
                for (QuestionAnswerHistory history : histories) {
                    data.put(history, calcScore(history.getCorrectRate(), history.getTimeConsumed(), history.getTimeConsumedMillisecond()));
                }
                sortedSet.addAll(data);
            }
            return sortedSet;
        }
    
        public List<QuestionAnswerHistory> getRanking() {
            RScoredSortedSet sortedSet = this.initRanking();
            Collection entries = sortedSet.entryRangeReversed(0, 20);
            List<QuestionAnswerHistory> results = new ArrayList<>();
            for (Object entry : entries) {
                QuestionAnswerHistory history = (QuestionAnswerHistory) ((ScoredEntry<Object>)entry).getValue();
                results.add(history);
            }
            return results;
        }
    
        public void updateRank(QuestionAnswerHistory history) {
            Long userId = 25L;
            //旧的最佳成绩
            QuestionAnswerHistory oldHistory = historyMapper.getBestByUserId(userId);
            //生成新的答题记录
            historyMapper.insert(history);
            //新的最佳成绩
            QuestionAnswerHistory newHistory = historyMapper.getBestByUserId(userId);
            System.out.println(history.getId());
            RScoredSortedSet<Object> sortedSet = this.initRanking();
            Integer userRank = sortedSet.revRank(oldHistory);
            if (newHistory != null && newHistory.getId().equals(history.getId())) {
                //刷新排名,删除旧成绩
                if (oldHistory != null) {
                    sortedSet.remove(oldHistory);
                    System.out.println("删除旧成绩");
                }
                System.out.println("刷新排名");
                sortedSet.add(calcScore(newHistory.getCorrectRate(), newHistory.getTimeConsumed(), newHistory.getTimeConsumedMillisecond()), newHistory);
            }
            if (userRank == null) {
                //新的排名,直接加上
                sortedSet.add(calcScore(history.getCorrectRate(), history.getTimeConsumed(), history.getTimeConsumedMillisecond()), history);
                System.out.println("新的排名,直接加上");
            }
        }
    
        private double calcScore(double correctRate, int timeConsumed, int timeConsumedMillisecond) {
            //为了精确
            BigDecimal bigDecimal = new BigDecimal(String.valueOf(correctRate));
            return (bigDecimal.multiply(new BigDecimal(100)).longValue()) << 32 | Integer.MAX_VALUE - (timeConsumed * 1000 + timeConsumedMillisecond);
        }
    
    }

测试用例：

package com.example.demo;
    
    import com.example.demo.common.Constant;
    import com.example.demo.mapper.QuestionAnswerHistoryMapper;
    import com.example.demo.model.QuestionAnswerHistory;
    import com.example.demo.service.QuestionAnswerHistoryService;
    import com.example.demo.thread.BaseTest;
    import org.apache.commons.lang3.RandomUtils;
    import org.junit.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    
    import java.math.BigDecimal;
    import java.util.List;
    
    /**
     * @author wzx
     * @time 2019/6/16
     */
    public class QuestionAnswerHistoryTest extends BaseTest {
    
        @Autowired
        private QuestionAnswerHistoryMapper historyMapper;
    
        @Autowired
        private QuestionAnswerHistoryService historyService;
    
        @Test
        public void test() {
            for (int i = 1; i < 101; i++) {
                QuestionAnswerHistory history = new QuestionAnswerHistory();
                history.setUserId((long) i);
                history.setAnswerFinish(true);
                BigDecimal bigDecimal = BigDecimal.valueOf(RandomUtils.nextDouble(0, 1))
                        .setScale(2, BigDecimal.ROUND_HALF_UP);
                history.setCorrectRate(bigDecimal.doubleValue());
                history.setTimeConsumed(RandomUtils.nextInt(15, 50));
                history.setTimeConsumedMillisecond(RandomUtils.nextInt(0, 1000));
                history.setStatus(Constant.GenerictStatus.NORMAL);
                historyMapper.insert(history);
            }
        }
    
        @Test
        public void queryRanking() {
            List<QuestionAnswerHistory> ranking = historyService.getRanking();
            for (QuestionAnswerHistory history : ranking) {
                System.out.println(history.getUserId() + "\t" + history.getCorrectRate() + "\t" + history.getTimeConsumed() +
                        "\t" + history.getTimeConsumedMillisecond());
            }
        }
    
        @Test
        public void updateRank() {
            Long userId = 25L;
            //生成新的答题记录
            QuestionAnswerHistory history = new QuestionAnswerHistory();
            history.setUserId(userId);
            history.setAnswerFinish(true);
            BigDecimal bigDecimal = BigDecimal.valueOf(RandomUtils.nextDouble(0, 1))
                    .setScale(2, BigDecimal.ROUND_HALF_UP);
            history.setCorrectRate(bigDecimal.doubleValue());
            history.setTimeConsumed(RandomUtils.nextInt(15, 50));
            history.setTimeConsumedMillisecond(RandomUtils.nextInt(0, 1000));
            history.setStatus(Constant.GenerictStatus.NORMAL);
            historyService.updateRank(history);
        }
    }

输入结果：

id 正确率	耗时
    74	0.94	38	151
    37	0.93	16	802
    97	0.92	31	475
    63	0.92	36	74
    35	0.92	39	516
    85	0.92	39	525
    17	0.9		19	821
    55	0.9		35	268
    64	0.89	37	39
    19	0.88	43	350
    49	0.86	25	373
    78	0.86	42	296
    76	0.84	19	399
    21	0.84	38	365
    54	0.83	26	7
    70	0.79	30	213
    20	0.78	18	112
    46	0.78	24	789
    27	0.76	23	195
    33	0.76	29	414
    7	0.75	20	661

其中，更新排名时需要判断成绩是否为最好，否则不更新。

[Link 1]: https://blog.csdn.net/wang704987562/article/details/92427317