Java聚合查询案例如何编写

本文目录导读：

1. 使用Java Stream API进行聚合查询
2. 使用Spring Data JPA进行数据库聚合查询
3. 使用MyBatis进行聚合查询
4. 使用Elasticsearch进行聚合查询
5. 高级聚合查询示例
6. 性能优化建议

我将为您介绍Java中实现聚合查询的几种常见案例，包括使用Stream API、数据库查询和框架工具。

使用Java Stream API进行聚合查询

基础数据模型

// 数据模型
public class Order {
    private Long id;
    private String customerName;
    private String product;
    private Integer quantity;
    private BigDecimal price;
    private String category;
    private LocalDate orderDate;
    // getters/setters/constructors
    public Order(Long id, String customerName, String product, 
                 Integer quantity, BigDecimal price, 
                 String category, LocalDate orderDate) {
        this.id = id;
        this.customerName = customerName;
        this.product = product;
        this.quantity = quantity;
        this.price = price;
        this.category = category;
        this.orderDate = orderDate;
    }
}

示例数据

public class AggregationExample {
    private static List<Order> getSampleOrders() {
        List<Order> orders = new ArrayList<>();
        orders.add(new Order(1L, "张三", "笔记本电脑", 2, new BigDecimal("5999.99"), "电子产品", LocalDate.of(2024, 1, 15)));
        orders.add(new Order(2L, "李四", "手机", 5, new BigDecimal("3999.99"), "电子产品", LocalDate.of(2024, 1, 20)));
        orders.add(new Order(3L, "张三", "书籍", 10, new BigDecimal("29.99"), "图书", LocalDate.of(2024, 2, 5)));
        orders.add(new Order(4L, "王五", "T恤", 20, new BigDecimal("89.99"), "服装", LocalDate.of(2024, 2, 10)));
        orders.add(new Order(5L, "李四", "耳机", 3, new BigDecimal("299.99"), "电子产品", LocalDate.of(2024, 3, 1)));
        orders.add(new Order(6L, "张三", "平板电脑", 1, new BigDecimal("3999.99"), "电子产品", LocalDate.of(2024, 3, 15)));
        return orders;
    }
}

各种聚合查询案例

public class StreamAggregationQueries {
    public static void main(String[] args) {
        List<Order> orders = getSampleOrders();
        // 案例1: 按客户分组统计总消费金额
        Map<String, BigDecimal> customerTotalSpending = orders.stream()
            .collect(Collectors.groupingBy(
                Order::getCustomerName,
                Collectors.mapping(
                    order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())),
                    Collectors.reducing(BigDecimal.ZERO, BigDecimal::add)
                )
            ));
        System.out.println("客户总消费金额: " + customerTotalSpending);
        // 案例2: 按类别统计总销量
        Map<String, Integer> categoryTotalQuantity = orders.stream()
            .collect(Collectors.groupingBy(
                Order::getCategory,
                Collectors.summingInt(Order::getQuantity)
            ));
        System.out.println("类别总销量: " + categoryTotalQuantity);
        // 案例3: 多级分组 - 按客户和类别统计
        Map<String, Map<String, List<Order>>> customerCategoryOrders = orders.stream()
            .collect(Collectors.groupingBy(
                Order::getCustomerName,
                Collectors.groupingBy(Order::getCategory)
            ));
        System.out.println("客户-类别分组: " + customerCategoryOrders);
        // 案例4: 统计每个客户购买的产品数量总和
        Map<String, Integer> customerProductCount = orders.stream()
            .collect(Collectors.groupingBy(
                Order::getCustomerName,
                Collectors.summingInt(Order::getQuantity)
            ));
        System.out.println("客户购买产品总数: " + customerProductCount);
        // 案例5: 计算平均每个订单的金额
        Double averageOrderValue = orders.stream()
            .mapToDouble(order -> order.getPrice().doubleValue() * order.getQuantity())
            .average()
            .orElse(0.0);
        System.out.println("平均订单金额: " + averageOrderValue);
        // 案例6: 找出最贵的订单
        Optional<Map.Entry<String, BigDecimal>> maxOrder = orders.stream()
            .collect(Collectors.toMap(
                Order::getCustomerName,
                order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())),
                BigDecimal::add
            ))
            .entrySet()
            .stream()
            .max(Map.Entry.comparingByValue());
        maxOrder.ifPresent(entry -> 
            System.out.println("消费最高的客户: " + entry.getKey() + ", 金额: " + entry.getValue()));
        // 案例7: 统计每个类别的订单数
        Map<String, Long> categoryOrderCount = orders.stream()
            .collect(Collectors.groupingBy(
                Order::getCategory,
                Collectors.counting()
            ));
        System.out.println("类别订单数: " + categoryOrderCount);
        // 案例8: 按月份统计销售额
        Map<Integer, BigDecimal> monthlySales = orders.stream()
            .collect(Collectors.groupingBy(
                order -> order.getOrderDate().getMonthValue(),
                Collectors.mapping(
                    order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())),
                    Collectors.reducing(BigDecimal.ZERO, BigDecimal::add)
                )
            ));
        System.out.println("月销售额: " + monthlySales);
    }
}

使用Spring Data JPA进行数据库聚合查询

Entity类

@Entity
@Table(name = "orders")
public class OrderEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "customer_name")
    private String customerName;
    private String product;
    private Integer quantity;
    private BigDecimal price;
    private String category;
    @Column(name = "order_date")
    private LocalDate orderDate;
    // getters/setters
}

Repository

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, Long> {
    // 1. 按客户分组统计总消费
    @Query("SELECT new com.example.dto.CustomerSummary(o.customerName, " +
           "SUM(o.price * o.quantity)) " +
           "FROM OrderEntity o " +
           "GROUP BY o.customerName")
    List<CustomerSummary> getCustomerTotalSpending();
    // 2. 按类别统计总销量
    @Query("SELECT o.category, SUM(o.quantity) as totalQuantity " +
           "FROM OrderEntity o " +
           "GROUP BY o.category")
    List<Object[]> getCategoryTotalQuantity();
    // 3. 统计每个类别的订单数和平均价格
    @Query("SELECT o.category, COUNT(o), AVG(o.price), SUM(o.quantity) " +
           "FROM OrderEntity o " +
           "GROUP BY o.category")
    List<Object[]> getCategoryStatistics();
    // 4. 按月份统计销售额
    @Query("SELECT MONTH(o.orderDate), YEAR(o.orderDate), SUM(o.price * o.quantity) " +
           "FROM OrderEntity o " +
           "GROUP BY YEAR(o.orderDate), MONTH(o.orderDate) " +
           "ORDER BY YEAR(o.orderDate), MONTH(o.orderDate)")
    List<Object[]> getMonthlySales();
    // 5. 使用Criteria API进行动态聚合
    List<OrderEntity> findByCustomerNameAndCategory(String customerName, String category);
}

DTO类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CustomerSummary {
    private String customerName;
    private BigDecimal totalAmount;
}

使用MyBatis进行聚合查询

Mapper接口

@Mapper
public interface OrderMapper {
    // 1. 按客户分组统计
    @Select("SELECT customer_name, SUM(price * quantity) as total_amount " +
            "FROM orders " +
            "GROUP BY customer_name")
    @Results({
        @Result(property = "customerName", column = "customer_name"),
        @Result(property = "totalAmount", column = "total_amount")
    })
    List<CustomerSummary> getCustomerTotalSpending();
    // 2. 复杂聚合查询
    @Select("SELECT category, " +
            "COUNT(*) as order_count, " +
            "SUM(quantity) as total_quantity, " +
            "AVG(price) as avg_price, " +
            "MAX(price * quantity) as max_order_amount " +
            "FROM orders " +
            "WHERE order_date BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY category " +
            "HAVING COUNT(*) > #{minOrderCount}")
    List<CategoryStats> getCategoryStats(@Param("startDate") LocalDate startDate,
                                         @Param("endDate") LocalDate endDate,
                                         @Param("minOrderCount") Long minOrderCount);
    // 3. XML映射查询
    List<MonthlySales> getMonthlySalesByCustomer(@Param("customerName") String customerName);
}

XML映射文件

<!-- OrderMapper.xml -->
<mapper namespace="com.example.mapper.OrderMapper">
    <resultMap id="MonthlySalesMap" type="com.example.dto.MonthlySales">
        <result property="year" column="year"/>
        <result property="month" column="month"/>
        <result property="totalAmount" column="total_amount"/>
        <result property="orderCount" column="order_count"/>
    </resultMap>
    <select id="getMonthlySalesByCustomer" resultMap="MonthlySalesMap">
        SELECT 
            YEAR(order_date) as year,
            MONTH(order_date) as month,
            SUM(price * quantity) as total_amount,
            COUNT(*) as order_count
        FROM orders
        WHERE customer_name = #{customerName}
        GROUP BY YEAR(order_date), MONTH(order_date)
        ORDER BY year, month
    </select>
</mapper>

使用Elasticsearch进行聚合查询

@Service
public class ElasticsearchAggregationService {
    @Autowired
    private ElasticsearchRestTemplate elasticsearchTemplate;
    public void performAggregationQueries() {
        // 创建查询条件
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        // 1. 按类别统计总销售额
        TermsAggregationBuilder categoryAgg = AggregationBuilders
            .terms("category_stats")
            .field("category.keyword")
            .subAggregation(
                AggregationBuilders.sum("total_sales")
                    .field("price")
            );
        queryBuilder.addAggregation(categoryAgg);
        // 2. 日期直方图聚合
        DateHistogramAggregationBuilder dateAgg = AggregationBuilders
            .dateHistogram("sales_over_time")
            .field("orderDate")
            .calendarInterval(DateHistogramInterval.MONTH)
            .subAggregation(
                AggregationBuilders.sum("monthly_revenue")
                    .field("price")
            );
        queryBuilder.addAggregation(dateAgg);
        // 3. 复杂嵌套聚合
        TermsAggregationBuilder nestedAgg = AggregationBuilders
            .terms("by_customer")
            .field("customerName.keyword")
            .size(10)
            .subAggregation(
                AggregationBuilders.terms("by_category")
                    .field("category.keyword")
                    .subAggregation(
                        AggregationBuilders.stats("sales_stats")
                            .field("price")
                    )
            );
        queryBuilder.addAggregation(nestedAgg);
        // 执行查询
        SearchHits<OrderDocument> searchHits = elasticsearchTemplate.search(
            queryBuilder.build(), OrderDocument.class);
        // 处理聚合结果
        Aggregations aggregations = searchHits.getAggregations();
    }
}

高级聚合查询示例

public class AdvancedAggregationQueries {
    // 1. 分区计算
    public static Map<String, BigDecimal> calculateTopNCustomerSales(List<Order> orders, int n) {
        return orders.stream()
            .collect(Collectors.groupingBy(
                Order::getCustomerName,
                Collectors.mapping(
                    order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())),
                    Collectors.reducing(BigDecimal.ZERO, BigDecimal::add)
                )
            ))
            .entrySet()
            .stream()
            .sorted(Map.Entry.<String, BigDecimal>comparingByValue().reversed())
            .limit(n)
            .collect(Collectors.toMap(
                Map.Entry::getKey,
                Map.Entry::getValue,
                (e1, e2) -> e1,
                LinkedHashMap::new
            ));
    }
    // 2. 滑动窗口聚合
    public static Map<Integer, BigDecimal> calculateMovingAverage(List<Order> orders, int windowSize) {
        return IntStream.range(0, orders.size())
            .boxed()
            .collect(Collectors.toMap(
                i -> i,
                i -> orders.subList(
                    Math.max(0, i - windowSize + 1), 
                    i + 1
                ).stream()
                    .map(order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())))
                    .reduce(BigDecimal.ZERO, BigDecimal::add)
                    .divide(BigDecimal.valueOf(Math.min(windowSize, i + 1)), RoundingMode.HALF_UP)
            ));
    }
    // 3. Top-N统计
    public static <T> Map<String, List<T>> getTopNPerGroup(
            List<Order> orders, 
            Function<Order, String> classifier,
            Comparator<Order> comparator,
            int n) {
        return orders.stream()
            .collect(Collectors.groupingBy(
                classifier,
                Collectors.collectingAndThen(
                    Collectors.toList(),
                    list -> list.stream()
                        .sorted(comparator.reversed())
                        .limit(n)
                        .collect(Collectors.toList())
                )
            ));
    }
    // 4. 使用并行流进行高性能聚合
    public static Map<String, BigDecimal> parallelAggregation(List<Order> orders) {
        return orders.parallelStream()
            .collect(Collectors.groupingByConcurrent(
                Order::getCategory,
                Collectors.mapping(
                    order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())),
                    Collectors.reducing(BigDecimal.ZERO, BigDecimal::add)
                )
            ));
    }
}

性能优化建议

public class AggregationOptimization {
    // 1. 使用原始类型避免装箱拆箱
    public static Map<String, Double> optimizedAggregation(List<Order> orders) {
        return orders.stream()
            .collect(Collectors.groupingBy(
                Order::getCategory,
                Collectors.summarizingDouble(order -> 
                    order.getPrice().doubleValue() * order.getQuantity())
            ))
            .entrySet()
            .stream()
            .collect(Collectors.toMap(
                Map.Entry::getKey,
                entry -> entry.getValue().getSum()
            ));
    }
    // 2. 预聚合减少数据量
    public static Map<String, BigDecimal> preAggregateAndCalculate(List<Order> orders) {
        // 先按客户分组预聚合
        Map<String, List<Order>> customerGroups = orders.stream()
            .collect(Collectors.groupingBy(Order::getCustomerName));
        // 再计算每个客户的统计信息
        Map<String, BigDecimal> result = new HashMap<>();
        customerGroups.forEach((customer, customerOrders) -> {
            BigDecimal total = customerOrders.stream()
                .map(order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())))
                .reduce(BigDecimal.ZERO, BigDecimal::add);
            result.put(customer, total);
        });
        return result;
    }
    // 3. 使用Stream的专有操作
    public static SummaryStatistics aggregateWithStatistics(List<Order> orders) {
        DoubleSummaryStatistics stats = orders.stream()
            .mapToDouble(order -> order.getPrice().multiply(BigDecimal.valueOf(order.getQuantity())).doubleValue())
            .summaryStatistics();
        return new SummaryStatistics(
            stats.getCount(),
            BigDecimal.valueOf(stats.getSum()),
            BigDecimal.valueOf(stats.getAverage()),
            BigDecimal.valueOf(stats.getMin()),
            BigDecimal.valueOf(stats.getMax())
        );
    }
}
@Data
@AllArgsConstructor
class SummaryStatistics {
    private long count;
    private BigDecimal sum;
    private BigDecimal average;
    private BigDecimal min;
    private BigDecimal max;
}

这些案例涵盖了Java中聚合查询的多种实现方式：

1. Stream API - 适合内存中的小型数据集合
2. JPA/MyBatis - 关系型数据库查询
3. Elasticsearch - 全文搜索和聚合分析
4. 并行处理 - 大数据量的性能优化

根据您的具体需求选择合适的实现方式，记得考虑数据量大小、实时性要求和系统复杂度等因素。