在Postgresql中使用DISTINCT和聚合函数？

Question

这是一个非常基本的问题，但出于某些原因，我找不到一个合理的解决方案。我会尽力解释。

假设你有一张活动门票（区域、排、座位号）。每张门票都属于一个参与者。多张门票可以属于同一个参与者。每个参与者都有一个价值（例如：参与者#1的价值为$10,000）。因此，这就是我想要做的：

1. Group the tickets by their section
2. Get number of tickets (count)
3. Get total worth of the attendees in that section

这里我遇到了问题：如果参会者#1价值$10,000并使用4张门票，sum(attendees.worth)将返回$40,000。这是不准确的，其价值应为$10,000。但是，当我在参会者上进行去重时，计数不准确。在理想情况下，最好能够像这样做：

select 
    tickets.section, 
    count(tickets.*) as count, 
    sum(DISTINCT ON (attendees.id) attendees.worth) as total_worth 
from 
    tickets 
    INNER JOIN 
    attendees ON attendees.id = tickets.attendee_id 
GROUP BY tickets.section

显然，这个查询不起作用。我该如何在一个查询中实现同样的功能？或者这是否可能？我更愿意避免子查询，因为这是一个更大解决方案的一部分，在其中我需要在多个表之间执行此操作。

另外，价值应该按票价平均分配。例如：$10,000 / 4。每张门票的参与者价值为$5,000。因此，如果门票在不同的区域，他们将带着他们的按比例计算的价值。

感谢您的帮助。

- Binary Logic

---

我知道已经有一段时间了，但我遇到了完全相同类型的问题，想知道你是否能够解决它。 - elynnaie

Answer 1

在参与者之前，您需要汇总门票：

select ta.section, sum(ta.numtickets) as count, sum(a.worth) as total_worth
from (select attendee_id, section, count(*) as numtickets
      from tickets
      group by attendee_id, section
     ) ta INNER JOIN
     attendees a
     ON a.id = ta.attendee_id
GROUP BY ta.section

你仍然存在一个与会者在多个部分拥有座位的问题。然而，你没有指定如何解决这个问题（分配价值？随机选择一个部分？归属所有部分？规范地选择一个部分？）

- Gordon Linoff

---

谢谢你，但这个方法还是会多次计算价值吧？价值是由门票数量决定的。例如：参与者1的价值为$10,000，使用了4张门票。每张门票为价值贡献$5,000。因此，如果每张门票在不同的区域，它将分别为其所在的区域增加$5,000。这样说清楚了吗？ - Binary Logic

这不会产生错误吗？派生表（“ta”）没有正确分组。我非常确定PostgreSQL会拒绝它。 - user330315

@a_horse_with_no_name……是的，我已经修复了代码以符合我的意图。 - Gordon Linoff

Answer 2

使用jsonb_object_agg：

select 
    tickets.section, 
    count(tickets.*) as count, 
    (
      SELECT SUM(value::int4)
      FROM jsonb_each_text(jsonb_object_agg(attendees.id, attendees.worth))
    ) as total_worth
from 
    tickets 
    INNER JOIN 
    attendees ON attendees.id = tickets.attendee_id 
GROUP BY tickets.section

- PaulZi