不能在同一查询中使用分组（分区）？ [英] Cannot use group by and over(partition by) in the same query?

问题描述

我有一个表 myTable 与3列。 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 中的每个元素重复了值。第三列可以有如下所示的任何值：

  col_1 | col_2 |价值
 ---------------------- 
 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（）在值列分区中由 col_1 分组，并按 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 
  

我尝试了以下SQL查询：

  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上，它出现以下错误：

  SQL0119N在
 GROUP BY子句中未指定以SELECT 
子句，HAVING子句或ORDER BY子句中指定的Value开头的表达式， SELECT子句，HAVING子句或ORDER 
具有列函数且没有GROUP BY子句的BY子句。你可以指出什么是错误的，你可以指出：
  

我没有太多的SQL经验。

谢谢。

解决方案

p>我找到了解决方案。

我不需要使用 OVER（PARTITION BY col_1），因为它是已经在 GROUP BY 子句中。因此，以下查询给出了正确的答案：

  SELECT col_1，col_2，sum（Value）as sum_value 
从myTable GROUP BY col_1，col_2 
  

因为我已经分组wrt col_1 和 col_2 。

Dave，谢谢，我从你的帖子中得到了想法。 / p>

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

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

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

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.

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

Thank you.

解决方案

I found the solution.

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

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

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

这篇关于不能在同一查询中使用分组（分区）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！