如何在BigQuery中去除多行重复的数组数据

你的核心需求是识别并去除同一Business下重复的数组块（比如前两个[a,b]组），保留唯一的数组组。可以通过以下SQL逻辑实现：

核心思路 #

1. 给每个连续的数组组分配唯一ID：利用窗口函数，遇到非空的Business时标记新组，让同组的所有行（包括后续空Business的行）共享同一个组ID。
2. 聚合每组的完整数组：将每个组的MyArray值合并成完整的数组/字符串，用于判断重复。
3. 筛选唯一数组组：对重复的数组，只保留第一次出现的组。
4. 展开唯一组的原始行：从原始数据中提取属于唯一组的行，得到最终结果。

PostgreSQL 示例代码 #

WITH grouped_data AS (
    SELECT
        Business,
        MyArray,
        -- 生成组ID：非空Business触发新组，累加计数
        SUM(CASE WHEN Business IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY (SELECT NULL)) AS group_id
    FROM your_table
),
array_groups AS (
    SELECT
        group_id,
        -- 聚合每组的MyArray为完整数组
        ARRAY_AGG(MyArray ORDER BY (SELECT NULL)) AS full_array
    FROM grouped_data
    GROUP BY group_id
),
unique_groups AS (
    SELECT
        group_id
    FROM (
        -- 对重复数组只保留第一次出现的组
        SELECT
            group_id,
            full_array,
            ROW_NUMBER() OVER (PARTITION BY full_array ORDER BY group_id) AS rn
        FROM array_groups
    ) t
    WHERE rn = 1
)
-- 提取唯一组的原始行
SELECT
    Business,
    MyArray
FROM grouped_data
WHERE group_id IN (SELECT group_id FROM unique_groups)
ORDER BY group_id;

SQL Server 示例代码（适配字符串聚合） #

如果使用SQL Server，可将数组聚合替换为字符串聚合（注意选择无冲突的分隔符）：

WITH grouped_data AS (
    SELECT
        Business,
        MyArray,
        SUM(CASE WHEN Business IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY (SELECT NULL)) AS group_id
    FROM your_table
),
array_groups AS (
    SELECT
        group_id,
        -- 用|作为分隔符，避免与MyArray内容冲突
        STRING_AGG(MyArray, '|') WITHIN GROUP (ORDER BY (SELECT NULL)) AS full_array_str
    FROM grouped_data
    GROUP BY group_id
),
unique_groups AS (
    SELECT
        group_id
    FROM (
        SELECT
            group_id,
            full_array_str,
            ROW_NUMBER() OVER (PARTITION BY full_array_str ORDER BY group_id) AS rn
        FROM array_groups
    ) t
    WHERE rn = 1
)
SELECT
    Business,
    MyArray
FROM grouped_data
WHERE group_id IN (SELECT group_id FROM unique_groups)
ORDER BY group_id;

注意事项 #

• ORDER BY (SELECT NULL)是假设原始数据的行顺序符合你的分组逻辑，如果表中有明确的排序字段（如主键、时间戳），请替换为该字段，确保分组顺序准确。
• 若MyArray包含分隔符（如SQL Server示例中的|），需更换为不会出现在数据中的特殊字符，避免聚合结果错误。

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