如何保存sql中的最后一个检查点以用于下一行 [英] How to save the last checkpoint in the sql to be used for next row

问题描述

有什么办法可以存储最后迭代的行结果并将其用于下一行迭代吗?

Is there any way I can store the last iterated row result and use that for next row iteration?

例如我有一个表 say(Time_Table).

For example I have a table say(Time_Table).

__   Key   type   timeStamp             
1 )    1     B    2015-06-28 09:00:00  
2 )    1     B    2015-06-28 10:00:00  
3 )    1     C    2015-06-28 11:00:00  
4 )    1     A    2015-06-28 12:00:00  
5 )    1     B    2015-06-28 13:00:00  

现在假设我的 exceptionTime 为 90 分钟，这是常数.如果我开始检查我的 Time_Table 那么:

Now suppose I have an exceptionTime of 90 minutes which is constant. If I start checking my Time_Table then:

1. 对于第一行，因为09:00:00，之前没有行，它会直接把这条记录放到我的目标表中.现在我的参考点是 9:00:00.

1. for the first row, as there is no row before 09:00:00, it will directly put this record into my target table. Now my reference point is at 9:00:00.

对于 10:00:00 处的第二行， 最后一个参考点是 09:00:00 和 TIMESTAMPDIFF(s,09:00:00,10:00:00) 是 60，小于所需的 90.我不会将此行添加到目标表中.

For the second row at 10:00:00, the last reference point was 09:00:00 and TIMESTAMPDIFF(s,09:00:00,10:00:00) is 60 which is less than the required 90. I do not add this row to my target table.

对于第三行，最后记录的异常是在 09:00:00 和 TIMESTAMPDIFF(s,09:00:00,11:00:00) 是 120，它大于所需的 90，所以我选择这条记录并将参考点设置为 11:00:00.

For the third row, the last recorded exception was at 09:00:00 and the TIMESTAMPDIFF(s,09:00:00,11:00:00) is 120 which is greater than the required 90 so I choose this record and set reference point to 11:00:00.

对于第四行 TIMESTAMPDIFF(s,11:00:00,12:00:00).同样它也不会被保存.

For the fourth row the TIMESTAMPDIFF(s,11:00:00,12:00:00). Similarly it will not be saved.

这个又被保存了.

目标表

__   Key   type   timeStamp             
1 )    1     B    2015-06-28 09:00:00  
2 )    1     C    2015-06-28 11:00:00   
3 )    1     B    2015-06-28 13:00:00 

有什么办法可以纯SQL解决这个问题?

Is there any way that I can solve this problem purely in SQL?

我的方法:

SELECT * FROM Time_Table A WHERE NOT EXISTS(
       SELECT 1 FROM Time_Table B
       WHERE  A.timeStamp > B.timeStamp
       AND    abs(TIMESTAMPDIFF(s,B.timeStamp,A.timeStamp)) > 90 
)

但这实际上行不通.

推荐答案

这在 Vertica 中仅使用纯 SQL 是不可能的.要在纯 SQL 中执行此操作，您需要能够执行 Vertica 产品不支持的递归查询.在其他数据库产品中，您可以使用 WITH 子句执行此操作.对于 Vertica，您将不得不在应用程序逻辑中执行此操作.这是基于语句查询块中的每个 WITH 子句都必须具有唯一名称.尝试对同一查询块中的 WITH 子句查询名称使用同名别名会导致错误.WITH 子句不支持 INSERT、DELETE、和 UPDATE 语句，并且您不能递归使用它们"来自 Vertica 7.1.x 文档

This is not possible using just pure SQL in Vertica. To do this in pure SQL you need to be able to perform a recursive query which is not supported in the Vertica product. In other database products you can do this using a WITH clause. For Vertica you are going to have to do it in the application logic. This is based on the statement "Each WITH clause within a query block must have a unique name. Attempting to use same-name aliases for WITH clause query names within the same query block causes an error. WITH clauses do not support INSERT, DELETE, and UPDATE statements, and you cannot use them recursively" from Vertica 7.1.x documentation

这篇关于如何保存sql中的最后一个检查点以用于下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！