UNNEST表达式引用的列产品既未分组也未汇总 [英] UNNEST expression references column products which is neither grouped nor aggregated at

问题描述

我有表结构

和此查询:

select user_id,min(timestamp) as first_order_time,max(timestamp) as most_recent_order_time,count(1) as no_orders,
 (SELECT SUM((item_price - extra_discount_total) / IFNULL(exchange_rate,1)) FROM UNNEST(products)) AS sums
from `order` 
where parent_order_id is null
group by 1
having min(timestamp)<timestamp('2017-12-31') and max(timestamp)>timestamp('2018-01-01')

第二行显示错误:

UNNEST表达式引用未分组的列产品也不汇总在[2:89]

UNNEST expression references column products which is neither grouped nor aggregated at [2:89]

exchange_rate 是订单表的一部分.

推荐答案

#standardSQL
SELECT 
  user_id,
  MIN(TIMESTAMP) AS first_order_time,
  MAX(TIMESTAMP) AS most_recent_order_time,
  COUNT(1) AS no_orders,
 SUM((SELECT SUM((item_price - extra_discount_total) / IFNULL(exchange_rate,1)) 
  FROM UNNEST(products))
 ) AS sums
FROM `order` 
WHERE parent_order_id IS NULL
GROUP BY 1
HAVING MIN(TIMESTAMP)<TIMESTAMP('2017-12-31') AND MAX(TIMESTAMP)>TIMESTAMP('2018-01-01')

这篇关于UNNEST表达式引用的列产品既未分组也未汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！