Блог

Главная - Вопросы по программированию - Присоединяйтесь к ближайшей дате в SQL

Присоединяйтесь к ближайшей дате в SQL

#sql #join #snowflake-cloud-data-platform #sql-date-functions #sqldatetime

Вопрос:

Привет, у меня есть две таблицы, к которым я должен присоединиться в поле ID, а затем в ближайшую дату ( только если дата находится в пределах одного часового диапазона больше или меньше), причина в том, что обе таблицы не обновляются одновременно, поэтому время между ними немного ( но максимальная задержка составляет 1 час). Пожалуйста, смотрите пример ниже:

Таблица 1:

 ID Start_Date         End_Date
1  1/14/2021 14:34    1/27/2021 10:31
1  2/4/2021 10:40     7/2/2021 13:01
2  8/2/2020 00:04      9/7/2020 11:26
2  11/4/2020 9:24     2/8/2021 16:22

Таблица 2:

 ID Start_date         End_Date
1  1/14/2021 13:47    1/27/2021 10:24
2  8/1/2020 23:57      9/7/2020 11:22
2  11/4/2020 9:12     2/8/2021 16:20

В этом примере: запись 1 из таблицы 1 должна присоединиться к записи 1 из таблицы 2.

записи 2 из таблицы 1 не должны присоединяться к таблице 2 ( поскольку для этого идентификатора нет записи в течение 1 часа с даты начала), запись 3,4 должна присоединяться к записи 3,4 в таблице 2 соответственно.

Комментарии:

1. Интересный вопрос.

Ответ №1:

Отказ от ответственности: Я не эксперт по снежинке, и у меня нет никакого способа протестировать решение, описанное ниже.

Предполагая, что вы можете использовать стандартный SQL в Snowflake, приведенный ниже запрос предварительно вычисляет разницу во времени между датами начала возможных связанных строк и ранжирует их от самого низкого до самого высокого. Наименьшая (минимальная разница во времени) выигрывает объединение.

Вы можете сделать:

 select *
from (
  select
    a.*,
    b.*,
    row_number() over(
      partition by a.id
      order by abs(timediff(second, a.start_date, b.start_date))
    ) as rn
  from table1 a
  join table2 b on a.id = b.id
   and a.start_date between dateadd(hour, -1, b.start_date) 
                        and dateadd(hour, 1, b.start_date)
) x
where rn = 1

Ответ №2:

Другим вариантом может быть:

 select *  from  table_1 inner join table_2 on table_1.id = table_2.id 
where      DATEADD(hour,1,table_1.d_1)   between table_2.d_1 and table_2.d_2  
or         DATEADD(hour,-1,table_1.d_2)  between table_2.d_1 and table_2.d_2

введите описание изображения здесь

Полный код вы можете вырезать/вставить в snowflake и запустить 🙂

 with table_1 as ( 
select 1 id,    TO_TIMESTAMP('1/14/2021 14:34',  'mm/dd/yyyy hh24:mi' ) d_1,     TO_TIMESTAMP('1/27/2021 10:31',  'mm/dd/yyyy hh24:mi' )  d_2 
union select 1 id,    TO_TIMESTAMP('2/4/2021 10:40',  'mm/dd/yyyy hh24:mi' )  d_1,     TO_TIMESTAMP('7/2/2021 13:01',  'mm/dd/yyyy hh24:mi' )  d_2 
union select 2 id,    TO_TIMESTAMP('8/2/2020 00:04',  'mm/dd/yyyy hh24:mi' )  d_1,     TO_TIMESTAMP('9/7/2020 11:26',  'mm/dd/yyyy hh24:mi' )  d_2 
union select 2 id,    TO_TIMESTAMP('11/4/2020 9:24',  'mm/dd/yyyy hh24:mi' )  d_1,     TO_TIMESTAMP('2/8/2021 16:22',  'mm/dd/yyyy hh24:mi' )  d_2 ) 
, table_2 as ( 
select 1 id,    TO_TIMESTAMP('1/14/2021 13:47',  'mm/dd/yyyy hh24:mi' ) 
d_1,     TO_TIMESTAMP('1/27/2021 10:24',  'mm/dd/yyyy hh24:mi' )  d_2 
union select 2 id,    TO_TIMESTAMP('8/1/2020 23:57',  'mm/dd/yyyy hh24:mi' )  d_1,     TO_TIMESTAMP('9/7/2020 11:22',  'mm/dd/yyyy hh24:mi' )  d_2 
union select 2 id,    TO_TIMESTAMP('11/4/2020 9:12',  'mm/dd/yyyy hh24:mi' )  d_1,     TO_TIMESTAMP(' 2/8/2021 16:20',  'mm/dd/yyyy hh24:mi' )  d_2   ) 

select *  from  table_1 inner join table_2 on table_1.id = table_2.id 
where      DATEADD(hour,1,table_1.d_1)   between table_2.d_1 and table_2.d_2  
or         DATEADD(hour,-1,table_1.d_2)  between table_2.d_1 and table_2.d_2
Метки: