CODEWITHSUNDEEP
sqlhiveql
Moosa
Moosa

Reputation: 3216

Select prev value in Hive SQL

I'm been poking at this for a while and haven't had luck. I have a table like the below. I'm trying to get the col value prior to the first text value per user. I'm on Hive.

user    ts            col      isnumber
1       1473811200    5         y
1       1473811205    10        y
1       1473811207    15        y
1       1473811212    text1     n
1       1473811215    text2     n
1       1473811225    30        y
2       1473811201    10        y
2       1473811205    text3     n
2       1473811207    20        y
2       1473811210    30        y

Output should be:

user    col
1       15
2       10

Upvotes: 2

Views: 68

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

Using windowed functions:

SELECT user_, prev
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY user_ ORDER BY ts) AS rn
      FROM (SELECT *, CASE 
                WHEN isnumber = 'y' THEN NULL
                WHEN LAG(isnumber,1) OVER(PARTITION BY user_ ORDER BY ts) = 'y'
                     THEN LAG(col,1) OVER(PARTITION BY user_ ORDER BY ts)
               END AS prev
             FROM tab) sub
    WHERE prev IS NOT NULL) sub2
WHERE rn = 1;

DBFiddle Demo

Upvotes: 1

Related Questions