这个话题其实可以用于扩大为各个sql语法的不同之处。本文clickhouse版本:20.7.2.30
--mysql
select
id ,name
from test.person
group by id;
msyql可以有上面的语法,hive sql却不行,hive的sql语法和mysql有不同之处众所周知,比如有group by 语法存在的时候,hive要求select后面的非聚合字段必须是group by后面的分组字段,这个其实很好理解,我们也接受了,因为严格来说确实是这样,对于同一个组的非分组字段会有不一样的数据,如果你也查询出来你算哪一个呢?并且如果你真的想随便查出来一个,hive里面可以用first() 函数。
--hive
select
id ,first(name) name
from test.person
group by id;
还有一些比如说hive不支持in 语法(老版本是这样,新版本没去细究),所以我们习惯用join解决,这些都没问题,但总的来说我们对sql的执行顺序的理解大致是一致的,如下:
from -> join -> where ->group by -> select ->having ->order by -> limit
然而最近clickhouse的使用让我发现clickhouse里面sql执行顺序似乎并非如此,比如 mysql 或者hive我们执行一下语句:
--原表test.person数据:
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | b |
| 3 | c |
+----+------+
select
id ,substr(name,1,1) name
from test.person
where name in ('aaa','b');
--得到结果:
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
当然是我们熟悉的from -> where ->select 顺序,在让我们看看clickhouse里面的结果:
大家可以看出少了一条数据,原因是它先处理了substr然后才做where筛选!所以筛选的是转换之后的name,当然要实现原来的效果只要将substr处理前后的name名字不一样即可,可见clickhouse会将select后面函数处理后的字段都做保留用于后面的计算,但如果你新名字和旧名字一样当然只保留处理之后的字段,而这对习惯了之前思维的开发者来说会出现大bug!
当然某些时候也可以利用这个特点简化我们的sql,由于select后面命名的新字段可以在select后面继续使用,我们可以用这样的语法:
甚至新字段名可以在group by 和where 后面使用,我们基本可以得出结论:clickhouse的select语法并不像msyql,hive语法在所有逻辑执行完之后执行,而是在执行前和之后都有,利用这个特性,很多时候我们可以少写很多子查询,sql代码更加精炼。