分组并在postgreSQL中汇总结果 [英] Group by and summarise the result in postgreSQL

问题描述

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

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

我的要求是获取按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 where fk1=$1
group by id
order by sum(num2) desc nulls last
limit 10;

select   id, sum(num3) val3 from details where fk1=$1            
group by id
order by sum(num3) desc nulls last
limit 10;

select   id, sum(num4) val4 from details where fk1=$1              
group by id
order by sum(num4) desc nulls last
limit 10;

select   id,sum(num5) val5 from details where fk1=$1            
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)

让我们说第一个查询返回

Lets say the first query returns

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

第二个查询返回

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

结果应该是

[
{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?

这只是WHERE子句中使用fk1的一种查询.我可能不得不频繁地用条件'WHERE fk2 = $ 3'或'WHERE fk3 = $ 4'查询.在极少数情况下，我可能不得不同时查询fk1，fk2和fk3的多个条件的组合；

This is just one kind of query with fk1 in the WHERE clause. I may have to query frequently with conditions 'WHERE fk2 = $3' OR 'WHERE fk3 = $4'. In rare cases I may have to query with the combinations of multiple conditions on fk1, fk2 and fk3 together;

我正在考虑三种方法

方法1:

• 创建摘要表smry_id_fk1，smry_id_fk2，smry_id_fk3
• 在每个DETAILS表的插入，更新和删除中，对值求和并插入/更新/删除各自的新表

方法2:

• 使用主键(id，fk1，fk2，fk3)创建摘要表smry_id_fk1_fk2_fk3

• Create a Summary table smry_id_fk1_fk2_fk3 with primary key (id, fk1, fk2, fk3)

在每个DETAILS表的插入，更新和删除中，对值求和并插入/更新/删除smry_id_fk1_fk2_fk3表 smry_id_fk1_fk2_fk3的可能值可能是

On each insert, update and delete of DETAILS table, SUM the values and insert/update/delete smry_id_fk1_fk2_fk3 table possible values for smry_id_fk1_fk2_fk3 could be

(1，fk1value，'N/A'，'N/A'，50，60，0，0，80)

(1, fk1value, 'N/A', 'N/A', 50, 60, 0, 0, 80)

(2，'N/A，fk2value，'N/A'，150，0，160，0，170)

(2, 'N/A, fk2value, 'N/A', 150, 0, 160, 0, 170)

(3，'N/A，'N/A'，fk3value，0，0，200，210，220)

(3, 'N/A, 'N/A', fk3value, 0, 0, 200, 210, 220)

方法3:

• 请勿创建任何汇总表.使用优化的查询从DETAILS表本身获取结果.

问题:

哪种方法更好?如果方法#3更好，如何在不影响性能的情况下达到期望的结果?

Which approach is better to go with? If Approach #3 is better, How do I achieve the desired result without compromising the performance?

推荐答案

您似乎想要ID和总和，总和在前10位.

You seem to want ids and sums where the sums are in the top 10 overall.

这似乎是与窗口函数的聚合:

This seems like aggregation with window functions:

select id,
       (case when seqnum_1 <= 10 then num1 end),
       (case when seqnum_2 <= 10 then num2 end),
       (case when seqnum_3 <= 10 then num3 end),
       (case when seqnum_4 <= 10 then num4 end),
       (case when seqnum_5 <= 10 then num5 end)
from (select id,
             sum(num1) as num1, sum(num2) as num2, sum(num3) as num3, sum(num4) as num4, sum(num5) as num5,
             row_number() over (order by sum(num1) nulls last) as seqnum_1,
             row_number() over (order by sum(num2) nulls last) as seqnum_2,
             row_number() over (order by sum(num3) nulls last) as seqnum_3,
             row_number() over (order by sum(num4) nulls last) as seqnum_4,
             row_number() over (order by sum(num5) nulls last) as seqnum_5
      from details d
      group by id
     ) d
where seqnum_1 <= 10 or seqnum_2 <= 10 or seqnum_3 <= 10 or seqnum_4 <= 10 or seqnum_5 <= 10;

这篇关于分组并在postgreSQL中汇总结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！