MySQL与R中筛选重复值并添加状态列的技术问询

Got it, let's tackle your problem step by step. First, we need to filter rows where either V1, V2, or both have duplicate values, then add a status column to mark the duplication type. Here's how to do it in MySQL and R separately:

1. 筛选存在重复值的行 #

We can use window functions to calculate the occurrence count of each value in V1 and V2, then filter rows where at least one count exceeds 1:

SELECT *
FROM (
    SELECT 
        *,
        COUNT(V1) OVER(PARTITION BY V1) AS v1_count,
        COUNT(V2) OVER(PARTITION BY V2) AS v2_count
    FROM your_table -- 替换成你的表名
) AS sub_query
WHERE v1_count > 1 OR v2_count > 1;

The subquery adds two helper columns (v1_count and v2_count) to track how many times each V1/V2 value appears. The outer query filters for rows where either column has duplicates.

2. 添加重复状态列 #

Extend the above query with a CASE WHEN statement to label the duplication type:

SELECT 
    *,
    CASE
        WHEN v1_count > 1 AND v2_count > 1 THEN 'Both'
        WHEN v1_count > 1 THEN 'V1'
        WHEN v2_count > 1 THEN 'V2'
    END AS duplicate_status
FROM (
    SELECT 
        *,
        COUNT(V1) OVER(PARTITION BY V1) AS v1_count,
        COUNT(V2) OVER(PARTITION BY V2) AS v2_count
    FROM your_table -- 替换成你的表名
) AS sub_query
WHERE v1_count > 1 OR v2_count > 1;

Note: We check for the Both case first to avoid overwriting labels with single-column duplicates.

We'll use the dplyr package for clean, readable code (install it first with install.packages("dplyr") if you haven't). Assume your dataset is stored in a data frame named df.

1. 筛选存在重复值的行 #

Use add_count() to quickly calculate occurrence counts for V1 and V2, then filter:

library(dplyr)

# 筛选出V1或V2有重复的行
df_filtered <- df %>%
    add_count(V1, name = "v1_count") %>%
    add_count(V2, name = "v2_count") %>%
    filter(v1_count > 1 | v2_count > 1)

add_count() automatically adds a count column for each grouped value without needing manual group_by()/ungroup() steps.

2. 添加重复状态列 #

Add a status column using case_when() to map count conditions to labels:

# 添加重复状态列
df_final <- df_filtered %>%
    mutate(duplicate_status = case_when(
        v1_count > 1 & v2_count > 1 ~ "Both",
        v1_count > 1 ~ "V1",
        v2_count > 1 ~ "V2"
    ))

# 可选：移除辅助计数列
df_final <- df_final %>% select(-v1_count, -v2_count)

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