hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Viral Parikh <viral.j.par...@gmail.com>
Subject Re: Return value from previous record if value is -1 using HIVE
Date Fri, 12 Sep 2014 16:34:24 GMT
To Whomsoever It May Concern,

I've got a table that contains a LocationId field. In some cases, where a
record shares the same foreign key, the LocationId might come through as -1.

What I want to do is in my select query is in the case of this happening,
the previous location.

Example data:

Record  FK     StartTime               EndTime          Location1
 110  2011/01/01 12.30        2011/01/01 6.10      4562       110
2011/01/01 3.40         2011/01/01 4.00       -13       110
2011/01/02 1.00         2011/01/02 8.00      8914       110
2011/01/02 5.00         2011/01/02 6.00       -15       110
2011/01/02 6.10         2011/01/02 6.30       -1

The -1 should come out as 456 for record 2, and 891 for record 4 and 5

Can someone help me do this wit Hive syntax?

I can do it using SQL syntax (as below) but since Hive doesnt support
subqueries in select clauses I am unable to get it.

SELECT  T1.record,
        T1.fk,
        T1.start_time,
        T1.end_time,
        CASE WHEN T1.location != -1 THEN Location
        ELSE
            (
            SELECT  TOP (1)
                    T2.location
            FROM    #temp1 AS T2
            WHERE   T2.record < T1.record
            AND     T2.fk = T1.fk
            AND     T2.location != -1
            ORDER   BY T2.Record DESC
            )
        ENDFROM    #temp1 AS T1

Thank you for your help in advance!

On Fri, Sep 12, 2014 at 11:33 AM, Viral Parikh <viral.j.parikh@gmail.com>
wrote:

> To Whomsoever It May Concern,
>
> I've got a table that contains a LocationId field. In some cases, where a
> record shares the same foreign key, the LocationId might come through as -1.
>
> What I want to do is in my select query is in the case of this happening,
> the previous location.
>
> Example data:
>
> Record  FK     StartTime               EndTime          Location1       110  2011/01/01
12.30        2011/01/01 6.10      4562       110  2011/01/01 3.40         2011/01/01 4.00
      -13       110  2011/01/02 1.00         2011/01/02 8.00      8914       110  2011/01/02
5.00         2011/01/02 6.00       -15       110  2011/01/02 6.10         2011/01/02 6.30
      -1
>
> The -1 should come out as 456 for record 2, and 891 for record 4 and 5
>
> Can someone help me do this wit Hive syntax?
>
> I can do it using SQL syntax (as below) but since Hive doesnt support
> subqueries in select clauses I am unable to get it.
>
> SELECT  T1.record,
>         T1.fk,
>         T1.start_time,
>         T1.end_time,
>         CASE WHEN T1.location != -1 THEN Location
>         ELSE
>             (
>             SELECT  TOP (1)
>                     T2.location
>             FROM    #temp1 AS T2
>             WHERE   T2.record < T1.record
>             AND     T2.fk = T1.fk
>             AND     T2.location != -1
>             ORDER   BY T2.Record DESC
>             )
>         ENDFROM    #temp1 AS T1
>
> Thank you for your help in advance!
>

Mime
View raw message