How to simulate rank/over function in Java to obtain group ranking

Background

##202302Class one for three yearsXie Chunhua 136.00202302三级二级Feng Shijie129.00202302Class Two of Three Years马 Gongcheng130.00202302 Class 2, Grade 3Wei Pianpian136.00

Examination batch	Class	Name	中文
202302	Class Three Years	张小明	130.00
202302	三Class one for every year	王二小	128.00

Assume we have the above data, and now there is a need to count the Chinese language sheets of each student How will you achieve the ranking of subject scores in the class and the ranking of the whole year?

It is easy for us to think of the implementation of rank() over()

over() is an analytical function that can be used in conjunction with rank(), dense_rank(), and row_number().

The syntax is as follows:

RANK() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
dense_rank() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

Explanation: partition by is used to group the result set. If not specified, it treats the entire result set as a group.

• The rank() function is mainly used for sorting and gives a sequence number. The same sequence number is given to the sorted and parallel data, and the ranking of the tie is cleared.

• dense_rank() has the same function as rank(), the difference is that the ranking of the tie is not vacated

• row_number() function is It is used in order, regardless of parallelism

rank The result is 1,2,2,4 dense_rank The result is 1,2,2,3 row_number The result is 1,2 ,3,4

In actual applications, there will be many situations where data is accessed from other external systems and the amount of data is not large, so it is even more important to use Java code to achieve group ranking. convenient.

Detailed design and implementation

Sort definition class OrderBy

public class OrderBy {
    private String orderByEL;
    /**
     * 是否升序
     */
    private boolean ascend;

    public OrderBy(){
        //默认升序
        this.ascend = true;
    }

    public String orderByEL(){
        return this.orderByEL;
    }

    public OrderBy orderByEL(String orderByEL){
        this.orderByEL = orderByEL;
        return this;
    }

    public OrderBy ascend(boolean ascend){
        this.ascend = ascend;
        return this;
    }

    public boolean ascend(){
        return this.ascend;
    }
}

This class defines the following attributes:

• sorted fileld

• Whether the order is ascending

Get ranking method

This method is defined as follows:

<T> void rankOver(List<T> dataList, String[] partitionByFields, List<OrderBy> orderByList, String resultField, int rankType);

This method provides 5 input parameters:

dataList sorted data set

partitionByFields array of grouping fields

orderByList sorting field collection

resultField field where ranking results are stored

rankType Ranking method

• 1: tie is not considered (row_number result is 1,2,3,4)

• 2 : Consider the tie, leave out the rankings occupied by the tie (rank results are 1,2,2,4)

• 3: Consider the tie, do not leave out the rankings occupied by the tie (dense_rank 1,2,2,3)

The specific implementation of this method is as follows

    public static <T> void rankOver(List<T> dataList, String[] partitionByFields, List<OrderBy> orderByList, String resultField, int rankType) {
        if (CollectionUtils.isEmpty(orderByList)) {
            return;
        }
        //STEP_01 剔除掉不参与排名的数据
        List<T> tempList = new ArrayList<>();
        for (T data : dataList) {
            boolean part = true;
            for (OrderBy rptOrderBy : orderByList) {
                Object o1 = executeSpEL(rptOrderBy.orderByEL(), data);
                if (o1 == null) {
                    //参与排序的值为null的话则不参与排名
                    part = false;
                    break;
                }
            }
            if (part) {
                tempList.add(data);
            }
        }
        if (CollectionUtils.isEmpty(tempList)) {
            return;
        }
        //STEP_02 分组
        Map<String, List<T>> groupMap = group(tempList, null, partitionByFields);
        for (List<T> groupDataList : groupMap.values()) {
            order(orderByList, groupDataList);
            if (rankType == 1) {
                int rank = 1;
                for (T temp : groupDataList) {
                    setFieldValue(temp, resultField, rank);
                    rank++;
                }
            } else {
                int prevRank = Integer.MIN_VALUE;
                int size = groupDataList.size();
                for (int i = 0; i < size; i++) {
                    T current = groupDataList.get(i);
                    if (i == 0) {
                        //第一名
                        setFieldValue(current, resultField, 1);
                        prevRank = 1;
                    } else {
                        T prev = groupDataList.get(i - 1);
                        boolean sameRankWithPrev = true;//并列排名
                        for (OrderBy rptOrderBy : orderByList) {
                            Object o1 = executeSpEL(rptOrderBy.orderByEL(), current);
                            Object o2 = executeSpEL(rptOrderBy.orderByEL(), prev);
                            if (!o1.equals(o2)) {
                                sameRankWithPrev = false;
                                break;
                            }
                        }
                        if (sameRankWithPrev) {
                            setFieldValue(current, resultField, getFieldValue(prev, resultField));
                            if (rankType == 2) {
                                ++prevRank;
                            }
                        } else {
                            setFieldValue(current, resultField, ++prevRank);
                        }
                    }
                }
            }
        }
    }

Use case

Define a student class:

public class Student {
    private String batch;
    private String banji;
    private String name;
    private Double yuwen;
    //extra
    private Integer rank1;
    private Integer rank2;
    
    public Student(String batch, String banji, String name, Double yuwen) {
        this.batch = batch;
        this.banji = banji;
        this.name = name;
        this.yuwen = yuwen;
    }
}

We write a method that returns the following data:

public List<Student> getDataList() {
    List<Student> dataList = new ArrayList<>();
    dataList.add(new Student("202302", "三年一班", "张小明", 130.0));
    dataList.add(new Student("202302", "三年一班", "王二小", 128.0));
    dataList.add(new Student("202302", "三年一班", "谢春花", 136.0));
    dataList.add(new Student("202302", "三年二班", "冯世杰", 129.0));
    dataList.add(new Student("202302", "三年二班", "马功成", 130.0));
    dataList.add(new Student("202302", "三年二班", "魏翩翩", 136.0));
    return dataList;
}

Obtain the class ranking and year ranking of students' Chinese scores. The rankings are tied and the ranked positions occupied by the tie are cleared.

List<Student> dataList = getDataList();
List<OrderBy> orderByList = new ArrayList<>();
orderByList.add(new OrderBy().orderByEL("yuwen").ascend(false));
//获取全校排名
DataProcessUtil.rankOver(dataList, new String[]{"batch"}, orderByList, "rank1", 2);
//获取班级排名
DataProcessUtil.rankOver(dataList, new String[]{"batch", "banji"}, orderByList, "rank2", 2);
log("语文单科成绩排名情况如下：");

Map<String, List<Student>> groupMap = DataProcessUtil.group(dataList, null, new String[]{"batch"});
for (Map.Entry<String, List<Student>> entry : groupMap.entrySet()) {
    log("考试批次：" + entry.getKey());
    for (Student s : entry.getValue()) {
        log(String.format("班级：%s 学生：%s 语文成绩：%s 班级排名：%s 全校排名：%s", s.getBanji(), s.getName(), s.getYuwen(), s.getRank2(), s.getRank1()));
    }
    log("");
}

The results are as follows:

The Chinese subject score ranking is as follows:

Examination batch: 202302
Class: Class 1, Grade 3 Student: Zhang Xiaoming Chinese score: 130.0 Class rank: 2 Whole school ranking: 3
Class: Class 1, Grade 3 Student: Wang Er Xiao Chinese language score: 128.0 Class rank: 3 Whole school ranking: 6
Class: Class 1, Grade 3 Student: Xie Chunhua Chinese score : 136.0 Class rank: 1 School ranking: 1
Class: Grade 2 student in Grade 3: Feng Shijie Chinese score: 129.0 Class rank: 3 Whole school ranking: 5
Class: Grade 2 student in Grade 3: Ma Gongcheng Chinese score: 130.0 Class Rank: 2 School Ranking: 3
Class: Grade 2 Class 2 Student: Wei Pianpian Chinese Score: 136.0 Class Rank: 1 School Ranking: 1

You can see that there are two in the school rankings Two tied for first place and two tied for third place, and the tied positions 2 and 4

were left vacant.

The above is the detailed content of How to simulate rank/over function in Java to obtain group ranking. For more information, please follow other related articles on the PHP Chinese website!