在对重复值求和时如何获得不同的总和？

The issue I am having is with Postgres, in that using SUM with non-unique values or "sales" results in non-unique cumulative sums or "running sales".

我遇到的问题是Postgres,因为使用具有非唯一值的SUM或“sales”会产生非唯一累积总和或“正在运行的销售额”。

Here is the query:

这是查询:

SELECT *, 
       SUM(Sales) OVER (ORDER BY sales DESC) as running_sales 
FROM t;

Here is the output

这是输出

Essentially I want this table to read:

基本上我希望这个表格如下:

4--Motherboards------- 22----399
5--RAID Controllers----22----421

Does anyone have any ideas on how I could do this?

有没有人对我如何做到这一点有任何想法?

Aside: If anyone is interested, this is what I was following

旁白:如果有人有兴趣,这就是我所关注的

1 个解决方案

#1

Make the order in the OVER clause unique by adding id as second ORDER BY item:

通过将id添加为第二个ORDER BY项,使OVER子句中的顺序唯一:

SELECT *, 
       SUM(Sales) OVER (ORDER BY sales DESC, id) as running_sales 
FROM   t
ORDER  BY sales DESC, id;

The way you had it, equal sales are treated as peers in the same FRAME and summed at once.
The tutorial you were following is no good for this particular query.

你拥有它的方式,平等销售在同一个框架中被视为同行,并立即汇总。您所关注的教程对此特定查询没有好处。

Also, you probably want to add an ORDER BY to the query itself to get stable results. This is very cheap, since it agrees with the sort order of the window function.

此外,您可能希望向查询本身添加ORDER BY以获得稳定的结果。这非常便宜,因为它与窗口函数的排序顺序一致。

The way it is implemented right now, you normally get the results from this query in the order of the window function for simple queries, because that's the cheapest way. But Postgres makes no guarantee about the ordering of rows without ORDER BY, the arbitrary order can change without warning.

它现在实现的方式,你通常会按照窗口函数的顺序从简单查询中获得此查询的结果,因为这是最便宜的方式。但Postgres不保证没有ORDER BY的行的排序,任意顺序可以在没有警告的情况下改变。

#1