DoytoQuery聚合查询方案示例详解

DoytoQuery 聚合查询 DoytoQuery 查询 2022-12-27 18:12:59 619人浏览泡泡鱼

Python 官方文档：入门教程 => 点击学习

摘要

目录1. 引言2. 聚合查询映射2.1. 前缀映射2.2. 分组聚合2.3. HAVING2.4. 动态查询2.5. 查询接口定义3. 完整示例4. 总结1. 引言聚合查询

1. 引言

聚合查询是数据库提供的另一种常用的用于数据的统计和计算的查询功能，它通过提供一系列聚合函数来汇总来自多个行的信息。

DoytoQuery采用字段前缀映射的方式来将字段名映射为聚合函数，再配合@GroupBy注解，Having接口以及Query对象，完成整条聚合查询语句的映射。

2. 聚合查询映射

2.1. 前缀映射

聚合函数是聚合查询的核心功能，常用的聚合函数有以下几种：

count
sum
max
min
avg
first
last
stddev_pop
stddev_samp
stddev
addToSet
push

使用聚合函数查询出来的数据也需要占用一列，这一列数据也需要映射到POJO对象的一个字段。我们可以利用这一特性，在定义POJO对象的字段时，采用聚合函数关键字加上大驼峰格式的列名即可。这个字段既可以用于映射查询语句，又可以保存返回的数据，一举两得。比如我们想统计分数列score的平均值，我们将字段名定义为avgScore，在构造查询语句时，这个字段就会被映射为avg(score) AS avgScore，依此类推。

2.2. 分组聚合

聚合查询中少不了针对部分表列进行分组聚合，这些用于分组的表列通常也需要查询出来，于是便可以将这些表列也定义到POJO对象中，同时需要与其他的聚合字段加以区别。DoytoQuery中采用添加@GroupBy注解的方式进行区分。

@Target(FIELD)
@Retention(RUNTIME)
public @interface GroupBy {
}

示例：

@Entity(name = "t_score")
private class ScoreGroupByStudentView {
    @GroupBy
    private Long studentId;
    private Double avgScore;
}

上述对象会被映射为如下sql语句：

SELECT student_id AS studentId, avg(score) AS avgScore FROM t_score GROUP BY student_id

2.3. HAVING

SQL语句使用HAVING子句针对聚合后的数据进行筛选过滤。DoytoQuery将实现了Having接口的对象映射为HAVING子句。字段的映射同时采用前缀映射和后缀映射方式。例如在实现了Having接口的对象中定义的字段avgScoreGe将被映射为HAVING avg(score) >= ?。

public interface Having extends Serializable {
}

2.4. 动态查询

动态查询WHERE语句的构造复用DoytoQuery中的动态查询方案。

另外，为Query对象提供了一个AggregationQuery接口用于构造HAVING条件。

public interface AggregationQuery extends DoytoQuery {
    Having getHaving();
}

2.5. 查询接口定义

最后，DoytoQuery中关于聚合查询的接口定义如下：

public interface DataQueryClient {
    // ...
    <V, Q extends AggregationQuery>
    List<V> aggregate(Q query, Class<V> viewClass);
}

3. 完整示例

现在我们来通过一个完整的示例，来演示DoytoQuery如何来实现对表t_student_score的聚合查询功能。

create table t_student_score
(
    id          bigint generated by default as identity primary key,
    school_term varchar(100)   not null,
    subject     varchar(100)   not null,
    student_no  varchar(10)    not null,
    score       numeric(10, 2) not null,
    is_deleted  boolean        not null default false
);

创建StudentScoreStatView用于映射聚合函数和分组字段。

@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity(name = "t_student_score")
public class StudentScoreStatView {
    @SuppressWarnings("java:S116")
    @GroupBy
    private String school_term;
    @GroupBy
    private String subject;
    private Integer countScore;
    private Double minScore;
    private Double maxScore;
    private Double avgScore;
}

创建StudentScoreHaving用于映射HAVING子句。

public class StudentScoreHaving implements Having {
    private Integer countGt;
}

创建StudentScoreStatViewQuery用于动态查询条件。

public class StudentScoreStatViewQuery extends PageQuery implements AggregationQuery {
    private String schoolTermGe;
    private List<String> subjectIn;
    private Double scoreGe;
    private Boolean isDeleted;
    private StudentScoreHaving studentScoreHaving;
    @Override
    public Having getHaving() {
        return studentScoreHaving;
    }
}

最后创建一个单元测试来进行验证。

@SpringBootTest
class StudentScoreStatTest {
    @Resource
    private DataQueryClient dataQueryClient;
    @Test
    void aggregateStudentScore() {
        StudentScoreStatViewQuery statQuery = StudentScoreStatViewQuery
                .builder()
                .schoolTermGe("2000")
                .subjectIn(Arrays.asList("Chinese", "Math", "English"))
                .scoreGe(60.)
                .isDeleted(false)
                .studentScoreHaving(StudentScoreHaving.builder().countGt(1).build())
                .sort("school_term,asc;subject,asc")
                .build();
        SqlAndArgs sqlAndArgs = RelationalQueryBuilder.buildSelectAndArgs(statQuery, StudentScoreStatView.class);
        assertThat(sqlAndArgs.getSql()).isEqualTo(
                "SELECT school_term, subject, count(score) AS countScore, min(score) AS minScore, " +
                        "max(score) AS maxScore, avg(score) AS avgScore " +
                        "FROM t_student_score " +
                        "WHERE school_term >= ? AND subject IN (?, ?, ?) AND score >= ? AND is_deleted = ? " +
                        "GROUP BY school_term, subject " +
                        "HAVING count(*) > ? " +
                        "ORDER BY school_term asc, subject asc");
        assertThat(sqlAndArgs.getArgs()).containsExactly("2000", "Chinese", "Math", "English", 60., false, 1);
        List<StudentScoreStatView> statList = dataQueryClient.aggregate(statQuery, StudentScoreStatView.class);
        assertThat(statList).hasSize(3)
                            .first().isEqualTo(new StudentScoreStatView("2022", "Chinese", 3, 85., 93., 89.));
    }
}

完整代码请查看 GitHub。