这是我正在运行的查询-正如我所说的,BigQuery表示它是有效的,但是一旦我运行查询,就会返回错误。我已经在Postgres中运行了此查询(对不同的UNION ALL语法进行了必要的更改),并且按预期方式工作。任何帮助将不胜感激,因为我是BQ的新手。谢谢。
Query Failed
Error: Field 'customerid' not found.
SELECT jobs.customerid,
jobs.total,
jobs.count,
city_max.city
FROM
(SELECT customerid,
sum(total) AS total,
count(*) AS count
FROM [events.transaction_20160301],[events.transaction_20160401] group by customerid ) AS jobs
JOIN
(SELECT customer_city_count.customerid,
city,
city_count
FROM
(SELECT customerid,
city,
count(*) AS city_count
FROM [events.transaction_20160301],[events.transaction_20160401]
GROUP BY customerid, city) AS customer_city_count
JOIN
(SELECT customerid,
max(ct) AS max_ct
FROM
(SELECT customerid,
city,
count(*) AS ct
FROM [events.transaction_20160301],[events.transaction_20160401]
GROUP BY customerid, city)
GROUP BY customerid) AS customer_max_city ON customer_max_city.customerid = customer_city_count.customerid
AND city_count = customer_max_city.max_ct) AS city_max ON city_max.customerid = jobs.customerid
BigQuery对别名/字段命名非常敏感。
尝试下面
SELECT jobs.customerid,
jobs.total,
jobs.count,
city_max.city
FROM
(SELECT customerid,
SUM(total) AS total,
COUNT(*) AS COUNT
FROM [events.transaction_20160301],[events.transaction_20160401] GROUP BY customerid ) AS jobs
JOIN
(SELECT customer_city_count.customerid as customerid,
city,
city_count
FROM
(SELECT customerid,
city,
COUNT(*) AS city_count
FROM [events.transaction_20160301],[events.transaction_20160401]
GROUP BY customerid, city) AS customer_city_count
JOIN
(SELECT customerid,
MAX(ct) AS max_ct
FROM
(SELECT customerid,
city,
COUNT(*) AS ct
FROM [events.transaction_20160301],[events.transaction_20160401]
GROUP BY customerid, city)
GROUP BY customerid) AS customer_max_city ON customer_max_city.customerid = customer_city_count.customerid
AND city_count = customer_max_city.max_ct) AS city_max ON city_max.customerid = jobs.customerid
这行下面只有一个更改
SELECT customer_city_count.customerid as customerid
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句