CODEWITHSUNDEEP
sqlhive
yanachen
yanachen

Reputation: 3753

How to get the second last date from records in hive?

I have a table like this:

    member        dato
    696382      2016-06-21
    35546232    2016-02-01
    9001107     2013-09-23
    40310785    2014-07-18
    3802508     2015-06-21
    74376545    2016-01-11
    14969202    2014-12-08
    17495001    2015-09-01
    17238917    2016-11-16

The dato is the date when a member buy a product which is from 2015-01-01 till yesterday. I want to get a new table which contains three columns: member, dato, dato_second. dato_second is the date which is the most closest to the dato. For example, 17238917 has three datos in history in addtion to the '2016-11-16': '2016-11-10', '2015-03-27', the new record would be 17238917, '2016-11-16', '2016-11-10'. So how to get the new table?

Upvotes: 1

Views: 1067

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

This is what you are looking for

select  member
       ,dato
       ,lag (dato) over (partition by member order by dato) as prev_dato            

from   mytab

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Hmmm. I think conditional aggregation and row_number() is the simplest solution:

select member,
       max(case when seqnum = 1 then dato end) as dato,
       max(case when seqnum = 2 then dato end) as dato_1
from (select t.*,
             row_number() over (partition by member order by dato desc) as seqnum
      from t
     ) t
group by member;

Upvotes: 1

Related Questions