如何在SQL Server中使用min-max填充NULL值

Question

我想使用min-max比较来获得前值和后值。

这是我的样品表。

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=322aa feb7970e25f9a85d0cd2f6c00d6

例如，这是temp01表。

我想开始&结束temp01中的NULL值。

所以，我填写

price = [NULL, NULL, 13000]

至

price = [12000, 12000, 13000]

因为CCD_ 2是CCD_ 3中的最小值。并且I填充结束NULL是[cat01, cat02]组中填充的最大值

| SEQ | cat01 | cat02 |   dt_day   | price |
+-----+-------+-------+------------+-------+
|  1  |  230  |   1   | 2019-01-01 |  NULL |
|  2  |  230  |   1   | 2019-01-02 |  NULL |
|  3  |  230  |   1   | 2019-01-03 | 13000 |
...
|  11 |  230  |   1   | 2019-01-11 |  NULL |
|  12 |  230  |   1   | 2019-01-12 |  NULL |
|  1  |  230  |   2   | 2019-01-01 |  NULL |
|  2  |  230  |   2   | 2019-01-02 |  NULL |
|  3  |  230  |   2   | 2019-01-03 | 12000 |
...
|  12 |  230  |   2   | 2019-01-11 |  NULL |
|  13 |  230  |   2   | 2019-01-12 |  NULL |

[结果]

| SEQ | cat01 | cat02 |   dt_day   | price | 
+-----+-------+-------+------------+-------+
|  1  |  230  |   1   | 2019-01-01 | 12000 | --START
|  2  |  230  |   1   | 2019-01-02 | 12000 | 
|  3  |  230  |   1   | 2019-01-03 | 13000 |
|  4  |  230  |   1   | 2019-01-04 | 12000 |
|  5  |  230  |   1   | 2019-01-05 |  NULL |  
|  6  |  230  |   1   | 2019-01-06 |  NULL |
|  7  |  230  |   1   | 2019-01-07 | 19000 |
|  8  |  230  |   1   | 2019-01-08 | 20000 |
|  9  |  230  |   1   | 2019-01-09 | 21500 | 
| 10  |  230  |   1   | 2019-01-10 | 21500 | 
| 11  |  230  |   1   | 2019-01-11 | 21500 | 
| 12  |  230  |   1   | 2019-01-12 | 21500 | 
| 13  |  230  |   1   | 2019-01-13 | 21500 | --END   
|  1  |  230  |   2   | 2019-01-01 | 12000 | --START
|  2  |  230  |   2   | 2019-01-02 | 12000 | 
|  3  |  230  |   2   | 2019-01-03 | 12000 | 
|  4  |  230  |   2   | 2019-01-04 | 17000 |
|  5  |  230  |   2   | 2019-01-05 | 22000 |   
|  6  |  230  |   2   | 2019-01-06 |  NULL |
|  7  |  230  |   2   | 2019-01-07 | 23000 |
|  8  |  230  |   2   | 2019-01-08 | 23200 |
|  9  |  230  |   2   | 2019-01-09 |  NULL |
| 10  |  230  |   2   | 2019-01-10 | 24000 |
| 11  |  230  |   2   | 2019-01-11 | 24000 | 
| 12  |  230  |   2   | 2019-01-12 | 24000 | 
| 13  |  230  |   2   | 2019-01-13 | 24000 | --END

请告诉我使用线性关系填充NULL的好方法。

Answer 1

查找价格为GROUP BYcat01, cat02的min()和max()。同时查找price is not null所在行的min和maxseq

之后，只需将inner join添加到您的表中并更新where price is null

with val as
(
select cat01, cat02, 
min_price = min(price), 
max_price = max(price),
min_seq   = min(case when price is not null then seq end),
max_seq   = max(case when price is not null then seq end)
from   temp01
group by cat01, cat02
)
update t
set    price = case when t.seq < v.min_seq then min_price
when t.seq > v.max_seq then max_price
end
FROM   temp01 t
inner join val v on   t.cat01 = v.cat01
and  t.cat02 = v.cat02
where  t.price is null  

dbfiddle

EDIT：返回price作为SELECT查询中的新列

with val as
(
select cat01, cat02, min_price = min(price), max_price = max(price),
min_seq = min(case when price is not null then seq end),
max_seq = max(case when price is not null then seq end)
from   temp01
group by cat01, cat02
)
select t.*,
new_price = coalesce(t.price,
case when t.seq < v.min_seq then min_price
when t.seq > v.max_seq then max_price
end)
FROM   temp01 t
left join val v on t.cat01 = v.cat01
and  t.cat02 = v.cat02

更新的dbfiddle