Re: Window Function with Ignore Nulls?

[praveen akinapally <pr...@gmail.com>]

Hi Hulbert,

Select id, last_value(address,true) over (partition by id order by
file_date) as address from address_table; works in Hive 0.13.1. Not sure
about Hive 0.11. Try and let me know.


Regards,

Praveen Akinapally

On Fri, Dec 19, 2014 at 5:43 AM, Hulbert, Leland <Leland.Hulbert@epsilon.com
> wrote:

> Hi,
>                 I'm currently on Hive 0.11.  I'm trying to pull the last
> non-null record from a dataset.  Ideally, I would use something like this:
>
> Select id, last_value(address ignore nulls) over (partition by id order by
> file_date) as address from address_table;
>
>                 But Hive doesn't seem to accept ignore nulls in window
> functions.  I've also tried this:
>
> Select id, nvl(address, lag(lag_address, 1) over (partition by id order by
> file_date)) as lag_address from address_table;
>
>                 But it won't recognize the lag_address field name.  My
> last option was this:
>
> Select id, case when address is not null then address else lag(address, 1)
> over (partition by id order by file_date) end as lag_address from
> address_table;
>
>                 Which throws an error that doesn't make sense to me
> (ParseException missing KW_END at 'over' near ')' )
>
>                 Is there a reasonable way to get this done in Hive?  I've
> got a couple dozen or more fields that need the same rule applied, so
> options that require a separate inner query per field are a performance
> issue.  Thanks for any direction you can give.
>
> Thanks,
>
> Lee
>
>
> ________________________________
>
> This e-mail and files transmitted with it are confidential, and are
> intended solely for the use of the individual or entity to whom this e-mail
> is addressed. If you are not the intended recipient, or the employee or
> agent responsible to deliver it to the intended recipient, you are hereby
> notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you are not one of the named
> recipient(s) or otherwise have reason to believe that you received this
> message in error, please immediately notify sender by e-mail, and destroy
> the original message. Thank You.
>



-- 
Thanks and Regards,

Praveen Akinapally
Phone: 321-432-5465