Java stream to get average value based on multiple tables-CodePudding

I have 3 related tables; user, movie and score:

*User:

id   | name  |
--------------
1    | Hans  |
2    | Carry |

*Movie:

id   | name  | budget |
----------------------
101  | Mov 1 | 200    |
102  | Mov 2 | 500    |
103  | Mov 2 | 300    |

*Score:

user_id   | movie_id  | score  |
--------------------------------
1         | 101       | 7      |
2         | 101       | 8      |
1         | 102       | 6      |
2         | 102       | 9      |

I am trying to find the 5 movies whose average score is max and then sort these movies based on their budget. So, is that possible using Java Stream? How can I get this kind of list?

Or if it is not possible, should I use Query and retrieve data from projection?

CodePudding user response：

Try this.

public static void main(String[] args) {
    record Score(int userId, int movieId, int score) {}
    record Movie(int id, String name, int budget) {}

    List<Movie> movies = List.of(
        new Movie(101, "Mov 1", 200),
        new Movie(102, "Mov 2", 500),
        new Movie(103, "Mov 3", 300));
    List<Score> scores = List.of(
        new Score(1, 101, 6),
        new Score(2, 101, 8),
        new Score(1, 102, 6),
        new Score(2, 102, 9));

    Map<Integer, Movie> movieMap = movies.stream()
        .collect(Collectors.toMap(Movie::id, Function.identity()));

    List<Movie> top5 = scores.stream()
        .collect(Collectors.groupingBy(
            Score::movieId, Collectors.averagingDouble(Score::score)))
        .entrySet().stream()
        .sorted(Collections.reverseOrder(Entry.comparingByValue()))
        .limit(5)
        .map(e -> movieMap.get(e.getKey()))
        .sorted(Collections.reverseOrder(Comparator.comparing(Movie::budget)))
        .toList();
    top5.stream().forEach(System.out::println);
}

output:

Movie[id=102, name=Mov 2, budget=500]
Movie[id=101, name=Mov 1, budget=200]