从多个列的SUM()中选择并加入postgreSQL [英] Select from SUM() of multiple columns and join in postgreSQL

问题描述

我有一个名为DETAILS的表，该表具有5个数字列DETAILS(id，key2，key3，num1，num2，num3，num4，num5). id，key2和key3的组合是主键.每个ID可能有多行.

I have a table called DETAILS which has 5 numeric columns DETAILS(id, key2, key3, num1, num2, num3, num4, num5). Combination of id, key2 and key3 is the primary key. Each id may have multiple rows.

我的要求是获取按ID分组的每列的前10个SUM值，如下所示.

My requirement is to get top 10 SUM values of each column grouped by id as below.

select   id
        ,sum(num1) val1
 from details   
group by id
order by sum(num1) desc nulls last
limit 10;

select   id
        ,sum(num2) val2
 from details  
group by id
order by sum(num2) desc nulls last
limit 10;

select   id
        ,sum(num3) val3
 from details   
group by id
order by sum(num3) desc nulls last
limit 10;

select   id
        ,sum(num4) val4
 from details   
group by id
order by sum(num4) desc nulls last
limit 10;

select   id
        ,sum(num5) val5
 from details
group by id
order by sum(num5) desc nulls last
limit 10;

我需要根据以下ID将上述结果合并

I need the above results to be combined based on the id as below

id, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)

说 第一个查询返回

[{id: 1, val1: 50}, {id: 2, val1: 60}, {id: 3, val1: 70}]

第二个查询返回

[{id: 3, val2: 150}, {id: 4, val2: 160}, {id: 3, val2: 170}]

结果应该是

[
{id: 1, val1: 50, val2: null}, 
{id: 2, val1: 60, val2: null}, 
{id: 3, val1: 70, val2: 150},
{id: 4, val1: null, val2: 160},
{id: 5, val1: null, val2: 170},
]

使用join或其他查询进行单个查询是否有可能?如果是这样，如何通过优化查询来实现?

Is this possible with single query using join or something? If so how do I achieve this with a optimised query?

推荐答案

我认为您希望对所有5个查询进行FULL OUTER JOIN:

I think that you want a FULL OUTER JOIN of all 5 queries:

with
  cte1 as (
    select id, sum(num1) val1 from details   
    group by id
    order by sum(num1) desc nulls last limit 10
  ),
  cte2 as (
    select id, sum(num2) val2 from details   
    group by id
    order by sum(num2) desc nulls last limit 10
  ),  
  cte3 as (
    select id, sum(num3) val3 from details   
    group by id
    order by sum(num3) desc nulls last limit 10
  ),  
  cte4 as (
    select id, sum(num4) val4 from details   
    group by id
    order by sum(num4) desc nulls last limit 10
  ),  
  cte5 as (
    select id, sum(num5) val5 from details   
    group by id
    order by sum(num5) desc nulls last limit 10
  )
select coalesce(c1.id, c2.id, c3.id, c4.id, c5.id) id,
       c1.val1, c2.val2, c3.val3, c4.val4, c5.val5
from cte1 c1
full outer join cte2 c2 on c2.id = c1.id
full outer join cte3 c3 on c3.id = c2.id
full outer join cte4 c4 on c4.id = c3.id
full outer join cte5 c5 on c5.id = c4.id

这篇关于从多个列的SUM()中选择并加入postgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！