使用Java 8 Stream API和SQL查询按日期分组获取最新记录教程

import java.time.LocalDateTime;
import java.util.Objects;

class Currency {
    private Integer id;
    private String name;
    private LocalDateTime lastReceived;

    public Currency(Integer id, String name, LocalDateTime lastReceived) {
        this.id = id;
        this.name = name;
        this.lastReceived = lastReceived;
    }

    public Integer getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public LocalDateTime getLastReceived() {
        return lastReceived;
    }

    @Override
    public String toString() {
        return "Currency{" +
               "id=" + id +
               ", name='" + name + '\'' +
               ", lastReceived=" + lastReceived +
               '}';
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Currency currency = (Currency) o;
        return Objects.equals(id, currency.id) && Objects.equals(name, currency.name) && Objects.equals(lastReceived, currency.lastReceived);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, lastReceived);
    }
}

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;

public class CurrencyProcessor {

    public static void main(String[] args) {
        List<Currency> data = Arrays.asList(
                new Currency(1, "USD", LocalDateTime.parse("2022-05-18T09:04:01.545")),
                new Currency(2, "USD", LocalDateTime.parse("2022-05-18T08:04:01.545")),
                new Currency(3, "USD", LocalDateTime.parse("2022-05-19T08:04:01.545")),
                new Currency(4, "USD", LocalDateTime.parse("2022-05-20T08:04:01.545")),
                new Currency(5, "USD", LocalDateTime.parse("2022-05-20T11:04:01.545")),
                new Currency(6, "BUSD", LocalDateTime.parse("2022-05-18T08:04:01.545"))
        );

        // 1. 获取所有货币类型在各日期的最新记录
        List<Currency> lastByDateForAllCurrencies = new ArrayList<>(data
                .stream()
                .collect(Collectors.groupingBy(
                        // 复合键：货币名称 + 日期
                        curr -> Arrays.asList(curr.getName(), curr.getLastReceived().toLocalDate()),
                        // 在每个分组中找到lastReceived最大的记录
                        Collectors.collectingAndThen(
                                Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
                                Optional::get // 将Optional<Currency>转换为Currency
                        )
                ))
                .values()); // 获取Map中的所有值

        System.out.println("所有货币类型在各日期的最新记录 (未排序):");
        lastByDateForAllCurrencies.forEach(System.out::println);

        // 如果需要排序，可以对结果列表进行排序
        lastByDateForAllCurrencies.sort(
                Comparator.comparing(Currency::getLastReceived)
                          .thenComparing(Currency::getName)
        );
        System.out.println("\n所有货币类型在各日期的最新记录 (已排序):");
        lastByDateForAllCurrencies.forEach(System.out::println);
    }
}

        // 2. 获取特定货币类型（例如USD）在各日期的最新记录
        String targetCurrency = "USD";
        List<Currency> lastUSDByDate = new ArrayList<>(data
                .stream()
                .filter(curr -> targetCurrency.equalsIgnoreCase(curr.getName())) // 过滤特定货币
                .collect(Collectors.groupingBy(
                        curr -> curr.getLastReceived().toLocalDate(), // 简化分组键为日期
                        Collectors.collectingAndThen(
                                Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
                                Optional::get
                        )
                ))
                .values());

        // 对结果进行排序
        lastUSDByDate.sort(Comparator.comparing(Currency::getLastReceived));

        System.out.println("\n特定货币 (" + targetCurrency + ") 在各日期的最新记录:");
        lastUSDByDate.forEach(System.out::println);

SELECT id, name, last_received
FROM (
    SELECT c.*,
           ROW_NUMBER() OVER (
               PARTITION BY name, to_char(last_received, 'yyyy-MM-dd')
               ORDER BY last_received DESC
           ) AS rn -- 为每个(name, date)分区内的记录按last_received降序编号
    FROM Currency c
    WHERE c.name = :currName -- 可选：如果只需要特定货币
) tbl
WHERE rn = 1 -- 选取每个分区中编号为1（即最新）的记录
ORDER BY last_received; -- 按日期排序最终结果