1. 分组聚合
ARRAY_AGG(参数1:字段名称) : 聚合同一个字段的多个值为一个数组对象
select product_name_in_sci_pdp,count(1),ARRAY_AGG(npi_product_name) from dim_product_mapping GROUP BY product_name_in_sci_pdp
2 数组拆分
2.1 数组拆分,还原为多行
select 'A' as fieldName1,'B' as fieldName2, "unnest"(ARRAY['CN','AR','BR','IN']) AS plant
2.2 数组拆分,重组为一个字符串
array_to_string(参数1:数组,参数2:拼接字符) : 将数组中的值拼接成新的字符串
select product_name_in_sci_pdp,count(1),array_to_string(ARRAY_AGG(npi_product_name),',') from dim_product_mapping GROUP BY product_name_in_sci_pdp
2.3 字符串拆分为数组
string_to_array(参数1:原始字符串,参数2:拆分字符) : 将原始字符串按拆分字符串拆分为一个数组
with t_level_1 as(
SELECT '123,456,789'::VARCHAR as include_plants
)
SELECT string_to_array(include_plants, ',') as plants from t_level_1
3. 数组去重
示例
select product_name_in_sci_pdp,count(1),ARRAY_AGG(DISTINCT npi_product_name) from dim_product_mapping GROUP BY product_name_in_sci_pdp
4. 数组取第一个元素
需要去一下重
SELECT demand_region,(ARRAY_AGG(demand_factory))[1] from dim_factory_sourcing_dependency where demand_process='Ship FG' GROUP BY demand_region ORDER BY demand_region
5.单个元素关联数组
使用 any() 方法实现
OR group_id = any(string_to_array(mgmt_team_group, ','))
5.判断数组包含元素
OR 'xxx' = any(mgmt_team_group)