如何编写Athena通用查询，匹配子集与超集表的字符串数组？

问题 #

在Athena中有两张基于S3 Parquet文件的外部表，表1是子集表，表2是超集表，两张表均无重复记录，且都包含字符串数组类型的article_list列。需要找出表2中包含表1每条记录的article_list所有元素的记录，并关联得到指定格式的结果。

表结构示例 #

表1（子集表）

表2（超集表）

期望结果 #

当前已实现针对特定数组组合的查询，但需要适配表1所有数组的通用查询：

SELECT ['ABC', 'PQR'] as article_list, 
    wh_code, 
    restock_date 
FROM "table_2" 
WHERE filter(ARRAY ['ABC', 'PQR'], x -> NOT CONTAINS(article_list, x)) = ARRAY[] 
group by wh_code, restock_date

通用查询方案 #

使用表1和表2的交叉关联，结合filter函数判断表2的article_list是否包含表1每条记录的所有数组元素，最终得到符合要求的结果：

SELECT 
    t1.article_list AS "article_list (table 1)",
    t2.wh_code,
    t2.restock_date
FROM table_1 t1
CROSS JOIN table_2 t2
WHERE filter(t1.article_list, x -> NOT CONTAINS(t2.article_list, x)) = ARRAY[]
ORDER BY t1.article_list, t2.wh_code, t2.restock_date;

逻辑说明 #

• CROSS JOIN：将表1的每条记录与表2的每条记录进行关联，确保能检查表2中所有记录是否匹配表1的每个数组。
• WHERE条件：遍历表1当前记录的article_list中每个元素，筛选出表2当前记录article_list不包含的元素；若结果为空数组，说明表2的这条记录包含表1当前记录的所有数组元素，符合筛选条件。
• 排序：按表1的数组、仓库编码、补货日期排序，让结果更规整。

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