DoytoQuery 聚合查詢方案示例詳解

更新時間：2022年12月27日 16:39:46 作者：f0rb

這篇文章主要為大家介紹了DoytoQuery 聚合查詢方案示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪

1. 引言

聚合查詢是數(shù)據(jù)庫提供的另一種常用的用于數(shù)據(jù)的統(tǒng)計和計算的查詢功能，它通過提供一系列聚合函數(shù)來匯總來自多個行的信息。

DoytoQuery采用字段前綴映射的方式來將字段名映射為聚合函數(shù)，再配合@GroupBy注解，Having接口以及Query對象，完成整條聚合查詢語句的映射。

2. 聚合查詢映射

2.1. 前綴映射

聚合函數(shù)是聚合查詢的核心功能，常用的聚合函數(shù)有以下幾種：

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

使用聚合函數(shù)查詢出來的數(shù)據(jù)也需要占用一列，這一列數(shù)據(jù)也需要映射到POJO對象的一個字段。我們可以利用這一特性，在定義POJO對象的字段時，采用聚合函數(shù)關(guān)鍵字加上大駝峰格式的列名即可。這個字段既可以用于映射查詢語句，又可以保存返回的數(shù)據(jù)，一舉兩得。比如我們想統(tǒng)計分數(shù)列score的平均值，我們將字段名定義為avgScore，在構(gòu)造查詢語句時，這個字段就會被映射為avg(score) AS avgScore，依此類推。

2.2. 分組聚合

聚合查詢中少不了針對部分表列進行分組聚合，這些用于分組的表列通常也需要查詢出來，于是便可以將這些表列也定義到POJO對象中，同時需要與其他的聚合字段加以區(qū)別。DoytoQuery中采用添加@GroupBy注解的方式進行區(qū)分。

@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子句針對聚合后的數(shù)據(jù)進行篩選過濾。DoytoQuery將實現(xiàn)了Having接口的對象映射為HAVING子句。字段的映射同時采用前綴映射和后綴映射方式。例如在實現(xiàn)了Having接口的對象中定義的字段avgScoreGe將被映射為HAVING avg(score) >= ?。

public interface Having extends Serializable {
}

2.4. 動態(tài)查詢

動態(tài)查詢WHERE語句的構(gòu)造復用DoytoQuery中的動態(tài)查詢方案。

另外，為Query對象提供了一個AggregationQuery接口用于構(gòu)造HAVING條件。

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

2.5. 查詢接口定義

最后，DoytoQuery中關(guān)于聚合查詢的接口定義如下：

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

3. 完整示例

現(xiàn)在我們來通過一個完整的示例，來演示DoytoQuery如何來實現(xiàn)對表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
);

創(chuàng)建StudentScoreStatView用于映射聚合函數(shù)和分組字段。

@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;
}

創(chuàng)建StudentScoreHaving用于映射HAVING子句。

public class StudentScoreHaving implements Having {
    private Integer countGt;
}

創(chuàng)建StudentScoreStatViewQuery用于動態(tài)查詢條件。

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

最后創(chuàng)建一個單元測試來進行驗證。

@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。

4. 總結(jié)

本文詳細介紹了DoytoQuery中的聚合查詢方案，包括聚合函數(shù)的前綴映射，@GroupBy注解，Having接口以及動態(tài)查詢構(gòu)造等方式，將對象映射為聚合查詢語句，用以完成聚合查詢，更多關(guān)于DoytoQuery 聚合查詢方案的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: