问题

https://stackoverflow.com/questions/66229242/java-streams-collect-excel-csv-to-a-list-filtering-based-on-the-sum-of-a-column

Suppose we have an excel spreadsheet that looks like:

StatusCount FirstName LastName ID

1 Tod Mahones 122145

0 Tod Mahones 122145

1 Tod Mahones 122145

-1 Tod Mahones 122145

1 Ronny Jackson 149333

1 Eliza Cho 351995

-1 Eliza Cho 351995

1 James Potter 884214

1 James Potter 884214

-1 Peter Walker 900248

1 Zaid Grits 993213

How can I be able to gather to a list of only the IDs of the people whose status count is a sum greater than 0, and if it is 0 or less then discard it. So in the excel spreadsheet above, the list in java should look like:

List<Integer> = [122145, 149333, 884214, 993213]

Update (adding in what I tried so far):

List<Integer> = csvFile.stream()

.map(Arrays::asList)

.filter(column -> column.get(0).equalsIgnoreCase("1")

.map(column -> column.get(3))

.map(Integer::parseInt)

.sorted()

.collect(Collectors.toList());

I collected them just by status counts of 1 but that isn't the right process, it should be to sum up the status count for each person or ID (I guess it is good to find any dupes) and if its > 0 then collect to the list, if not then discard.

Update 2: I forgot to mention that the csv file is brought into java as a List<String[]> where the List contains the rows of the csv and the String[] is the contents of the rows, so it would be like:

[[1, Tod, Mahones, 122145],[0, Tod, Mahones, 122145], [1, Tod, Mahones, 122145], ...]

解答

对ID 列有序的 csv 文件，按 ID 列有序分组并对 StatusCount 列求和，找出求和的值大于 0 的那些 ID。用 Java 实现需要熟练使用 Stream 中的 Collectors 工具类，否则很难进行计算。

用Java 下的开源包 SPL 很容易写，只要 1 句：

SPL 提供了 JDBC 供 Java 调用，把上面的脚本存为 filter.splx，在 Java 中以存储过程的方式调用脚本文件：

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st = con.prepareCall("call filter()");
st.execute();

…

SPL 源代码：https://github.com/SPLWare/esProc

问答搜集