mutate on grouped database column using 'first()' without collect()'ing first

Question

Without using the `collect()' function I'm getting an error with my code

joined_table %>%
  filter(message.y == 'CURR') %>%
  filter(parameter.y == 'Volt') %>%
  select(flight, timestamp.y, value) %>%
  #collect() %>%
  group_by(flight) %>%
  mutate(first = first(timestamp.y)) %>%
  mutate(shifted = (timestamp.y - first)/ 60)

Error:

Error: Window function `first_value()` is not supported by this database

I wish to take the timestamp column and shift it to begin from zero for each flight for ploting. If I add in the commented collect() the function works as it imports the data into R. This is very early in the pipline and has a serious overhead for me as the database is 80Gb.

What alternatives do i have?

Can I use some other functions to achieve the same result as I get from those 2 mutates? (Maybe something from zoo?)
Should I move from SQLite to some other database that supports first_value()?
??

You can still group and join back on the original. – moodymudskipper Apr 17 '18 at 21:09 — moodymudskipper, Apr 17 '18 at 21:09

score 0 · Answer 1 · answered Apr 17 '18 at 10:30

0

RTFM

SQLite does not support window functions and as there is quite abit of data, migration is in order.

answered Apr 17 '18 at 10:30

Karl Uibo

353
4
10

mutate on grouped database column using 'first()' without collect()'ing first

1 Answers1