使用数据库中的Java流制作数据透视表

来自数据库行和表的原始数据不能为人类读者提供太多了解。 相反，如果我们对数据执行某种聚合，则人类更有可能看到数据模式
在展示给我们之前。 数据透视表是聚合的一种特定形式，我们可以在其中应用排序，求平均值或求和之类的操作，也可以对列值进行分组。

在本文中，我将展示如何在不编写SQL的情况下，可以用纯Java从数据库计算数据透视表。 您可以轻松地重用和修改本文中的示例，以满足您自己的特定需求。

在以下示例中，我使用了开源Speedment （它是Java Stream ORM）和MySQL的开源Sakila电影数据库内容。 Speedment适用于任何主要的关系数据库类型，例如MySQL，PostgreSQL，Oracle，MariaDB，Microsoft SQL Server，DB2，AS400等。

旋转

我将构造一个Map的Actor对象，并为每个Actor ，相应的List电影，一个特殊的电影分级的Actor出现在这里是为特定的枢轴如何进入一个例子。 Actor可能看起来像口头上表示：

“约翰·多伊（John Doe）参加了9部评级为'PG-13'的电影和4部评级为'R'的电影”。

我们将计算数据库中所有参与者的枢轴值。 Sakila数据库具有此特定应用程序感兴趣的三个表：

1）包含所有影片以及影片评级的“影片”（例如“ PG-13”，“ R”等）。
2）包含（组成）演员的“演员”（例如“ MICHAEL BOLGER”，“ LAURA BRODY”等）。
3）“电影演员”，以多对多的关系将电影和演员联系在一起。

解决方案的第一部分涉及将这三个表连接在一起。 联接是使用Speedment的JoinComponent创建的，可以通过以下方式获得：

// Visit https://github.com/speedment/speedment
// to see how a Speedment app is created. It is easy!
Speedment app = …;

JoinComponent joinComponent = app.getOrThrow(JoinComponent.class);

一旦有了JoinComponent ，就可以开始定义计算关系表所需的Join关系：

Join<Tuple3<FilmActor, Film, Actor>> join = joinComponent
        .from(FilmActorManager.IDENTIFIER)
        .innerJoinOn(Film.FILM_ID).equal(FilmActor.FILM_ID)
        .innerJoinOn(Actor.ACTOR_ID).equal(FilmActor.ACTOR_ID)
        .build(Tuples::of);

build()采用方法引用Tuples::of ，该方法引用将解析为采用三个类型的实体的构造函数； FilmActor ， Film和Actor ，这将创建一个包含这些特定实体的复合不可变Tuple3 。 元组内置于Speedment中。

有了Join对象，我们现在可以使用从Joi​​n对象获得的标准Java Stream创建数据透视图：

Map<Actor, Map<String, Long>> pivot = join.stream()
    .collect(
        groupingBy(
            // Applies Actor as a first classifier
            Tuple3::get2,
            groupingBy(
                // Applies rating as second level classifier
                tu -> tu.get1().getRating().get(),
                counting() // Counts the elements 
                )
            )
        );

现在已经计算了枢轴Map ，我们可以像这样打印其内容：

// pivot keys: Actor, values: Map<String, Long>
pivot.forEach((k, v) -> { 
    System.out.format(
        "%22s  %5s %n",
        k.getFirstName() + " " + k.getLastName(),
        V
    );
});

这将产生以下输出：

MICHAEL BOLGER  {PG-13=9, R=3, NC-17=6, PG=4, G=8} 
           LAURA BRODY  {PG-13=8, R=3, NC-17=6, PG=6, G=3} 
     CAMERON ZELLWEGER  {PG-13=8, R=2, NC-17=3, PG=15, G=5}
...

任务完成！ 在上面的代码中，方法Tuple3::get2将从元组（ Actor ）中检索第三个元素，而方法tu.get1()将从元组（ Film ）中检索第二个元素。

Speedment将自动从Java渲染SQL代码，并将结果转换为Java Stream。 如果启用流日志记录，我们可以确切看到如何呈现SQL：

SELECT 
    A.`actor_id`,A.`film_id`,A.`last_update`, 
    B.`film_id`,B.`title`,B.`description`,
    B.`release_year`,B.`language_id`,B.`original_language_id`,
    B.`rental_duration`,B.`rental_rate`,B.`length`,
    B.`replacement_cost`,B.`rating`,B.`special_features`,
    B.`last_update`, C.`actor_id`,C.`first_name`,
    C.`last_name`,C.`last_update`
FROM 
    `sakila`.`film_actor` AS A
INNER JOIN 
    `sakila`.`film` AS B ON (B.`film_id` = A.`film_id`) 
INNER JOIN 
    `sakila`.`actor` AS C ON (C.`actor_id` = A.`actor_id`)

加入自定义元组

正如我们在上面的示例中所注意到的，由于在连接阶段仅将FilmActor对象用于将Film和Actor实体链接在一起，因此在Stream中没有实际使用FilmActor对象。 此外，通用Tuple3有一般get0() get1()和get2()是没有说他们装的是什么东西的方法。

所有这些都可以通过定义我们自己的称为ActorRating的自定义“元组”来ActorRating如下所示：

private static class ActorRating {
    private final Actor actor;
    private final String rating;

    public ActorRating(FilmActor fa, Film film, Actor actor) {
        // fa is not used. See below why
        this.actor = actor;
        this.rating = film.getRating().get();
    }

    public Actor actor() {
        return actor;
    }

    public String rating() {
        return rating;
    }

}

当使用build()方法build() Join对象时，我们可以提供一个自定义构造函数，该构造函数要应用于数据库的传入实体。 这是我们将要使用的功能，如下所示：

Join<ActorRating> join = joinComponent
    .from(FilmActorManager.IDENTIFIER)
    .innerJoinOn(Film.FILM_ID).equal(FilmActor.FILM_ID)
    .innerJoinOn(Actor.ACTOR_ID).equal(FilmActor.ACTOR_ID)
    .build(ActorRating::new); // Use a custom constructor

Map<Actor, Map<String, Long>> pivot = join.stream()
    .collect(
        groupingBy(
            ActorRating::actor,
            groupingBy(
                ActorRating::rating,
                counting()
            )
         )
    );

在此示例中，我们证明了带有构造函数的类（方法参考ActorRating:new被解析为new ActorRating(fa, actor, film) ），该FilmActor函数只是完全丢弃了链接的FilmActor对象。 该类还为其属性提供了更好的名称，这使代码更具可读性。 使用自定义ActorRating类的解决方案将产生与第一个示例完全相同的输出结果，但使用时看起来要好得多。 我认为在大多数情况下，与使用通用元组相比，编写自定义元组值得付出额外的精力。

使用平行旋转

Speedment的一件很酷的事情是，它支持开箱即用的Stream方法parallel() 。 因此，如果您的服务器具有许多CPU，则在运行数据库查询和联接时可以利用所有这些CPU内核。 这就是并行枢轴的样子：

Map<Actor, Map<String, Long>> pivot = join.stream()
    .parallel()  // Make our Stream parallel
    .collect(
        groupingBy(
            ActorRating::actor,
            groupingBy(
                ActorRating::rating,
                counting()
            )
         )
    );

我们只需要添加一行代码即可进行并行聚合。 当我们达到1024个元素时，将启动默认的并行拆分策略。 因此，并行枢转将仅在大于此值的表或联接上进行。 应该注意的是，Sakila数据库仅包含1000部电影，因此我们必须在更大的数据库上运行代码才能真正受益于并行性。

试试看！

在本文中，我们展示了如何在不编写任何SQL代码的情况下，就可以使用Java从数据库计算数据透视表。 访问GitHub上的 Speedment开源以了解更多信息。

在《用户指南》中阅读有关其他功能的更多信息。

翻译自: https://www.javacodegeeks.com/2018/05/making-pivot-tables-with-java-streams-from-databases.html