CODEWITHSUNDEEP
hadoophivehiveql
pshan99
pshan99

Reputation: 23

Hive: select previous nth row with minimum value for a column

I have data like this

ID  START_DATE STATUS
10  2013-05-29 FREE
10  2013-05-29 PAID
10  2014-05-30 PAID
10  2014-11-29 FREE
10  2014-12-02 PAID
10  2015-09-29 PAID
10  2015-12-02 PAID
10  2016-04-04 PAID
10  2016-04-05 FREE

My Output should be only contain rows where status = "FREE". Every time the status is FREE, I need to get the previous minimum date where the Status = "PAID".

ID STATUS PREVIOUS_MIN_PAID_START_DATE
10 FREE   NULL
10 FREE   2013-05-29
10 FREE   2014-12-02

The LAG() function only gives the first previous value, how can I get the previous minimum (nth) value?

SELECT
  ID,
  STATUS,
  LAG(CASE WHEN STATUS = 'PAID' THEN  START_DATE, 1)  
     OVER (PARTITION BY ID ORDER BY START_DATE) AS previous_paid_start_date
FROM
  TEMP
WHERE
  STATUS = 'FREE'

Upvotes: 2

Views: 1517

Answers (1)

o-90
o-90

Reputation: 17593

Not sure why you were receiving downvotes, I thought this was a pretty interesting (and well described problem). Anyways, here is a way to do this, although I must admit it feels sub-optimal and hacky.

Basically, what you need is an index that creates a group of the "current" FREE and all subsequent PAID until you reach the next FREE (I hope I've understood this correctly). To illustrate:

id  start_date status idx
10  2013-05-29 FREE   0
10  2013-05-29 PAID   1 
10  2014-05-30 PAID   1
10  2014-11-29 FREE   1
10  2014-12-02 PAID   2
10  2015-09-29 PAID   2
10  2015-12-02 PAID   2
10  2016-04-04 PAID   2
10  2016-04-05 FREE   2

Then from there you can get the minimum start_date where the status is PAID over the window of id and and the newly created index.

Query:

WITH tmp_table AS (
  SELECT *
    , SUM(flg) OVER (PARTITION BY id ROWS UNBOUNDED PRECEDING) AS s
  FROM (
    SELECT *
      , LEAD(CASE WHEN status='FREE' THEN 1 ELSE 0 END, 1, 0) OVER (PARTITION BY id) AS flg
    FROM database.original_table) x )

SELECT id
  , status
  , prev_date
FROM (
  SELECT t.id, t.status, t.s, b.prev_date
  FROM tmp_table t
  LEFT OUTER JOIN (
    SELECT id, s, MIN(start_date) AS prev_date
    FROM tmp_table
    WHERE status='PAID'
    GROUP BY id, s ) b
  ON b.id=t.id AND b.s=t.s ) f
WHERE status='FREE'

Output:

id status prev_date
10 FREE   NULL
10 FREE   2013-05-29
10 FREE   2014-12-02

Upvotes: 1

Related Questions