Строки SQL-фильтра по перекрытию диапазона
У меня есть таблица снежинок, которая примерно похожа на показанную ниже. Я хочу отфильтровать все строки с одинаковым именем, поставщиком и перекрывающимся диапазоном напряжения.
До:
После:
Выполнить только имя и напряжение легко с помощью group by и count() > 1. Но я не знаю, как применить такое сравнение строк друг с другом по отдельности.
Я думал сделать это с помощью оконных функций min/max, таких как
with min_max AS (
SELECT NAME
, PROVIDER
, MIN_VOLT
, MAX_VOLT
, MAX(max_volt) OVER (PARTITION BY PROVIDER, NAME) AS set_max
, MIN(min_volt) OVER (PARTITION BY PROVIDER, NAME) AS set_min
FROM Table
)
SELECT NAME, PROVIDER, MIN_VOLT, MAX_VOLT
FROM min_max
WHERE MIN_VOLT NOT BETWEEN set_min AND set_min AND MAX_VOLT NOT BETWEEN set_min AND set_max;
Однако это не возвращает строк, поскольку включает строку, которую сравнивают в диапазоне макс/мин. Я не уверен, как заставить его сравнивать каждую строку независимо друг от друга.
Обновлено: Для ясности: строки, которые мы хотим удалить, — это те строки, диапазон напряжения которых перекрывается с другой строкой, имеющей то же ИМЯ и значение ПОСТАВЩИКА. Например, строки 1, 2 и 3 в приведенной выше таблице имеют одно и то же имя и поставщика, а диапазон напряжения перекрывается (10–100, 80–150, 120–155), поэтому их все следует удалить. Принимая во внимание, что строка 4 имеет то же имя и поставщика, но ее диапазон напряжения не пересекается с другими строками, поэтому его следует сохранить.
Вы говорите, что удалите верхние 3, поскольку следующая строка min_volt находится внутри приведенной выше, в примере 80 находится между 10 и 100?
@Nathan_Sav, да, точно, тогда строка 4 будет сохранена, поскольку ее диапазон не лежит в пределах остальных.
Я добавил более подробную информацию в пост @Charlieface
Я думаю, вам следует добавить еще одну строку к вашим образцам данных, например MASD, Tele, 160, 180, чтобы продемонстрировать, что вы сохраните обе строки для MASD-Tele, поскольку нет перекрытия.
Ответы 2
Похоже, LEAD и LAG должны сработать
with min_max AS (
SELECT
NAME
, PROVIDER
, MIN_VOLT
, MAX_VOLT
, LAG(MAX_VOLT) OVER (PARTITION BY PROVIDER, NAME ORDER BY MIN_VOLT, MAX_VOLT) AS prev_max
, LEAD(MIN_VOLT) OVER (PARTITION BY PROVIDER, NAME ORDER BY MIN_VOLT, MAX_VOLT) AS next_min
FROM ORENNIA_ANALYTICS_QC.ORENNIA.PRE_PROCESSED_SUBSTATIONS
)
SELECT
NAME,
PROVIDER,
MIN_VOLT,
MAX_VOLT
FROM min_max
WHERE (prev_max IS NULL OR MIN_VOLT > prev_max)
AND (next_min IS NULL OR MAX_VOLT < next_min);
Это отлично работает. Спасибо!
Вы можете попробовать проверить существование
select *
from my_table a
where not exists(select *
from my_table b
where a.name = b.name and
a.provider = b.provider and
a.min_volt <> b.min_volt and
a.max_volt <> b.max_volt and
((a.min_volt between b.min_volt and b.max_volt) or
(a.max_volt between b.min_volt and b.max_volt)));
Как узнать, какую строку оставить?