检查序列号

Question

我有一个aprox 25数字的列表，它们应该是sequentials。我得去找找有没有丢了。即，拥有：... 60001231 60001232 60001234 ...

我需要找到丢失的"60001233“。在Excel中，这很简单，但这是一个非常大的数据库，而我完全是SQL或Power BI的初学者。我在计算列中思考，但我不知道如何插入一个“滞后”运算符来将一个数字与前一个数字相减。

提前感谢！

Answer 1

这在MS访问中是非常低效的，但是。。。只需要几个数字，就可以了：

select n, next_n
from (select t.*,
             (select t.*,
                     min(t2.n) as next_n
              from t t2
              where t2.n > t.n
             ) as t
       from t
      ) t
where next_n <> n + 1;

最里面的子查询计算每一行的"next“值。然后，外部查询对此进行过滤，以便只返回不相邻的值。

Answer 2

您可能还希望显示缺少的数字。如果是这样，请研究一下我以前的文章：

Find and Generate Missing Values in an Access Table

它还包含一个演示( .mdb格式)的链接以及完整的代码。

​

​

Answer 3

Power BI解决方案

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSVCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Column1"}, {"Column1.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "Custom", each if [Index]=1 then [Column1]-1 else [Column1.1]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each [Column1]-[Custom]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [Custom.1]<>1 then "not consecutive" else "consecutive"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Index.1", "Column1.1", "Custom", "Custom.1"})
in
    #"Removed Columns"