With the following table, I need to make a query to replace the null values of “time” (number of minutes, integer) by a value consistent with the preceding and following values, with partitioning by “esz” and sorting by ascending “rank”. In the example, the data is already sorted.
Ideally:
- the value should be as close as possible to the preceding one (+1) when it exists
- the value should be as close as possible to the one that follows (-1) if none precedes (rank 1)
- if the difference between the preceding and following values is insufficient (0 or 1, for example), then the value must be equal to the smaller of the two.
- the value must never be less than the previous one, nor greater than the following one.
No “esz” can have only empty “time” values
I’ve tried LAG() and LEAD() but, as the null values can be anywhere, I don’t think this is the best option.
I guess I’d have to use a recursive query, but I can’t do it.
Can anyone help me find a solution?
Thank you very much
| id | esz | code | rank | time |
|---|---|---|---|---|
| 1 | 1 | “SOM” | 1 | 5 |
| 2 | 1 | “QUI” | 2 | NULL |
| 3 | 1 | “VER” | 3 | 10 |
| 4 | 1 | “NSC” | 4 | 15 |
| 5 | 1 | “3SM” | 99 | NULL |
| 6 | 2 | “QUI” | 1 | 7 |
| 7 | 2 | “VER” | 2 | NULL |
| 8 | 2 | “SOM” | 3 | NULL |
| 9 | 2 | “NSC” | 4 | 12 |
| 10 | 2 | “3SM” | 99 | NULL |
| 11 | 3 | “NSC” | 1 | NULL |
| 12 | 3 | “VER” | 2 | NULL |
| 13 | 3 | “QUI” | 3 | 11 |
| 14 | 3 | “SOM” | 4 | 12 |
| 15 | 3 | “3SM” | 99 | NULL |
| 16 | 4 | “SOM” | 1 | 2 |
| 17 | 4 | “QUI” | 2 | NULL |
| 18 | 4 | “NSC” | 3 | 3 |
| 19 | 4 | “VER” | 4 | NULL |
| 20 | 4 | “3SM” | 99 | NULL |
| 21 | 5 | “NSC” | 1 | NULL |
| 22 | 5 | “SOM” | 2 | 4 |
| 23 | 5 | “VER” | 3 | NULL |
| 24 | 5 | “QUI” | 4 | 7 |
| 25 | 5 | “3SM” | 99 | NULL |