PostgreSQL: как выяснить пропущенные числа в столбце с помощью generate_series()?

Question

SELECT commandid 
FROM results 
WHERE NOT EXISTS (
    SELECT * 
    FROM generate_series(0,119999) 
    WHERE generate_series = results.commandid 
    );

У меня есть колонка в results типа int но различные тесты не прошли и не были добавлены в таблицу. Я хотел бы создать запрос, который возвращает список commandid которые не найдены в results, Я думал, что вышеупомянутый запрос будет делать то, что я хотел. Тем не менее, это даже не работает, если я использую диапазон, который находится за пределами ожидаемого возможного диапазона commandid (как отрицательные числа).

Answer 1

Приведенные примеры данных:

create table results ( commandid integer primary key);
insert into results (commandid) select * from generate_series(1,1000);
delete from results where random() < 0.20;

Это работает:

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE NOT EXISTS (SELECT 1 FROM results WHERE commandid = s.i);

как и эта альтернативная формулировка:

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
LEFT OUTER JOIN results ON (results.commandid = s.i) 
WHERE results.commandid IS NULL;

Оба приведенных выше результата, по-видимому, приводят к идентичным планам запросов в моих тестах, но вы должны сравнить их со своими данными в базе данных, EXPLAIN ANALYZE чтобы увидеть, что лучше.

объяснение

Обратите внимание, что вместо NOT IN Я использовал NOT EXISTS с подзапросом в одной формулировке и обычным OUTER JOIN в другом. Серверу БД гораздо проще их оптимизировать, и это позволяет избежать запутанных проблем, которые могут возникнуть при NULLв NOT IN,

Я изначально отдавал предпочтение OUTER JOIN формулировка, но по крайней мере в 9.1 с моими данными испытаний NOT EXISTS Форма оптимизируется по тому же плану.

Оба будут работать лучше, чем NOT IN Формулировка ниже, когда серия большая, как в вашем случае. NOT IN Раньше Pg требовал линейного поиска IN список для каждого тестируемого кортежа, но изучение плана запроса показывает, что Pg может быть достаточно умен, чтобы хэшировать его сейчас. NOT EXISTS (превращается в JOIN планировщиком запросов) и JOIN работать лучше

NOT IN Формулировка запутанная в присутствии NULL commandidи может быть неэффективным:

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE s.i NOT IN (SELECT commandid FROM results);

так что я бы избежал этого. С 1 000 000 строк два других закончились за 1,2 секунды и NOT IN Формулировка работала с привязкой к процессору, пока мне не стало скучно и я ее отменил.

Answer 2

Как я уже упоминал в комментарии, вы должны сделать обратный запрос выше.

SELECT
    generate_series
FROM
    generate_series(0, 119999)
WHERE
    NOT generate_series IN (SELECT commandid FROM results);

На этом этапе вы должны найти значения, которые не существуют в пределах commandid столбец в выбранном диапазоне.

Answer 3

Я не очень опытный гуру SQL, но мне нравятся другие способы решения проблемы. Просто сегодня у меня была похожая проблема - найти неиспользуемые числа в одном символьном столбце. Я решил свою проблему с помощью pl/pgsql и был очень заинтересован в том, какова будет скорость моей процедуры. Я использовал метод @Craig Ringer, чтобы сгенерировать таблицу с последовательным столбцом, добавить миллион записей, а затем удалить каждую 99-ю запись. Эта процедура работает около 3 секунд при поиске пропущенных номеров:

-- creating table
create table results (commandid character(7) primary key);
-- populating table with serial numbers formatted as characters
insert into results (commandid) select cast(num_id as character(7)) from generate_series(1,1000000) as num_id;
-- delete some records
delete from results where cast(commandid as integer) % 99 = 0;

create or replace function unused_numbers()
  returns setof integer as
$body$
declare
   i integer;
   r record;
begin
   -- looping trough table with sychronized counter:
   i := 1;
   for r in
      (select distinct cast(commandid as integer) as num_value
      from results
      order by num_value asc)
   loop
      if not (i = r.num_value) then
            while true loop
               return next i;

               i = i + 1;
               if (i = r.num_value) then
                     i = i + 1;
                     exit;
                  else
                     continue;
               end if;
            end loop;
         else
            i := i + 1;
      end if;
   end loop;

   return;
end;
$body$
  language plpgsql volatile
  cost 100
  rows 1000;

select * from unused_numbers();

Может быть, кому-то это пригодится.

Answer 4

Если вы используете красное смещение AWS, вам, возможно, придется игнорировать вопрос, поскольку он не поддерживает generate_series, Вы получите что-то вроде этого:

select 
    startpoints.id    gapstart, 
    min(endpoints.id) resume 
from (
     select id+1 id 
     from   yourtable outer_series 
     where not exists 
         (select null 
          from   yourtable inner_series 
          where  inner_series.id = outer_series.id + 1
         )
     order by id
     ) startpoints,   

     yourtable endpoints 
where 
    endpoints.id > startpoints.id 
group by 
    startpoints.id;