mysql - 在 BigQuery 中 UNION ALL 或 CONCATENATE 数据集

Question

我正在使用 BigQuery 控制台，需要对 12 个不同的数据集进行联合，但信息相同，只需更改 de dataset_id，因为所有数据集的日期范围都相同。

我尝试将 union all 函数放在第一个查询的末尾和另一个查询的末尾，但不起作用。

Error: SELECT list expression references hits.contentgroup.contentgroup2 which is neither grouped nor aggregated at [2:3]

这是查询:

SELECT
  hits.contentgroup.contentgroup2 CampaignGrouping,
  custd.value member_PK,
  'Web' Canal,
  'ES' AS country_id,
  SUM(hits.contentGroup.contentGroupUniqueViews2) VistasUnicas
FROM
  `id_project.11773102.ga_sessions*`,
  UNNEST(customdimensions) custd,
  UNNEST(hits) AS hits
WHERE
  1 = 1
  AND PARSE_TIMESTAMP('%Y%m%d', REGEXP_EXTRACT(_table_suffix, r'.*_(.*)')) BETWEEN TIMESTAMP('2017-04-25') AND TIMESTAMP('2017-04-30')
  AND custd.index=30
  and hits.contentGroup.contentGroup2 <> '(not set)'
  AND custd.value <> 'null'
  AND hits.contentGroup.contentGroupUniqueViews2 IS NOT NULL
UNION ALL
SELECT
  hits.contentgroup.contentgroup2 CampaignGrouping,
  custd.value member_PK,
  'Web' Canal,
  'ES' AS country_id,
  SUM(hits.contentGroup.contentGroupUniqueViews2) VistasUnicas
FROM
  `id_project.11773102.ga_sessions*`,
  UNNEST(customdimensions) custd,
  UNNEST(hits) AS hits
WHERE
  1 = 1
  AND PARSE_TIMESTAMP('%Y%m%d', REGEXP_EXTRACT(_table_suffix, r'.*_(.*)')) BETWEEN TIMESTAMP('2017-04-25') AND TIMESTAMP('2017-04-30')
  AND custd.index=30
  and hits.contentGroup.contentGroup2 <> '(not set)'
  AND custd.value <> 'null'
  AND hits.contentGroup.contentGroupUniqueViews2 IS NOT NULL
 GROUP BY
  1, 2
ORDER BY 5 ASC

谢谢。

Answer 1

您需要将GROUP BY与联合中的第一个查询一起使用，例如:

SELECT
  hits.contentgroup.contentgroup2 CampaignGrouping,
  custd.value member_PK,
  'Web' Canal,
  'ES' AS country_id,
  SUM(hits.contentGroup.contentGroupUniqueViews2) VistasUnicas
FROM
  `bigquery-aaaaa-162814.11773102.ga_sessions*`,
  UNNEST(customdimensions) custd,
  UNNEST(hits) AS hits
WHERE
  1 = 1
  AND PARSE_TIMESTAMP('%Y%m%d', REGEXP_EXTRACT(_table_suffix, r'.*_(.*)')) BETWEEN TIMESTAMP('2017-04-25') AND TIMESTAMP('2017-04-30')
  AND custd.index=30
  and hits.contentGroup.contentGroup2 <> '(not set)'
  AND custd.value <> 'null'
  AND hits.contentGroup.contentGroupUniqueViews2 IS NOT NULL
GROUP BY 1, 2
UNION ALL
SELECT ...

作为 UNION ALL 与 GROUP BY 的具体示例:

#standardSQL
WITH T AS (
  SELECT 1 AS x, 'foo' AS y UNION ALL
  SELECT 1, 'bar' UNION ALL
  SELECT 2, 'foo'
)
SELECT x, STRING_AGG(y, ',') AS y
FROM T
GROUP BY x
UNION ALL
SELECT SUM(x), y
FROM T
GROUP BY y;