正如标题所示,我想选择用GROUP BY分组的每组行中的第一行。

具体来说,如果我有一个如下所示的采购表:

SELECT * FROM purchases;

我的输出:

id customer total
1 Joe 5
2 Sally 3
3 Joe 2
4 Sally 1

我想查询每个客户的最大购买量(总购买量)。类似于:

SELECT FIRST(id), customer, FIRST(total)
FROM  purchases
GROUP BY customer
ORDER BY total DESC;

预期输出:

FIRST(id) customer FIRST(total)
1 Joe 5
2 Sally 3

当前回答

查询:

SELECT purchases.*
FROM purchases
LEFT JOIN purchases as p 
ON 
  p.customer = purchases.customer 
  AND 
  purchases.total < p.total
WHERE p.total IS NULL

这是怎么回事!(我去过那里)

我们希望确保每次购买的总金额最高。


一些理论知识(如果您只想了解查询,请跳过此部分)

让Total是一个函数T(customer,id),其中它返回一个给定名称和id的值为了证明给定的总数(T(customer,id))是最高的,我们必须证明我们想证明

∀x T(customer,id)>T(customer,x)(这个总数高于所有其他该客户的总计)

OR

∃x T(customer,id)<T(customers,x)(不存在更高的总数该客户)

第一种方法需要我们获取我不太喜欢的名字的所有记录。

第二个将需要一个聪明的方式来表示,没有比这个更高的记录了。


返回SQL

如果我们在表的名称和总数小于连接表的情况下留下连接表:

LEFT JOIN purchases as p 
ON 
p.customer = purchases.customer 
AND 
purchases.total < p.total

我们确保要加入的同一用户的另一条记录总数较高的所有记录:

+--------------+---------------------+-----------------+------+------------+---------+
| purchases.id |  purchases.customer | purchases.total | p.id | p.customer | p.total |
+--------------+---------------------+-----------------+------+------------+---------+
|            1 | Tom                 |             200 |    2 | Tom        |     300 |
|            2 | Tom                 |             300 |      |            |         |
|            3 | Bob                 |             400 |    4 | Bob        |     500 |
|            4 | Bob                 |             500 |      |            |         |
|            5 | Alice               |             600 |    6 | Alice      |     700 |
|            6 | Alice               |             700 |      |            |         |
+--------------+---------------------+-----------------+------+------------+---------+

这将有助于我们在不需要分组的情况下筛选每次购买的最高总额:

WHERE p.total IS NULL
    
+--------------+----------------+-----------------+------+--------+---------+
| purchases.id | purchases.name | purchases.total | p.id | p.name | p.total |
+--------------+----------------+-----------------+------+--------+---------+
|            2 | Tom            |             300 |      |        |         |
|            4 | Bob            |             500 |      |        |         |
|            6 | Alice          |             700 |      |        |         |
+--------------+----------------+-----------------+------+--------+---------+

这就是我们需要的答案。

其他回答

在Postgres中,您可以这样使用array_agg:

SELECT  customer,
        (array_agg(id ORDER BY total DESC))[1],
        max(total)
FROM purchases
GROUP BY customer

这将为您提供每个客户最大购买量的id。

需要注意的一些事项:

array_agg是一个聚合函数,因此它与GROUP BY一起工作。arrayagg允许您指定仅限于自身的排序,因此它不会约束整个查询的结构。如果您需要执行与默认值不同的操作,还可以使用语法对NULL进行排序。一旦我们构建了数组,我们就使用第一个元素。(Postgres数组是1索引的,而不是0索引的)。您可以以类似的方式对第三个输出列使用array_agg,但max(total)更简单。与DISTINCT ON不同,使用array_agg可以保留GROUP BY,以防出于其他原因需要。

这是我们如何通过使用windows函数实现的:

    create table purchases (id int4, customer varchar(10), total integer);
    insert into purchases values (1, 'Joe', 5);
    insert into purchases values (2, 'Sally', 3);
    insert into purchases values (3, 'Joe', 2);
    insert into purchases values (4, 'Sally', 1);
    
    select ID, CUSTOMER, TOTAL from (
    select ID, CUSTOMER, TOTAL,
    row_number () over (partition by CUSTOMER order by TOTAL desc) RN
    from purchases) A where RN = 1;

在PostgreSQL中,另一种可能是将first_value窗口函数与SELECT DISTINCT结合使用:

select distinct customer_id,
                first_value(row(id, total)) over(partition by customer_id order by total desc, id)
from            purchases;

我创建了一个组合(id,total),因此两个值都由同一个聚合返回。当然,您可以始终应用first_value()两次。

通过我的测试,公认的OMG Ponies“受任何数据库支持”解决方案的速度很快。

在这里,我提供了一种相同的方法,但更完整、更干净的任何数据库解决方案。考虑联系(假设希望每个客户只获得一行,甚至每个客户最多获得多条记录),将为采购表中的实际匹配行选择其他采购字段(例如purchase_payment_id)。

任何数据库都支持:

select * from purchase
join (
    select min(id) as id from purchase
    join (
        select customer, max(total) as total from purchase
        group by customer
    ) t1 using (customer, total)
    group by customer
) t2 using (id)
order by customer

这个查询相当快,特别是当采购表上有一个类似(customer,total)的复合索引时。

备注:

t1、t2是可以根据数据库删除的子查询别名。注意:截至2017年1月本次编辑,MS-SQL和Oracle数据库目前不支持using(…)子句。您必须自己将其扩展到例如t2.id=purchase.id等。USING语法适用于SQLite、MySQL和PostgreSQL。

这样对我来说很有效:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article
              GROUP BY s2.article)
ORDER BY article;

选择每篇文章的最高价格