BigQuery：按用户计算跨行数组元素的最大交集

需求回顾 #

给定包含row_id、user、data的表格数据，需按用户分组，找出至少在两行数据中存在的元素个数最多的交集数组：

• 仅考虑两两行之间的交集，不认可多行分散元素构成的集合
• 若存在多个元素数量相同的最大交集，优先选择对应row_id更小的组合所生成的交集

实现思路 #

1. 按用户分组，保留每个用户的所有行数据（包含row_id和data数组）
2. 对每个用户的行数据，生成所有两两行的唯一组合（避免重复计算同一对行）
3. 计算每对行数据的交集，记录交集的元素数量、交集数组，以及该组合中最小的row_id（用于优先级排序）
4. 对每个用户的所有交集结果，按元素数量降序、最小row_id升序排序，取第一个结果作为该用户的max_intersection

代码示例（Python + Pandas） #

假设原始数据已加载为Pandas DataFrame：

import pandas as pd
from itertools import combinations
from ast import literal_eval

# 模拟加载原始数据
data = {
    'row_id': [1,2,3,4,5,6],
    'user': ['Mark','Mark','Mark','Mark','Ben','Ben'],
    'data': ['[1,2,3]','[1,2,4]','[1]','[3,4]','[1,2,3]','[2]']
}
df = pd.DataFrame(data)

# 将字符串格式的数组转为实际列表
df['data'] = df['data'].apply(literal_eval)

# 处理单个用户数据的函数
def get_max_intersection(user_rows):
    intersections = []
    # 生成所有两两行的组合
    for (idx1, row1), (idx2, row2) in combinations(user_rows.iterrows(), 2):
        set1 = set(row1['data'])
        set2 = set(row2['data'])
        # 计算交集并转为有序列表
        intersect = sorted(set1 & set2)
        min_row_id = min(row1['row_id'], row2['row_id'])
        intersections.append({
            'intersect': intersect,
            'length': len(intersect),
            'min_row_id': min_row_id
        })
    if not intersections:
        return []
    # 按规则排序：元素数量降序，最小row_id升序
    intersections.sort(key=lambda x: (-x['length'], x['min_row_id']))
    return intersections[0]['intersect']

# 按用户分组处理并生成结果
result = df.groupby('user').apply(get_max_intersection).reset_index(name='max_intersection')

print(result)

输出结果 #

user max_intersection
0    Ben              [2]
1   Mark           [1, 2]

关键细节说明 #

• 用combinations生成两两行组合，避免重复计算同一对行的交集
• 将交集转为有序列表，保证输出格式与示例一致
• 排序规则严格遵循需求：先以交集元素数量为第一优先级，数量相同时以对应组合的最小row_id为第二优先级

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