如何按表A粒度关联聚合不同粒度表B以获取installs数据

需求分析 #

需要以表A的字段粒度为基准，对表B的installs字段进行求和，核心规则是：

• 当表A的字段为具体值时，表B中对应字段需等于该值，或为NULL；
• 当表A的字段为NULL时，表B中对应字段无限制（只要其他非NULL字段匹配）。

解决方案（SQL） #

SELECT 
    A.id,
    A.country,
    A.platform,
    A.retargeting,
    SUM(B.installs) AS installs
FROM 
    表A A
LEFT JOIN 
    表B B ON 
        A.id = B.id
        AND A.country = B.country
        AND (A.platform IS NULL OR B.platform = A.platform OR B.platform IS NULL)
        AND (A.retargeting IS NULL OR B.retargeting = A.retargeting OR B.retargeting IS NULL)
GROUP BY 
    A.id, A.country, A.platform, A.retargeting;

逻辑说明 #

1. 主表关联：以表A为基准，通过LEFT JOIN确保结果完全匹配表A的行结构；
2. 匹配规则：
   • 强制匹配id和country（表A中country均为非NULL值）；
   • platform字段：表A非NULL时，匹配表B中等于该值或为NULL的行；表A为NULL时，不限制表B的platform；
   • retargeting字段：规则与platform一致；
3. 分组求和：按表A的所有字段分组，对匹配到的表B行的installs求和，得到每个分组的总安装量。

对应示例验证 #

• 表A第一行（id=1, Italy, iOS, true）：仅匹配表B中完全一致的行，求和结果为99；
• 表A第二行（id=1, France, iOS, NULL）：匹配表B中id=1, France, iOS的所有行，求和结果为100；
• 表A第三行（id=2, Italy, Android, false）：匹配表B中id=2, Italy, Android且retargeting为false或NULL的行，求和500+400=900；
• 表A第四行（id=2, Italy, NULL, NULL）：匹配表B中id=2, Italy的所有行，求和500+400+300=1200。

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