google-bigquery - 大查询，如果重复记录(展平)

Question

这是关于以下问题的解决方案 BigQuery SQL IF over repeated record :我已经尝试创建一个测试表并尝试了给定的查询，但它实际上并没有选择住在纽约和芝加哥的人。测试数据如下:

{"fullname": "John Smith", "citiesLived": [{"place": "newyork"}, {"place": "chicago"}, {"place": "seattle"}]}
{"fullname": "Adam Smith", "citiesLived": [{"place": "newyork"}, {"place": "chicago"}, {"place": "phil"}]}
{"fullname": "Adam Jefferson", "citiesLived": [{"place": "boston"}, {"place": "chicago"}, {"place": "seattle"}]}

查询如下:

SELECT
  *
FROM (
  SELECT
    fullname,
    IF (citiesLived.place == 'newyork', 1, 0) AS ny,
    IF (citiesLived.place == 'chicago', 1, 0) AS chi
  FROM (FLATTEN(tester.citiesLived, citiesLived))
  OMIT
    RECORD IF citiesLived.place = 'seattle')
WHERE
  ny == 1
  AND chi == 1

Answer 1

您不需要执行 FLATTEN(通常在 BigQuery 查询中很少需要 FLATTEN)，只需 OMIT IF 就足够了:

SELECT fullname FROM tester.citiesLived
OMIT RECORD IF NOT (
  SOME(citiesLived.place = "newyork") AND
  SOME(citiesLived.place = "chicago"))

OMIT IF 的条件表明，如果居住的城市中有一些是纽约，而一些是芝加哥——那么它符合您的条件。但两者都不正确的记录 - 应该被省略(因此 NOT 谓词)。