能否将SQL代码转为R代码？求R实现数据Delta计算方案

Hey there! This is absolutely solvable in R, and the dplyr package (from the tidyverse suite) makes this task really intuitive. Let's walk through this step by step to match your exact requirements.

First, let's start with your sample data (I'll use the dput code you provided):

# Load required packages
library(dplyr)
library(lubridate)

# Your sample data
df <- structure(list(a = c(10L, 11L, 10L, 10L, 11L, 10L), 
                     b = c(2L, 5L, 3L, 2L, 4L, 2L), 
                     c = c("31.01.2020", "31.01.2020", "31.01.2020", "31.12.2019", "31.12.2019", "31.12.2019")), 
                row.names = c(NA, -6L), class = "data.frame")

Step 1: Clean Dates & Summarize Values by Group #

First, we need to convert your date column (c) from a character string to a proper date format—this ensures we can correctly sort dates later. Then we'll group by a (your ID) and c (date), and sum up the b values:

summary_df <- df %>%
  # Convert character date to actual date format (day.month.year)
  mutate(c = dmy(c)) %>%
  # Group by a and date
  group_by(a, c) %>%
  # Sum the b values for each group
  summarize(total_b = sum(b), .groups = "drop")

Running this will give you the summarized table you mentioned:

Step 2: Calculate Delta (Latest Date - Earliest Date) #

Now we need to compute the difference between the most recent total_b and the oldest total_b for each a value. We'll group by a, sort the dates, then subtract the first (oldest) value from the last (newest) value:

delta_df <- summary_df %>%
  group_by(a) %>%
  # Sort dates in ascending order (oldest to newest)
  arrange(c) %>%
  # Calculate delta: latest total_b minus earliest total_b
  summarize(delta = last(total_b) - first(total_b), .groups = "drop")

This will give you your desired result:

Bonus: One-Liner Version #

If you want to combine both steps into a single pipeline (to keep things concise), you can do this:

delta_df <- df %>%
  mutate(c = dmy(c)) %>%
  group_by(a, c) %>%
  summarize(total_b = sum(b), .groups = "drop") %>%
  group_by(a) %>%
  arrange(c) %>%
  summarize(delta = last(total_b) - first(total_b), .groups = "drop")

All of this should work perfectly with your data, and it's easy to adapt if you have more dates or additional groups later!

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