多关联表SQL行删除需求及R语言实现方案咨询

Got it, let's break down how to delete rows from your interconnected tables in R—this is similar to writing a SQL DELETE with joins, but we'll use R's tools (mostly tidyverse/dplyr since it's intuitive for this kind of work) to get it done. First, let's recreate your tables in R so we can work with real examples:

# Recreate your three tables in R
annonymizedData <- data.frame(
  userID = c(12345, 12346, 12567, 12789, 12903),
  AssigID = c(10001, 10001, 10003, 10003, 10004),
  Score = c(4, 5, 9, 8, 7),
  Time_on_Task = c(60, 70, 80, 67, 73)
)

Anonymized_users <- data.frame(
  userID = c(12345, 12346, 12567, 12789, 12903),
  Teacher = c(FALSE, FALSE, FALSE, FALSE, TRUE)
)

Assignments <- data.frame(
  AssigID = c(10001, 10001, 10003, 10003, 10004),
  type = c(1, 1, 2, 2, 3)
)

Key Scenarios & Solutions #

I'll cover common deletion cases based on your table relationships—adjust these to match your exact conditions.

1. Delete rows from annonymizedData where the user is a Teacher #

First, identify which users are teachers from the Anonymized_users table, then remove their records from the main data table:

Using dplyr (tidyverse)

This is the most readable approach, similar to SQL joins:

library(dplyr)

# Option 1: Get teacher IDs first, then filter them out
teacher_user_ids <- Anonymized_users %>% 
  filter(Teacher == TRUE) %>% 
  pull(userID) # Extract just the userID column as a vector

cleaned_data <- annonymizedData %>% 
  filter(!userID %in% teacher_user_ids) # Keep rows NOT in the teacher IDs list

# Option 2: Join tables directly and filter
cleaned_data <- annonymizedData %>%
  left_join(Anonymized_users, by = "userID") %>%
  filter(Teacher == FALSE) %>%
  select(-Teacher) # Remove the extra Teacher column we joined in

Using Base R

If you prefer not to use tidyverse packages:

teacher_user_ids <- Anonymized_users$userID[Anonymized_users$Teacher == TRUE]
cleaned_data <- annonymizedData[!annonymizedData$userID %in% teacher_user_ids, ]

2. Delete rows from annonymizedData for assignments of type 3 #

Similarly, first get the assignment IDs that are type 3, then filter those out:

# Using dplyr
type3_assignment_ids <- Assignments %>% 
  filter(type == 3) %>% 
  pull(AssigID) %>% 
  unique() # Avoid duplicates since Assignments has repeated AssigIDs

cleaned_data <- annonymizedData %>% 
  filter(!AssigID %in% type3_assignment_ids)

3. Delete rows that match multiple linked conditions (e.g., Teacher + Assignment Type 3) #

For more complex rules, use anti_join to remove rows that match a combined condition across all three tables:

# First, find all (userID, AssigID) pairs that meet your deletion criteria
rows_to_remove <- annonymizedData %>%
  left_join(Anonymized_users, by = "userID") %>%
  left_join(Assignments, by = "AssigID") %>%
  filter(Teacher == TRUE & type == 3) %>%
  select(userID, AssigID) # Keep only the unique identifiers for deletion

# Remove those pairs from the original data
cleaned_data <- annonymizedData %>%
  anti_join(rows_to_remove, by = c("userID", "AssigID"))

Important Note #

Unlike SQL's DELETE which modifies the original table directly, R typically creates a new "cleaned" data frame. If you want to overwrite the original table, just assign the result back to the original variable:

# Overwrite the original annonymizedData with cleaned data
annonymizedData <- annonymizedData %>% filter(!userID %in% teacher_user_ids)

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