starrocks 3.3.2版本
在使用row_number去重之后发现结果和预想中的不同
测试：
1、将 右边结果落表 join 可以得到预想结果
2、使用and trim(a.only_id) = trim(c.only_id) 作为关联条件也能得到预想结果
3、使用 group by 代替 row_number进行去重也能得到预想结果
猜测：
1、row_number 往数据中加非打印字符
sql 如下
select a.标准名称,
ARRAY_JOIN(ARRAY_DISTINCT(ARRAY_REMOVE(split(group_concat( c.成分_标签,’,’),’,’),’’)),’,’) as 成分,
ARRAY_JOIN(ARRAY_DISTINCT(ARRAY_REMOVE(split(group_concat( c.症状_标签,’,’),’,’),’’)),’,’) as 症状,
ARRAY_JOIN(ARRAY_DISTINCT(ARRAY_REMOVE(split(group_concat( c.功能功效_标签,’,’),’,’),’’)),’,’) as 功能功效,
ARRAY_JOIN(ARRAY_DISTINCT(ARRAY_REMOVE(split(group_concat( c.产品类型_标签,’,’),’,’),’’)),’,’) as 产品类型
from (
select ‘ac_product_lib_data’ as table_type, concat(task_id,’,’,平台名称,’,’,商品ID) as only_id,
upper(标准品牌名) as 标准名称, 商品名称规范化 as 商品名称,商品关联名
from social.ac_product_lib_data
where modified = 1 and 是否删除 = 0 and 是否存疑 = 0 and 图片信息缺失 = 0 and 商品名称规范化 != ‘’
union
select ‘ac_product_lib_urgent_data’ as table_type, concat(task_id,’,’,hash_id) as only_id,
upper(标准品牌名) as 标准名称, 产品 as 商品名称,商品关联名
from social.ac_product_lib_urgent_data
where 是否删除 = 0 and modified = 1 and 产品 != ‘’
union
select ‘ac_product_lib_manual_data’ as table_type, id as only_id,
upper(标准品牌名) as 标准名称, 商品名称规范化 as 商品名称,商品关联名
from social.ac_product_lib_manual_data
where 商品名称规范化 !=’’
) a
left join (
select *
from (
select *,row_number() over (partition by table_type,only_id) as rn
from (
select table_type,only_id,成分_标签,症状_标签,功能功效_标签,产品类型_标签 from social.sa_brand_trade_tag_data_anno
)a
)a
where rn = 1
) c
on a.table_type = c.table_type
and a.only_id = c.only_id
– and replace(a.only_id,’’,’’) = replace(c.only_id,’’,’’)
– and trim(a.only_id) = trim(c.only_id)
where 标准名称 like ‘%INNE%’
group by 1