จัดกลุ่มและสรุปผลลัพธ์ใน postgreSQL

Question

ฉันมีตารางชื่อ DETAILS ซึ่งมีคอลัมน์ตัวเลข 5 คอลัมน์ DETAILS (id, key2, key3, num1, num2, num3, num4, num5) การรวมกันของ id, fk1, fk2, fk3, key2 และ key3 เป็นคีย์หลัก แต่ละรหัสอาจมีหลายแถวเนื่องจากคีย์หลักเป็นการรวมกันของ (id, fk1, fk2, fk3)

ความต้องการของฉันคือรับค่า SUM 10 อันดับแรกของแต่ละคอลัมน์ที่จัดกลุ่มตามรหัสด้านล่าง

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, sum(num1), sum(num2), sum(num3), sum(num4), sum(num5)

ให้บอกว่าแบบสอบถามแรกส่งกลับ

[{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 หรือบางอย่าง? ถ้าเป็นเช่นนั้นฉันจะบรรลุสิ่งนี้ด้วยแบบสอบถามที่ปรับให้เหมาะสมได้อย่างไร

นี่เป็นเพียงแบบสอบถามประเภทหนึ่งที่มี fk1 ในส่วนคำสั่ง WHERE ฉันอาจต้องสอบถามบ่อยครั้งด้วยเงื่อนไข 'WHERE fk2 =$3' OR 'WHERE fk3 = $4 '. ในบางกรณีที่เกิดขึ้นไม่บ่อยฉันอาจต้องค้นหาด้วยการรวมกันของหลายเงื่อนไขใน fk1, fk2 และ fk3 เข้าด้วยกัน

ฉันกำลังคิดถึงสามวิธี

แนวทาง # 1:

• สร้างตารางสรุป smry_id_fk1, smry_id_fk2, smry_id_fk3
• ในการแทรกอัปเดตและลบตาราง DETAILS แต่ละรายการให้รวมค่าและแทรก / อัปเดต / ลบตารางใหม่ตามลำดับ

แนวทาง # 2:

• สร้างตารางสรุป smry_id_fk1_fk2_fk3 ด้วยคีย์หลัก (id, fk1, fk2, fk3)

• ในการแทรกอัปเดตและลบตาราง DETAILS แต่ละรายการ SUM ค่าและแทรก / อัปเดต / ลบ smry_id_fk1_fk2_fk3 ตารางค่าที่เป็นไปได้สำหรับ smry_id_fk1_fk2_fk3 อาจเป็น

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

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

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

แนวทาง # 3:

• อย่าสร้างตารางสรุปใด ๆ ใช้แบบสอบถามที่ปรับให้เหมาะสมเพื่อรับผลลัพธ์จากตาราง DETAILS เอง

คำถาม:

แนวทางใดดีกว่าที่จะไปกับ? หากแนวทาง # 3 ดีกว่าฉันจะบรรลุผลลัพธ์ที่ต้องการโดยไม่ลดทอนประสิทธิภาพได้อย่างไร

Answer 1

ดูเหมือนว่าคุณจะต้องการรหัสและผลรวมที่ผลรวมอยู่ใน 10 อันดับแรกโดยรวม

ดูเหมือนว่าจะรวมกับฟังก์ชันหน้าต่าง:

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;