防止组中的行重复计数，方法是

Question

下面是我的模式和问题的基本要点：http://sqlfiddle.com/#!1/72ec9/4/2

请注意，periods表可以引用可变的时间范围-它可以是整个赛季，也可以是几场比赛或一场比赛。对于给定的团队和年份，所有周期行都表示唯一的时间范围。

我已经编写了一个查询，它连接多个表，并使用GROUP BY periods.year来汇总一个赛季的得分(请参阅sqlfiddle)。但是，如果教练在同一年有两个职位，GROUP BY将计算同一期间行两次。当教练担任两个职位，但仍然总结一年由多个时期组成的时期时，我如何摆脱重复？如果有更好的方法来创建模式，如果您能指出它，我也将不胜感激。

Answer 1

使用显示的here所示的distinct

代码：

select periods.year as year,
sum(coalesce(periods.wins, 0)) as wins,
sum(coalesce(periods.losses, 0)) as losses,
sum(coalesce(periods.ties, 0)) as ties,
array_agg( distinct positions.id) as position_id,
array_agg( distinct positions.name) as position_names

from periods_positions_coaches_linking

join coaches on coaches.id = periods_positions_coaches_linking.coach
join positions on positions.id = periods_positions_coaches_linking.position
join periods on periods.id = periods_positions_coaches_linking.period

where coaches.id = 1

group by periods.year, positions.id
order by periods.year;

Answer 2

在您的情况下，最简单的方法可能是划分位置：

select periods.year as year,
       sum(coalesce(periods.wins, 0))/COUNT(distinct positions.id) as wins,
       sum(coalesce(periods.losses, 0))/COUNT(distinct positions.id) as losses,
       sum(coalesce(periods.ties, 0))/COUNT(distinct positions.id) as ties,
       array_agg(distinct positions.id) as position_id,
       array_agg(distinct positions.name) as position_names
from periods_positions_coaches_linking join
     coaches
     on coaches.id = periods_positions_coaches_linking.coach join
     positions
     on positions.id = periods_positions_coaches_linking.position join
     periods
     on periods.id = periods_positions_coaches_linking.period
where coaches.id = 1
group by periods.year
order by periods.year;

头寸的数量衡量了胜负和平局，因此将其除以可以调整计数。