如何按1:1匹配规则过滤表格中对应type的冗余记录？

规则说明 #

核心逻辑：对于每个item，若存在type=101的记录，需从同item的type=111记录中移除等量条目，同时完全排除所有type=101的记录，仅保留剩余的type=111记录。

原始数据 #

item|qty|batch|type
item1|2|ten|111
item1|2|ten|111
item1|2|ten|111
item1|2|ten|101
item1|4|ten|111
item2|2|five|111
item2|2|five|101
item2|2|five|111

期望输出 #

item|qty|batch|type
item1|2|ten|111
item1|2|ten|111
item1|4|ten|111
item2|2|five|111

实现方案 #

方案1：使用Python Pandas #

通过分组统计和窗口编号实现过滤，逻辑清晰易扩展：

import pandas as pd

# 读取原始数据（可替换为直接构造DataFrame或读取文件）
df = pd.read_csv("data.csv", sep="|")

# 统计每个item的type=101记录数量
type101_count = df[df["type"] == 101].groupby("item").size().reset_index(name="count_101")

# 给每个item的type=111记录按顺序编号
df["rank"] = df[df["type"] == 111].groupby("item").cumcount() + 1

# 合并统计结果并过滤
result = df.merge(type101_count, on="item", how="left")
result["count_101"] = result["count_101"].fillna(0)
final_result = result[(result["type"] == 111) & (result["rank"] > result["count_101"])].drop(columns=["rank", "count_101"])

# 按原始格式输出
print(final_result.to_csv(sep="|", index=False))

方案2：使用SQL（以PostgreSQL为例） #

通过窗口函数和子查询在数据库层面完成过滤：

WITH type101_stats AS (
    -- 统计每个item的type=101记录数
    SELECT item, COUNT(*) AS count_101
    FROM your_table
    WHERE type = 101
    GROUP BY item
),
ranked_111 AS (
    -- 给每个item的type=111记录按顺序编号
    SELECT 
        t.*,
        ROW_NUMBER() OVER (PARTITION BY t.item ORDER BY t.batch, t.qty) AS rn
    FROM your_table t
    WHERE t.type = 111
)
-- 筛选出编号大于type101数量的111记录
SELECT r.item, r.qty, r.batch, r.type
FROM ranked_111 r
LEFT JOIN type101_stats s ON r.item = s.item
WHERE r.rn > COALESCE(s.count_101, 0)
ORDER BY r.item, r.rn;

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