如何使BigQuery在具有IN而不是IN的重复字符串字段上进行筛选？
如何使BigQuery在具有IN而不是IN的重复字符串字段上进行筛选？
提问于 2021-04-20 09:58:41
我正在尝试使用重复字符串字段筛选BigQuery表中的行。我希望基于至少有一个来自一个预定义列表的值的行进行筛选，但不包含来自第二个预定义列表的任何值。
这是我的疑问：
SELECT em.asin, em.category
FROM
`my_proj.amazon_data.electronics_meta` as em,
UNNEST(em.category) as sub_cat
WHERE sub_cat IN ('Television & Video', 'Televisions', 'Television & Video')
AND sub_cat NOT IN (
'DVD Players & Recorders',
'Projection Screens',
'VCRs',
'Blu-ray Players',
'Blu-ray Players & Recorders'
)这就是我得到的结果：
我希望第一行不会出现，因为它包含category字段中的“录像机”。
我也试过
AND LOWER(TRIM(sub_cat)) NOT IN (
'dvd players & recorders',
'projection screens',
'vcrs',
'blu-ray players',
'blu-ray players & recorders'
)和
AND NOT (sub_cat IN (
'DVD Players & Recorders',
'Projection Screens',
'VCRs',
'VCRs',
'Blu-ray Players',
'Blu-ray Players & Recorders'
))但这并没有改变结果。
附加信息：--这是表的架构：
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-04-20 10:06:08
使用存在性
SELECT em.asin, em.category
FROM
`my_proj.amazon_data.electronics_meta` as em
WHERE
exists ( select *
from unnest(category) as sub_cat
where sub_cat IN ('Television & Video', 'Televisions', 'Television & Video')
)
and not exists ( select *
from unnest(category) as sub_cat
where sub_cat IN (
'DVD Players & Recorders',
'Projection Screens',
'VCRs',
'Blu-ray Players',
'Blu-ray Players & Recorders'
)
)Stack Overflow用户
发布于 2021-04-20 11:00:00
您还可以在FROM子句中使用聚合来完成此操作：
SELECT em.asin, em.category
FROM `my_proj.amazon_data.electronics_meta` em CROSS JOIN
(SELECT COUNTIF(sub_cat IN ('Television & Video', 'Televisions', 'Television & Video')) as cnt_required,
COUNTIF( 'DVD Players & Recorders', 'Projection Screens', 'VCRs', 'Blu-ray Players', 'Blu-ray Players & Recorders'
) as cnt_not_allowed
FROM unnest(category) cat
) c
WHERE c.cnt_required > 0 AND c.cnt_not_allowed = 0;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67176650
复制相关文章
相似问题
腾讯云开发者
