Учитывая последовательность чисел, как определить пропущенные числа

Я хотел бы получить все пропущенные числа в последовательности чисел.
Просто интересно, есть ли лучший подход, чем ниже?

SELECT x
  FROM 
  (
    SELECT x,
           LAG(x,1) OVER ( ORDER BY x ) prev_x
      FROM 
       ( SELECT * FROM
        ( SELECT 1 AS x ),
        ( SELECT 2 AS x ),
        ( SELECT 3 AS x ),
        ( SELECT 4 AS x ),
        ( SELECT 5 AS x ),
        ( SELECT 6 AS x ),
        ( SELECT 8 AS x ),
        ( SELECT 10 AS x ),
        ( SELECT 11 AS x )
       )
  ) 
 WHERE x-prev_x > 1;
Источник

3 ответа

Решение

Самое короткое решение в Postgres - это стандартный SQL EXCEPT:

WITH tbl(x) AS (SELECT unnest ('{1,2,3,4,5,6,8,10,11}'::int[]))
-- the CTE provides a temp table - might be an actual table instead
SELECT generate_series(min(x), max(x)) FROM tbl
EXCEPT ALL
TABLE  tbl;

Функция возврата набора unnest() специфичен для Postgres и обладает самым коротким синтаксисом для предоставления набора чисел в виде таблицы.

Также работает с дубликатами или значениями NULL в данных.

TABLE tbl это (стандартный SQL!) короткий синтаксис для SELECT * FROM tbl:

Связанные (с дополнительными пояснениями):

Источник

Позвольте мне быть честным с вами!
Любое другое рабочее решение будет лучше, чем представленное в вопросе - по одной простой причине - это неправильно! он не возвращает пропущенные номера вообще! это скорее показывает число после следующего разрыва. вот и все (надеюсь, вы оцените, что я открыла вам глаза на это)

Теперь о лучших решениях - есть множество вариантов для вас.
Примечание: ниже приведены варианты ТОЛЬКО для BigQuery!

Опция 1

BigQuery Standard SQL - см. Как включить стандартный SQL

WITH YourTable AS (
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 10 AS x UNION ALL
  SELECT 11 AS x
),
nums AS (
  SELECT num 
  FROM UNNEST(GENERATE_ARRAY((SELECT MIN(x) FROM YourTable), (SELECT MAX(x) FROM YourTable))) AS num
)
SELECT num FROM nums
LEFT JOIN YourTable ON num = x
WHERE x IS NULL
ORDER BY num

Вариант 2

BigQuery Legacy SQL вы можете попробовать ниже (здесь вам нужно установить значения start/min и end/max внутри выражения select для таблицы nums

SELECT num FROM (
  SELECT num FROM (
    SELECT ROW_NUMBER() OVER() AS num, * 
    FROM (FLATTEN((SELECT SPLIT(RPAD('', 11, '.'),'') AS h FROM (SELECT NULL)), h))
  ) WHERE num BETWEEN 1 AND 11
) AS nums
LEFT JOIN (
  SELECT x FROM
    (SELECT 1 AS x),
    (SELECT 2 AS x),
    (SELECT 3 AS x),
    (SELECT 6 AS x),
    (SELECT 8 AS x),
    (SELECT 10 AS x),
    (SELECT 11 AS x)
) AS YourTable
ON num = x
WHERE x IS NULL

Вариант 3

BigQuery Legacy SQL - если вы не хотите зависеть от min и max и нуждаетесь в установке этих значений - вы можете использовать приведенное ниже решение - для этого требуется только установить достаточно высокий максимум, чтобы соответствовать ожидаемому росту (например, 1000)

SELECT num FROM (
  SELECT num FROM (
    SELECT ROW_NUMBER() OVER() AS num, * 
    FROM (FLATTEN((SELECT SPLIT(RPAD('', 1000, '.'),'') AS h FROM (SELECT NULL)), h))
  ) WHERE num BETWEEN 1 AND 1000
) AS nums
LEFT JOIN YourTable
ON num = x
WHERE x IS NULL
AND num BETWEEN (SELECT MIN(x) FROM YourTable) AND (SELECT MAX(x) FROM YourTable)

Вариант 4 (почему-то - мой любимый до сих пор)

BigQuery Standard SQL - без явных объединений

WITH YourTable AS (
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x UNION ALL
  SELECT 6 AS x UNION ALL
  SELECT 8 AS x UNION ALL
  SELECT 10 AS x UNION ALL
  SELECT 11 AS x
)
SELECT num
FROM (SELECT x, LEAD(x) OVER(ORDER BY x) AS next_x FROM YourTable),  
     UNNEST(GENERATE_ARRAY(x + 1,next_x - 1)) AS num
WHERE next_x - x > 1
ORDER BY x
Источник

Ваш запрос может быть написан гораздо более кратко, например так:

SELECT x
FROM (
    SELECT x,
           lag(x, 1) OVER ( ORDER BY x ) prev_x
    FROM ( VALUES (1), (2), (3), (4), (5), (6), (8), (10), (11) ) v(x)
) sub
WHERE x-prev_x > 1;

Это вернет следующее наивысшее значение после промаха (8, 10), а не сами недостающие значения (7, 9). Но, конечно, у вас нет удобных значений.

Если вы знаете диапазон значений в последовательности, то вы можете использовать это:

SELECT s.x
FROM generate_series(<<min>>, <<max>>) s(x)
LEFT JOIN my_table t ON s.x = t.x
WHERE t.x IS NULL;

Это возвращает фактические пропущенные значения.

Если вы не знаете диапазон значений, вам нужно добавить подзапрос:

SELECT s.x
FROM ( SELECT min(x), max(x) FROM my_table ) r
JOIN generate_series(r.min, r.max) s(x) ON true
LEFT JOIN my_table t ON s.x = t.x
WHERE t.x IS NULL;

В качестве альтернативы, вместо LEFT JOIN:

SELECT x
FROM ( SELECT min(x), max(x) FROM my_table ) r,
     generate_series(r.min, r.max) s(x)
WHERE NOT EXISTS (SELECT 1 FROM my_table t WHERE t.x = s.x);
Источник
Другие вопросы по тегам