Рекурсивный запрос для замены нулевых значений
В следующей таблице мне нужно сделать запрос на замену нулевых значений «времени» (количество минут, целое число) значением, соответствующим предыдущему и последующему значениям, с разделением по «esz» и сортировкой по возрастанию «ранга». ». В примере данные уже отсортированы. В идеале:
- значение должно быть как можно ближе к предыдущему (+1), если оно существует.
- значение должно быть как можно ближе к следующему (-1), если ни одно из них не предшествует (ранг 1)
- если разница между предыдущим и последующим значениями недостаточна (например, 0 или 1), то значение должно быть равно меньшему из двух.
- значение никогда не должно быть меньше предыдущего или больше следующего. Ни один «esz» не может иметь только пустые значения «time». Я пробовал LAG() и LEAD(), но, поскольку нулевые значения могут быть где угодно, я не думаю, что это лучший вариант. Думаю, мне придется использовать рекурсивный запрос, но я не могу этого сделать. Может ли кто-нибудь помочь мне найти решение? Большое спасибо
Пример вывода:
Я могу справиться с большинством случаев в таблице с помощью LAG(), LEAD() и CASE. Но с некоторыми из них у меня все еще есть проблемы: например, id=11 и id=12. Либо мне нужно вернуться назад от значения id=13, либо я должен знать при заполнении id=11, что мне нужно оставить значение, доступное для заполнения id=12.
Мой последний запрос:
SELECT
esz,
code,
rank,
CASE
WHEN time IS NOT NULL THEN time
ELSE
CASE
WHEN rank = 1 THEN 1
WHEN LAG(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL AND LEAD(time) OVER(PARTITION BY esz ORDER BY rank) IS NULL THEN LAG(time) OVER(PARTITION BY esz ORDER BY rank) + 1
WHEN LEAD(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL AND LAG(time) OVER(PARTITION BY esz ORDER BY rank) IS NULL THEN LEAD(time) OVER(PARTITION BY esz ORDER BY rank) - 1
WHEN LAG(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL AND LEAD(time) OVER(PARTITION BY esz ORDER BY rank) IS NOT NULL THEN
CASE
WHEN LEAD(time) OVER(PARTITION BY esz ORDER BY rank) - LAG(time) OVER(PARTITION BY esz ORDER BY rank) <= 1 THEN LAG(time) OVER(PARTITION BY esz ORDER BY rank)
ELSE LAG(time) OVER(PARTITION BY esz ORDER BY rank) +1
END
WHEN rank > LEAD(rank) OVER(PARTITION BY esz ORDER BY rank) OR LEAD(rank) OVER(PARTITION BY esz ORDER BY rank) IS NULL THEN 999
END
END AS time
FROM mytable
ORDER BY esz, rank
Вот результат:
Я выделил значения, которые отличаются от ожидаемого результата. Не идеально, но противоречий нет. Однако может возникнуть проблема, если будет вставлено более трех последовательных переопределений.
Я добавил пример решения в свой пост. заранее спасибо
"Мне нужно сделать запрос на замену..." Но где ваш запрос? Stackoverflow не является (бесплатной) службой написания кода...
Ваш комментарий кажется полным упреков... Мне мой код не показался интересным, поскольку он не достигает цели. Но если для получения помощи мне придется предоставить подтверждение своей работы, я без проблем добавлю последнюю версию.
Сколько последовательных значений NULL появится в столбце time для условия: over(partition by esz order by ranks). - @Николас
К сожалению, я этого не знаю. Пример таблицы основан на двух наборах: - теоретический список, ранг которого для каждого esz основан на времени в минутах. - список отступлений с указанием для каждого ESZ, кода и желаемого звания. Время переопределения неизвестно, известен только желаемый ранг. Количество переопределений является случайным. Для каждого esz оно варьируется от 0 до количества рангов.
В таблице эти два набора упорядочены следующим образом: esz, Rank, а затем, для того же ранга, приоритет имеет переопределение. Затем новый ранг каждой строки пересчитывается. Когда теоретическое время известно, мы оставляем его как есть. Когда имеет место отступление и теоретическое время неизвестно, мы должны вычислить новый ранг, соответствующий теоретическому времени в установленном порядке. Надеюсь, я ясно объяснил свою потребность. Поскольку я не знаю количества или положения переопределений, мне сложно использовать LAG() и LEAD(). В любом случае я не могу этого сделать.
Ответы 4
Попробуйте использовать свой полный набор данных — я добавил сюда шестую группу со всем временем NULL, чтобы посмотреть, что произойдет:
with data(id, esz, code, rank, time) as (
select 1, 1, 'SOM', 1, 5 union all
select 2, 1, 'QUI', 2, NULL union all
select 3, 1, 'VER', 3, 10 union all
select 4, 1, 'NSC', 4, 15 union all
select 5, 1, '3SM', 99, NULL union all
select 6, 2, 'QUI', 1, 7 union all
select 7, 2, 'VER', 2, NULL union all
select 8, 2, 'SOM', 3, NULL union all
select 9, 2, 'NSC', 4, 12 union all
select 10, 2, '3SM', 99, NULL union all
select 11, 3, 'NSC', 1, NULL union all
select 12, 3, 'VER', 2, NULL union all
select 13, 3, 'QUI', 3, 11 union all
select 14, 3, 'SOM', 4, 12 union all
select 15, 3, '3SM', 99, NULL union all
select 16, 4, 'SOM', 1, 2 union all
select 17, 4, 'QUI', 2, NULL union all
select 18, 4, 'NSC', 3, 3 union all
select 19, 4, 'VER', 4, NULL union all
select 20, 4, '3SM', 99, NULL union all
select 21, 5, 'NSC', 1, NULL union all
select 22, 5, 'SOM', 2, 4 union all
select 23, 5, 'VER', 3, NULL union all
select 24, 5, 'QUI', 4, 7 union all
select 25, 5, '3SM', 99, NULL union all
select 26, 6, 'YYY', 50, NULL union
select 27, 6, 'ZZZ', 99, NULL
)
select d.id, d.esz, d.code, d.rank, time, --grp, ptime, pdelta, ntime, ndelta,
coalesce(time,
case when ntime-ndelta < ptime+pdelta
then
-- neither ptime and ntime are NULL: we take the smallest
-- but not smaller than latest NOT NULL one
greatest(ntime-ndelta, ptime)
else
-- one of ptime, ntime may be NULL
-- as latest option we take the ordering in the group of NULL
coalesce(ptime+pdelta, ntime-ndelta,pdelta)
end
) as time_output
from (
select d.*,
-- pdelta: delta in the NULL group from previous NOT NULL in increasing order
row_number() over(partition by esz, grp order by rank) as pdelta,
-- ndelta: delta in the NULL group from next NOT NULL in decreasing order
row_number() over(partition by esz, grp order by rank desc) as ndelta
from (
select d.*,
-- ptime is the latest NOT NULL in the partition in the range before up to to the previous row
coalesce(time, ltime) as ptime,
-- attribution of an unique number to each group of consecutive NULL in the partition
-- that will allow to give an ordering number (pdelta and ndelta) to each NULL inside each subgroup
row_number() over(partition by esz order by rank) -
sum(case when time is null then 1 end) over(partition by esz,
case when time is null then 1 end
order by case when time is null then 1 end, rank) as grp,
-- ntime is the first next NOT NULL in the partition in the range after starting from the next row
coalesce(time, ftime )
as ntime
from (
-- the technique to get around the fact that PostgreSQL doesn't have
-- "ignore nulls" option in first_value() and last_value()
select id, esz, code, rank, time,
coalesce(time, max(time) over (partition by esz, pgrp)) as ltime,
coalesce(time, max(time) over (partition by esz, ngrp)) as ftime
from
(
select id, esz, code, rank, time,
count(time) over(partition by esz order by rank) as pgrp,
count(time) over(partition by esz order by rank desc) as ngrp
from data
)
) d
) d
) d
order by esz, rank ;
https://dbfiddle.uk/8Z48_mg4 для сравнения с версией ORACLE, поддерживающей first_value/last_value с «игнорировать нули» https://dbfiddle.uk/ycoe6qPj
Вы можете начать с чего-то вроде этого DBFIDDLE (https://dbfiddle.uk/adwfE2_v):
SELECT
id,
esz,
code,
rank,
CASE WHEN NOT TIME is null
THEN time
WHEN LEAD(time) OVER (PARTITION BY esz ORDER BY id) - LAG(time) OVER (PARTITION BY esz ORDER by id) >=2
OR LEAD(time) OVER (PARTITION BY esz ORDER BY id) is null
THEN (LAG(time) OVER (ORDER BY id))+1
ELSE 9999
END,
LAG(time) OVER (PARTITION BY esz ORDER by id) as LAG,
LEAD(time) OVER (PARTITION BY esz ORDER by id) as LEAD
FROM mytable
И продолжайте добавлять WHEN к оператору CASE, пока не перестанете видеть значения 9999 или NULL.
(После этого вы потеряете столбцы lag и lead 😉, что может быть полезно только при отладке)
выход:
Я нашел способ правильно решить эту проблему.
Поскольку в ваших входных данных есть два сценария NULL. Итак, спроектируйте запрос в соответствии с этим...
select id, esz, code, ranks,
-- lag(times) over(partition by esz order by ranks) as prev_time,
-- lead(times) over(partition by esz order by ranks) as next_time,
times,
CASE
-- Previous || Current* || Next :: Scenario
-- ANY || NOT NULL* || ANY
WHEN(times is not NULL)
THEN times
-- NOT NULL || NULL* || NOT NULL
WHEN(times is NULL AND
(lag(times) over(partition by esz order by ranks)) is not NULL AND
(lead(times) over(partition by esz order by ranks)) is not NULL)
THEN CASE
WHEN((lag(times) over(partition by esz order by ranks) + 1) <
(lead(times) over(partition by esz order by ranks)) - 1)
THEN ((lag(times) over(partition by esz order by ranks)) + 1)
ELSE
((lead(times) over(partition by esz order by ranks)) - 1)
END
-- NOT NULL || NULL || NULL* || ANY (esz = 4 and 2)
WHEN(times is NULL AND
(lag(times) over(partition by esz order by ranks)) is NULL AND
(lag(times, 2) over(partition by esz order by ranks)) is not NULL)
THEN CASE
WHEN((lead(times) over(partition by esz order by ranks)) is NULL)
THEN (lag(times, 2) over(partition by esz order by ranks) + 2)
ELSE CASE
WHEN((lag(times, 2) over(partition by esz order by ranks) + 2) <
(lead(times) over(partition by esz order by ranks) - 1))
THEN (lag(times, 2) over(partition by esz order by ranks) + 2)
ELSE (lead(times) over(partition by esz order by ranks) - 1)
END
END
-- ANY || NULL* || NULL || NOT NULL (esz = 3)
WHEN(times is NULL AND
(lead(times) over(partition by esz order by ranks)) is NULL AND
(lead(times, 2) over(partition by esz order by ranks)) is not NULL)
THEN CASE
WHEN ((lag(times) over(partition by esz order by ranks)) is NULL)
THEN (lead(times, 2) over(partition by esz order by ranks) - 2)
ELSE CASE
WHEN((lag(times) over(partition by esz order by ranks) + 1) <
(lead(times, 2) over(partition by esz order by ranks) - 2))
THEN (lag(times) over(partition by esz order by ranks) + 1)
ELSE (lead(times, 2) over(partition by esz order by ranks) - 2)
END
END
-- NOT NULL || NULL* || ANY
WHEN(times is NULL AND
(lag(times) over(partition by esz order by ranks)) is not NULL)
THEN ((lag(times) over(partition by esz order by ranks)) + 1)
-- NULL || NULL* || NOT NULL
WHEN(times is NULL AND
(lag(times) over(partition by esz order by ranks)) is NULL AND
(lead(times) over(partition by esz order by ranks)) is not NULL)
THEN ((lead(times) over(partition by esz order by ranks)) - 1)
ELSE -999
END as time_output
from T1
Запустите запрос: db<>fiddle
Вывод с проверкой:
Это решение работает для двух сценариев null сверху или снизу или для обоих, следующим образом:
value | null | *(Current Position = NULL) | null | value
value | null | *(Current Position = NULL) | anything
anything | *(Current Position = NULL) | null | value
Итак, перейдите на страницу db<>fiddle, и там я объясняю все условия одно за другим.
Если необходимо, добавьте сценарий Case-03 (null-CurrentPos-null).
Я думаю, вы понимаете мой подход. Итак, спроектируйте свой запрос в соответствии с этим.
Спасибо @art-bindu. Вы сделали то, что я отказался сделать, думая, что может существовать более простое решение: посмотреть дальше, чем предыдущая строка и следующая строка. Я протестировал ваш запрос на большем наборе: более 5 строк на esz и более последовательных нулевых значений. Как вы сказали, необходима была адаптация, и я с ней справился без проблем. Однако возникла нестыковка: в какой-то момент две последовательные доли не увеличивались (9-я из ряда r-2 и 8-я из ряда r+2). Поэтому я переработал, чтобы обрабатывать еще больше случаев. Вот результат с использованием нескольких технических значений:
- «индекс»: COUNT (время) OVER(PARTITION BY esz ORDER BY Rank),
- для каждого esz — «max_time» значений каждого индекса: MAX(time) OVER(PARTITION BY esz, index)
- «next_time», соответствующий LEAD(time_max) OVER(ORDER BY esz, Rank) Я просматриваю строки 3 раза, и если при последнем запуске остались NULL, я присваиваю значение «next_time». Остальные значения могут соответствовать либо значениям в середине длинной серии последовательных NULL, либо значениям, которые заключены в рамки LAG() и LEAD(), разница которых невелика.
Вот мой запрос:
WITH
e1 AS (SELECT esz, code, rank, time AS initial_time, time FROM dni.test),
e2_1 AS (SELECT esz, code, rank, initial_time, time, COUNT(time) OVER(PARTITION BY esz ORDER BY rank) AS i FROM e1),
e2_2 AS (SELECT *, CASE WHEN MAX(time) OVER(PARTITION BY esz, i) IS NULL THEN 1 ELSE MAX(time) OVER(PARTITION BY esz, i) END AS max_time FROM e2_1),
e2_3 AS (SELECT *, LEAD(max_time) OVER(ORDER BY esz, rank) AS next_time FROM e2_2),
e2_4 AS (
SELECT esz, code, rank, initial_time,
CASE
WHEN time IS NOT NULL THEN time
ELSE
CASE
WHEN i = LAG(i) OVER(ORDER BY esz, rank) AND max_time = LAG(max_time) OVER(ORDER BY esz, rank) THEN
CASE
WHEN next_time - max_time > 1 THEN next_time -1
WHEN next_time - max_time < 0 OR next_time IS NULL THEN max_time +1
ELSE time
END
ELSE
CASE
WHEN next_time = 1 THEN 1
ELSE next_time -1
END
END
END AS time,
i, max_time, next_time
FROM e2_3
),
e3_1 AS (SELECT esz, code, rank, initial_time, time, COUNT(time) OVER(PARTITION BY esz ORDER BY rank) AS i FROM e2_4),
e3_2 AS (SELECT *, CASE WHEN MAX(time) OVER(PARTITION BY esz, i) IS NULL THEN 1 ELSE MAX(time) OVER(PARTITION BY esz, i) END AS max_time FROM e3_1),
e3_3 AS (SELECT *, LEAD(max_time) OVER(ORDER BY esz, rank) AS next_time FROM e3_2),
e3_4 AS (
SELECT esz, code, rank, initial_time,
CASE
WHEN time IS NOT NULL THEN time
ELSE
CASE
WHEN i = LAG(i) OVER(ORDER BY esz, rank) AND max_time = LAG(max_time) OVER(ORDER BY esz, rank) THEN
CASE
WHEN next_time - max_time > 1 THEN next_time -1
WHEN next_time - max_time < 0 OR next_time IS NULL THEN max_time +1
ELSE time
END
ELSE
CASE
WHEN next_time = 1 THEN 1
ELSE next_time -1
END
END
END AS time,
i, max_time, next_time
FROM e3_3
),
e4_1 AS (SELECT esz, code, rank, initial_time, time, COUNT(time) OVER(PARTITION BY esz ORDER BY rank) AS i FROM e3_4),
e4_2 AS (SELECT *, CASE WHEN MAX(time) OVER(PARTITION BY esz, i) IS NULL THEN 1 ELSE MAX(time) OVER(PARTITION BY esz, i) END AS max_time FROM e4_1),
e4_3 AS (SELECT *, LEAD(max_time) OVER(ORDER BY esz, rank) AS next_time FROM e4_2),
e4_4 AS (
SELECT esz, code, rank, initial_time,
CASE
WHEN time IS NOT NULL THEN time
ELSE
CASE
WHEN i = LAG(i) OVER(ORDER BY esz, rank) AND max_time = LAG(max_time) OVER(ORDER BY esz, rank) THEN
CASE
WHEN next_time - max_time > 1 THEN next_time -1
WHEN next_time - max_time < 0 OR next_time IS NULL THEN max_time +1
ELSE next_time
END
ELSE
CASE
WHEN next_time = 1 THEN 1
ELSE next_time -1
END
END
END AS time,
i, max_time, next_time
FROM e4_3
)
SELECT * FROM e4_4 ORDER BY esz, rank
Полагаю, вы могли бы сделать лучше, но это работает.
Спасибо @Nicolas... Я рад помочь тебе... Должен высказать свои сомнения... Это также помогает нам чему-то научиться.
Загрузите образец вывода.... Не делайте слишком сложного... @Nicolas