聚合、子查询与 CTE：把统计问题拆成可验证的阶段

import java.util.*;
public class Main {
    record Order(long user,String status,Long amount) {}
    record Summary(long user,long rows,long nonNull,Long sum) {}
    static class Acc { long rows,nonNull,sum; }
    static List<Summary> aggregate(List<Order> orders) {
        Map<Long,Acc> groups=new HashMap<>();
        for (Order order:orders) {
            if (!"PAID".equals(order.status())) continue;
            Acc a=groups.computeIfAbsent(order.user(),k->new Acc());
            a.rows++;
            // COUNT(amount)与SUM都忽略NULL，COUNT(*)仍计此行。
            if (order.amount()!=null) {
                a.sum=Math.addExact(a.sum,order.amount());
                a.nonNull++;
            }
        }
        List<Summary> result=new ArrayList<>();
        for (var entry:groups.entrySet()) {
            Acc a=entry.getValue();
            // 全空组保留NULL，与真实零金额组区分。
            result.add(new Summary(entry.getKey(),a.rows,a.nonNull,a.nonNull==0?null:a.sum));
        }
        result.sort(Comparator.comparingLong(Summary::user));
        return List.copyOf(result);
    }
    static List<Summary> having(List<Summary> groups,long threshold) {
        // NULL比较不能得到TRUE，因此不进入阈值结果。
        return groups.stream().filter(s->s.sum()!=null&&s.sum()>=threshold).toList();
    }
    static void eq(Object expected,Object actual) {
        if (!Objects.equals(expected,actual)) throw new AssertionError(actual);
    }
    public static void main(String[] args) {
        List<Summary> groups=aggregate(List.of(
            new Order(1,"PAID",50L),new Order(1,"PAID",null),
            new Order(1,"CANCELLED",999L),new Order(2,"PAID",null),
            new Order(3,"PAID",0L)));
        eq(List.of(new Summary(1,2,1,50L),new Summary(2,1,0,null),new Summary(3,1,1,0L)),groups);
        eq(List.of(groups.get(0),groups.get(2)),having(groups,0));
        eq(List.of(groups.get(0)),having(groups,50));
        eq(List.of(),having(groups,51));
        eq(List.of(),aggregate(List.of()));
        try {
            aggregate(List.of(new Order(1,"PAID",Long.MAX_VALUE),new Order(1,"PAID",1L)));
            throw new AssertionError("overflow");
        } catch (ArithmeticException expected) {}
        System.out.println("PASS 6 aggregation tests");
    }
}