CODEWITHSUNDEEP
hivehiveql
kspr
kspr

Reputation: 1040

Hive: Add a column with a value repeated of a specific columnn in a specific row?

I have a table in Hive that looks like this called Products.

'Root Product | Product | Date 
     A            A       2012
     A            B       2013
     A            C       2013 
     D            D       2014 
     D            E       2015

Is it possible to add fourth column repeating the value of the date present in the column Date when Root Product == Product (the date of the root product)? Such that

'Root Product | Product | Date | Root Date
     A            A       2012     2012
     A            B       2013     2012
     A            C       2013     2012
     D            D       2014     2014
     D            E       2015     2014

Upvotes: 0

Views: 101

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Using max window function.

select root_product
      ,product
      ,date
      ,max(case when root_product = product then date end) over(partition by root_product) as root_date
from tbl

Upvotes: 1

Related Questions