不能在同一个查询中使用 group by 和 over(partition by)? [英] Cannot use group by and over(partition by) in the same query?

问题描述

我有一个包含 3 列的表 myTable.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 列可以有任何值，如下所示:

I have a table myTable with 3 columns. col_1 is an INTEGER and the other 2 columns are DOUBLE. For example, col_1={1, 2}, col_2={0.1, 0.2, 0.3}. Each element in col_1 is composed of all the values of col_2 and col_2 has repeated values for each element in col_1. The 3rd column can have any value as shown below:

    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

我想要的是使用聚合函数 SUM() 在 Value 列分区上按 col_1 并按 分组col_2.上表应如下所示:

What I want is to use an aggregate-function SUM() on the Value column partition by col_1 and grouped by col_2. The Above table should look like this:

    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

我尝试了以下 SQL 查询:

I tried the following SQL query:

SELECT col_1, col_2, sum(Value) over(partition by col_1) as sum_value
from myTable
GROUP BY col_1, col_2

但是在 DB2 v10.5 上，它给出了以下错误:

But on DB2 v10.5 it gave the following error:

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.

你能指出什么是错误的.我对 SQL 没有太多经验.

Can you kindly point out what is wrong. I do not have much experience with SQL.

谢谢.

推荐答案

我找到了解决方案.

我不需要使用 OVER(PARTITION BY col_1) 因为它已经在 GROUP BY 子句中.因此，以下查询为我提供了正确答案:

I do not need to use OVER(PARTITION BY col_1) because it is already in the GROUP BY clause. Thus, the following query gives me the right answer:

SELECT col_1, col_2, sum(Value) as sum_value
from myTable GROUP BY col_1, col_2

因为我已经将 w.r.t col_1 和 col_2 分组.

since I am already grouping w.r.t col_1 and col_2.

戴夫，谢谢，我从你的帖子中得到了这个想法.

Dave, thanks, I got the idea from your post.

这篇关于不能在同一个查询中使用 group by 和 over(partition by)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！