【书摘】SUMIF条件聚合：将行级别筛选和聚合分析合二为一

喜乐君注：本文来自于《数据可视化分析（第 2 版）：分析原理与 Tableau、SQL 实践》第八章，有助于辅助理解筛选和计算的关系、组合。‍‍‍

第八章

8.7.3 SUMIF条件聚合：将行级别筛选和聚合分析合二为一

在本书6.1.1节，笔者介绍了“独立筛选”和“条件计算筛选”两个筛选情景，后者的典型是SUM+IF函数。从计算的角度，SUMIF表达式是建立在数据表行级别计算基础上的聚合计算。它的结构如下：

SUM( IF[condition指定条件] THEN [measure度量] END )

这里以下面两个案例为例，介绍如何使用SUM+IF表达式完成聚合。

问题1：消费者细分，（公司的）2021年利润总和

问题2：消费者细分，（公司的）2020年的利润、2021年的利润和同比增长率

1．将筛选器与问题详细级别、聚合独立计算

对于问题1，可以把“利润总和”之外的部分，全部视为独立的范围筛选——它们限定聚合的大小。问题中没有维度，可以视为是最高详细级别——“公司”的利润总和，即如下结构：

消费者细分，（公司的）2021年利润总和

这是标准的问题结构，只需要把字段拖入视图中，并对结果做必要的调整即可。如图8-50所示，这里的利润总和对应聚合计算SUM([利润])。

图8-50 将筛选和聚合独立分开是数据分析的标准方式

在这里，筛选和聚合相互独立，而且筛选优先于聚合，这样只需要对符合条件的交易明细做聚合，这种方式性能最快、效率最好。

当然，受Excel中SUMIF函数的影响，很多初学者会把筛选嵌套在聚合计算中。借用 SQL的逻辑表达，如下所示。

SELECT
SUM(IF(细分="消费者" AND YEAR(订单日期)=2021, 利润, null)) AS 利润
FROMtableau.superstore;

这种方式并没有独立的筛选环节，而是“把不符合条件的数据明细强制修改为null”，间接实现筛选功能，这就要求查询引擎需要遍历所有明细行，再对辅助列做聚合。虽然结果相同，但是在大数据面前，这种方式将降低计算的性能，因此也是笔者不推荐的方式。

但凡能将筛选独立于问题和聚合的，都优先考虑。

当然，也不意味着SUMIF表达式毫无用处，而应该限定在特殊的场景中——多个聚合的计算范围不同。下面结合案例讲解。

2．必要时，将筛选条件与聚合组合为“条件聚合表达式”

问题2的特殊之处在于，两个利润分别对应不同的日期筛选范围。例如，领导指定要完成如图8-51右侧所示的靶心图样式。基于一个聚合度量的分年度显示方式，使得自定义调整变得异常困难。如果能把不同年度的聚合计算完全独立为两个字段，就可以增强布局的自由度。此时就需要“条件聚合”。

图8-51 基于2020年和2021年独立的利润字段完成自定义配置

“条件聚合”，顾名思义，就是在聚合值中直接包含判断条件，可以使用IF函数和聚合函数组合为表达式来完成。参考Excel中SUMIF函数的样式，在Tableau中可以进行如下计算。

【2020年利润】 = SUM(IIF( YEAR([订单日期])=2020,[利润],null))

【2021年利润】 = SUM(IIF( YEAR([订单日期])=2021,[利润],null))

基于条件聚合字段，就可以自由灵活地调整它们在视图中的位置和作用了。如图8-52所示，不同年度的利润相互独立，又能在视图中有机结合。

图8-52 在 Tableau中实现“条件聚合”，并自定义配置视图角色

在这个过程中，笔者依然把“订单日期”加入筛选器，和细分字段筛选器一样，旨在优化“条件聚合”的性能。上述过程可以对应如下SQL语句。

SELECT
SUM(IF(YEAR(订单日期)=2020,利润,null)) as P2021,
SUM(IF(YEAR(订单日期)=2021,利润,null)) as P2020,
SUM(IF(YEAR(订单日期)=2021,利润,null))/ SUM( IF(YEAR(订单日期)=2020,利润,null))-1  as  growth
FROM tableau.superstore
WHERE细分="消费者" AND YEAR(订单日期) >= 2020;

在数据分析中，对性能和灵活的取舍是数据分析师的重要工作之一。以这里的“条件聚合”为例，它的优势在于赋予了不同年度聚合字段极大的灵活性，而其弊端在于查询过程中较低的性能，同时难以维护，不具有可持续性（到了下一年度，计算需要重写）。

为了在灵活性和性能之间保持平衡，Excel中就有了SUMIF函数，Power BI又延伸了 SUMX和CALCULATE函数的计算，从而把聚合和条件独立分开。

完成同环比的另一个思路则是引入窗口计算，将视图维度作为聚合值二次计算的依据，在SQL中称为窗口计算（Window Calculation），Tableau对应表计算（Table Calculation）。这就是本书第9章的内容了。

@喜乐君 咨询顾问｜上海唯知唯识创始人‍‍‍‍‍‍‍‍‍‍

业务分析师、数据咨询顾问

Tableau Visionary 2021～2024

《数据可视化分析:Tableau原理与实践》2020.8

《业务可视化分析:从问题到图形的Tableau方法》2021.7

《数据可视化分析:分析原理与Tableau、SQL实践》2023.9‍‍‍

………… MORE …………