如何按id聚合数组行并获取colour列的去重数组值？

以下是针对不同工具场景的具体实现方法，覆盖小数据集、大数据集及数据库场景：

Python Pandas 实现 #

适合处理中小规模数据集，通过扁平化数组、分组去重再重组完成需求：

代码示例 #

import pandas as pd
from collections import OrderedDict

# 构造示例数据
data = {
    'id': [1, 2, 1, 2],
    'colour': [['Red', 'Blue', 'Yellow'], ['Green'], ['White', 'Blue'], ['Green', 'Black']]
}
df = pd.DataFrame(data)

# 方式1：去重但不保留元素顺序（基于set实现）
result = df.groupby('id')['colour'].agg(
    lambda x: list(set([item for sublist in x for item in sublist]))
).reset_index()

# 方式2：去重且保留元素首次出现的顺序
result_ordered = df.groupby('id')['colour'].agg(
    lambda x: list(OrderedDict.fromkeys([item for sublist in x for item in sublist]))
).reset_index()

print(result_ordered)

输出结果：

id                     colour
0   1  [Red, Blue, Yellow, White]
1   2           [Green, Black]

Spark SQL/DataFrame 实现 #

针对大数据场景，Spark的分布式处理能力更高效：

SQL 语句方式 #

SELECT 
    id,
    ARRAY(collect_set(flattened_colour)) AS colour
FROM (
    SELECT 
        id,
        explode(flatten(colour)) AS flattened_colour
    FROM your_table
) t
GROUP BY id;

DataFrame API 方式 #

from pyspark.sql import SparkSession
from pyspark.sql.functions import flatten, explode, collect_set, array

spark = SparkSession.builder.appName("array_aggregate").getOrCreate()

# 构造示例数据
data = [
    (1, [["Red", "Blue", "Yellow"]]),
    (2, [["Green"]]),
    (1, [["White", "Blue"]]),
    (2, [["Green", "Black"]])
]
df = spark.createDataFrame(data, ["id", "colour"])

# 处理流程：扁平化嵌套数组 → 展开元素 → 分组去重聚合 → 还原嵌套格式
result_df = df.withColumn("flattened", flatten(col("colour"))) \
              .select("id", explode(col("flattened")).alias("colour_item")) \
              .groupBy("id") \
              .agg(collect_set("colour_item").alias("colour_list")) \
              .withColumn("colour", array(col("colour_list")))

result_df.show(truncate=False)

输出结果：

+---+---------------------------+
|id |colour                     |
+---+---------------------------+
|1  |[[Red, Blue, Yellow, White]]|
|2  |[[Green, Black]]           |
+---+---------------------------+

PostgreSQL SQL 实现 #

如果数据存储在PostgreSQL中，可直接用SQL语句处理：

SELECT 
    id,
    ARRAY[ARRAY_AGG(DISTINCT colour_item ORDER BY colour_item)] AS colour
FROM (
    SELECT 
        id,
        unnest(unnest(colour)) AS colour_item
    FROM your_table
) t
GROUP BY id;

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