Google BigQuery标准SQL：统计仅搜索单个纽约机场的访客数量

我来帮你搞定这个BigQuery SQL查询的需求！根据你的描述，我们需要找出那些只搜索过JFK、LGA、EWR、MSY这四个纽约机场中的某一个，并且完全没搜索过其他三个纽约机场的访客总数。

核心思路 #

要实现这个统计，我们需要先对每个访客的纽约机场搜索记录做聚合，然后筛选出仅搜索过单个纽约机场的访客，最后统计他们的数量。

完整SQL查询 #

WITH visitor_ny_airports AS (
  SELECT
    visitor_id,
    -- 收集该访客搜索过的所有纽约指定机场的去重列表
    ARRAY_AGG(DISTINCT searched_to) AS ny_airports_searched
  FROM
    `your-project.your-dataset.your-table` -- 替换成你的实际表路径
  WHERE
    searched_to IN ('JFK', 'LGA', 'EWR', 'MSY')
  GROUP BY
    visitor_id
),
filtered_visitors AS (
  SELECT
    visitor_id
  FROM
    visitor_ny_airports
  WHERE
    ARRAY_LENGTH(ny_airports_searched) = 1 -- 仅搜索了一个纽约机场
)
SELECT
  COUNT(DISTINCT visitor_id) AS single_ny_airport_visitor_count
FROM
  filtered_visitors;

代码解释 #

1. CTE visitor_ny_airports：

   • 按visitor_id分组，收集每个访客搜索过的所有纽约指定机场的去重数组。这里只筛选了目标为纽约机场的记录，因为我们只关心访客对这些机场的搜索行为。

2. CTE filtered_visitors：

   • 筛选出那些搜索过的纽约机场数组长度为1的访客——这意味着他们只搜索过四个纽约机场中的一个，完全没碰过其他三个。

3. 最终统计：

   • 用COUNT(DISTINCT)确保每个访客只被计数一次，得到符合条件的访客总数。

扩展：按单个机场统计访客数 #

如果你想知道每个纽约机场分别有多少仅搜索它的访客，可以用下面的查询：

WITH visitor_ny_airports AS (
  SELECT
    visitor_id,
    ARRAY_AGG(DISTINCT searched_to) AS ny_airports_searched
  FROM
    `your-project.your-dataset.your-table`
  WHERE
    searched_to IN ('JFK', 'LGA', 'EWR', 'MSY')
  GROUP BY
    visitor_id
)
SELECT
  ny_airport,
  COUNT(DISTINCT visitor_id) AS visitor_count
FROM (
  SELECT
    visitor_id,
    ny_airports_searched[OFFSET(0)] AS ny_airport
  FROM
    visitor_ny_airports
  WHERE
    ARRAY_LENGTH(ny_airports_searched) = 1
)
GROUP BY
  ny_airport
ORDER BY
  visitor_count DESC;

注意事项 #

• 记得把your-project.your-dataset.your-table替换成你实际的BigQuery项目、数据集和表名。
• 如果searched_to字段存在大小写不一致的情况（比如有的是jfk有的是JFK），可以用LOWER(searched_to) IN ('jfk', 'lga', 'ewr', 'msy')来统一处理。

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