给定一个数字序列，如何识别缺失的数字

Question

我想在数字序列中得到所有缺失的数字。
只是想知道是否有比下面更好的方法?

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;

Answer 1

我跟你说实话吧!
任何其他可行的解决方案都会比提出问题更好——原因很简单——它是错误的!它根本不返回丢失的数字!它显示的是下一个间隙之后的数字。这就是全部(希望你会感激我打开了你的眼睛)

现在，关于更好的解决方案——有很多选择供你选择。
注意:以下选项只适用于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 -如果您不想依赖于最小和最大，并且需要设置这些值-您可以使用以下解决方案-它只需要设置足够高的最大值以适应您的预期增长(例如，我设置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

Answer 2

在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是SELECT * FROM tbl的(标准SQL!)短语法:

• 在psql中有SELECT * FROM的快捷方式吗?

相关(有更多解释):

• 选择其他表
中不存在的行如何在PostgreSQL中有效地检查序列中使用和未使用的值

Answer 3

你的查询可以写得更简洁，像这样:

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);