BigQuery新手求助：基于unnested嵌套数组统计符合条件的唯一用户

别担心，刚上手BigQuery的嵌套数组确实容易绕晕，我来一步步帮你实现需求——统计并列出符合条件的唯一persistent_user_id值。

核心思路拆解 #

你的需求需要两步关键操作：

• 先筛选出user_dim.user_properties.key为"status"且值为"Active"的记录
• 从这些记录中提取user_dim.user_properties.key为"persistent_user_id"的唯一值

完整SQL示例 #

假设你的数据表路径是your-project.your-dataset.your-table，可以用以下查询实现：

-- 先筛选出状态为Active的用户，同时提取他们的persistent_user_id
WITH active_users AS (
  SELECT
    -- 从user_properties数组中提取persistent_user_id的字符串值
    (SELECT value.string_value 
     FROM UNNEST(user_dim.user_properties) 
     WHERE key = 'persistent_user_id') AS persistent_user_id
  FROM
    `your-project.your-dataset.your-table`
  WHERE
    -- 用EXISTS判断用户是否有status=Active的属性，避免数据膨胀
    EXISTS (
      SELECT 1 
      FROM UNNEST(user_dim.user_properties)
      WHERE key = 'status' AND value.string_value = 'Active'
    )
)
-- 提取唯一的persistent_user_id并过滤空值
SELECT DISTINCT persistent_user_id
FROM active_users
WHERE persistent_user_id IS NOT NULL
ORDER BY persistent_user_id;

代码细节解释 #

1. UNNEST(user_dim.user_properties)：把嵌套的数组拆成一行行的结构化数据，方便我们筛选和提取属性值
2. EXISTS子句：高效判断当前用户是否存在"status=Active"的属性，相比直接JOIN UNNEST，能避免因数组多元素导致的数据重复
3. 子查询提取persistent_user_id：假设每个用户的user_properties中只有一个persistent_user_id，用子查询可以精准提取对应值
4. DISTINCT：确保最终结果里的persistent_user_id都是唯一的
5. 空值过滤：排除那些没有persistent_user_id属性的用户记录

可选扩展：统计每个persistent_user_id的用户数量 #

如果需要同时统计每个persistent_user_id对应的用户数，可以调整最后一步查询：

SELECT
  persistent_user_id,
  COUNT(*) AS user_count
FROM active_users
WHERE persistent_user_id IS NOT NULL
GROUP BY persistent_user_id
ORDER BY user_count DESC;

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