Как определить есть ли NULL в некоем интервале

Question

Дана таблица с тремя полями

group_id	index_old	index_new
1	2	2
1	4	4
1	7	null
1	8	8
1	12	12
2	14	null
2	18	null
2	19	19
2	23	23
2	24	24
2	29	29
3	30	null
3	31	null
3	34	34
3	37	37
3	39	null
3	40	40
3	42	null
3	47	47

Столбец index_new повторяет столбец index_old, но некоторые значения пропущены. Столбец index_old уникален и возрастает. Разница между соседними значениями случайна. group_id уникален и возрастает, количество значений в каждой группе случайно.

Надо получить сгруппированную по group_id таблицу, в которой будет указано (все равно как - лучше булевым значением, но можно и какой-то цифрой) отсутствует ли NULL после минимального не NULL значения в каждой группе в столбце index_new. Другими словами - если в начале группы идут NULL - это нас не беспокоит, дальше появляется не-NULL-значение и нам надо убедится, что после этого значения нету пропущенных.

В примере: В первой группе минимум 2, после него идет NULL вместо 7 - группа ошибочная. Во второй группе сначала идут NULL, минимум 19, после этого NULL не присутствует - подходит. В третьей группе сначала NULL, минимум 34, затем NULL вместо 39 и 42 - ошибка.

Итого в ответе:

group_id	null_flag
1	f
2	t
3	f

Или, в ответе можно так, посчитать количество NULL:

group_id	null_count
1	1
2	0
3	2

Потом я отфильтрую по нулю.

Что я делаю: группирую, беру минимум в каждой группе в index_new, считаю количество объектов бОльших минимума для обоих столбцов и сравниваю количество. Если разница равна нулю - это успех, не равна нулю - неудача.

Можно по-другому, например с помощью EXCEPT или INTERSECT. (Кстати, какой способ лучше - считать и сравнивать количество или пересечь и посчитать, что там осталось?)

Но вопрос вот в чем: во всех моих решениях надо применять сравнение с эталонным столбцом, то есть я не нашел способа, чтобы используя только столбец index_new определить есть ли в нем NULL-значения после определенного значения. Можно ли так сделать? Второй вопрос: какие решения вам видятся более удачными чем мои?

UPD

Пока перечитывал вопрос (кстати, о пользе задавать вопросы), подумал, что можно не сравнивать с количеством в другом столбце, а считать и сравнивать с count(*). Но не уверен, что это будет быстрее.

Answer 1

Можно выбрать строки, когда index_new после not null идет null. Пример:

select *
from(
  select *
    ,lag(index_new,1,index_new)over(partition by group_id order by  index_old) prev_new
  from test
) t
where index_new is  null and prev_new is not null

group_id	index_old	index_new	prev_new
1	7	null	4
3	39	null	37
3	42	null	40

fiddle

Группировка обычная. Если счетчик равен 0, значит группа не включает искомые строки.

select group_id,
  sum(case when index_new is  null and prev_new is not null then 1 else 0 end) cnt
from(
  select *
    ,lag(index_new,1,index_new)over(partition by group_id order by  index_old) prev_new
  from test
) t
group by group_id

group_id	cnt
1	1
2	0
3	2

fiddle

Из двух подряд идущих null будет взят только 1, если это имеет значение.

БЛОГ НА HUSL

Как определить есть ли NULL в некоем интервале

Ответы (1 шт):