垂直合并到一组中的多行上

Question

这个表是一个大混乱查询的(假例子(结果。

=> WITH data(run, host, status, field_ip, control_ip, field_data, control_data) AS (
VALUES (1, 1, 'no_control', '10.0.0.1', NULL, 'foo', NULL),
(1, 2, 'good', '10.0.0.1', '10.0.0.1', 'bar', 'bar'),
(1, 3, 'problem_1', '10.0.0.1', NULL, 'bar', NULL),
(1, 3, 'problem_2', '10.0.0.2', NULL, 'baz', NULL),
(1, 3, NULL, NULL, '192.168.1.1', NULL, 'wallace'))
SELECT * FROM data;
run | host |   status   | field_ip | control_ip  | field_data | control_data 
-----+------+------------+----------+-------------+------------+--------------
1 |    1 | no_control | 10.0.0.1 |             | foo        | 
1 |    2 | good       | 10.0.0.1 | 10.0.0.1    | bar        | bar
1 |    3 | problem_1  | 10.0.0.1 |             | bar        | 
1 |    3 | problem_2  | 10.0.0.2 |             | baz        | 
1 |    3 |            |          | 192.168.1.1 |            | wallace

我想合并"；control_ip"；以及"；control_ data"；根据以下规则，从非NULL行到NULL行的字段：分别考虑具有相同"run"one_answers"host"值的每组行。在每个此类组中：

1. 所有字段都不是NULL的每一行都将以不变的方式输出，并从组中删除。(例如，行(1,2(的输出将保持不变。这是其组中唯一的一行，因此该组现在已结束。(
2. 对于"field_ip"one_answers"field_data"字段为NOT NULL，但"control_ip"或"control_data"为NULL的每一行，在组中查找相反情况的另一行，并将其"control_ip"one_answers"control_data'"字段复制到第一行。然后输出修改后的行并将其从组中删除。
   • 如果不存在"相反情况为真的另一行"，则输出第一行不变(例如，输出第(1,1(行不变(
   • 如果有多个"相反的另一行"，请任意选择一个，并在复制其值后丢弃它。(示例数据集中没有此示例。(
   • 如果只有一个"相反的另一行"，请不要在复制其值后丢弃它。(例如，行(1,3，NULL，NULL，'192.168.1.1'，NULL，'wallace'(应将其control_ip/control_Data值复制到(1,3、problem_1,10.0.0.1(和(1,3和problem_2,10.0.0.2(行上。(
3. 重复步骤2，直到组中不再有"field_ip"one_answers"field_data"字段为NOT NULL的行
4. 放弃组中的所有剩余行(这些行的field_ip和field_data都为NULL(

上述样本数据的期望输出为：

run | host |   status   | field_ip | control_ip  | field_data | control_data 
-----+------+------------+----------+-------------+------------+--------------
1 |    1 | no_control | 10.0.0.1 |             | foo        | 
1 |    2 | good       | 10.0.0.1 | 10.0.0.1    | bar        | bar
1 |    3 | problem_1  | 10.0.0.1 | 192.168.1.1 | bar        | wallace
1 |    3 | problem_2  | 10.0.0.2 | 192.168.1.1 | baz        | wallace

这是一个类似于SQL server中如何将多行文本连接到单个文本字符串的问题？，但这个答案对我来说不起作用，因为GROUP BY run, host加聚合每个组只能发出一行，而在某些情况下，我需要每个组发出多行。我尝试了一些涉及GROUP BY run, host, field_ip的东西，但第三(1,3(行被视为自己的组，这不好。此外，我使用的是PostgreSQL(12(，而不是SQL Server，并且AIUI PIVOT是SQL Server。

注意：大混乱查询的最后一步，即生成所示的表，是对run、host和field_ip=control_ip执行FULL JOIN的结果。如果从前驱开始到完全加入更容易做到我所要求的，请告诉我。

你有什么建议？

Answer 1

嗯。规则不是100%明确的，但这确实符合你想要做的：

select t.*
from (select t.*,
max(field_ip) over (partition by run, host) as imputed_field_ip,
count(*) over (partition by run, host) as cnt
from t
) t
where cnt = 1 or field_ip is null;

Answer 2

使用MAX() OVER ()可以用作"一种垂直合并"，前提是只有一个NOT NULL值可供选择。

我在下面添加了一行示例数据，展示了我如何处理可能有多个值可供选择的问题；

• 我只从对应的field_列为NULL的行中选取

最后，我排除了field_列都是NULL的记录。

WITH
data(run, host, status, field_ip, control_ip, field_data, control_data)
AS
(
VALUES (1, 1, 'no_control', '10.0.0.1', NULL, 'foo', NULL),
(1, 2, 'good', '10.0.0.1', '10.0.0.1', 'bar', 'bar'),
(1, 3, 'problem_0', '10.0.0.1', '192.168.2.1', 'bar', 'zaphod'),
(1, 3, 'problem_1', '10.0.0.1', NULL, 'bar', NULL),
(1, 3, 'problem_2', '10.0.0.2', NULL, 'baz', NULL),
(1, 3, NULL, NULL, '192.168.1.1', NULL, 'wallace')
),
replace_nulls AS
(
SELECT
run,
host,
status,
field_ip,
COALESCE(
control_ip,
MAX(CASE WHEN field_ip IS NULL THEN control_ip END) OVER (PARTITION BY run, host)
)
AS control_ip,
field_data,
COALESCE(
control_data,
MAX(CASE WHEN field_data IS NULL THEN control_data END) OVER (PARTITION BY run, host)
)
AS control_data
FROM
data
)
SELECT
replace_nulls.*
FROM
replace_nulls
WHERE
field_ip IS NOT NULL
OR
field_data IS NOT NULL
;

演示：https://dbfiddle.uk/?rdbms=postgres_13&fiddle=204569771c9afb372b971e1e8740b80