hive - 同一查询中的计算列 - Spark、Hive

Question

我需要使用派生列中的前一个值来获取同一列的下一个结果。

默认情况下，每个名称的第一次出现将为 1

派生列 = num(i) + 派生 (i-1)

Name, Num, derived
A       0       1
A       1       2
A       0       2      
B       0       1
B       0       1
B       1       2
C       0       1
C       0       1
D       1       1
D       1       2
D       1       3       

下面是我的一半代码——使用了窗口延迟功能但无法实现

spark.sql("""
select

z.name as name,

z.num as num,

case

  when z.row_num='1' then '1'

  else (lag(num) over(partition by z.name order by name))

  end as derived

from

(select name,num,row_number() over(partition by name order by name) as row_num

from result)z""").show()

Answer 1

您可以使用 session 变量

select
  z.name as name,
  z.num as num,       
  @derived := case
  when z.row_num='1' then '1'
  else num + @derived
  end as derived
from
  (select name,num,row_number() over(partition by name order by name) as row_num
  from result)z,(SELECT @derived:= 0) d1

哪个会给你

name | num | derived:--- | --: | :------A    |   0 | 1      A    |   1 | 2      A    |   0 | 2      B    |   0 | 1      B    |   0 | 1      B    |   1 | 2      C    |   0 | 1      C    |   0 | 1      D    |   1 | 1      D    |   1 | 2      D    |   1 | 3      

分贝<> fiddle here