筛选位置精讲:SQL、Tableau、POWER BI

在喜乐君的体系中,筛选作为问题分析的关键部分特别强调,在即将发布的新书中,筛选更是作为单独章节全面展开,并引入了Reference LOD构成了三个层次。

先看问题的三个构成部分,如下所示:

在SQL、Tableau中,都可以明确区分上述三个部分,并进而理解它们之间的关系。而在POWER BI,筛选和维度统称之为“filter context”,作为一个整体评估它们对聚合的影响。

1、回顾:筛选的重要性和两类位置

在喜乐君的体系中,筛选首先明确了两个位置:独立筛选条件和结合计算的筛选条件。

比如,上面问题中,“2022年”是独立筛选,它对所有指标有效,因此“产量(总和)”就是“YTD产量”之意。而“当月产量”指标中又包含了一个更小范围的筛选条件“当月”,它仅仅对“当月产量”这个指标有效。

如何设置独立筛选和计算筛选?

简单一句话:所有指标的公共范围尽可能增加到独立筛选(有助于提高性能);多个指标之中差异化的部分通过计算实现筛选(性能虽低但灵活性高)。

和计算结合的筛选,其典型代表是SUMIF,它就是SUM和IF条件判断的结合。接下来以SQL为例,介绍二者的语法和差异。

当然,这里仅以行级别(非聚合)筛选为例。

2、SQL和Tableau中的筛选位置

比如,“消费者市场,2019年利润总和、2020年利润总和及年度同比”,这里可以把公共的筛选器“消费者市场”和日期的最大范围加入where筛选器,从而优化性能;然后把两个指标中差异化的范围写入计算中,这样就实现了SUMIF的条件聚合。

如下所示:

-- 在SQL中使用SUMIF条件聚合
-- 消费者市场,2019年利润、2020年利润,及同比
SELECT
SUM( IF(YEAR(订单日期)=2020,利润,null)) as P2020,
SUM( IF(YEAR(订单日期)=2019,利润,null)) as P2019,
SUM( IF(YEAR(订单日期)=2020,利润,null))/SUM( IF(YEAR(订单日期)=2019,利润,null))-1 as growth
FROM tableau.superstore
WHERE 细分="消费者" AND YEAR(订单日期) >= 2019;

同样的过程,如果在Tableau中实现也并非难事。

比如,“消费者市场、2022年,各类别、子类别的(YTD)销售额总和、MTD销售额、利润率”。

在Tableau中,可以把消费者、2022年对应的筛选加入筛选器,而把MTD对应的差异化范围加入计算之中,通过SUM和if的嵌套完成。

3、独立筛选和条件计算的差异

虽然从技术上看,两种筛选殊道同归,但是性能差异很大,之前喜乐君在博客中有所阐述(【高级】Tableau性能优化之关键:两种计算对性能的影响实证)。

这里不妨用可视化方式解读它们的差异,如下所示:

左侧是SUMIF的思路,喜乐君称之为“间接筛选”,在筛选的背后是“真计算、伪筛选”。为什么是“伪筛选”?因为所有明细依然参与了最后的SUM聚合,只是不满足条件的数据值被改为了null。这个过程中,出现了一个临时的逻辑字段(if字段)。

右侧的筛选是明细筛选先于聚合,聚合时,指定条件之外的明细已经不见了。这里没有临时的逻辑字段,不过可以把筛选过程视为一个临时的明细表(temporary table)。

相比之下,后者的性能更高、更容易理解;前者在计算上更灵活、对非专业人员更加友好。

4、one more step百尺竿头,更进一步

理解到这里,业务分析师就能平衡性能和灵活性,完成大部分的筛选了——事实上,这就是Tableau所采用的逻辑。

不过,随着指标越来越多,有些工具就思考,能否在计算中,也把筛选条件单独出来,同时独立于视图的公共筛选器之外。这就是POWER Bi的思考了。

在POWER BI中,为了把筛选独立于聚合之外,同时还要保持二者的先后关系,于是就有了CALCULATE表达式——聚合和聚合的筛选条件变成了表达式的两个参数。如下:

CALCULATE(聚合表达式,筛选条件)

比如,furniture家具的销售额,可以如下所示:

SUM with Filter furniture =
CALCULATE( SUM('Global-Superstore'[Sales]),
           FILTER('Global-Superstore', 
                  'Global-Superstore'[Category]="Furniture")
)

同理,如果要计算2020年的销售额,则可以如下所示:

SUM with Filter Y2020 = 
CALCULATE( SUM('Global-Superstore'[Sales]),  
           FILTER('Global-Superstore', 
                   Year('Global-Superstore'[Date])=2020)
 )

如此,既实现了多个指标的差异化计算(自带筛选范围),又实现了表达式内部聚合和条件的分离。

这种分离不再是SUMIF的“伪筛选”,而是真正的筛选,相当于把临时明细表加入到计算中。喜乐君绘制如下的可视化过程,辅助理解SUMIF的筛选和这里CALCULTE中filter独立筛选的差异。

注意,在右侧的聚合过程中,FILTER生成了一个临时的明细表(temporary detail table),它减少了明细的数据,然后作为聚合的对象。

从SQL的角度看,CALCULATE函数相当于一个SUM之中嵌套了一个SELECT查询,如下所示:

SELECT
( -- 嵌套查询
  SELECT SUM(a.利润)
  from tableau.superstore as a
  where YEAR(a.订单日期)=2019
) as P2019,
( -- 嵌套查询
  SELECT SUM(a.利润)
  from tableau.superstore as a
  where YEAR(a.订单日期)=2020
) as P2020
-- SUM( IF(YEAR(订单日期)=2019,利润,null)) as P2019
FROM tableau.superstore
WHERE 细分="消费者" AND YEAR(订单日期) >= 2019;

我在Mysql中测试发现,这样的性能不及之前的SUM+if判断,不知道Power BI中是否有相应的优化方法。

4、总结:条件计算的多重理解

至此,这里介绍了SQL中的两种筛选位置(where中的独立筛选、SUM中的if计算筛选),以及上述两种筛选位置在Tableau中的对应形式。

而在POWER BI中,SUMIF的“伪筛选”,并升级为了CALCULATE的“真筛选”,同时又保持了筛选的独立性。这个是一个本质性的进步。

当然,也正因为此,POWER BI中,filter的结果就是一个table,这和Tableau、SQL中的逻辑有很大差异。这种基于table的理解方式,需要更高的技术理解,这也阻碍了业务用户的使用。

当然,在IT的领域中,POWER BI也成为当之无愧的王者了。

喜乐君

Dec 8, 2022

发表评论

Fill in your details below or click an icon to log in:

WordPress.com 徽标

您正在使用您的 WordPress.com 账号评论。 注销 /  更改 )

Twitter picture

您正在使用您的 Twitter 账号评论。 注销 /  更改 )

Facebook photo

您正在使用您的 Facebook 账号评论。 注销 /  更改 )

Connecting to %s

网站通过 WordPress.com 打造.

向上 ↑

%d 博主赞过: