按prod和geo分组，获取各rule列首个非空值的SQL实现问题

需求：按prod和geo列分组，为每个分组下的rule_1、rule_2、rule_3、rule_4列分别获取第一个非空值。

尝试了如下SQL，但该语句仅对第一组prod-geo组合生效，后续分组无法正常获取对应组的目标值：

SELECT prod, geo,
(SELECT rule_1 FROM temp WHERE rule_1 IS NOT NULL LIMIT 1) as rule_1,
(SELECT rule_2 FROM temp WHERE rule_2 IS NOT NULL LIMIT 1) as rule_2,
(SELECT rule_3 FROM temp WHERE rule_3 IS NOT NULL LIMIT 1) as rule_3,
(SELECT rule_4 FROM temp WHERE rule_4 IS NOT NULL LIMIT 1) as rule_4
FROM temp
GROUP BY prod, geo

问题原因 #

原SQL里的子查询没有关联外层的prod和geo字段，相当于每次都从整张表取全局的首个非空值，而非当前分组内的，所以只有第一组看起来“正常”，后续分组结果都不对。

解决方案 #

方案1：关联子查询（适配多数SQL数据库） #

修改子查询，让它只查询当前prod-geo分组内的非空值，再取第一个：

SELECT 
    prod, 
    geo,
    (SELECT rule_1 FROM temp t2 WHERE t2.prod = t1.prod AND t2.geo = t1.geo AND t2.rule_1 IS NOT NULL LIMIT 1) AS rule_1,
    (SELECT rule_2 FROM temp t2 WHERE t2.prod = t1.prod AND t2.geo = t1.geo AND t2.rule_2 IS NOT NULL LIMIT 1) AS rule_2,
    (SELECT rule_3 FROM temp t2 WHERE t2.prod = t1.prod AND t2.geo = t1.geo AND t2.rule_3 IS NOT NULL LIMIT 1) AS rule_3,
    (SELECT rule_4 FROM temp t2 WHERE t2.prod = t1.prod AND t2.geo = t1.geo AND t2.rule_4 IS NOT NULL LIMIT 1) AS rule_4
FROM temp t1
GROUP BY prod, geo

方案2：窗口函数实现（更简洁，支持窗口函数的数据库可用） #

用FIRST_VALUE窗口函数，按prod和geo分组，优先取非空值：

SELECT DISTINCT
    prod,
    geo,
    FIRST_VALUE(rule_1) OVER (PARTITION BY prod, geo ORDER BY CASE WHEN rule_1 IS NOT NULL THEN 0 ELSE 1 END) AS rule_1,
    FIRST_VALUE(rule_2) OVER (PARTITION BY prod, geo ORDER BY CASE WHEN rule_2 IS NOT NULL THEN 0 ELSE 1 END) AS rule_2,
    FIRST_VALUE(rule_3) OVER (PARTITION BY prod, geo ORDER BY CASE WHEN rule_3 IS NOT NULL THEN 0 ELSE 1 END) AS rule_3,
    FIRST_VALUE(rule_4) OVER (PARTITION BY prod, geo ORDER BY CASE WHEN rule_4 IS NOT NULL THEN 0 ELSE 1 END) AS rule_4
FROM temp

如果数据有明确的排序依据（比如按创建时间），可以把ORDER BY里的判断替换成对应的字段，比如ORDER BY create_time ASC，确保取到分组内最早出现的非空值。

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