如何在R语言中实现筛选40岁以上英国作者的SQL查询功能？

Got it, let's turn that SQL logic into R code that does exactly what you need—filtering authors over 40 years old who are from England (or any country starting with "England"). I'll cover two common approaches so you can pick what fits your workflow.

Base R Approach #

If you prefer sticking to base R without extra packages (though lubridate makes date handling cleaner), here's how to do it:

First, ensure your dateofbirth column is formatted as a Date type. If it's not, convert it first:

# Convert to Date type (adjust the format to match your data's structure)
autors$dateofbirth <- as.Date(autors$dateofbirth, format = "%Y-%m-%d")

Then apply the filters to get your desired subset:

# Filter for authors over 40 and from England (or England-*)
filtered_authors <- autors[
  # Calculate the cutoff date (40 years ago) and compare to date of birth
  (Sys.Date() - seq(Sys.Date(), by = "-40 years", length.out = 1)) > autors$dateofbirth &
  # Match countries starting with "England" (equivalent to SQL's LIKE 'England%')
  grepl("^England", autors$country),
  # Keep all columns (matches SELECT * in SQL)
  ]

Tidyverse (dplyr + lubridate) Approach #

This is more readable and aligns with modern R workflows. You'll need the dplyr and lubridate packages (plus stringr, which is included in the tidyverse suite):

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

# Convert dateofbirth to Date type if needed
autors <- autors %>%
  mutate(dateofbirth = as.Date(dateofbirth, format = "%Y-%m-%d"))

# Apply filters with intuitive pipe syntax
filtered_authors <- autors %>%
  filter(
    # Check if date of birth is earlier than 40 years ago
    (Sys.Date() - years(40)) > dateofbirth,
    # Match countries starting with "England" (same as LIKE 'England%')
    str_starts(country, "England")
  )

Key Notes: #

• The ^England in grepl() and str_starts() ensures we only match countries that start with "England"—exactly like your SQL's LIKE 'England%'.
• Sys.Date() pulls today's date, and subtracting 40 years gives the cutoff: anyone born before this date is over 40 years old.
• Both methods return all columns from the original data frame, just like SELECT * in SQL.

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