Compare data with previous day | Hive HQL

user_18492524 0
Viewed 69

I have a table called table_1 with all the history of some users as you can see here for the day 2022-03-02:

+-----------+-------------+----------+------------+-----+
| ID        |   Name      | Document | Date       | opt |
+-----------+-------------+----------+------------+-----+
| 1a        | Albert      | 11111111 | 2022-03-02 | xxx |
| 2a        | Dani        | 22222222 | 2022-03-02 | xxx |
+-----------+-------------+----------+------------+-----+

The next day I have the same thing but with a change in the document:

+-----------+----------+----------+------------+-----+
| ID        |   Name   | Document | Date       | opt |
+-----------+----------+----------+------------+-----+
| 1a        | Albert   | 11111111 | 2022-03-02 | xxx |
| 2a        | Dani     | 22222222 | 2022-03-02 | xxx |
| 1a        | Albert   | 55555555 | 2022-03-03 | xxx |
| 2a        | Dani     | 22222222 | 2022-03-03 | xxx |
+-----------+----------+----------+------------+-----+

As you can see there is a change in the Document column on the day 2022-03-03. What I want to do is get this information in a new table with the rows that have changes, in this case it would be like this:

+-----------+---------+----------+------------+----------+------+
| ID        |   Name  | Document | Date       | new_doc  | opt  | 
+-----------+---------+----------+------------+----------+------+
| 1a        | Albert  | 11111111 | 2022-03-02 | 55555555 | xxx  |
+-----------+---------+----------+------------+----------+------+

I'm trying to do this with subqueries, but I can't find a way to compare the data for the day specified in Date with the day before.

How could I do this? Thanks in advance!

hiveql hive hql sql
1 Answers

The following should work. You might only need to change the t.date < t2.date clause on the join conditions to t.date = t2.date - 1 day. But I think that this depends on your underlying ETL and also on the type of your date field. In any case, there is a hive function to do this.

select t.id, t.name, t.document, t.`date`, t2.document as new_doc, t.opt 
from t join t t2 
on t.name = t2.name 
and t.`date` = date_sub(t2.`date` , 1)
and t.document != t2.document;
user_130514080
Related