在Apache Superset中实现分组后去重Target求和的自定义SQL指标

表结构与数据 #

现有表XYZ数据如下：

Dist        SM     Code    Target
0000022520  ISR03   SR06    48
0000022520  ISR03   SR06    48
0000022520  ISR03   SR06    48
0000022520  ISR03   SR06    48
0000022520  ISR03   SR06    48
0000022521  ISR03   SR07    16

需求 #

创建Apache Superset自定义SQL指标，按Dist、SM、Code分组后取每个组的唯一Target值，再对这些值求和（示例结果为64）。

尝试的SQL与报错 #

尝试使用子查询写法，Superset报错Error: Custom SQL fields cannot contain sub-queries：

SUM(Target_total) AS total_target
FROM (
    SELECT SUM(DISTINCT Target) AS Target_total
    FROM XYZ  GROUP BY "Dist", "SM", "Code"
) AS subquery;

方法1：使用窗口函数替代子查询 #

利用窗口函数标记每组的第一条记录，仅对该记录的Target值求和，无需子查询即可实现需求：

SUM(CASE WHEN ROW_NUMBER() OVER (PARTITION BY "Dist", "SM", "Code" ORDER BY "Target") = 1 THEN "Target" ELSE 0 END) AS total_target

原理说明 #

• ROW_NUMBER() OVER (PARTITION BY "Dist", "SM", "Code" ORDER BY "Target")：按Dist、SM、Code分组，给每组内的记录编号，第一条记录编号为1
• CASE语句仅保留每组第一条记录的Target值，其余为0
• 最后通过SUM求和得到所有组唯一Target的总和

方法2：数据库层面创建预聚合视图 #

在数据库中预先创建聚合视图，将分组后的唯一Target值存储起来，再在Superset中基于该视图创建指标：

1. 在数据库执行创建视图语句：

CREATE VIEW XYZ_AGG AS
SELECT DISTINCT "Dist", "SM", "Code", "Target"
FROM XYZ;

2. 在Superset中连接该视图，直接创建指标：

SUM("Target") AS total_target

方法3：利用Superset原生分组与指标组合 #

在Superset探索界面：

• 先将Dist、SM、Code添加到分组维度
• 添加指标SUM(DISTINCT "Target")，此时会得到每个组的唯一Target值
• 若需最终求和结果，可将该探索结果保存为数据集，再基于此数据集创建仅包含SUM(Target)的指标

内容的提问来源于stack exchange，提问作者Deb