如何对UNION集合操作返回的数据进行值聚合与NULL值重排？

需求概述 #

我需要处理一种特殊的结果集重组需求，具体规则如下：

• 当单列存在多个非NULL值且对应行的其余列均为NULL时，需将该列的所有值按行排列（非NULL值优先居上）
• 对每一列单独处理，把所有非NULL值移动到结果集的顶部行，NULL值放在下方
• 如果某一行没有任何非NULL值，则全部显示NULL

示例场景 #

以下是测试用的SQL语句，它通过UNION组合了多个单行结果：

SELECT * FROM (
 (SELECT 6 + 2 AS val1, NULL AS val2, NULL AS val3, NULL AS val4, 5 + 5 AS val5, NULL AS val6 FROM DUAL)
 UNION
 (SELECT NULL AS val1, 6 - 2 AS val2, NULL AS val3, NULL AS val4, 9 - 3 AS val5, 7 - 3 AS val6 FROM DUAL)
 UNION
 (SELECT NULL AS val1, NULL AS val2, 6 * 2 AS val3, NULL AS val4, NULL AS val5, NULL AS val6 FROM DUAL)
 UNION
 (SELECT NULL AS val1, NULL AS val2, NULL AS val3, 6 / 2 AS val4, NULL AS val5, NULL AS val6 FROM DUAL)
) A;

当前实际返回结果 #

执行上述SQL后，得到的结果是：

+------+------+------+--------+------+------+
| val1 | val2 | val3 | val4   | val5 | val6 |
+------+------+------+--------+------+------+
| 8    | NULL | NULL | NULL   | 10   | NULL |
| NULL | 4    | NULL | NULL   | 6    | 4    |
| NULL | NULL | 12   | NULL   | NULL | NULL |
| NULL | NULL | NULL | 3.0000 | NULL | NULL |
+------+------+------+--------+------+------+
4 rows in set (0.00 sec)

期望的重组结果 #

我们需要将结果集重组为如下形式，把各列的非NULL值集中到顶部行，多余的非NULL值依次向下排列：

+------+------+------+--------+------+------+
| val1 | val2 | val3 | val4   | val5 | val6 |
+------+------+------+--------+------+------+
| 8    | 4    | 12   | 3.0000 | 10   | 4    |
| NULL | NULL | NULL | NULL   | 6    | NULL |
+------+------+------+--------+------+------+
1 row in set (0.00 sec)

针对MySQL 8.0+的解决方案 #

根据Gordon Linoff的思路，我们可以利用ROW_NUMBER()窗口函数为每一列的非NULL值分配行号，然后通过自连接将各列的值按行号对应整合，具体SQL如下：

WITH column_rows AS (
    -- 为每个列的非NULL值分配行号，确保非NULL值行号靠前
    SELECT
        val1,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN val1 IS NOT NULL THEN 0 ELSE 1 END) AS rn1,
        val2,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN val2 IS NOT NULL THEN 0 ELSE 1 END) AS rn2,
        val3,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN val3 IS NOT NULL THEN 0 ELSE 1 END) AS rn3,
        val4,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN val4 IS NOT NULL THEN 0 ELSE 1 END) AS rn4,
        val5,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN val5 IS NOT NULL THEN 0 ELSE 1 END) AS rn5,
        val6,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN val6 IS NOT NULL THEN 0 ELSE 1 END) AS rn6
    FROM (
        SELECT 6 + 2 AS val1, NULL AS val2, NULL AS val3, NULL AS val4, 5 + 5 AS val5, NULL AS val6 FROM DUAL
        UNION
        SELECT NULL AS val1, 6 - 2 AS val2, NULL AS val3, NULL AS val4, 9 - 3 AS val5, 7 - 3 AS val6 FROM DUAL
        UNION
        SELECT NULL AS val1, NULL AS val2, 6 * 2 AS val3, NULL AS val4, NULL AS val5, NULL AS val6 FROM DUAL
        UNION
        SELECT NULL AS val1, NULL AS val2, NULL AS val3, 6 / 2 AS val4, NULL AS val5, NULL AS val6 FROM DUAL
    ) A
),
-- 提取所有需要的行号，覆盖所有列的非NULL值数量
all_rows AS (
    SELECT rn1 AS rn FROM column_rows WHERE val1 IS NOT NULL
    UNION
    SELECT rn2 AS rn FROM column_rows WHERE val2 IS NOT NULL
    UNION
    SELECT rn3 AS rn FROM column_rows WHERE val3 IS NOT NULL
    UNION
    SELECT rn4 AS rn FROM column_rows WHERE val4 IS NOT NULL
    UNION
    SELECT rn5 AS rn FROM column_rows WHERE val5 IS NOT NULL
    UNION
    SELECT rn6 AS rn FROM column_rows WHERE val6 IS NOT NULL
)
-- 按行号匹配各列对应值，聚合得到最终结果
SELECT
    MAX(CASE WHEN cr.rn1 = ar.rn THEN cr.val1 END) AS val1,
    MAX(CASE WHEN cr.rn2 = ar.rn THEN cr.val2 END) AS val2,
    MAX(CASE WHEN cr.rn3 = ar.rn THEN cr.val3 END) AS val3,
    MAX(CASE WHEN cr.rn4 = ar.rn THEN cr.val4 END) AS val4,
    MAX(CASE WHEN cr.rn5 = ar.rn THEN cr.val5 END) AS val5,
    MAX(CASE WHEN cr.rn6 = ar.rn THEN cr.val6 END) AS val6
FROM all_rows ar
LEFT JOIN column_rows cr ON 
    cr.rn1 = ar.rn OR cr.rn2 = ar.rn OR cr.rn3 = ar.rn OR 
    cr.rn4 = ar.rn OR cr.rn5 = ar.rn OR cr.rn6 = ar.rn
GROUP BY ar.rn
ORDER BY ar.rn;

方案说明 #

1. column_rows CTE：为每个列的非NULL值分配递增行号，通过排序规则保证非NULL值的行号从1开始，NULL值行号靠后
2. all_rows CTE：收集所有列中出现过的有效行号，确保结果集的行数等于所有列中非NULL值数量的最大值
3. 最终查询：通过左连接将各列对应行号的值匹配，用MAX()聚合函数提取每行的非NULL值，最后按行号排序得到符合要求的结果

内容的提问来源于stack exchange，提问作者Nɪsʜᴀɴᴛʜ ॐ