如何用Base R实现指定SQL分组查询（无需第三方包）

Got it, let's replicate that SQL logic using only base R—no extra packages required. Here's a step-by-step solution that matches the behavior of your query (counting all flights per day with count(*) and calculating average departure delay, then filtering for days with over 1000 flights):

Step 1: Load the Dataset #

First, make sure you have the nycflights13 package installed and loaded to access the flights data:

# Install if you haven't already
install.packages("nycflights13")

# Load the package and data
library(nycflights13)
data(flights)

Step 2: Aggregate by Month & Day #

We'll use aggregate() to compute both the flight count and average delay in one pass. To mimic count(*) (which counts all rows, including those with missing dep_delay values), we'll add a dummy column of 1s and sum it for each group:

# Add a dummy column to count total flights per group
flights$dummy <- 1

# Perform the aggregation
flight_summary <- aggregate(
  cbind(count = dummy, avg_delay = dep_delay) ~ month + day,
  data = flights,
  FUN = function(x) {
    # Sum the dummy column for count, calculate mean delay for dep_delay
    if (deparse(substitute(x)) == "dummy") {
      sum(x)
    } else {
      mean(x, na.rm = TRUE) # Ignore NA values in delay
    }
  }
)

# Clean up the dummy column (optional)
flights$dummy <- NULL

Step 3: Filter Busy Days #

Use subset() to keep only rows where the flight count exceeds 1000:

busy_days <- subset(flight_summary, count > 1000)

Alternative Approach (Split + Lapply) #

If you prefer a more explicit grouping approach, you can split the data by month and day, then compute metrics for each group:

# Split flights into groups by month and day
flight_groups <- split(flights, list(flights$month, flights$day), drop = TRUE)

# Calculate metrics for each group
flight_summary_list <- lapply(flight_groups, function(group) {
  data.frame(
    month = unique(group$month),
    day = unique(group$day),
    count = nrow(group), # Total flights in the group (matches count(*))
    avg_delay = mean(group$dep_delay, na.rm = TRUE)
  )
})

# Combine list into a data frame
flight_summary <- do.call(rbind, flight_summary_list)

# Filter busy days
busy_days <- subset(flight_summary, count > 1000)

Verify Against dplyr #

Just to confirm this matches your dplyr workflow, here's a quick comparison:

# Your dplyr code (for reference)
library(dplyr)
dplyr_result <- flights %>%
  group_by(month, day) %>%
  summarize(
    count = n(),
    avg_delay = mean(dep_delay, na.rm = TRUE)
  ) %>%
  filter(count > 1000)

# Check if results are identical (should return TRUE)
all.equal(busy_days, as.data.frame(dplyr_result))

Both base R methods will produce the same output as your dplyr code, no external packages needed.

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