sql - 在sql中如何获得由两列分组的总和的最大值

Question

SELECT county, category_name, SUM(bottle_qty*(btl_price-state_btl_cost)) AS profit
FROM sales
GROUP BY county, category_name
ORDER BY profit DESC

我想要每个县的利润，以及哪个 category_name 会在该县产生最大的利润。

所以我只想要第一行，第8行和第11行：

标签： sqlpostgresqlgroup-bymaxgreatest-n-per-group

Answer 1

您可以使用distinct on来解决这个每组最大 n 的问题：

SELECT DISTINCT ON(county)
    county, 
    category_name,
    SUM(bottle_qty*(btl_price-state_btl_cost)) AS profit 
FROM sales 
GROUP BY county, category_name 
ORDER BY county, profit DESC

---

但是，这不允许您控制结果集中行的顺序。在这种情况下，请row_number()改用：

SELECT *
FROM (
    SELECT
        county, 
        category_name,
        SUM(bottle_qty*(btl_price-state_btl_cost)) AS profit 
        ROW_NUMBER() OVER(
            PARTITION BY county 
            ORDER BY SUM(bottle_qty*(btl_price-state_btl_cost)) DESC
        ) rn
    FROM sales 
    GROUP BY county, category_name 
) t
WHERE rn = 1
ORDER BY profit