Pytanie Jak rozwiązać luki i problemy wysp w R i wydajności vs SQL?


Zastanawiałem się, czy problemy z wyspami i lukami można skutecznie rozwiązać w języku R, podobnie jak w SQL. Mam dostępne następujące dane, jeśli przeanalizujemy jeden identyfikator:

ID StartDate  StartTime EndDate      EndTime 
1  19-05-2014 19:00     19-05-2014   20:00
1  19-05-2014 19:30     19-05-2014   23:30
1  19-05-2014 16:00     19-05-2014   18:00
1  20-05-2014 20:00     20-05-2014   20:30

Zauważ, że pierwsze dwa rzędy nakładają się, co chciałbym zrobić, to połączenie nakładających się wierszy, co skutkuje:

ID StartDate  StartTime EndDate      EndTime 
1  19-05-2014 19:00     19-05-2014   23:30
1  19-05-2014 16:00     19-05-2014   18:00
1  20-05-2014 20:00     20-05-2014   20:30

Czy istnieje sposób, aby to zrobić w R?

Mam świadomość, że odbywa się to w SQL, ale ponieważ moje dane są już w R, wolę zrobić to w R. Po drugie, mam kilka pytań dotyczących wydajności wyszukiwania luk i wysp, Wiem, że SQL jest bardzo szybki w robiąc to, ale zastanawiam się, czy R jest szybszy ze względu na wszystkie dane znajdujące się w pamięci.

Chciałbym użyć data.table Aby to zrobić, ale nie wiem jak.

AKTUALIZACJA - Odpowiedź na Arun

Utworzono następujący przypadek testowy, który zawiera każdą możliwą orientację interwałową.

dat <- structure(
  list(ID = c(1L, 1L, 1L, 1L, 1L, 1L), 
       stime = structure(c(as.POSIXct("2014-01-15 08:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 11:30:00"),
                           as.POSIXct("2014-01-15 12:00:00")),
                         class = c("POSIXct", "POSIXt"), tzone = ""),
       etime = structure(c(as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 11:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"), 
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 12:30:00"),
                           as.POSIXct("2014-01-15 13:00:00")), 
                         class = c("POSIXct", "POSIXt"), tzone = "")
  ),
  .Names = c("ID", "stime", "etime"),
  sorted = c("ID", "stime", "etime"),
  class = c("data.table", "data.frame"),
  row.names = c(NA,-6L)
)

Spodziewam się, że przerwa między godziną 8:30 a 10:00 zostanie "sklejona" w godzinach 10: 00-11: 00, ale tak nie było. Wynik był następujący:

   idx ID               stime               etime
1:   4  1 2014-01-15 08:00:00 2014-01-15 10:00:00
2:   3  1 2014-01-15 10:00:00 2014-01-15 11:00:00
3:   6  1 2014-01-15 11:30:00 2014-01-15 13:00:00

Poniższy zestaw danych zapewnia dokładniejsze testowanie:

# The numbers represent seconds from 1970-01-01 01:00:01
dat <- structure(
  list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), 
       stime = structure(c(as.POSIXct("2014-01-15 08:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 11:30:00"),
                           as.POSIXct("2014-01-15 12:00:00"),
                           as.POSIXct("2014-01-15 07:30:00"),
                           as.POSIXct("2014-01-15 08:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 10:00:00")
                           ),
                         class = c("POSIXct", "POSIXt"), tzone = ""),
       etime = structure(c(as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 11:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"), 
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 12:30:00"),
                           as.POSIXct("2014-01-15 13:00:00"),
                           as.POSIXct("2014-01-15 08:30:00"),
                           as.POSIXct("2014-01-15 09:00:00"),
                           as.POSIXct("2014-01-15 09:30:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 10:00:00"),
                           as.POSIXct("2014-01-15 10:30:00"),
                           as.POSIXct("2014-01-15 11:00:00")
                           ), 
                         class = c("POSIXct", "POSIXt"), tzone = "")
  ),
  .Names = c("ID", "stime", "etime"),
  sorted = c("ID", "stime", "etime"),
  class = c("data.table", "data.frame"),
  row.names = c(NA,-6L)
)

Nasz wynik to:

   idx ID               stime               etime
1:   4  1 2014-01-15 08:00:00 2014-01-15 10:00:00
2:   3  1 2014-01-15 10:00:00 2014-01-15 11:00:00
3:   6  1 2014-01-15 11:30:00 2014-01-15 13:00:00
4:  12  2 2014-01-15 07:30:00 2014-01-15 09:30:00
5:  13  2 2014-01-15 09:00:00 2014-01-15 11:00:00

Teraz dla respondenta o ID = 2 widzimy, że interwały zachodzą na siebie, ale nie są zgłaszane jako jeden interwał. Właściwym rozwiązaniem byłoby:

   idx ID               stime               etime
1:   ?  1 2014-01-15 08:00:00 2014-01-15 11:00:00
3:   ?  1 2014-01-15 11:30:00 2014-01-15 13:00:00
4:  ??  2 2014-01-15 07:30:00 2014-01-15 11:00:00

Aktualizacja - Testy porównawcze i testy oraz duże zestawy danych

Mam następujący zestaw danych z około 1000 użytkowników, z których każdy ma 500 razy, dając 0,5 miliona wierszy. Możesz pobrać zestaw danych na mój dysk Google, w tym rozwiązanie na Dysku Google.

SQL Server 2014 na laptopie z 8 GB pamięci RAM, 64-bitowym, i5-4210U CPU @ 1.70Ghz - 2,39 GHz zajmuje około 5 sekund, aby to zrobić, korzystając z rozwiązania dostarczonego przez Itzik Ben-Gan w SQL. 5 sekund wyklucza proces tworzenia funkcji. Ponadto, żadne indeksy nie są tworzone dla żadnej tabeli.

PS: Używam library(lubridate);


11
2018-06-03 20:19


pochodzenie


jakie jest rozwiązanie SQL? - eddi
Drogi Eddi, oto kilka przykładów tego problemu: sqlmag.com/blog/.... Itzik Ben-Gan był w stanie to zrobić w imponującym 2 sekundy. - Snowflake
@alexis_laz, tak by działało tutaj, ale POSIXct może mieć w nich także milisekundy, a to by się nie udawało (ponieważ IRanges :: reduce niejawnie konwertowałoby to na zakresy całkowite). - Arun


Odpowiedzi:


Oto bardzo prosty pomysł. Zamów według czasu rozpoczęcia, a następnie znajdź skumulowaną maksymalną godzinę zakończenia. Gdy to zrobisz, grupy pokrywające się są po prostu tymi, w których następny czas rozpoczęcia jest nadal mniejszy lub równy bieżącemu łącznemu maksymalnemu czasowi zakończenia (wszystko zrobione przez ID):

setorder(dat, ID, stime) # ordering by ID is unnecessary, it's just prettier

dat[, etime.max := as.POSIXct(cummax(as.numeric(etime)), origin = '1970-01-01'), by = ID]

# find the grouping of intervals (1:.N hack is to avoid warnings when .N=1)
dat[, grp := cumsum(c(FALSE, stime[2:.N] > etime.max[1:(.N-1)]))[1:.N], by = ID]

dat[, .(stime = min(stime), etime = max(etime)), by = .(ID, grp)][, grp := NULL][]
#   ID               stime               etime
#1:  1 2014-01-15 08:00:00 2014-01-15 11:00:00
#2:  1 2014-01-15 11:30:00 2014-01-15 13:00:00
#3:  2 2014-01-15 07:30:00 2014-01-15 11:00:00

Ponieważ nie trzeba szukać wszystkich możliwych nakładek, jest to bardzo szybkie. Na symulowanym zbiorze danych, który z grubsza odpowiada opisowi OP, jest natychmiastowy dla mnie (<0,2 s).


9
2018-06-03 21:14



Może to zabrzmieć głupio ode mnie, ale czy mógłbyś wyjaśnić, co masz na myśli, mówiąc o skumulowanym maksimum czasu zakończenia? (Nie jestem jeszcze zbyt dobry w zakresie data.table, ale bardzo chcę się dowiedzieć, jak działa trzecia linia twojego kodu). - Snowflake
Mam na myśli maksymalny czas zakończenia do aktualnego punktu. Sprawdzić ?cummax dla przykładu. Jeśli chodzi o trzecią linię, sprawdź, co każdy kawałek osobno oblicza (dla pojedynczego ID), a wtedy to będzie miało sens. Może również pomóc w przekształceniu wizualizacji etime.max do POSIXct (Właściwie to właśnie to zredagowałem, ułatwiałem czytanie). - eddi
Dzięki Eddi, rozumiem teraz twój kod, ale zastanawiam się, co to jest ". (Rzeczy)". Myślałem, że powinien być na listach, ale nie mogę znaleźć niczego w notacji nawiasów kropkowych. A może przeoczyłem to. Wydaje się, że to bardzo eleganckie rozwiązanie, które może działać. Sprawdzę to z pełnymi 500 000 wierszy, których używam do testowania i zgłoś się ponownie :). - Snowflake
Kropka jest odpowiednikiem pisania list a imo jest znacznie bardziej czytelne. Chcę powiedzieć, że został wprowadzony niedawno, ponieważ zacząłem to ostatnio robić, ale nie pamiętam tego na pewno. Fajnie, czekam na testy :) - eddi
Dzięki Eddi, było znacznie szybciej niż SQL. Dostałem 1 sekundę na dane testowe. - Snowflake