未使用collect()时调用first()处理分组列报错，需实现航班时间戳归零

Why You're Seeing the Error #

The error pops up because you’re running your dplyr pipeline directly against a database backend (since you left collect() commented out). Not all databases support the first() window function that dplyr tries to translate to, so the operation fails at the database level.

Option 1: Pull Data to Local Memory (Simplest for Small Datasets) #

If your dataset fits comfortably in your machine’s memory, just uncomment the collect() step. This pulls the filtered data into R’s local environment, where all dplyr window functions work seamlessly:

joined_table %>% 
  filter(message.y == 'CURR') %>% 
  filter(parameter.y == 'Volt') %>% 
  select(flight, timestamp.y, value) %>% 
  collect() %>%  # Uncomment this to bring data into local R memory
  group_by(flight) %>% 
  mutate(first = first(timestamp.y)) %>% 
  mutate(shifted = (timestamp.y - first)/ 60)

Option 2: Database-Compatible Alternative (For Large Datasets) #

If your data is too big to pull locally, use database-friendly functions to calculate the starting timestamp per flight.

Use min() for Earliest Time Offset

Most SQL databases support min(), which gives you the earliest timestamp in each flight group—perfect for creating a 0-based minute offset:

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

Use row_number() for First Row Timestamp

If you specifically need the timestamp from the first row in each group (not just the earliest time), use row_number() (supported by most databases) to mark and propagate the first entry:

joined_table %>% 
  filter(message.y == 'CURR') %>% 
  filter(parameter.y == 'Volt') %>% 
  select(flight, timestamp.y, value) %>% 
  group_by(flight) %>% 
  mutate(row_num = row_number()) %>% 
  mutate(first = ifelse(row_num == 1, timestamp.y, NA)) %>% 
  fill(first, .direction = "down") %>%  # Carry the first timestamp through the group
  mutate(shifted = (timestamp.y - first)/ 60) %>% 
  select(-row_num)  # Clean up the helper column

Quick Notes #

• min() is usually the better choice for time offsets since it uses the actual earliest timestamp, regardless of row order in the database.
• If you stick with first(), add an arrange(timestamp.y) step before grouping to ensure rows are ordered correctly in the database (some databases require explicit ordering for window functions like first()).

内容的提问来源于stack exchange，提问作者Karl Uibo