终于有人讲明白SQL中Filter与Where子句的差别了

一、背景

在SQL的诸多使用场景中，我们经常会碰到的一种是对某张表进行过滤聚合，比如：统计某列中大于3的有多少行，常规方法是使用Where子句，案例如下：

select 
       count(1)
 from (SELECT explode(array(1,2,3,4,5,6)) as num)
where num > 3
;

使用 Explain 查看一下其执行计划：

job0 is root job
In Job job0:
root Tasks: M1
In Task M1:
    VALUES: __tvf_arg_0 : {[1,2,3,4,5,6]}
        Statistics: Num rows: 1.0, Data size: 40.0
        TVF: EXPLODE(__tvf_arg_0) (__tf_0_0)
            Statistics: Num rows: 5.0, Data size: 20.0
            FIL: GT(__tf_0_0,3)
                Statistics: Num rows: 3.75, Data size: 1.0
                AGGREGATE: 
                 UDAF: COUNT(1) (__agg_0_count)[Complete]
                    Statistics: Num rows: 1.0, Data size: 8.0
                    FS: output: Screen
                        schema:
                          __agg_0 (bigint) AS _c0
                        Statistics: Num rows: 1.0, Data size: 8.0
OK

从执行计划可以看出：

1. 输入一个原始value，包含6个元素的列表；
2. 使用 explode 表生成函数将其炸开，变成有6行记录的列；
3. 再通过where 过滤，只保留大于 3 的记录；
4. 最后，count 计数。

除此之外，还可以使用Filter子句，代码如下：

select 
       count(1) filter (where num > 3)
  from (SELECT explode(array(1,2,3,4,5,6)) as num)
;

使用 Explain 查看一下其执行计划：

job0 is root job
In Job job0:
root Tasks: M1
In Task M1:
    VALUES: __tvf_arg_0 : {[1,2,3,4,5,6]}
        Statistics: Num rows: 1.0, Data size: 40.0
        TVF: EXPLODE(__tvf_arg_0) (__tf_0_0)
            Statistics: Num rows: 5.0, Data size: 20.0
            SEL: IF(GT(__tf_0_0,3),1,null) __agg_0_p0
                Statistics: Num rows: 5.0, Data size: 20.0
                AGGREGATE: 
                 UDAF: COUNT(__agg_0_p0) (__agg_0_count)[Complete]
                    Statistics: Num rows: 1.0, Data size: 8.0
                    FS: output: Screen
                        schema:
                          __agg_0 (bigint) AS _c0
                        Statistics: Num rows: 1.0, Data size: 8.0
OK

从执行计划可以看出：

1. 输入一个原始value，包含6个元素的列表；
2. 使用 explode 表生成函数将其炸开，变成有6行记录的列；
3. 再通过IF 逻辑判断，大于 3 的记录赋值为1，否则为NULL；
4. 最后，count 计数。

Filter 子句在我看来等同于如下代码 ：

select 
      count(if(num > 3,1,null))
 from (SELECT explode(array(1,2,3,4,5,6)) as num)
;

使用 Explain 查看一下其执行计划：

job0 is root job
In Job job0:
root Tasks: M1
In Task M1:
    VALUES: __tvf_arg_0 : {[1,2,3,4,5,6]}
        Statistics: Num rows: 1.0, Data size: 40.0
        TVF: EXPLODE(__tvf_arg_0) (__tf_0_0)
            Statistics: Num rows: 5.0, Data size: 20.0
            SEL: IF(GT(__tf_0_0,3),1,null) __agg_0_p0
                Statistics: Num rows: 5.0, Data size: 20.0
                AGGREGATE: 
                 UDAF: COUNT(__agg_0_p0) (__agg_0_count)[Complete]
                    Statistics: Num rows: 1.0, Data size: 8.0
                    FS: output: Screen
                        schema:
                          __agg_0 (bigint) AS _c0
                        Statistics: Num rows: 1.0, Data size: 8.0
OK

如上，Filter 与在count()中使用 IF的执行计划是一致的。

二、总结

从执行计划中可以看出，Where 是先过滤再聚合，Filter 是聚合时再过滤，他们使用场景如下：

1. 小样本：二者皆可
2. 大样本：Filter 灵活性高，适合一次性计算多列；只计算单列的话使用Where。