Hive lag window partition

Question

Here is my table :

sensor_name, ext_value, int_value, growth
47ACXVMACSENS01, 238, 157, 1
47ACXVMACSENS01, 157, 256, 2
47ACXVMACSENS01, 895, 345, 3
47ACXVMACSENS01, 79, 861, 3
91DKCVMACSENS02, 904, 858, 1
91DKCVMACSENS02, 925, 588, 1
91DKCVMACSENS02, 15, 738, 1
91DKCVMACSENS02, 77, 38, 2

The 3 first columns (sensor_name, ext_value, int_value) are given data, the 4th column is a calculated column that I would like to have, this growth column is based on the value of columns (ext_value, int_value) for each group of sensor_name.

The result of the growth column is calculated as follow : for each group of sensor_name, the int_value of each row is compared with the ext_value of the previous row, if no previous row then its ext_value is 0, if the int_value of the current row is higher than the ext_value of the previous row then the growth value increases of 1. If the current int_value is lower than the previous row ext_value then the growth remains at the same value than the previous value of growth.

In the example above,

for the very first row, 157 is compared with the previous row ext_value that doesn't exist so it's 0, 
157 > 0 then growth value increase of 1 from 0.
on the 2nd row, 256 > 238 then growth = 1+1=2
on the 3rd row, 345 > 159 then growth = 2+1=3
on the 4th row, 861 < 895 then growth remains at the same previous value, so 3.

then the logic is re-applied to the second set of sensor_name :
1st row, 858 > 0 (because there is now previous row for this sensor_name) then growth = 1
2nd row, 588 < 904 then growth = 1
3rd row, 738 < 925 then growth = 1
4th row, 38 > 15 then growth = 1+1=2

I've tried by using the lag window over sensor_name partition but it doesn't give me correct result until now.

How can I solve this ?

The problem with your data is that "previous row" cannot to be defined because there seems no any column defining the order of rows. To be able to use LAG analytic function you should specify ORDER BY column, so function will be able to pick previous row correctly. For example last three rows in your example: why are they in such order? If you rely on the order in the file- this is bad idea because it is being read in parallel, not sequentially. Table is not ordered in Hive. You need some sequence or timestamp to be able to pick the previous row — leftjoin, Nov 27 '19 at 06:51
Read also this answer about order of rows: https://stackoverflow.com/a/56679664/2700344 — leftjoin, Nov 27 '19 at 06:56
the data are ordered like this because there is a column rcv_time indicating the time that these data are received in the format yyyy-MM-dd HH:mm:ss that i don't represent here from the less recent to the most recent — Talia9, Nov 27 '19 at 08:55

score 1 · Accepted Answer · answered Nov 27 '19 at 09:58

Use lag to get previous ext_value, calculate growth flag and use running count to calculate growth. I added rcv_time column as you said in the comment:

with your_table as ( --use your table instead of this
select stack(8,
'47ACXVMACSENS01', 238, 157, '2019-11-01 10:10:01',
'47ACXVMACSENS01', 157, 256, '2019-11-01 10:10:02',
'47ACXVMACSENS01', 895, 345, '2019-11-01 10:10:03',
'47ACXVMACSENS01', 79, 861,  '2019-11-01 10:10:04',
'91DKCVMACSENS02', 904, 858, '2019-11-01 10:10:05',
'91DKCVMACSENS02', 925, 588, '2019-11-01 10:10:06',
'91DKCVMACSENS02', 15, 738,  '2019-11-01 10:10:07',
'91DKCVMACSENS02', 77, 38,  '2019-11-01 10:10:08'
) as (sensor_name, ext_value, int_value, rcv_time )
)

select sensor_name, ext_value, int_value, 
       count(case when int_value>prev_ext_value then true end) over(partition by sensor_name order by rcv_time) growth
from
(
select sensor_name, ext_value, int_value, rcv_time, 
       lag(ext_value,1,0) over(partition by sensor_name order by rcv_time) prev_ext_value
  from your_table
)s;

Result:

47ACXVMACSENS01 238     157     1
47ACXVMACSENS01 157     256     2
47ACXVMACSENS01 895     345     3
47ACXVMACSENS01 79      861     3
91DKCVMACSENS02 904     858     1
91DKCVMACSENS02 925     588     1
91DKCVMACSENS02 15      738     1
91DKCVMACSENS02 77      38      2

The result produced is exactly as in your example

Hive lag window partition

1 Answers1