由于版本控制崩溃而丢失记录

Question

有两个表。您必须在维护版本控制的同时连接它们表"ft"：

| id | param1 | param2 |          dfrom        |           dto         |
|----|--------|--------|-----------------------|-----------------------|
|  1 |  'SS'  |  'DD'  | '01.04.2010 12:30:20' | '17.04.2010 13:10:14' |
|  1 |  'ZZ'  |  null  | '17.04.2010 13:10:15' | '18.01.2010 04:13:15' |
|  1 |  'ZZ'  |  'GG'  | '18.01.2010 04:13:16' | '12.12.2010 00:00:00' |

表"st"：

| id | param3 |          dfrom        |           dto         |
|----|--------|-----------------------|-----------------------|
|  1 |  'KK'  | '01.03.2010 12:30:20' | '02.04.2010 13:10:14' |
|  1 |  'QQ'  | '02.04.2010 13:10:15' | '12.12.2010 00:00:00' |

结果：

| id | param1 | param2 | param3 |          dfrom        |            dto        |
|  1 |  'ZZ'  |  'DD'  |  'QQ'  | '18.01.2010 04:13:16' | '12.12.2010 00:00:00' |
|  1 |  null  |  null  |  'KK'  | '01.03.2010 12:30:20' | '01.04.2010 12:30:19' |
|  1 |  'SS'  |  'DD'  |  'KK'  | '01.04.2010 12:30:20' | '02.04.2010 13:10:14' |
|  1 |  'SS'  |  'DD'  |  'QQ'  | '02.04.2010 13:10:15' | '17.04.2010 13:10:14' |
|  1 |  'ZZ'  |  null  |  'QQ'  | '17.04.2010 13:10:15' | '18.01.2010 04:13:15' |

我输了2行，我不明白如何考虑这样一个事实，即一条记录出现在所有记录之前(dfrom(，但在其他两条记录的范围内关闭

Answer 1

基本上，您希望按日期(可能还有id(拆分数据并重新组合：

with cte as (
select id, param1, param2, null as param3, dfrom, 'ft' as which
from ft
union all
select id, null, null, param3, dfrom, 'st'
from st
)
select id, dfrom, lead(dfrom) over (order by dfrom),
max(max(param1)) over (partition by grp_ft) as param1,
max(max(param2)) over (partition by grp_ft) as param2,
max(max(param3)) over (partition by grp_st) as param3
from (select cte.*,
max(dfrom) filter (where which = 'ft') over (partition by id order by dfrom) as grp_ft,
max(dfrom) filter (where which = 'st') over (partition by id order by dfrom) as grp_st
from cte
) cte
group by id, dfrom, grp_ft, grp_st;

这将按参数值拆分数据。然后，它使用窗口函数对它们进行重新组合，累积以前的值(如果有的话(。如果Postgres支持LAG()上的IGNORE NULLS选项，则不需要子查询。

这是一个数据库<>不停摆弄

此版本未考虑最终日期。问题是你没有解释该怎么做(如果你想修改，我建议你问一个新问题(。

问题是数据中似乎有有效的NULL值。通常情况下，我只会将值重置为NULL，但这似乎是一个太过分的假设。