时间戳

首页 > 解决方案 > 从多列的 SUM() 中选择并加入 postgreSQL

sql - 从多列的 SUM() 中选择并加入 postgreSQL

问题描述

我有一个名为 DETAILS 的表，它有 5 个数字列 DETAILS（id、key2、key3、num1、num2、num3、num4、num5）。id、key2 和 key3 的组合是主键。每个 id 可能有多行。

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

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 组合上述结果

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},
]

这可能与使用连接或其他东西的单个查询有关吗？如果是这样，我如何通过优化查询来实现这一点？

标签： sqlpostgresqlgroup-by

解决方案

我认为您想要所有 5 个查询的 FULL OUTER JOIN：

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

推荐阅读