如何将sql中的最后一个检查点保存下来以供下一行使用

Question

有没有办法存储最后迭代的行结果，并将其用于下一行迭代？

例如，我有一个表，名为 (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  

假设我有一个常量为90分钟的exceptionTime。 如果我开始检查我的Time_Table，则：

1. 对于第一行，在09:00:00之前没有行，它将直接将此记录放入目标表中。现在我的参考点是9:00:00。

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

3. 对于第三行，上一个异常记录是在09:00:00记录的，而TIMESTAMPDIFF(s,09:00:00,11:00:00)为120，大于所需的90，因此我选择此记录并将参考点设置为11:00:00。

4. 对于第四行，TIMESTAMPDIFF(s,11:00:00,12:00:00)也不会被保存。

5. 这一行再次被保存。

目标表

__   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解决这个问题？

我的方法：

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 
)

但这实际上并不会起作用。

- AmanS

---

@AmanS，Vertica是否支持递归CTE？如果是的话，类似于**demo**这样的东西应该可以工作。 - Lukasz Szozda

@lad2025，Vertica不支持递归CTE。 - AmanS

@AmanS 很高兴知道这个消息，但对你来说不是很好。我想知道Vertica是否支持类似奇怪更新的东西。你可以创建临时表，使用奇怪的更新来设置状态，然后显示结果。否则，你可能需要一些类型的游标/循环。 - Lukasz Szozda

@Webeng不起作用了。如果您提到的方法可行，请提供演示链接。 - AmanS

我们可以假设在Time_Table中不会有多个具有完全相同“时间戳”的记录吗？ - quest4truth

另外，您使用的Vertica版本是哪个？ - quest4truth

Answer 1

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

- Anilof

Answer 2

肯定可以，（不是纯 SQL）可以使用 LAG （自 7.1.x 起）取决于您使用的 Vertica 版本 或者创建自定义 UDx（用户定义扩展）

在 Java 中的 UDx 可以访问前一行，类似于只有一步的 LAG（标签#性能） （GitHub 上有许多 UDx 示例）

public class UdxTestFactory extends AnalyticFunctionFactory {

    @Override
    public AnalyticFunction createAnalyticFunction(ServerInterface srvInterface) {
        return new Test();
    }

    @Override
    public void getPrototype(ServerInterface srvInterface, ColumnTypes argTypes,
                             ColumnTypes returnType) {
        argTypes.addInt();
        argTypes.addInt();
        returnType.addInt();
    }

    @Override
    public void getReturnType(ServerInterface srvInterface, SizedColumnTypes argTypes,
                              SizedColumnTypes returnType) throws UdfException {
        returnType.addInt();
    }

    private class Test extends AnalyticFunction {

        @Override
        public void processPartition(ServerInterface srvInterface, AnalyticPartitionReader inputReader, AnalyticPartitionWriter outputWriter)
                throws UdfException, DestroyInvocation {

            SizedColumnTypes inTypes = inputReader.getTypeMetaData();
            ArrayList<Integer> argCols = new ArrayList<Integer>();

            inTypes.getArgumentColumns(argCols);

            outputWriter.setLongNull(0);

            while (outputWriter.next()) {
                long v1 = inputReader.getLong(argCols.get(0)); // previous row
                inputReader.next();
                long v2 = inputReader.getLong(argCols.get(0)); // curent row
                outputWriter.setLong(0, v2 - v1);
            }
        }

    }


}

将编译后的类编译并合并到一个单独的jar文件中，为了简便起见，将其命名为TestLib.jar

$ javac -classpath /opt/vertica/bin/VerticaSDK.jar /opt/vertica/sdk/BuildInfo.java UdxTestFactory.java -d . 
$ jar -cvf TestLib.jar com/vertica/sdk/BuildInfo.class com/vertica/JavaLibs/*.class

加载库和函数

CREATE OR REPLACE LIBRARY TestFunctions AS '/home/dbadmin/TestLib.jar' LANGUAGE 'JAVA';
CREATE OR REPLACE ANALYTIC FUNCTION lag1 AS LANGUAGE 'java' NAME 'com.vertica.JavaLibs.UdxTestFactory' LIBRARY TestFunctions;

然后...使用它

SELECT 
    lag1(col1, null) OVER (ORDER BY col2) AS col1_minus_col2 
FROM ...

- whoopdedoo