Вот установка (это на самом деле не так сложно...):
Таблица Названия должностей
| PersonID | JobTitle | StartDate | EndDate |
|----------|----------|-----------|---------|
| A | A1 | 1 | 5 |
| A | A2 | 6 | 10 |
| A | A3 | 11 | 15 |
| B | B1 | 2 | 4 |
| B | B2 | 5 | 7 |
| B | B3 | 8 | 11 |
| C | C1 | 5 | 12 |
| C | C2 | 13 | 14 |
| C | C3 | 15 | 18 |
Таблица Транзакции:
| PersonID | TransDate | Amt |
|----------|-----------|-----|
| A | 2 | 5 |
| A | 3 | 10 |
| A | 12 | 5 |
| A | 12 | 10 |
| B | 3 | 5 |
| B | 3 | 10 |
| B | 10 | 5 |
| C | 16 | 10 |
| C | 17 | 5 |
| C | 17 | 10 |
| C | 17 | 5 |
Желаемый результат:
| PersonID | JobTitle | StartDate | EndDate | Amt |
|----------|----------|-----------|---------|-----|
| A | A1 | 1 | 5 | 15 |
| A | A2 | 6 | 10 | 0 |
| A | A3 | 11 | 15 | 15 |
| B | B1 | 2 | 4 | 15 |
| B | B2 | 5 | 7 | 0 |
| B | B3 | 8 | 11 | 5 |
| C | C1 | 5 | 12 | 0 |
| C | C2 | 13 | 14 | 0 |
| C | C3 | 15 | 18 | 30 |
Этот SQL дает мне желаемый результат:
select jt.PersonID, jt.JobTitle, jt.StartDate, jt.EndDate, coalesce(sum(amt), 0) as amt
from JobTitles jt left join
Transactions t
on jt.PersonId = t.PersonId and
t.TransDate between jt.StartDate and jt.EndDate
group by jt.PersonID, jt.JobTitle, jt.StartDate, jt.EndDate;
Таблицы для R:
JobTitles <- structure(list(PersonID = structure(c(1L, 1L, 1L, 2L, 2L, 2L,
3L, 3L, 3L), .Label = c("A", "B", "C"), class = "factor"), JobTitle = structure(1:9, .Label = c("A1",
"A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3"), class = "factor"),
StartDate = c(1L, 6L, 11L, 2L, 5L, 8L, 5L, 13L, 15L), EndDate = c(5L,
10L, 15L, 4L, 7L, 11L, 12L, 14L, 18L)), .Names = c("PersonID",
"JobTitle", "StartDate", "EndDate"), class = "data.frame", row.names = c(NA,
-9L))
Transactions <- structure(list(PersonID = structure(c(1L, 1L, 1L, 1L, 2L, 2L,
2L, 3L, 3L, 3L, 3L), .Label = c("A", "B", "C"), class = "factor"),
TransDate = c(2L, 3L, 12L, 12L, 3L, 3L, 10L, 16L, 17L, 17L,
17L), Amt = c(5L, 10L, 5L, 10L, 5L, 10L, 5L, 10L, 5L, 10L,
5L)), .Names = c("PersonID", "TransDate", "Amt"), class = "data.frame", row.names = c(NA,
-11L))
Как перевести SQL
в рабочий код dplyr
? Я застреваю на left_join
:
left_join(JobTitles, Transactions,
by = c("PersonID" = "PersonID",
"StartDate" < "TransDate",
"EndDate" >= "TransDate"))
# Error: cannot join on columns 'TRUE' x '' : index out of bounds
dplyr
является то, что мы должны сначала выполнить (левое) соединение, чтобы получить все данные, после чего идет фильтрация, группировка и суммирование. Это будет очень неэффективно (с точки зрения памяти и скорости), если у вас большие данные. Если вы не привязаны к dplyr, извлеките?foverlaps
из data.table или?findOverlaps
из пакетов IRanges (если вы работаете в памяти). - person Arun   schedule 09.12.2014sqldf
? - person zx8754   schedule 09.12.2014dplyr
, и на самом деле я надеялся, что кто-то упомянет что-то об эффективности, как только что сделал @Arun. - person JasonAizkalns   schedule 09.12.2014foverlaps
. Глядя на пример в руководстве CRAN, кажется, что вам нужны как начало, так и конец в двух объектах data.table. Это правильно? В этом вопросе фрейм данных Transactions не имеет начала и конца. Он содержит только даты транзакций, которые могут находиться между начальной и конечной датами в другом фрейме данных, JobTitles. Можно ли в этом случае использоватьforverlaps
? - person jazzurro   schedule 10.12.2014sqldf
вместе сfoverlaps
(77 с против 3,8 с, 1,4 ГБ против 1 ГБ) только в части соединения (что похоже на запрос OP здесь). И, насколько мне известно, задача соединения интервалов не может быть выполнена более эффективно, чем алгоритмы, предназначенные для эффективной работы с интервалами. Я не знаю баз данных, способных оптимизировать более эффективно, чем интервальные соединения. Кажется, вы говорите об оптимизации, но не знаете, какие оптимизации могут сделать ее (более) эффективной, что сбивает с толку. - person Arun   schedule 11.12.2014sqldf
сPostgreSQL
- это было в 3 раза медленнее. Опять же, если у вас есть конкретные оптимизации, дайте мне знать, и я буду рад попробовать (и изменить свое мнение). Я не знаю, это сильно отличается от того, что я думаю, что это невозможно реализовать. Мои тесты пока не показали никакой разницы. Пожалуйста, покажите пример, если вы считаете, что его можно улучшить. - person Arun   schedule 11.12.2014