SQL 不能在同一个查询中使用 group by 和 over(partition by)？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29250503/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot use group by and over(partition by) in the same query?
提问by user3557405
I have a table myTablewith 3 columns. col_1is an INTEGERand the other 2 columns are DOUBLE. For example, col_1={1, 2}, col_2={0.1, 0.2, 0.3}. Each element in col_1is composed of all the values of col_2and col_2has repeated values for each element in col_1. The 3rd column can have any value as shown below:
我有一个myTable3 列的表。col_1是一个INTEGER，其他 2 列是DOUBLE. 例如，col_1={1, 2}, col_2={0.1, 0.2, 0.3}。中的每个元素col_1都由 的所有值组成，col_2并且col_2中的每个元素都有重复的值col_1。第 3 列可以具有任何值，如下所示：
col_1 | col_2 | Value
----------------------
1 | 0.1 | 1.0
1 | 0.2 | 2.0
1 | 0.2 | 3.0
1 | 0.3 | 4.0
1 | 0.3 | 5.0
2 | 0.1 | 6.0
2 | 0.1 | 7.0
2 | 0.1 | 8.0
2 | 0.2 | 9.0
2 | 0.3 | 10.0
What I want is to use an aggregate-function SUM()on the Valuecolumn partition by col_1and grouped by col_2. The Above table should look like this:
我想要的是SUM()在Value列分区 bycol_1和 grouped by上使用聚合函数col_2。上表应如下所示：
col_1 | col_2 | sum_value
----------------------
1 | 0.1 | 1.0
1 | 0.2 | 5.0
1 | 0.3 | 9.0
2 | 0.1 | 21.0
2 | 0.2 | 9.0
2 | 0.3 | 10.0
I tried the following SQL query:
我尝试了以下 SQL 查询：
SELECT col_1, col_2, sum(Value) over(partition by col_1) as sum_value
from myTable
GROUP BY col_1, col_2
But on DB2 v10.5 it gave the following error:
但是在 DB2 v10.5 上，它给出了以下错误：
SQL0119N An expression starting with "Value" specified in a SELECT
clause, HAVING clause, or ORDER BY clause is not specified in the
GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER
BY clause with a column function and no GROUP BY clause is specified.
Can you kindly point out what is wrong. I do not have much experience with SQL.
你能指出什么是错误的。我对 SQL 没有太多经验。
Thank you.
谢谢你。
采纳答案by user3557405
I found the solution.
我找到了解决方案。
I do not need to use OVER(PARTITION BY col_1)because it is already in the GROUP BYclause. Thus, the following query gives me the right answer:
我不需要使用，OVER(PARTITION BY col_1)因为它已经在GROUP BY子句中了。因此，以下查询为我提供了正确答案：
SELECT col_1, col_2, sum(Value) as sum_value
from myTable GROUP BY col_1, col_2
since I am already grouping w.r.t col_1and col_2.
因为我已经将 wrtcol_1和col_2.
Dave, thanks, I got the idea from your post.
戴夫，谢谢，我从你的帖子中得到了这个想法。
回答by Dave
Yes, you can, but you should be consistent regarding the grouping levels. That is, if your query is a GROUP BY query, then in an analytic function you can only use "detail" columns from the "non-analytic" part of your selected columns. Thus, you can use either the GROUP BY columns or the non-analytic aggregates, like this example:
是的，您可以，但您应该在分组级别方面保持一致。也就是说，如果您的查询是 GROUP BY 查询，那么在分析函数中，您只能使用所选列的“非分析”部分中的“详细信息”列。因此，您可以使用 GROUP BY 列或非分析聚合，如下例所示：
select product_id, company,
sum(members) as No_of_Members,
sum(sum(members)) over(partition by company) as TotalMembership
From Product_Membership
Group by Product_ID, Company
Hope that helps
希望有帮助
SELECT col_1, col_2, sum(Value) over(partition by col_1) as sum_value
-- also try changing "col_1" to "col_2" in OVER
from myTable
GROUP BY col_2,col_1
