CODEWITHSUNDEEP
sqloracle-databasefunctionhiveimpala
INAM
INAM

Reputation: 35

I want lag function to only return if previous data is available

Sample Data is below

In this example for 6th Jan lag will return apple, but i want this to be null because 5th Jan is missing, I need help for this give me idea or sql code in impala hive, I already have the code , but for 6th Jun it returns apple which is normal behavior of the lag function , but i need a trick so that the code can return null. Thanks.

Upvotes: 1

Views: 437

Answers (2)

Popeye
Popeye

Reputation: 35930

You can use analytical function first_value as follows:

Select date,
       first_value(sold) over (order by date 
                              range between 1 preceding and 1 preceding) as sold
  From your_table
Order by date;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can use a max() and range:

select t.*,
      max(sold) over (order by date range between interval '1' day preceding and interval '1' day preceding)
from t;

This is standard SQL but will not work in all databases. So, a case expression might be simplest:

select t.*,
       (case when lag(date) over (order by date) = date - interval '1' day
             then lag(sold) over (order by date)
        end) as prev_days_sold
from t;

Date functions are notoriously database-dependent, so the syntax might be slightly different depending on the database.

Upvotes: 3

Related Questions