google-bigquery - 删除Firebase Analytics中导出到BigQuery的行，并使用不太受欢迎的值

与第三方共享BigQuery中的Firebase Analyics表之前，我需要删除城市少于5行的行。我如何做到这一点而又不丢失表结构？

挑战：


BigQuery中的Firebase Analytics数据具有嵌套行，因此我不想丢失嵌套结构。
不同地区的某些城市使用相同的名称。这意味着我在计数时至少需要使用两个字段（城市，地区）。
有时城市和/或地区可以为空。我不想丢失那些行。

最佳答案

快速查询：

SELECT *,
  IFNULL(user_dim.geo_info.city,'_')+IFNULL(user_dim.geo_info.region,'_') cityregion,
FROM [dataset.app_events_20160607]
HAVING cityregion NOT IN (
  SELECT cityregion FROM (
    SELECT COUNT(*) c, IFNULL(user_dim.geo_info.city,'_')+IFNULL(user_dim.geo_info.region,'_') cityregion
    FROM [dataset.app_events_20160607]
    GROUP BY 2
    HAVING c<6
  )
)

您必须将选项设置为：


允许大结果
结果不平坦


快速说明：


SELECT *, [...] cityregion带来所有结果，而不会丢失结构并添加新列cityregion。
IFNULL(..., '_') replaces null values with _`，因此我们可以将城市和地区结合在一起，以防万一。
HAVING cityregion NOT IN查看新的cityregion列，以检查子查询中是否不存在该列。
子查询会计算城市区域的每种组合，并仅列出少于6次的城市。