缺失记录的Lead函数

Question

我正在使用下面的查询

 select  id,
         number_sequ,
         startvalue
         lead(startvalue,1,0) over (partition by id order by number_sequ) AS End_value
 from mytable

填充以下输出

id             number_sequ   startvalue       End_value
----            -----       ----------       -----------
AAA             1             30              20
AAA             2             20              10
AAA             4             10              15
AAA             5             15              0
BBB             1             12              23
BBB             3             23              34
BBB             4             34              0

但是序列中有缺失的记录

id         number_sequ   startvalue       End_value
----         -----       ----------       -----------
AAA          3           
BBB          2

我尝试了不同的方法来查找序列中缺少的数字，并尝试插入零值。然后我就可以使用lead函数了。找不到有效的方法

INSERT INTO mytable (id, number_sequ, startvalue)
    select id ,number_sequ ,'0'
    from mytable
    where (some condition to specify missing data)

有谁能帮我解决上面的问题吗？

Answer 1

您可以使用以下方法获取缺少的值:生成所有可能的值，然后过滤掉存在的值。

select i.id, n.n, 0 as start_value
from (select id, min(number_seq) as min_ns, max(number_seq) as max_ns
      from mytable
      group by id
     ) i join
     (select row_number() over (partition by number_seq) as n
      from mytable
     ) n
     on n.n <= i.max_ns left join -- just a bunch of numbers
     mytable t
     on t.id = i.id and
        t.number_seq = n.n 
where t.id is null;

您可以在select之前弹出insert，以便将这些值插入到表中。

请注意，这将使用原始数据生成所需的序列号。因此，它假设您的表中有足够的行来存储所需的数字。

Answer 2

如果缺少的值始终位于现有值之间，则可以使用雪花的JavaScript UDTFs查找差距

例如，下面的函数查找序列中的空格，然后我们使用它来生成“空”行：

create or replace table x(id int, seq int, startVal int) as select * from
values(1,1,11),(1,2,12),(1,4,14),(2,2,22),(2,5,25);


CREATE OR REPLACE FUNCTION find_gaps(SEQ float)
RETURNS TABLE (GAP float)
LANGUAGE JAVASCRIPT
AS '
  {
    initialize: function(argumentInfo, context) {
      this.lastRow = null;
    },
    processRow: function (row, rowWriter, context) {
      let curRow = row.SEQ;
      if (this.lastRow == null || this.lastRow + 1 == curRow) {
        this.lastRow = curRow;
      } else {
        while (this.lastRow + 1 < curRow) {
          this.lastRow++;
          rowWriter.writeRow({GAP: this.lastRow});
        }
      }
    }
  }'
;

select id, seq, startVal from x 
union all  
select id, gap, 0 from x,     
  table(find_gaps(seq::float) 
    over (partition by id order by seq));

----+-----+----------+
 ID | SEQ | STARTVAL |
----+-----+----------+
 1  | 1   | 11       |
 1  | 2   | 12       |
 1  | 4   | 14       |
 2  | 2   | 22       |
 2  | 5   | 25       |
 2  | 3   | 0        |
 2  | 4   | 0        |
 1  | 3   | 0        |
----+-----+----------+

你也可以使用这个函数的变体，例如，如果你知道每个id的值的范围，只需给它提供期望的min/max即可。此外，如果您的输入包含空值，您可能需要一些特殊的东西(但是-结果应该是什么？：)

Answer 3

除了建议的解决方案之外，如果您仍然想坚持使用Lead函数，

Lead函数分析有值的数据，它的结果可以基于分区的空值，但它用于分析的数据应该有值。在我看来，你所做的将缺失的序列包含在你的结果中是正确的。

http://www.mysqltutorial.org/mysql-window-functions/mysql-lead-function/