当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Hive check next row

问题描述:

I am writing a hive query to check the next record for every userid. For ex: for userid U1, if the key is same (K1) for the next record, then update the classify column for the row as manual match, but if the user's next record's has different key then leave it blank

userid category timestamp key classify

U1 imported 04-02-2016 05:02:01 k1 NULL

U2 deleted 04-02-2016 05:00:00 k3 NULL

U1 matched 04-02-2016 04:49:00 k1 **manual match**

U4 imported 04-02-2016 04:50:01 k1 NULL

U3 matched 04-02-2016 04:48:00 k2 NULL

U4 matched 04-02-2016 04:47:00 k4 **NULL**

I am trying to start with this

select LEAD(description) OVER (PARTITION BY userid order by timestamp ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) from adithya.smartmatching where subcategory = 'SmartExpense Imported'

and then implement a case condition for the classify column. But I am getting an error for the above code itself. Can somebody help me?

Getting error : Your query has the following error(s):

Your query has the following error(s):

Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: Expecting left window frame boundary for function LEAD((TOK_TABLE_OR_COL description)) Window Spec=[PartitioningSpec=[partitionColumns=[(TOK_TABLE_OR_COL userid)]orderColumns=[(TOK_TABLE_OR_COL timestamp) ASC]]window(start=currentRow, end=range(1 FOLLOWING))] as _wcol0 to be unbounded. Found : 0

Thanks

网友答案:

have you created partition in table adithya.smartmatching .if yes then you must not used partition by select LEAD(description) OVER (PARTITION BY userid order by timestamp ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) from adithya.smartmatching where subcategory = 'SmartExpense Imported'

after removing partition by (partition) its work.used like select LEAD(description) OVER (PARTITION (userid) order by timestamp ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) from adithya.smartmatching where subcategory = 'SmartExpense Imported'

分享给朋友:
您可能感兴趣的文章:
随机阅读: