如何在三列表格中筛选符合Type-Plan No全匹配规则的ID

针对你提到的大数据量场景，常规关联查询效率太低，这里提供两种高效的分组聚合方案，核心思路是验证每个ID下的所有Type都关联了完全相同的Plan No集合，且无缺失：

方案1：SQL（适合分布式数据库/大数据平台，如BigQuery、Spark SQL） #

这种方案可以直接在大数据平台上运行，处理百万级以上数据毫无压力：

WITH id_type_plans AS (
  -- 生成每个ID+Type对应的去重排序后的Plan No集合
  SELECT
    ID,
    Type,
    ARRAY_AGG(DISTINCT `Plan No` ORDER BY `Plan No`) AS type_plans
  FROM your_table
  GROUP BY ID, Type
),
id_total_plans AS (
  -- 生成每个ID对应的所有去重排序后的Plan No总集合
  SELECT
    ID,
    ARRAY_AGG(DISTINCT `Plan No` ORDER BY `Plan No`) AS total_plans
  FROM your_table
  GROUP BY ID
),
id_valid_check AS (
  -- 校验每个ID下所有Type的Plan No集合是否都等于ID的总Plan No集合
  SELECT
    itp.ID,
    LOGICAL_AND(ARRAY_TO_STRING(itp.type_plans, ',') = ARRAY_TO_STRING(itp_total.total_plans, ',')) AS is_valid
  FROM id_type_plans itp
  JOIN id_total_plans itp_total ON itp.ID = itp_total.ID
  GROUP BY itp.ID, itp_total.total_plans
)
-- 输出符合条件的ID及对应的Plan No列表
SELECT
  vc.ID,
  ARRAY_TO_STRING(itp_total.total_plans, ', ') AS plan_list
FROM id_valid_check vc
JOIN id_total_plans itp_total ON vc.ID = itp_total.ID
WHERE vc.is_valid = TRUE
ORDER BY vc.ID;

代码说明： #

• id_type_plans：给每个ID下的每个Type生成唯一且排序的Plan No集合，排序是为了保证集合一致性（比如[200,300]和[300,200]会被判定为相同）
• id_total_plans：统计每个ID下所有出现过的Plan No总集合
• id_valid_check：用LOGICAL_AND确保当前ID下的所有Type的Plan No集合都等于总集合
• 最后关联总集合表，格式化输出结果

注意：不同数据库的集合函数略有差异，比如MySQL用GROUP_CONCAT(DISTINCT Plan NoORDER BYPlan No)替代ARRAY_AGG，PostgreSQL直接用ARRAY_AGG即可，按需调整。

方案2：Python pandas（适合本地/中小规模数据） #

如果数据可以导入本地处理，用pandas的分组聚合也能高效完成：

import pandas as pd

# 读取数据（示例为CSV，可替换为数据库读取逻辑）
df = pd.read_csv('your_source_data.csv')

# 步骤1：获取每个ID的总Plan No集合（排序保证一致性）
id_total_plans = df.groupby('ID')['Plan No'].apply(lambda x: sorted(x.unique())).reset_index(name='total_plans')

# 步骤2：获取每个ID+Type的Plan No集合
id_type_plans = df.groupby(['ID', 'Type'])['Plan No'].apply(lambda x: sorted(x.unique())).reset_index(name='type_plans')

# 步骤3：校验每个Type的集合是否等于对应ID的总集合
merged = id_type_plans.merge(id_total_plans, on='ID')
merged['is_match'] = merged.apply(lambda row: row['type_plans'] == row['total_plans'], axis=1)

# 步骤4：筛选出所有Type都匹配的ID
id_valid = merged.groupby('ID')['is_match'].all().reset_index(name='is_valid')

# 步骤5：格式化输出结果
result = id_valid[id_valid['is_valid']].merge(id_total_plans, on='ID')
result['plan_list'] = result['total_plans'].apply(lambda x: ', '.join(map(str, x)))

# 打印结果
print(result[['ID', 'plan_list']].to_string(index=False))

代码说明： #

• 核心逻辑和SQL一致，先拆分每个维度的集合，再校验一致性
• 用sorted()确保集合的顺序统一，避免因顺序不同导致的误判
• 如果数据量超大（超过内存），可以替换为Dask库，语法和pandas几乎一致，支持分布式处理

测试你的示例数据 #

用你的示例数据运行后，会得到如下结果：

ID    plan_list
183217760  200, 300, 400
183218746      200, 300
183218747      200, 300
183219126      200, 300
183220269          200
183220271          200

（注：你的示例中183220269和183220271的所有Type都只关联200，符合条件）

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