sql group by having用法_SQL解析器与SQL生成器

作为数据产品公司的一员，天天都要和各种数据库的SQL打交道。可以参考之前的几篇SQL相关文章：

• SQL的弱点(1)：复杂SQL不易理解，以及软件工程如何来帮忙
• SQL的弱点(2)：不支持从所有列中去除某列
• SQL的弱点(3)：缺少静态类型检查

本文主要介绍一下SQL解析器(SQL Parser)和 SQL生成器(SQL Generator)

SQL解析器

对于SQL解析器，我们有“编译原理”的支持。解析SQL的过程，和解析Java/Python程序的过程本质上是一样的。

比如：

• 最早在C/C++中，我们可以使用lex & yacc 来做词法和语法分析
• 在Java中, JavaCC或者ANTLR

在这些工具中，一般是把目标语言的语法规则描述为 BNF 的方式，举个yacc的文件例子：https://github.com/jgarzik/sqlfun/blob/master/sql.y

select_stmt: SELECT select_opts select_expr_list                        { sqlp_select_nodata(pstate, $2, $3); } ;    | SELECT select_opts select_expr_list     FROM table_references     opt_where opt_groupby opt_having opt_orderby opt_limit     opt_into_list { sqlp_select(pstate, $2, $3, $5); } ;;select_expr_list: select_expr { $$ = 1; }    | select_expr_list ',' select_expr {$$ = $1 + 1; }    | '*' { sqlp_select_all(pstate); $$ = 1; }    ;select_expr: expr opt_as_alias ;opt_as_alias: AS NAME { sqlp_alias(pstate, $2); free($2); }  | NAME              { sqlp_alias(pstate, $1); free($1); }  | /* nil */  ;opt_limit: /* nil */ | LIMIT expr { sqlp_limit(pstate, 0); }  | LIMIT expr ',' expr             { sqlp_limit(pstate, 1); }  ; opt_where: /* nil */    | WHERE expr { sqlp_where(pstate); };opt_groupby: /* nil */    | GROUP BY groupby_list opt_with_rollup                             { sqlp_group_by_list(pstate, $3, $4); };opt_orderby: /* nil */ | ORDER BY groupby_list { sqlp_order_by(pstate, $3); }   ;

看似比较复杂，但是由于是有“编译原理”理论支持的，所以，相关配套非常成熟。这一类可以看成是“Parser Generator”，借助其它工具来解析语法描述文件，从而生成该语言的解析器。

当然，也有能简化这个过程的新方式，比如“Parser Combinator”，名字中的 Combinator 意味着其可以把多个现有的解析器组合成新的解析器。说的有点绕，具体的区别可以参考如下回答：

https://stackoverflow.com/a/5057470

“

One of the main differences between the tools you listed, is that ANTLR, Bison and their friends are parser generators, whereas Parsec is a parser combinator library.

A parser generator reads in a description of a grammar and spits out a parser. It is generally not possible to combine existing grammars into a new grammar, and it is certainly not possible to combine two existing generated parsers into a new parser.

A parser combinator OTOH does nothing but combine existing parsers into new parsers. Usually, a parser combinator library ships with a couple of trivial built-in parsers that can parse the empty string or a single character, and it ships with a set of combinators that take 1 or more parsers and return a new one that, for example, parses the sequence of the original parsers (e.g. you can combine a d parser and an o parser to form a do parser), the alternation of the original parsers (e.g. a 0 parser and a 1 parser to a 0|1 parser) or parses the original parse multiple times (repetetion).

What this means is that you could, for example, take an existing parser for Java and an existing parser for HTML and combine them into a parser for JSP.

Most parser generators don't support this, or only support it in a limited way. Parser combinators OTOH only support this and nothing else.

比如找个 Scala Parser Combinator 解析SQL的例子：https://github.com/stephentu/scala-sql-parser/blob/master/src/main/scala/parser.scala

class SQLParser extends StandardTokenParsers {  def select: Parser[SelectStmt] =    "select" ~> projections ~      opt(relations) ~ opt(filter) ~      opt(groupBy) ~ opt(orderBy) ~ opt(limit) ";") ^^ {    case p ~ r ~ f ~ g ~ o ~ l => SelectStmt(p, r, f, g, o, l)  }  def projections: Parser[Seq[SqlProj]] = repsep(projection, ",")  def projection: Parser[SqlProj] =    "*" ^^ (_ => StarProj()) |    expr ~ opt("as" ~> ident) ^^ {      case expr ~ ident => ExprProj(expr, ident)    }  def groupBy: Parser[SqlGroupBy] =    "group" ~> "by" ~> rep1sep(expr, ",") ~ opt("having" ~> expr) ^^ {      case k ~ h => SqlGroupBy(k, h)    }  def orderBy: Parser[SqlOrderBy] =    "order" ~> "by" ~> rep1sep( expr ~ opt("asc" | "desc") ^^ {      case i ~ (Some("asc") | None) => (i, ASC)      case i ~ Some("desc") => (i, DESC)    }, ",") ^^ (SqlOrderBy(_))  def limit: Parser[Int] = "limit" ~> numericLit ^^ (_.toInt)}

直接使用Scala描述新的语法很方便(有编译器支持)。

SQL解析器在BI中的应用不是很多，主要是用于：

• 解析用户输入的“自定义字段”的聚合类型：普通聚合函数、窗口函数、普通函数
• 对于用户输入的SQL做一些高级分析，比如：判断是否有性能问题，是否可以优化等

另外，SQL Parser并不是本文的中心内容，所以，不再深入讨论，感兴趣的朋友可以参考“编译原理”相关书籍。

SQL生成器

相对SQL解析器，SQL生成器在BI中的应用要广泛的多。比如：我们需要针对不同的数据库自动生成其对应的SQL。

而不同的数据库，其SQL语法是有很多不一样的。比如，举个常见的查询场景为例。

针对Postgresql，我们生成的SQL是：

select  "member_id" as "member_id__0",  count(distinct "order_id") as "order_id__cnt_distinct__0"from "orders"group by "member_id__0"limit 20000

对于SQL Server，生成的SQL则是：

select top 20000  [member_id] [member_id__0],  count(distinct [order_id]) [order_id__cnt_distinct__0]from [orders]group by [member_id]order by (select 0)

对于Oracle，生成的SQL则是：

select  "v0" "member_id__0",  "v1" "order_id__cnt_distinct__0"from (  select    "x"."v0",    "x"."v1",    rownum "rn"  from (    select      "member_id" "v0",      count(distinct "order_id") "v1"    from "orders"    group by "member_id"  ) "x"  where rownum <= (0 + 20000))where "rn" > 0

这里造成SQL差异比较大的主要是“LIMIT”的支持。当然这只是一个小的点，具体的SQL的差异还是挺多的。

由于各个数据库的SQL差异比较大，所以，我们最好不要直接用StringBuilder来拼接SQL (容易产生非法的SQL，并且非常容易有“SQL注入”的风险)。

而符合同时动态生成各种数据库的SQL，相关的库则没有SQL Parser那么多。常见的有：

• Java的Jooq：https://github.com/jooq/jooq
• Python的SQLAlchemy：https://www.sqlalchemy.org/

据我比较熟悉的Jooq来举例，生成上述的SQL的代码类似于：

DSL.using(SQLDialect.POSTGRES)   .select(      field(name("member_id")).as("member_id__0"),      countDistinct(name("order_id")).as("order_id__cnt_distinct__0")   )   .from(name("orders"))   .groupBy(       "member_id__0",       "order_id__cnt_distinct__0"    )   .limit(20000)

Jooq 对于避免SQL注入，辅助生成SQL的帮助还是挺大的。不过Jooq本身也有些问题：

问题一：对于SQL的封装还有限，举例来说：对于不同的数据库，其可以放在 Group By 从句中的内容是不同的：

• 有的数据库支持 "Group By 1,2,3" 的方式
• 有的数据库需要使用列的别名 "Group By Alias1, Alias2 "
• 而有的数据库则必须要把原始计算带入到GroupBy中， “Group By Column1, to_date(Column2)”

问题二：对于Jooq本身支持的数据库 (Jooq的开源版本可以连接一些开源的数据库，而其付费版本可以额外支持连接一些商业数据库)，其使用还是比较方便的。但是：对于其不支持的数据库，则很难通过修改代码来支持。由于Jooq本身主要关注于支持流行度排名在前30名的数据库，导致很多的数据库不支持。比如：Apache Spark、Apache Kylin、Clickhouse、以及国内的很多数据库，比如阿里云的MaxCompute等。

问题二是比较难以解决的，曾经Jooq也想去实现一种：通过配置文件等形式，让用户方便扩展新的数据库的支持。但是，后来这个实现最终没有发布，因为：这个实现和Jooq的商业利益是相违背的。出于Jooq商业利益的角度，不可能让每个人都能自行支持新的数据库，否则的话，将没有人来购买其商业版本(因为用户可以自行实现对于商业数据库的语法支持)。但是，近些年来各种新的数据库也是层出不穷，Jooq的做法感觉有些“作茧自缚”了。

但是、比较遗憾的是，Java里并没有能很好取代Jooq的库。

研究 dbt 时的偶得

前几天在了解如何使用 dbt (data build tool)这个工具来构建数据仓库时，发现dbt的理念真的非常棒。

dbt的主页：https://www.getdbt.com/

真的是用“软件工程”的实现来做数仓。很符合和我在文章《SQL的弱点(1)：复杂SQL不易理解，以及软件工程如何来帮忙》中探寻的工具，有如下理念：

• Version Control & Code Review
• Automated Testing
• Sandboxing & Environments
• Documentation
• Modularity
• Package Management

dbt的内容将留给未来某篇文章，这里只描述和SQL生成器相关的内容。

dbt的主体是组织在不同目录的多个SQL文件。但是会使用 Jinja 模板 和 Macros 来辅助SQL的编写。比如Jinja模板：https://docs.getdbt.com/docs/building-a-dbt-project/jinja-macros#jinja

{% set payment_methods = ["bank_transfer", "credit_card", "gift_card"] %}select    order_id,    {% for payment_method in payment_methods %}    sum(case when payment_method = '{{payment_method}}' then amount end) as {{payment_method}}_amount,    {% endfor %}    sum(amount) as total_amountfrom app_data.paymentsgroup by 1

会最终生成如下的SQL来执行：

select    order_id,    sum(case when payment_method = 'bank_transfer' then amount end) as bank_transfer_amount,    sum(case when payment_method = 'credit_card' then amount end) as credit_card_amount,    sum(case when payment_method = 'gift_card' then amount end) as gift_card_amount,    sum(amount) as total_amountfrom app_data.paymentsgroup by 1

哇，模板 + SQL居然能产生这种神奇的效果。

于是，我就在想我是否能在Java中也实现类似的功能，是否能用于观远的产品中？(Jinja 和 dbt 都是 Python写的)

接下来，我发现了Java中神奇的 string-template 项目: https://github.com/antlr/stringtemplate4

神奇的string template使得“SQL生成器”变成了“SQL解析器”的反过程

stringtemplate4 也是一个“模板引擎”，说到模板引擎，可能大家的第一印象就是用于后端生成 web页面时，把数据绑定进web模板中，比如：Apache Velocity 项目等。stringtemplate4 的起源也是作者为了开发 jGuru.com 网站时，总结了作为页面模板的最基本元素。

同时，stringtemplate4 的作者也是 著名的Java 解析器 ANTLR 的作者！所以，在“编译原理”的理论指导下，使用类似BNF的语法来写“模板”，一定会出现很多有趣的应用吧。

关于 stringtemplate4 的起源，以及它背后的理念等，可以参考其作者的论文：http://www.cs.usfca.edu/~parrt/papers/mvc.templates.pdf 非常有启发，非常建议阅读。

话不多说，接下来进入我最喜欢的“展示代码”环节，让我们来看看如何用 stringtemplate4 替代 jooq来生成SQL吧。

尝试1、生成最简单的SELECT

让我们来先从最简单的 select col1, col2 from table1 开始：

首先我们java中定义一个数据结构来代表这个SELECT语句:

@Data@Builderclass SelectStmt {    public List columnNames;public String tableName;}

接下来，我们编写一个模板文件，并放在java项目的resource目录中：

select_stmt(stmt) ::= "SELECT  FROM "

(注意模板中 < > 内的内容，以及对于单值和多值的处理)

最终，在测试程序中调用模板文件来生成SQL：

public class TestST {    public static void main(String[] args) {        SelectStmt stmt = SelectStmt.builder()                .columnNames(Lists.newArrayList("col1", "col2"))                .tableName("table1")                .build();        STGroup group = new STGroupFile(Resources.getResource("postgresql/template.stg"));        ST st = group.getInstanceOf("select_stmt");        st.add("stmt", stmt);        System.out.println(st.render());    }}

我们可以得到如下的SQL

SELECT col1,col2 FROM table1

尝试2、增加列的别名支持

接下来，我们来增加一下 column alias 的支持，我们首先来扩展一下 SelectStmt 的定义，增加 ColumnOptAlias 类来存储 列名+别名，注意：别名是可选的，当alias为null时，意味着没有别名。

@Data@AllArgsConstructorclass ColumnOptAlias {    public String name;    public String alias;}@Data@Builderclass SelectStmt {    public List columns;public String tableName;}

接下来，修改模板文件来适应这个新的变化

select_stmt(stmt) ::= <<SELECT FROM >>column_opt_alias(col) ::= <if(col.alias)>AS alias>%>

注意：

• 这里由之前的一条规则变为了现在的两条规则
• 之前的“尝试1”中的规则使用了类似Java中单行字符串的方式，所以需要使用 \ 来转义字符串中的出现的双引号。但是本例子中为了书写方便，使用了“多行”的方式来描述规则
• 使用 << >> 的方式时，规则中的空格、换行符等都会保留在最终生成的SQL中，但是使用 的方式则会忽略这些空格、换行符

最后，修改测试程序：(注意：列col1 有了别名“alias1”，而col2则没有别名)

public class TestST {    public static void main(String[] args) {        SelectStmt stmt = SelectStmt.builder()                .columns(Lists.newArrayList(                        new ColumnOptAlias("col1", "alias1"),                        new ColumnOptAlias("col2", null)                ))                .tableName("table1")                .build();        STGroup group = new STGroupFile(Resources.getResource("postgresql/template.stg"));        ST st = group.getInstanceOf("select_stmt");        st.add("stmt", stmt);        System.out.println(st.render());    }}

我们可以得到如下的SQL

SELECT col1 AS alias1,col2FROM table1

尝试3、增加LIMIT的支持 (Postgresql 语法)

首先，对于SelectStmt，我们增加一个新的字段 limit， 当limit设置为 null 时，则是不限制返回行数，否则返回指定的行数。

@Data@Builderclass SelectStmt {    public List columns;public String tableName;public Long limit;}

接下来，修改模板文件来适应这个新的变化

select_stmt(stmt) ::= <<SELECT FROM >>column_opt_alias(col) ::= <if(col.alias)>AS alias>%>limit_stmt(stmt) ::= <if(stmt.limit)>LIMIT %>

这里又增加了一个新的规则 limit_stmt, 修改调用代码来设置 SelectStmt 的limit:

SelectStmt stmt = SelectStmt.builder()                .columns(Lists.newArrayList(                        new ColumnOptAlias("col1", "alias1"),                        new ColumnOptAlias("col2", null)                ))                .tableName("table1")                .limit(100l)                .build();

我们得到如下SQL

SELECT col1 AS alias1,col2FROM table1LIMIT 100

尝试4、增加LIMIT支持(SQL Server语法)

对于SQL Server，我们可以新建个文件 /mssql/template.stg 来描述其语法规则

select_stmt(stmt) ::= <<SELECT FROM >>column_opt_alias(col) ::= <if(col.alias)>AS alias>%>limit_stmt(stmt) ::= <if(stmt.limit)>TOP %>

我们可以得到如下SQL

SELECT TOP 100 col1 AS alias1,col2FROM table1

尝试5、增加LIMIT支持(旧版Oracle语法)

对于低版本的Oracle，由于还不支持Limit从句，其支持稍微复杂，让我们来生成一个类似于上面jooq生成的sql。首先定义语法规则:

select_stmt(stmt) ::= <<>>nolimit_select_stmt(stmt) ::= <<SELECT FROM >>column_opt_alias(col) ::= <if(col.alias)>AS alias>%>column_final_display(col) ::= ""with_limit_stmt(stmt) ::= <<SELECT FROM (SELECT ,rownum "rn"FROM () "x"WHERE rownum \<= (0 + ))WHERE "rn" > 0>>

模板比之前要复杂些，包含了5条规则，可以得到如下针对Oracle的SQL

SELECT alias1,col2FROM (   SELECT alias1,col2         ,rownum "rn"   FROM (        SELECT col1 AS alias1,col2        FROM table1   ) "x"   WHERE rownum <= (0 + 100))WHERE "rn" > 0

尝试6、支持转义从而避免SQL注入

上面虽然我们验证了模板的丰富功能，以及其可组合的特性。但是，我们仍然有一个问题没有解决，因为实际场景中，很难保证 列名，字符串 等都是由简单字符构成的。

比如：如果我们的列名中 col2 变为了 col2_with"\_special 这种包含特殊字符的列名。那么生成的SQL将是非法的。

在stringtemplate4 的论文《http://www.cs.usfca.edu/~parrt/papers/mvc.templates.pdf》 中，也提到了这个问题， 模板本身是处理不了这种转义的，但是我们可以借助于java的力量。

“

How then does one escape strings, a common and necessary operation?

The inescapable fact is that there must be some Java code somewhere that computes HTML escape sequences because the computation cannot be done in the template. Besides, Java already provides heavy support, such as the java.text package, for dealing with text, numbers, dates, and so on. Since neither the model nor the controller may contain this computation, the code must exist elsewhere.

该作者说，对于这个转义的支持，对传统的MVC引入了一个新的角色：renderer, MVC就变成了：MVCR (model-view-controller-renderer)。

对于我们的这个程序来说，我们可以对于“列名”这种需要转义的地方，定义其对应的 toString() 方法即可。

我们来修改一下java中对应的类定义：

@AllArgsConstructorclass Name {    public String name;    @Override    public String toString() {        String escape = "\"";        return escape +                name.replace(escape, escape + escape)                + escape;    }}@Data@AllArgsConstructorclass ColumnOptAlias {    public Name name;    public Name alias;}@Data@Builderclass SelectStmt {    public List columns;public Name tableName;public Long limit;}

注意我们对于需要转义的列名、表名等，由String类型变为了Name类，这个类的toString是用于在Template中展示的。

我们把col2替换为包含特殊字符的 col2_with"\_special 。接下来，我们可以得到的SQL变为：

SELECT "col1" AS "alias1","col2_with""\_special"FROM "table1"LIMIT 100

完美！当然对于不同的SQL，我们需要不同的转义字符，这些我们同样可以配置在不同的文件中。另外用同样的方式，我们也可以支持把 String、Date、Number等类型的数据转为对应数据库支持的格式。

展望

stringtemplate4的应用场景非常广，值得深入研究。比如：

1. 对于 Apache DolphinScheduler 项目来说，其实也有很多可以用到“模板”的地方，比如：SQL任务、Shell任务中替换变量，发送的Email中配置不同的展示模板等。

2. 比如：前几天阿里云的直播《EMR Spark-SQL性能极致优化揭秘 Native Codegen Framework》https://developer.aliyun.com/article/771622?groupCode=aliyunemr， Spark开源版本会把Spark任务翻译为 Java 的代码来最终执行(Whole-stage code generation，也是 spark 2.0 版本发布时宣传的提升性能的一大法宝)。不过Java本身对于向量计算(vector computing)等贴近CPU的计算优化支持不太好，而现在利用CPU特性来追求急速计算的开源项目越来越多，比如：“clickhouse”、“duckdb”，以及 “weld” (https://github.com/weld-project/weld)， 阿里云的分享主要就是扩展Spark，从Java Codegen扩充为支持 Native Codegen。(具体的实现是依赖于 weld 这个项目)。视频不错，值得学习。不过很多内容都是点到为止，很多如何实现 Native Codegen 的细节并没有说明。

理论上，我们也可以使用 stringtemplate4 来实现 Java Codegen 和 Native Codegen。以 IF(expr1, expr2, expr3) 这个函数来举例， Spark对应的Java Code代码如下：

@ExpressionDescription(  usage = "_FUNC_(expr1, expr2, expr3) - If `expr1` evaluates to true, then returns `expr2`; otherwise returns `expr3`.",  examples = """    Examples:      > SELECT _FUNC_(1        a  """)case class If(predicate: Expression, trueValue: Expression, falseValue: Expression)  extends ComplexTypeMergingExpression {  override def doGenCode(ctx: CodegenContext, ev: ExprCode): ExprCode = {    val condEval = predicate.genCode(ctx)    val trueEval = trueValue.genCode(ctx)    val falseEval = falseValue.genCode(ctx)    val code =      code"""         |${condEval.code}         |boolean ${ev.isNull} = false;         |${CodeGenerator.javaType(dataType)} ${ev.value} = ${CodeGenerator.defaultValue(dataType)};         |if (!${condEval.isNull} && ${condEval.value}) {         |  ${trueEval.code}         |  ${ev.isNull} = ${trueEval.isNull};         |  ${ev.value} = ${trueEval.value};         |} else {         |  ${falseEval.code}         |  ${ev.isNull} = ${falseEval.isNull};         |  ${ev.value} = ${falseEval.value};         |}       """.stripMargin    ev.copy(code = code)  }}

这里是拼接成了 java的代码，而有了 stringtemplate4， 我们是不是可以在Spark中，只是生成一种中间数据结构，类似于 Abstract Syntax Tree (AST)，不过更偏向于把物理计划变为方便模板替换的数据结构。最终真正执行的时候，我们可以通过应用不同的模板，来既可以生成 Java 代码，也可以生成 LLVM/weld 代码。

3. 其它领域应该也有不少可以应用的场景，鼓励大家都来发挥想象力！

最后，最重要的信息：如果你也想深入研究各种数据库，深入研究Spark，并用技术来解决数据处理 + BI + AI的真实挑战，欢迎加入“观远数据”！We are hiring!