蜂巢如何增加条件特定的值? [英] hive how to increment the values specific to condition?

问题描述

我有如下表格:

itm    numbr
1        2
11       21
NULL     31
12       41
NULL     51
13       61

我想用itm，numbr和额外的列输出，如下所述:

i want to output with itm,numbr and with extra column as mentioned below:

   itm      numbr    incr
    1        2        1
    11       21       1
    NULL     31       2
    12       41       2
    NULL     51       3
    13       61       3

仅当ITM等于NULL时，incr才应增加其值，否则应显示与先前值相同的值.

the incr should increase its value only when ITM is equal to NULL else it should display the same value as previous value.

1. 我已经尝试过row_sequence

1. i have tried with row_sequence

select itm,numbr,row_sequence() as incr from tablename;

但显示1,2,3,4,5,6.

but it displays 1,2,3,4,5,6.

2. 然后我尝试过

select itm,numbr,case when itm=NULL then row_sequence()+1 else row_sequence() end as incr from table;

它引发错误失败:RuntimeException org.apache.hadoop.hive.ql.metadata.HiveException:有状态表达式不能在CASE内部使用".

it throws error "FAILED: RuntimeException org.apache.hadoop.hive.ql.metadata.HiveException: Stateful expressions cannot be used inside of CASE".

请给我建议. tia.

please advice me. tia.

推荐答案

未经测试但分析功能应该可以为您提供帮助，因为incr列的值取决于其他行的值:

Untested but analytical function should help you, as value of the incr column depends on the values of other rows:

WITH tmpincr as (
  SELECT 
      itm
    , nmbr
    -- get in tmpincr 1 for NULL, 0 for not null
    , if(isnull(itm), 1, 0) as tmpincr
   FROM tablename
)
SELECT 
    itm
  , nmbr
  -- get the sum of tmpincr for all rows before current one when ordered by itm
  , SUM(tmpincr) OVER (
      ORDER BY nmbr
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as incr
  FROM tmpincr
  ORDER BY nmbr
;

这篇关于蜂巢如何增加条件特定的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！