CODEWITHSUNDEEP
sqlhadoophiveapache-spark-sql
BigD
BigD

Reputation: 888

date wise JOIN in Hive making issue

I need to join two tables based on following condition. my table A looks like 

  id,date
  12,20190114
  13,20190118
  14,20190123

Table B looks like

  id,date
  13,20190108
  12,20190108
  13,20190101
  13,20190115
  14,20190129
  14,20190122

when i apply join Condition , i need to consider followings

   1. id should be same for both tables
   2. date from table A should join with the date previous to the table B
    dates(table B dates are weekly basis... I need to find the current week).

That is table B dates are weekly dates. for example for id=13 table A date is 20190118 and the corresponding date from the table B is 20180115 , that is the current week the table A part of...

my result should after JOIN be like

  id,a.date,b.date
  13,20190118,2018015
  12,20190114,20190108
  14,20190123,20190122

can some one tell me how to acheieve this in hive

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269863

Does this work in Hive?

select a.id, a.date, max(b.date)
from a join
     b
     on a.id = b.id and b.date <= a.date
group by a.id, a.date;

Here is a db<>fiddle showing that it works on the data provides, albeit in Postgres.

Upvotes: 1

Related Questions