Apache Superset关联查询避免数据重复的技术咨询

问题背景 #

• 基于Apache Superset查询，仅支持SELECT语句，无法使用UNION，不能修改数据库
• 关联opentripview和closedtripview时出现聚合结果异常，数值远大于实际值，原因是每条open数据与所有对应closed数据关联，产生笛卡尔积导致重复计算
• 单独查询两张表聚合结果正确，需要用FULL JOIN保留无对应closed数据的open行（对应closed字段显示0）

当前使用的SQL语句：

SELECT 
concat(opentripview.locid,'_',opentripview.assettype) AS Key,
opentripview.locid AS GLID,
opentripview.assettype AS Material,
SUM(opentripview.tripdays) AS OpenTripDays, 
COUNT(opentripview.tripdays) AS OpenTripCount, 
SUM(closedtripview.tripdays) AS ClosedTripDays, 
COUNT(closedtripview.tripdays) AS ClosedTripCount 

FROM opentripview 

INNER JOIN closedtripview

ON concat(opentripview.locid,'_',opentripview.assettype)
=concat(closedtripview.locid,'_',closedtripview.assettype)
AND opentripview.assettype=closedtripview.assettype
AND opentripview.locid=closedtripview.locid

WHERE (opentripview.assettype like '%828' or opentripview.assettype like '%24024')

AND opentripview.locid='1000094424'

AND opentripview.division='PA'

AND closedtripview.TripStartDate < DATE '2024-05-01'
AND closedtripview.TripEndDate >= DATE '2024-02-01'
AND closedtripview.TripEndDate < DATE '2024-05-01'

GROUP BY      concat(opentripview.locid,'_',opentripview.assettype),
opentripview.locid,opentripview.assettype;

原数据示例：

Key     GLID          Material     OTD     OTC     CTD     CTC
X_X     1000094424    828          1000    10      0       0
X_X     1000094424    828          2000    20      0       0
X_X     1000094424    828          0       0       500     10
X_X     1000094424    828          0       0       750     10

关联后错误结果：

Key     GLID          Material     OTD     OTC     CTD     CTC
X_X     1000094424    828          9000    90      3750    60

解决方案 #

核心思路是先分别对两张表按聚合维度统计，再关联结果，避免先关联产生笛卡尔积后再聚合导致的重复计算。同时用FULL JOIN保留两边的所有行，用COALESCE将NULL值转为0。

修改后的SQL：

SELECT 
    COALESCE(o.Key, c.Key) AS Key,
    COALESCE(o.GLID, c.GLID) AS GLID,
    COALESCE(o.Material, c.Material) AS Material,
    COALESCE(o.OpenTripDays, 0) AS OpenTripDays,
    COALESCE(o.OpenTripCount, 0) AS OpenTripCount,
    COALESCE(c.ClosedTripDays, 0) AS ClosedTripDays,
    COALESCE(c.ClosedTripCount, 0) AS ClosedTripCount
FROM (
    -- 先聚合opentripview的数据
    SELECT 
        concat(locid,'_',assettype) AS Key,
        locid AS GLID,
        assettype AS Material,
        SUM(tripdays) AS OpenTripDays,
        COUNT(tripdays) AS OpenTripCount
    FROM opentripview
    WHERE (assettype LIKE '%828' OR assettype LIKE '%24024')
      AND locid = '1000094424'
      AND division = 'PA'
    GROUP BY concat(locid,'_',assettype), locid, assettype
) o
FULL JOIN (
    -- 先聚合closedtripview的数据
    SELECT 
        concat(locid,'_',assettype) AS Key,
        locid AS GLID,
        assettype AS Material,
        SUM(tripdays) AS ClosedTripDays,
        COUNT(tripdays) AS ClosedTripCount
    FROM closedtripview
    WHERE (assettype LIKE '%828' OR assettype LIKE '%24024')
      AND locid = '1000094424'
      AND TripStartDate < DATE '2024-05-01'
      AND TripEndDate >= DATE '2024-02-01'
      AND TripEndDate < DATE '2024-05-01'
    GROUP BY concat(locid,'_',assettype), locid, assettype
) c
ON o.Key = c.Key
AND o.GLID = c.GLID
AND o.Material = c.Material;

说明 #

1. 子查询分别对两张表按Key、GLID、Material聚合，得到每个维度下的正确统计值，避免了跨表关联时的笛卡尔积
2. 使用FULL JOIN确保两边所有聚合后的行都被保留，不管是否有对应匹配项
3. COALESCE函数将关联后出现的NULL值替换为0，满足无对应数据时显示0的需求
4. 注意将原WHERE条件拆分到对应的子查询中，确保过滤逻辑正确

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