一次汇总和订购 [英] Summing and ordering at once

问题描述

我有一张订单表。在那里，我需要找出3个partner_id的总和最大，并将这3个从大到小排序。

I have a table of orders. There I need to find out which 3 partner_id's have made the largest sum of amount_totals, and sort those 3 from biggest to smallest.

    testdb=# SELECT amount_total, partner_id FROM sale_order;
     amount_total | partner_id 
    --------------+------------
          1244.00 |          9
          3065.90 |         12
          3600.00 |          3
          2263.00 |         25
          3000.00 |         10
          3263.00 |          3
           123.00 |         25
          5400.00 |         12
    (8 rows)

只是启动SQL，我发现它令人困惑...

Just starting SQL, I find it confusing ...

推荐答案

汇总金额

如果要列出汇总金额，可以很简单：

Aggregated amounts

If you want to list aggregated amounts, it can be as simple as:

SELECT partner_id, sum(amount_total) AS amout_suptertotal
FROM   sale_order
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  3;

GROUP BY 1 中的1是一个数值参数，它引用 SELECT 列表中的位置。在这种情况下，只是 GROUP BY partner_id 的符号快捷方式。

The 1 in GROUP BY 1 is a numerical parameter, that refers to the position in the SELECT list. Just a notational shortcut for GROUP BY partner_id in this case.

这忽略了特殊情况，即超过三个伙伴将有资格并任意选择3个（因为缺乏定义）。

This ignores the special case where more than three partner would qualify and picks 3 arbitrarily (for lack of definition).

SELECT partner_id, amount_total
FROM   sale_order
JOIN  (
   SELECT partner_id, rank() OVER (ORDER BY sum(amount) DESC) As rnk
   FROM   sale_order
   GROUP  BY 1
   ORDER  BY 2
   LIMIT  3
   ) top3 USING (partner_id)
ORDER  BY top3.rnk;

另一方面，如果超过3个合作伙伴有资格进入前3名，则此伙伴包括所有同行。 窗口函数 rank（） 给你。

This one, on the other hand includes all peers if more than 3 partner qualify for the top 3. The window function rank() gives you that.

这里的技术是在子查询中按 partner_id 分组 top3 并在聚合后使窗口函数 rank（）附加排名（窗口函数在聚合函数之后执行）。在窗口函数之后应用 ORDER BY ，最后应用 LIMIT 。全部合并在一个子查询中。

The technique here is to group by partner_id in the subquery top3 and have the window function rank() attach ranks after the aggregation (window functions execute after aggregate functions). ORDER BY is applied after window functions and LIMIT is applied last. All in one subquery.

然后，我将基表连接到此子查询中，这样，结果和顺序中只剩下最高的狗， rnk 。

Then I join the base table to this subquery, so that only the top dogs remain in the result and order by rnk.

窗口函数需要PostgreSQL 8.4或更高版本。

Window functions require PostgreSQL 8.4 or later.

这是相当先进的东西。您应该以更简单的方式开始学习SQL。

This is rather advanced stuff. You should start learning SQL with something simpler probably.

这篇关于一次汇总和订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！