最近在重构代码,原来的一个查询语句是这么写的。
select *
from (select t.product_id,
t.rate_id,
t.year
t.value,
to_char(t.time, ‘‘yyyyMMddhh24miss’’) time,
row_number() over(partition by t.product_id,t.rate_id,t.year order by t.time desc) rank
from table t
where t.id = :id)
where rank = 1;
这个语句用了Orcal的函数。
查询时,当product_id、rate_id、year三个条件相同时,如果有多条数据,取time最大的那条数据。
由于这次重构要更换数据库,这个函数肯定不能用了。所以针对数据的分组、排序、去重要放到代码中实现;退一步说,把这种逻辑放到sql中实现,不太好,应该由代码去实现。
参考了这位大佬的博客后可以实现分组(product_id、rate_id、year)过滤的功能,但是不能实现过滤时通过time字段排序,获取time最大的那条数据。
因为如果某条数据的组合key(type-name)已经在map里包含时,就不会记录该条数据,相当于被过滤了。
想到他的distinct的实现方式,即如果组合key有多条数据,那么永远只取第一条,后面的不会记录(相当于被过滤)。
那么我可以先sort,再distinct。
@Data
public class Dish{
private String name; //菜的名称
private Boolean vegetaian; //是否为素
private Integer calories; //卡路里
private Type type; //类型(肉 鱼 其他)
private String expireTime; //过期时间(yyyyMMddHH24miss)
public Dish() {
}
public Dish(String name, Boolean vegetaian, Integer calories, Type type,String expireTime) {
this.name = name;
this.vegetaian = vegetaian;
this.calories = calories;
this.type = type;
this.expireTime = expireTime;
}
public enum Type {MEAT, FISH, OTHER} //肉 鱼 其他
}
public class DishList {
//数据准备
public static List<Dish> getDish1List() {
return Arrays.asList(
new Dish("pork", false, 800, Dish.Type.MEAT,"20200918143856"),
new Dish("beef", false, 750, Dish.Type.MEAT,"20200930143856"),
new Dish("pork", false, 800, Dish.Type.MEAT,"20200918143856"),
new Dish("beef", false, 800, Dish.Type.MEAT,"20201018143856"),
new Dish("beef", false, 701, Dish.Type.MEAT,"20200918153856"),
new Dish("chicken", false, 400, Dish.Type.MEAT,"20200918143856"),
new Dish("french fries", true, 530, Dish.Type.OTHER,"20200918143856"),
new Dish("rice", true, 350, Dish.Type.OTHER,"20200918143856"),
new Dish("season fruit", true, 120, Dish.Type.OTHER,"20200918143856"),
new Dish("pizza", true, 550, Dish.Type.OTHER,"20200918143856"),
new Dish("prawns", false, 300, Dish.Type.FISH,"20200918143856"),
new Dish("salmon", false, 450, Dish.Type.FISH,"20200918143856")
);
}
}
//通用过滤函数
public static Predicate distinctByKey(Function<? super T, ?> function) {
Map<Object, Boolean> seen = new ConcurrentHashMap<>();
return t -> seen.putIfAbsent(function.apply(t), Boolean.TRUE) == null;
}
//自定义分组过滤条件
public static Function<Dish, String> distinctByKeyFunction() {
return (Dish dish) -> dish.getType() + “-” + dish.getName();
}
public static void main(String[] args) {
Comparator<Dish> byNameASC = Comparator.comparing(Dish::getName);
Comparator<Dish> byTypeASC = Comparator.comparing(Dish::getType);
Comparator<Dish> byExpireTimeASC = Comparator.comparing(Dish::getExpireTime).reversed();
Comparator<Dish> finalComparator = byNameASC.thenComparing(byTypeASC).thenComparing(byExpireTimeASC);
List<Dish> dish1List = DishList.getDish1List();
List<Dish> distinctDishList = dish1List.stream().sorted(finalComparator).filter(distinctByKey(distinctByKeyFunction())).collect(Collectors.toList());
System.out.println(distinctDishList);
}
运行结果:
从运行结果的图中,可以看到原来的List中的三条name=“beef”,且type=“MEAT”数据,在经过过滤后只剩下一条数据,且是过期时间最大的那条
Dish(name=beef, vegetaian=false, calories=800, type=MEAT, expireTime=20201018143856)
成功实现了功能替换。