SQL 窗口函数已分组，但仍"must be an aggregate expression or appear in GROUP BY clause"

Question

我有一个SQL(presto(查询，假设它是这样的：

select
id
, product_name
, product_type
, sum(sales) as total_sales
, sum(sales) over (partition by type) as sales_by_type
from some_table
group by 1,2,3

当我运行这个时，我得到一个错误，告诉我窗口函数需要出现在GROUP BY子句中。用子查询打破这种局面的最佳解决方案是什么？或者我需要做一些语法更改才能正常工作？

Answer 1

如果您想要该类型的总销售额，那么您需要嵌套sum()s:

select id, product_name, product_type,
sum(sales) as total_sales,
sum(sum(sales)) over (partition by type) as sales_by_type
from some_table
group by 1,2,3;

如果你还想要所有销售额的总和，那么：

select id, product_name, product_type,
sum(sales) as total_sales,
sum(sum(sales)) over (partition by type) as sales_by_type,
sum(sum(sales)) over () as total_total_sales
from some_table
group by 1,2,3;

Answer 2

您需要的是下面的内容

select
id
, product_name
, product_type
, sum(sales) over () as total_sales
, sum(sales) over (partition by type) as sales_by_type
from some_table

或

select
id
, product_name
, product_type
, sum(sales) over (partition by (select 1)) as total_sales
, sum(sales) over (partition by type) as sales_by_type
from some_table

这两种方法都适用于sql server。但不确定它对presto是否有效。

我也看到了下面的变化。

over (partition by null)