I have data as below. The data is in Datalake in the parquet format, partitioned by order_dt.
| cust_num | id1 | sum1 | final_sum | order_dt |
|---|---|---|---|---|
| 123 | 1 | 10 | null | 7-Sep |
| 123 | 1 | 27 | null | 7-Sep |
| 123 | 1 | 30 | null | 6-Sep |
| 123 | 1 | 25 | null | 12-Sep |
| 123 | 1 | 40 | 28 | 14-Sep |
I am trying to use the window function in pyspark to select data. My requirement is as below.
For every customer number and id combination select the data with the below condition along with the base record.
- Order_dt < order_dt-7
- sum <= final_sum
The first records does not have any matching record in the given data set. But the last record has a match in rec_nums 1,2 and 3. Out of this the recs 2 and 3 are filtered as they do not match the 2nd condition. So the output would look like.
| cust_num | id1 | sum1 | final_sum | order_dt_t1 | sumt2 | order_dt_t2 |
|---|---|---|---|---|---|---|
| 123 | 1 | 40 | 28 | 14-Sep | 10 | 7-Sep |
| 123 | 1 | 40 | 28 | 14-Sep | 27 | 7-Sep |
I used window.partitionBy function and an orderBy.ranegebetween clause to get the first condition. But i am not able to specify the second condition in the orderBy.
I tried writing a self join query. But that is taking too long to run. The data volume is 20 million. So a self join query can produce a lot of data with the volume growing into billions. Below is the query i used. Fairly simple one. But may not be the most efficient.
select t1.cust_num,t1.id1,t1.sum1,t1.final_sum,t1.order_dt as order_dt_t1,t2.sum_t2,t2.order_dt as order_dt_t2
from tab1 as t1 left outer join tab1 as t2
on
t1.cust_num=t2.cust_num and
t1.id1=t2.id1 and
t2.order_dt<date_sub(t1.order_dt,3) and
t1.sum1<t2.final_sum
Is there a more efficient way to tackle this problem ?