Joining on date or the next closest date after

user_19714433 0
Viewed 67

I have two tables I'm trying to join on date and account. For table one the field for date is continuous and is type string. Whereas for table two the field date has a portion of the date field as end of month only.

Edit: The sample I provided below is an example of tables I am working with. Originally I had only mentioned about joining the ID and Date fields but there are other fields in both tables that I am trying to keep in the final output. On a larger scale Table 1 has thousands of IDs with records continuously recorded daily across multiple years. In Table 2 this is similar but at some point the date field switched from only end of month data to the same daily date data as table one. The tables below have been updated.

A sample of the data set could be seen as follows:

Table1:
| ID | Date         |
| -- | ----------   |      
| 1  | "2022-01-30" |
| 1  | "2022-01-31" |
| 1  | "2022-02-01" |
| 1  |  "2022-02-02"|

Table2:
| ID   | Date         |Field_flag|
| ---- | ----------   | -------  | 
| 1    | "2021-12-31" |a         | 
| 1    | "2022-01-31" |a         |
| 1    | "2022-02-01" |a         |
| 1    | "2022-02-02" |b         |
| 1    |  "2022-02-03"|b         |

Desired result:
| table1.ID | table1.Date | table2.date| table2.Field_flag|
| --------  | ----------  | ---------- |  --------------- |  
| 1         | "2022-01-30"|"2022-01-31"| a                |
| 1         | "2022-01-31"|"2022-01-31"| a                |
| 1         | "2022-02-01"|"2022-02-01"| a                | 
| 1         | "2022-02-02"|"2022-02-02"| b                |

Are there any suggestions on how to approach this type of result? I'm currently temp fielding and sub sampling the date fields into month and year as a temporary solution but would like something like an inner join as such to work.

Select table1.*
      ,table2.date as date_table2
      ,table2.Field_flag
FROM table1
INNER JOIN (SELECT * FROM Table2)
ON  table1.id = table2.id and (table1.date = table2.date or table1.date < table2.date)
pyspark apache-spark-sql
2 Answers

One way to handle this would be to use a correlated subquery to find the same or closest but greater date for each date in the first table.

SELECT
    t1.ID,
    t1.Date AS Date_t1,
    (SELECT t2.Date FROM Table2 t2
     WHERE t2.ID = t1.ID AND t2.Date >= t1.Date
     ORDER BY t2.Date LIMIT 1) AS Date_t2
FROM Table1 t1
ORDER BY t1.ID, t1.Date;

screen capture from demo link below

Demo

user_18632290

This might not be the optimal way but throwing it into the mix. We can join the second table twice - once for the daily dates and once for the month end dates.

data2_2_sdf = spark.sql('''
    select *,
        datediff(dt, lag(dt) over (partition by id order by dt)) as dt_diff_lag,
        datediff(lead(dt) over (partition by id order by dt), dt) as dt_diff_lead
        from data2
    ''')

data2_2_sdf.createOrReplaceTempView('data2_2')

# +---+----------+----+-----------+------------+
# | id|        dt|flag|dt_diff_lag|dt_diff_lead|
# +---+----------+----+-----------+------------+
# |  1|2021-12-31|   a|       null|          31|
# |  1|2022-01-31|   a|         31|           1|
# |  1|2022-02-01|   a|          1|           1|
# |  1|2022-02-02|   b|          1|           1|
# |  1|2022-02-03|   b|          1|        null|
# +---+----------+----+-----------+------------+

spark.sql('''
    select a.*, coalesce(b.dt, c.dt) as dt2, coalesce(b.flag, c.flag) as flag
    from data1 a
    left join (select * from data2_2 where dt_diff_lag>=28 or dt_diff_lead>=28) b
    on a.id=b.id and year(a.dt)*100+month(a.dt)=year(b.dt)*100+month(b.dt)
    left join (select * from data2_2 where dt_diff_lag=1 and coalesce(dt_diff_lead, 1)=1) c
    on a.id=c.id and a.dt=c.dt
    '''). \
    show()

# +---+----------+----------+----+
# | id|        dt|       dt2|flag|
# +---+----------+----------+----+
# |  1|2022-02-01|2022-02-01|   a|
# |  1|2022-01-31|2022-01-31|   a|
# |  1|2022-01-30|2022-01-31|   a|
# |  1|2022-02-02|2022-02-02|   b|
# +---+----------+----------+----+
user_82795850
Related