代码之家 › 专栏 › 技术社区 › Cristian E. Nuno

SQL:更新GROUP BY以包含基于另一列的最大值的值

group-by postgresql sql

Cristian E. Nuno · 技术社区 · 6 年前

问题

在使用 GROUP BY 语句和聚合函数?

概述

这是我的表格示例:

id  | year | quarter | wage | comp_id | comp_industry |
123 | 2012 | 1       | 1000 | 456     | abc           |
123 | 2012 | 1       | 2000 | 789     | def           |
123 | 2012 | 2       | 1500 | 789     | def           |
456 | 2012 | 1       | 2000 | 321     | ghi           |
456 | 2012 | 2       | 2000 | 321     | ghi           |

计算每个人的 wage quarter 和

SELECT SUM(wage) AS sum_wage
FROM t1
GROUP BY id, year, quarter, sum_wage;

结果

id  | year | quarter | sum_wage | 
123 | 2012 | 1       | 3000     |
123 | 2012 | 2       | 1500     |
456 | 2012 | 1       | 2000     |
456 | 2012 | 2       | 2000     |

期望输出

我想更新我的查询以包含 comp_industry 工资 一刻钟 和 year 年 .

id  | year | quarter | sum_wage | comp_industry
123 | 2012 | 1       | 3000     | def
123 | 2012 | 2       | 1500     | def
456 | 2012 | 1       | 2000     | ghi
456 | 2012 | 2       | 2000     | ghi

Get value based on max of a different column grouped by another column 和 Fetch the row which has the Max value for a column

任何帮助或建议将不胜感激!

2 回复 | 直到 6 年前

D-Shih 6 年前

你可以试着用 窗口函数 SUM ROW_NUMBER .

id , year , quarter 列排序方式 wage rn = 1 .

架构(PostgreSQL v9.6)

CREATE TABLE T (
   id INT, 
   year INT,
   quarter INT,
   wage INT,
   comp_id INT,
  comp_industry VARCHAR(50)
);


INSERT INTO T VALUES (123 , 2012 , 1 , 1000 , 456    ,'abc');
INSERT INTO T VALUES (123 , 2012 , 1 , 2000 , 789    ,'def');
INSERT INTO T VALUES (123 , 2012 , 2 , 1500 , 789    ,'def');
INSERT INTO T VALUES (456 , 2012 , 1 , 2000 , 321    ,'ghi');
INSERT INTO T VALUES (456 , 2012 , 2 , 2000 , 321    ,'ghi');

SELECT id, year,quarter ,sum_wage, comp_industry FROM (
  SELECT *,
           SUM(wage)  OVER (PARTITION BY  id, year, quarter  order by year ) sum_wage,
           ROW_NUMBER() OVER (PARTITION BY  id, year, quarter order by wage desc) rn
    FROM T
) t1
where rn = 1;

| id  | year | quarter | sum_wage | comp_industry |
| --- | ---- | ------- | -------- | ------------- |
| 123 | 2012 | 1       | 3000     | def           |
| 123 | 2012 | 2       | 1500     | def           |
| 456 | 2012 | 1       | 2000     | ghi           |
| 456 | 2012 | 2       | 2000     | ghi           |

View on DB Fiddle

Error_2646 6 年前

我不是100%确定我理解这个问题,这对你有用吗?

SELECT id, 
       year, 
       quarter, 
       comp_industry, 
       SUM(wage)
  FROM (SELECT id, 
               year, 
               quarter,
               comp_industry, 
               wage
          FROM (SELECT TMP.*,
                       RANK() OVER
                         ( PARTITION BY id, 
                                        year, 
                                        quarter
                               ORDER BY wage_sum DESC         
                         ) wage_rnk
                  FROM (SELECT t1.*,
                               SUM(wage) OVER
                                 ( PARTITION BY id, 
                                                year, 
                                                quarter 
                                 ) wage_sum
                        FROM t1
                        GROUP BY id, 
                                 year, 
                                 quarter
                       ) TMP
               ) TMP2
         WHERE wage_rnk = 1
       ) TMP3
 GROUP  
    BY id, 
       year, 
       quarter, 
       comp_industry;