SQL 中的 GROUP BY/聚合函数混淆

GROUP BY / aggregate function confusion in SQL

提问by Chris

I need a bit of help straightening out something, I know it's a very easy easy question but it's something that is slightly confusing me in SQL.

我需要一些帮助来理顺一些东西，我知道这是一个非常简单的问题，但它在 SQL 中让我有点困惑。

This SQL query throws a 'not a GROUP BY expression' error in Oracle. I understand why, as I know that once I group by an attribute of a tuple, I can no longer access any other attribute.

此 SQL 查询在 Oracle 中引发“不是 GROUP BY 表达式”错误。我明白为什么，因为我知道，一旦我按元组的属性分组，我就无法再访问任何其他属性。

SELECT * 
FROM order_details 
GROUP BY order_no

However this one does work

但是这个确实有效

SELECT SUM(order_price)
FROM order_details
GROUP BY order_no

Just to concrete my understanding on this.... Assuming that there are multiple tuples in order_details for each order that is made, once I group the tuples according to order_no, I can still access the order_price attribute for each individual tuple in the group, but only using an aggregate function?

只是为了具体说明我对此的理解......假设每个订单的 order_details 中有多个元组，一旦我根据 order_no 对元组进行分组，我仍然可以访问组中每个单独元组的 order_price 属性，但只使用聚合函数？

In other words, aggregate functions when used in the SELECT clause are able to drill down into the group to see the 'hidden' attributes, where simply using 'SELECT order_no' will throw an error?

换句话说，在 SELECT 子句中使用的聚合函数能够深入到组中以查看“隐藏”属性，而简单地使用“SELECT order_no”会抛出错误？

回答by Jonathan Leffler

In standard SQL (but not MySQL), when you use GROUP BY, you must list all the result columns that are not aggregates in the GROUP BY clause. So, if order_detailshas 6 columns, then you must list all 6 columns (by name - you can't use *in the GROUP BY or ORDER BY clauses) in the GROUP BY clause.

在标准 SQL（但不是 MySQL）中，当您使用 GROUP BY 时，您必须在 GROUP BY 子句中列出所有不是聚合的结果列。因此，如果order_details有 6 列，那么您必须*在 GROUP BY 子句中列出所有 6 列（按名称 - 您不能在 GROUP BY 或 ORDER BY 子句中使用）。

You can also do:

你也可以这样做：

SELECT order_no, SUM(order_price)
  FROM order_details
 GROUP BY order_no;

That will work because all the non-aggregate columns are listed in the GROUP BY clause.

这将起作用，因为所有非聚合列都列在 GROUP BY 子句中。

You could do something like:

你可以这样做：

SELECT order_no, order_price, MAX(order_item)
  FROM order_details
 GROUP BY order_no, order_price;

This query isn't really meaningful (or most probably isn't meaningful), but it will 'work'. It will list each separate order number and order price combination, and will give the maximum order item (number) associated with that price. If all the items in an order have distinct prices, you'll end up with groups of one row each. OTOH, if there are several items in the order at the same price (say ￡0.99 each), then it will group those together and return the maximum order item number at that price. (I'm assuming the table has a primary key on (order_no, order_item)where the first item in the order has order_item = 1, the second item is 2, etc.)

这个查询没有真正意义（或者很可能没有意义），但它会“工作”。它将列出每个单独的订单号和订单价格组合，并给出与该价格相关的最大订单项目（数量）。如果订单中的所有商品都有不同的价格，您最终会得到每组一行。OTOH，如果订单中有几件商品的价格相同（比如每件 0.99 英镑），那么它会将这些商品组合在一起并返回该价格的最大订单商品编号。（我假设该表(order_no, order_item)在订单中的第一个项目有一个主键order_item = 1，第二个项目是 2 等）

回答by DoOrDie

SELECT * 
FROM order_details 
GROUP BY order_no

In the above query you are selecting all the columns because of that its throwing an error not group by something like.. to avoid that you have to mention all the columns whichever in select statement all columns must be in group by clause..

在上面的查询中，您正在选择所有列，因为它会抛出一个错误，而不是按类似...分组，以避免您必须提及所有列，无论在 select 语句中，所有列都必须在 group by 子句中。

 SELECT * 
    FROM order_details 
    GROUP BY order_no,order_details,etc

etc it means all the columns from order_details table.

等它意味着 order_details 表中的所有列。

回答by tomdxb0004

use Common table expression(CTE) to avoid this issue.

使用公用表表达式（CTE）来避免这个问题。

multiple CTes also come handy, pasting a case where I have used...maybe helpful

多个 CTes 也派上用场，粘贴一个我用过的案例......也许有帮助

with ranked_cte1 as  
( select r.mov_id,DENSE_RANK() over ( order by r.rev_stars desc )as rankked from ratings r  ),

ranked_cte2 as  ( select * from movie where mov_id=(select mov_id from ranked_cte1 where rankked=7 ) )  select * from ranked_cte2

 select * from movie where mov_id=902

回答by DoOrDie

To use group by clause you have to mention all the columns from select statement in to group by clause but not the column from aggregate function.

要使用 group by 子句，您必须在 group by 子句中提及 select 语句中的所有列，而不是来自聚合函数的列。

TO do this instead of group by you can use partition by clause you can use only one port to group as a partition by.

要执行此操作而不是 group by，您可以使用 partition by 子句，您只能使用一个端口来分组为分区依据。

you can also make it as partition by 1

您也可以将其设置为 1 个分区