Блог

У меня есть два стола:

Таблица 1: Таблица, содержащая список событий с уникальными идентификаторами для каждого уникального события ( event_id ), дату события ( date_event ) и пол каждого человека ( gender ):

 person| event_id| date_event | gender|
----- |---------|------------|-------|
a     |  86i    | 2012-01-25 |   m   |
a     |  87i    | 2012-05-30 |   m   |
a     |  88i    | 2012-09-20 |   m   |
a     |  89i    | 2012-12-20 |   m   |
b     |  15i    | 2015-04-06 |   f   |
b     |  16i    | 2016-07-06 |   f   |
b     |  17i    | 2016-04-30 |   f   |
b     |  18i    | 2016-11-28 |   f   |
----- |---------|------------|-------|
 

Таблица 2: Таблица дат с перечислением дат начала ( date_start ) и окончания ( date_end ), представляющих диапазоны времени, в течение которых каждый человек был зачислен в программу:

 person| date_start | date_end   |
----- |------------|------------|
a     | 2012-02-05 | 2012-03-30 |
a     | 2012-06-26 | 2012-08-28 |
a     | 2012-09-15 | 2012-12-31 |
b     | 2015-01-24 | 2015-03-30 |
b     | 2016-07-01 | 2016-10-01 |
b     | 2016-11-25 | 2016-12-30 |
----- |------------|------------|
 

Я хотел бы иметь возможность фильтровать таблицу 1, чтобы она содержала только записи о событиях, которые произошли, когда человек был зарегистрирован в программе, которая будет указана как date_event «между date_start и date_end «. Моя желаемая результирующая таблица будет выглядеть следующим образом:

Желаемый результат:

 person| event_id| date_event | gender|
----- |---------|------------|-------|
a     |  88i    | 2012-09-20 |   m   |
a     |  89i    | 2012-12-20 |   m   |
b     |  16i    | 2016-07-06 |   f   |
b     |  18i    | 2016-11-28 |   f   |
----- |---------|------------|-------|
 

Я попробовал несколько вариантов следующего, но безрезультатно:

 CREATE TABLE temptable AS
SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 
USING(person);

CREATE TABLE desiredresult AS
SELECT * 
FROM temptable 
WHERE date_event BETWEEN date_start AND date_end
GROUP BY person;
 

Я признаю, что я определенно новичок в SQL и привык использовать R, где я могу достичь желаемого результата, используя приведенный ниже код. Однако набор данных, который я использую, огромен, поэтому мне нужно выяснить, как выполнять эти операции на сервере SQL.

 library(dplyr)
library(lubridate)

desiredresult <- table1 %>%
    left_join(table2) %>%
    group_by(person) %>%
    mutate(keep_record = date_event %within% interval(start_date, end_date)) %>%
    filter(keep_record == TRUE)
 

Заранее спасибо.

Нет причин создавать временную таблицу и тратить на это время обработки. Просто выберите прямо из СОЕДИНЕНИЯ. (См. Demd)

 select t1.* 
  from table1 t1
  join table2 t2 
    on (     t1.person = t2.person
         and t1.date_event between t2.date_start and t2.date_end 
       ) ;