[HQL] How to compare same column between rows?

[Mungeol Heo <mu...@gmail.com>]

Hi,

My question is that does Hive able to compare same column between rows.
For instance, I have a table which contains data like below.

name, value
a, 1
a, 2
b, 1
c, 1
a, 13
b, 11

What I need is to compare 'value' columns between rows which have same 'name'.

For the name 'a'
first 'a' comes, then count = 1
next 'a' comes, then, compare 'value', if '2 - 1 >= 10' then 'count + 1'
next 'a' comes, then, compare 'value', if '13 - 2 >= 10' then 'count + 1'

For the name 'b'
first 'b' comes, then count = 1
next 'b' comes, then, compare 'value', if '11 - 1 >= 10' then 'count + 1'

For the name 'c'
first 'c' comes, then count = 1

And, the result what I will get should be like below.

name, count
a, 2
b, 2
c, 1

Is it possible without UDF?
If I have to use UDF, Is there one out there already exists?

I will write a UDF for this, but still I want to make sure about the
questions which I mentioned above.
Any help will be great.
Thanks.

Best regards,

- Mungeol

Re: [HQL] How to compare same column between rows?

[Suhas Gogate <vg...@pivotal.io>]

I think custom UDAF is the only way to achieve what you need. See existing
UDAFs available in Hive @
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inAggregateFunctions(UDAF)

--Suhas

On Mon, Nov 17, 2014 at 4:14 PM, Mungeol Heo <mu...@gmail.com> wrote:

> Hi,
>
> My question is that does Hive able to compare same column between rows.
> For instance, I have a table which contains data like below.
>
> name, value
> a, 1
> a, 2
> b, 1
> c, 1
> a, 13
> b, 11
>
> What I need is to compare 'value' columns between rows which have same
> 'name'.
>
> For the name 'a'
> first 'a' comes, then count = 1
> next 'a' comes, then, compare 'value', if '2 - 1 >= 10' then 'count + 1'
> next 'a' comes, then, compare 'value', if '13 - 2 >= 10' then 'count + 1'
>
> For the name 'b'
> first 'b' comes, then count = 1
> next 'b' comes, then, compare 'value', if '11 - 1 >= 10' then 'count + 1'
>
> For the name 'c'
> first 'c' comes, then count = 1
>
> And, the result what I will get should be like below.
>
> name, count
> a, 2
> b, 2
> c, 1
>
> Is it possible without UDF?
> If I have to use UDF, Is there one out there already exists?
>
> I will write a UDF for this, but still I want to make sure about the
> questions which I mentioned above.
> Any help will be great.
> Thanks.
>
> Best regards,
>
> - Mungeol
>