配置单元访问上一行的值 [英] Hive access previous row value

问题描述

我也提到了同样的问题 此处

I have the same issue mentioned here

但是，问题出在Hive数据库上.当我在桌上的解决方案上看起来像

However, the problem is on Hive database. When I try the solution on my table that looks like

Id   Date             Column1    Column2
1    01/01/2011       5          5 => Same as Column1
2    02/01/2011       2          18 => (1 + (value of Column2 from the previous row)) * (1 + (Value of Column1 from the current row)) i.e. (1+5)*(1+2)
3    03/01/2011       3          76 => (1+18)*(1+3) = 19*4

我收到错误

FAILED: SemanticException Recursive cte cteCalculation detected (cycle: ctecalculation -> cteCalculation).

在这种情况下可能的解决方法是什么

What is the workaround possible in this case

推荐答案

您将为此编写一个UDF.
在下面，您可以看到一个非常简单的(！)简化的UDF，可以满足您的需求.
想法是将前一次执行的值存储在UDF中的变量中，每次返回(stored_value+1)*(current_value+1)，然后将其存储到下一行.
您需要注意要获取的第一个值，因此有一个特殊情况.
同样，您必须将排序后的数据传递给函数，因为它只是逐行传递并执行所需的操作，而无需考虑任何顺序.

You will have to write a UDF for this.
Below you can see a very (!!) simplified UDF for what you need.
The idea is to store the value from the previous execution in a variable inside the UDF and each time return (stored_value+1)*(current_value+1) and then store it for the next line.
You need to take care of the first value to get, so there is a special case for that.
Also, you have to pass the data ordered to the function as it simply goes line by line and performs what you need without considering any order.

您必须添加jar并创建一个函数，将其命名为cum_mul.

You have to add your jar and create a function, lets call it cum_mul.

SQL将为:

select id,date,column1,cum_mul(column1) as column2
from
(select id,date,column1 from myTable order by id) a  

UDF的代码:

import org.apache.hadoop.hive.ql.exec.UDF;

public class cum_mul extends UDF  {

    private int prevValue;
    private boolean first=true;

    public int evaluate(int value) {
        if (first) {
            this.prevValue = value;
            first = false;
            return value; 
        }
        else {
            this.prevValue = (this.prevValue+1)*(value+1);
            return this.prevValue;      
        }
      }
}

这篇关于配置单元访问上一行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！