如何在复杂BigQuery查询中关联用户邮箱与地理位置数据

问题 #

现有一段可在BigQuery中从Google Workspace活动日志提取IP地址并转换为地理位置数据的SQL查询，运行正常。需求是将用户邮箱地址整合到输出结果中，邮箱与IP地址均存储在表workspace-data.Logs.activity中，可分别通过email和ip_address字段访问。

原查询代码：

WITH
  source_of_ip_addresses AS (
  SELECT
    REGEXP_REPLACE(SAFE_CONVERT_BYTES_TO_STRING(ip_address), 'xxx', '0') ip,
    COUNT(1) c
  FROM
    `workspace-data.Logs.activity`
  WHERE
    SAFE_CONVERT_BYTES_TO_STRING(ip_address) IS NOT NULL
  GROUP BY
    1 )
SELECT
  city_name,
  country_name,
  country_iso_code,
  SUM(c) c,
  ST_GEOGPOINT(AVG(longitude), AVG(latitude)) point
FROM (
  SELECT
    ip,
    city_name,
    country_name,
    country_iso_code,
    c,
    latitude,
    longitude,
    geoname_id
  FROM (
    SELECT
      *,
      NET.SAFE_IP_FROM_STRING(ip) & NET.IP_NET_MASK(4,
        mask) network_bin
    FROM
      source_of_ip_addresses,
      UNNEST(GENERATE_ARRAY(9,32)) mask
    WHERE
      BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = 4 )
  JOIN
    `fh-bigquery.geocode.201806_geolite2_city_ipv4_locs`
  USING
    (network_bin,
      mask) )
WHERE
  city_name IS NOT NULL
GROUP BY
  city_name,
  country_name,
  country_iso_code,
  geoname_id
ORDER BY
  c DESC

当前输出：

期望输出：

解决方案 #

核心是在数据分组阶段将email与ip绑定，确保统计维度为「用户-IP」，后续关联地理数据后再按「用户-地理位置」聚合。修改后的完整SQL如下：

WITH
  source_of_ip_addresses AS (
  SELECT
    email,
    REGEXP_REPLACE(SAFE_CONVERT_BYTES_TO_STRING(ip_address), 'xxx', '0') ip,
    COUNT(1) c
  FROM
    `workspace-data.Logs.activity`
  WHERE
    SAFE_CONVERT_BYTES_TO_STRING(ip_address) IS NOT NULL
    AND email IS NOT NULL -- 过滤无邮箱的记录
  GROUP BY
    email, ip ) -- 按邮箱和IP分组，统计每个用户对应IP的访问次数
SELECT
  email,
  city_name,
  country_name,
  country_iso_code,
  SUM(c) c,
  ST_GEOGPOINT(AVG(longitude), AVG(latitude)) point
FROM (
  SELECT
    email,
    ip,
    city_name,
    country_name,
    country_iso_code,
    c,
    latitude,
    longitude,
    geoname_id
  FROM (
    SELECT
      *,
      NET.SAFE_IP_FROM_STRING(ip) & NET.IP_NET_MASK(4, mask) network_bin
    FROM
      source_of_ip_addresses,
      UNNEST(GENERATE_ARRAY(9,32)) mask
    WHERE
      BYTE_LENGTH(NET.SAFE_IP_FROM_STRING(ip)) = 4 )
  JOIN
    `fh-bigquery.geocode.201806_geolite2_city_ipv4_locs`
  USING
    (network_bin, mask) )
WHERE
  city_name IS NOT NULL
GROUP BY
  email, city_name, country_name, country_iso_code, geoname_id -- 加入邮箱作为分组维度
ORDER BY
  c DESC

关键改动说明 #

1. CTE分组调整：在source_of_ip_addresses中加入email字段，并将GROUP BY改为email, ip，统计每个用户对应IP的访问次数，同时添加email IS NOT NULL过滤无效记录。
2. 传递邮箱字段：在后续关联地理数据的子查询中保留email字段，确保该字段能传递到最终聚合环节。
3. 最终聚合维度：在最终SELECT和GROUP BY中加入email，实现按「用户-地理位置」的维度聚合，输出每个用户在对应地理位置的访问次数。

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