Java 8与SQL：高效获取每日最新货币数据教程

import java.time.LocalDateTime;
import java.time.LocalDate; // 用于日期部分

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 +
               '}';
    }
}

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
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")),
            new Currency(7, "EUR", LocalDateTime.parse("2022-05-18T10:00:00.000")),
            new Currency(8, "EUR", LocalDateTime.parse("2022-05-18T09:00:00.000"))
        );

        // 1. 获取所有货币在每天的最新记录 (无序)
        List<Currency> lastByDate = new ArrayList<>(data
                .stream() // Stream<Currency>
                .collect(Collectors.groupingBy(
                    // 分组键：货币名称 + 日期 (LocalDate)
                    curr -> Arrays.asList(curr.getName(), curr.getLastReceived().toLocalDate()),
                    // 在每个组内，找到lastReceived最大的Currency对象
                    Collectors.collectingAndThen(
                        Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
                        Optional::get // 将Optional<Currency>转换为Currency
                    )
                )) // 结果是一个 Map<List<Object>, Currency>
                .values() // 获取Map中所有的Currency值，形成一个Collection
        );

        System.out.println("所有货币在每天的最新记录 (无序):");
        lastByDate.forEach(System.out::println);

        // 2. 如果需要排序，可以对结果列表进行排序
        lastByDate.sort(
            Comparator.comparing(Currency::getName)
                      .thenComparing(Currency::getLastReceived)
        );
        System.out.println("\n所有货币在每天的最新记录 (按名称和时间排序):");
        lastByDate.forEach(System.out::println);
    }
}

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

public class CurrencyProcessorSpecific {

    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"))
        );

        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                                                     -- 为每行分配一个行号
    FROM Currency c
    WHERE c.name = :currName -- 如果需要筛选特定货币
) tbl
WHERE rn = 1                 -- 只保留每个分区的第一行（即时间最新的记录）
ORDER BY last_received;      -- 最后按时间排序