BigQuery中ARRAY_AGG聚合时嵌套分组去重的实现方法咨询

现有如下结构的业务数据表：

需求为基于id、valuation两个字段分组，将其余字段聚合为数组，当前已编写的查询语句如下：

WITH sample as (
SELECT 1023 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'chop' as type,
'c1023' as id_cus, 'julian' as name, '12345' as phone, 'julian@gmail.com' as    mail
UNION ALL
SELECT 1023 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'ext' as type,
'c1023' as id_cus, 'julian' as name, '12345' as phone, 'julian@gmail.com' as    mail
UNION ALL
SELECT 1021 as id,10 as valuation,'inegi' as    origin, 2021-01-01 as date_lead,'ext' as type,
'in-2020' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as  mail
UNION ALL
SELECT 1021 as id,10 as valuation,'inegi' as    origin, 2021-01-01 as date_lead,'ext' as type,
'in-2020' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as  mail
UNION ALL
SELECT 1021 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'int' as type,
'c1021' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as    mail
UNION ALL
SELECT 1021 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'int' as type,
'c1021' as id_cus, 'lucas' as name, '202342' as phone, 'lucas@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'type' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'chop' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'rad' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'uls' as type,
'c1040' as id_cus, 'julieta' as name, '123123' as phone, 'julieta@gmail.com' as mail
)
SELECT id,valuation,ARRAY_AGG(STRUCT(origin,date_lead,type,id_cus,name,phone,mail)) as lead 
FROM sample
GROUP BY id,valuation

当前查询输出的结果结构如下：

聚合得到的lead数组中，name、phone、mail三个字段存在大量重复值，需要对这三个字段再做一次分组聚合，最终期望实现的输出结构如下：

可通过两次分层聚合实现需求，先按id、valuation加三个用户维度字段分组聚合业务属性，再按id、valuation聚合生成最终结构，对应SQL如下：

WITH sample as (
SELECT 1023 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'chop' as type,
'c1023' as id_cus, 'julian' as name, '12345' as phone, 'julian@gmail.com' as    mail
UNION ALL
SELECT 1023 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'ext' as type,
'c1023' as id_cus, 'julian' as name, '12345' as phone, 'julian@gmail.com' as    mail
UNION ALL
SELECT 1021 as id,10 as valuation,'inegi' as    origin, '2021-01-01' as date_lead,'ext' as type,
'in-2020' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as  mail
UNION ALL
SELECT 1021 as id,10 as valuation,'inegi' as    origin, '2021-01-01' as date_lead,'ext' as type,
'in-2020' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as  mail
UNION ALL
SELECT 1021 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'int' as type,
'c1021' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as    mail
UNION ALL
SELECT 1021 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'int' as type,
'c1021' as id_cus, 'lucas' as name, '202342' as phone, 'lucas@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'type' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'chop' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'rad' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, '2021-01-01' as date_lead,'uls' as type,
'c1040' as id_cus, 'julieta' as name, '123123' as phone, 'julieta@gmail.com' as mail
),
-- 第一次分组：按id、valuation + 用户维度字段聚合业务属性
first_agg AS (
    SELECT 
        id,
        valuation,
        name,
        phone,
        mail,
        ARRAY_AGG(STRUCT(origin, date_lead, type, id_cus)) AS business_attrs
    FROM sample
    GROUP BY id, valuation, name, phone, mail
)
-- 第二次分组：按id、valuation聚合生成最终结构
SELECT 
    id,
    valuation,
    ARRAY_AGG(STRUCT(name, phone, mail, business_attrs)) AS lead
FROM first_agg
GROUP BY id, valuation

注：原示例SQL中日期字段未加引号会被识别为数值计算，已修正为字符串格式。

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