如何用Java Stream API按周/月分组统计查询结果并求和

现有实体与Repository定义 #

StatsEntity 实体类 #

@Entity
@Table(name = "stats", schema = "main")
@Getter @Setter
@AllArgsConstructor @NoArgsConstructor
public class StatsEntity implements Serializable {
    private static final long serialVersionUID = 743865321018464769L;

    @EmbeddedId
    private StatsEntityIds id;

    @Column(length = 500, name = "total_count")
    private Integer totalCount;

    @Column(length = 500, name = "success")
    private Integer success;

    @Column(length = 500, name = "errors")
    private Integer errors;
}

StatsEntityIds 复合主键类 #

@Embeddable
@Getter  @Setter
public class StatsEntityIds implements Serializable {
    private static final long serialVersionUID = 743865321018464768L;

    @Column(length = 255, name = "date")
    private String date;

    @Column(length = 100, name = "workflow")
    private String workflow;

    @Column(length = 100, name = "type")
    private String type;
}

StatsRepository 数据访问接口 #

@Repository
public interface StatsRepository extends JpaRepository<StatsEntity, StatsEntityIds> {
    List<StatsEntity> findByIdWorkflowOrderByIdDate(String workflow);
}

现有按完整日期分组求和逻辑 #

已实现通过Stream API按YYYY-MM-dd格式的日期分组，并对统计字段求和：

return this.statsRepository.findByIdWorkflowOrderByIdDate(request.getWorkflow())
        .parallelStream()
        .map(statEntity -> new StatsDto(
                statEntity.getId().getDate(),
                statEntity.getId().getWorkflow(),
                statEntity.getTotalCount(),
                statEntity.getSuccess(),
                statEntity.getErrors()))
        .collect(Collectors.toMap(
                StatsDto::getDate,
                Function.identity(),
                (val1, val2) -> new StatsDto(
                        val1.getDate(),
                        val1.getWorkflow(),
                        Integer.sum(val1.getTotalCount(), val2.getTotalCount()),
                        Integer.sum(val1.getSuccess(), val2.getSuccess()),
                        Integer.sum(val1.getErrors(), val2.getErrors())
                )
        ))
        .values()
        .stream()
        .sorted(Comparator.comparing(StatsDto::getDate))
        .collect(Collectors.toList());

按周分组求和实现 #

要按周分组，先将字符串日期解析为LocalDate，再生成YYYY-Www格式的周标识（ISO周规则：每周从周一开始，一年第一周至少包含4天）作为分组键：

private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
private static final DateTimeFormatter WEEK_FORMATTER = DateTimeFormatter.ofPattern("yyyy-'W'ww");

public List<StatsDto> getWeeklyStats(StatsRequest request) {
    return this.statsRepository.findByIdWorkflowOrderByIdDate(request.getWorkflow())
            .parallelStream()
            .map(statEntity -> {
                LocalDate date = LocalDate.parse(statEntity.getId().getDate(), DATE_FORMATTER);
                String weekKey = date.format(WEEK_FORMATTER);
                return new StatsDto(
                        weekKey,
                        statEntity.getId().getWorkflow(),
                        statEntity.getTotalCount(),
                        statEntity.getSuccess(),
                        statEntity.getErrors());
            })
            .collect(Collectors.toMap(
                    StatsDto::getDate,
                    Function.identity(),
                    (val1, val2) -> new StatsDto(
                            val1.getDate(),
                            val1.getWorkflow(),
                            Integer.sum(val1.getTotalCount(), val2.getTotalCount()),
                            Integer.sum(val1.getSuccess(), val2.getSuccess()),
                            Integer.sum(val1.getErrors(), val2.getErrors())
                    )
            ))
            .values()
            .stream()
            .sorted(Comparator.comparing(StatsDto::getDate))
            .collect(Collectors.toList());
}

按月分组求和实现 #

按月分组提取YYYY-MM格式的月标识作为分组键：

private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
private static final DateTimeFormatter MONTH_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM");

public List<StatsDto> getMonthlyStats(StatsRequest request) {
    return this.statsRepository.findByIdWorkflowOrderByIdDate(request.getWorkflow())
            .parallelStream()
            .map(statEntity -> {
                LocalDate date = LocalDate.parse(statEntity.getId().getDate(), DATE_FORMATTER);
                String monthKey = date.format(MONTH_FORMATTER);
                return new StatsDto(
                        monthKey,
                        statEntity.getId().getWorkflow(),
                        statEntity.getTotalCount(),
                        statEntity.getSuccess(),
                        statEntity.getErrors());
            })
            .collect(Collectors.toMap(
                    StatsDto::getDate,
                    Function.identity(),
                    (val1, val2) -> new StatsDto(
                            val1.getDate(),
                            val1.getWorkflow(),
                            Integer.sum(val1.getTotalCount(), val2.getTotalCount()),
                            Integer.sum(val1.getSuccess(), val2.getSuccess()),
                            Integer.sum(val1.getErrors(), val2.getErrors())
                    )
            ))
            .values()
            .stream()
            .sorted(Comparator.comparing(StatsDto::getDate))
            .collect(Collectors.toList());
}

性能优化建议 #

如果数据量较大，建议直接在Repository层通过JPQL或原生SQL分组查询，减少内存处理压力。例如按月分组的JPQL（MySQL为例）：

SELECT 
    FUNCTION('DATE_FORMAT', s.id.date, '%Y-%m') as month,
    s.id.workflow,
    SUM(s.totalCount) as totalCount,
    SUM(s.success) as success,
    SUM(s.errors) as errors
FROM StatsEntity s
WHERE s.id.workflow = :workflow
GROUP BY month, s.id.workflow
ORDER BY month

对应Repository方法：

@Query("SELECT new com.yourpackage.StatsDto(" +
       "FUNCTION('DATE_FORMAT', s.id.date, '%Y-%m'), " +
       "s.id.workflow, " +
       "SUM(s.totalCount), " +
       "SUM(s.success), " +
       "SUM(s.errors)) " +
       "FROM StatsEntity s " +
       "WHERE s.id.workflow = :workflow " +
       "GROUP BY FUNCTION('DATE_FORMAT', s.id.date, '%Y-%m'), s.id.workflow " +
       "ORDER BY FUNCTION('DATE_FORMAT', s.id.date, '%Y-%m')")
List<StatsDto> findMonthlyStatsByWorkflow(@Param("workflow") String workflow);

注：不同数据库需替换日期格式化函数，比如PostgreSQL用TO_CHAR。

内容的提问来源于stack exchange，提问作者davidvera