SQL 和 Java 代码，分别实现分组排序

 

1、需求说明

 

需求：
       同一个用户可以在多个加油站加油，有个表会记录会员在每个油站的累计加油次数，现在需要统计每个会员，加油最多的前五个加油站。
 
表设计：

CREATE TABLE `oil_record` (
	`cust_id`  bigint(20) UNSIGNED NOT NULL COMMENT '客户ID' ,
	`org_code`  varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 
	                                      NOT NULL DEFAULT '' COMMENT '油站编码' ,
	`oil_count`  int(11) NOT NULL DEFAULT 0 COMMENT '累计加油消费次数' ,
	`create_time`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
	`update_time`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间' ,
	PRIMARY KEY (`cust_id`, `org_code`)
)

 

2、SQL 脚本实现

 

	SELECT
		cust_id,oil_count,rank,org_code
	FROM
		(SELECT *,
			IF (
				@p = cust_id,
				CASE WHEN @s := oil_count THEN @r :=@r + 1 END,
			  @r := 1
			) AS rank,@p := cust_id ,@s := oil_count
		FROM
			oil_record,
			(SELECT @p := NULL ,@s := NULL ,@r := 0) r
		ORDER BY
			cust_id,oil_count DESC
		) s
	WHERE
		s.rank < 6;
	

 

3、Java 代码实现

 
实体类：

import lombok.Data;

import java.util.Date;

@Data
public class OilRecordVO{

    private String custId;
    
    private String orgCode;

    private Integer oilCount;

    private Date createTime;

    private Date updateTime;
}

 
这里用的是 mybatis，提供xml 文件做参考，省略 dao 层代码

    <select id="getOilRecords" resultType="com.zir.vo.OilRecordVO">
        SELECT
            dd.cust_id,
            dd.org_code,
            dd.oil_count
        FROM
            oil_record dd
    </select>
    

 
实现类：

// 采用 jdk8 新特性实现
public List<OilRecordVO> getGroupTop5List() {
    List<OilRecordVO> oilRecordVOList  = new ArrayList<>();
    List<OilRecordVO> records =  oilRecordDao.getOilRecords();
    long startTime =  System.currentTimeMillis();
    // 分组
    Map<String,List<OilRecordVO>> map= records.stream()
            .collect(Collectors.groupingBy(OilRecordVO::getCustId));
    // 排序
    map.forEach((k,v) -> {
        List<OilRecordVO> vos = v.stream().sorted(
                Comparator.comparing(OilRecordVO::getOilCount).reversed()
        ).collect(Collectors.toList());
        // 选前五
        for (int t = 0; t<vos.size(); t++) {
            if (t<5) 
             oilRecordVOList.add(vos.get(t));
        }
    });
    long endTime =  System.currentTimeMillis();
    long timeDif = (endTime - startTime);
    log.info("求分组排序耗时 ： " + timeDif + " ms");
    return oilRecordVOList;
}
    

 
       数据量不大时，建议使用 SQL 脚本的方法实现，数据量达到百万级的表，建议使用 Java 代码实现。
 
 
 
 
 
 
 
 
.