提问





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


具体来说,如果我有一个purchases表,看起来像这样:


SELECT * FROM purchases;


我的输出:


id | customer | total
---+----------+------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1


我想查询每个customer所做的最大购买[[totalid。这样的事情:


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

最佳参考


在Oracle 9.2+上(不是最初提到的8i +),SQL Server 2005 +,PostgreSQL 8.4 +,DB2,Firebird 3.0 +,Teradata,Sybase,Vertica:



WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rk
      FROM PURCHASES p)
SELECT s.*
  FROM summary s
 WHERE s.rk = 1


受任何数据库支持:



但是你需要添加逻辑来打破关系:


  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

其它参考1


PostgreSQL 中,这通常更简单,更快(下面有更多性能优化):


SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;


或者更短(如果不是很清楚)具有序数的输出列:


SELECT DISTINCT ON (2)
       id, customer, total
FROM   purchases
ORDER  BY 2, 3 DESC, 1;


如果total可以为NULL(不管怎样都会受到伤害,但是你想要匹配现有索引):


...
ORDER  BY customer, total DESC NULLS LAST, id;


主要观点




  • DISTINCT ON 是该标准的PostgreSQL扩展(其中SELECT列表中只有DISTINCT被定义)。[112]

  • DISTINCT ON子句中列出任意数量的表达式,组合的行值定义重复项。手册:[113]



      显然,如果它们至少不同,则认为两行是不同的
      一列值。 在此比较中,空值被视为相等。



    大胆强调我的。

  • DISTINCT ON可以与 ORDER BY 结合使用。前导表达式必须以相同的顺序匹配前导DISTINCT ON表达式。您可以将其他表达式添加到ORDER BY以从每个对等组中选择一个特定行。我添加id作为最后一项来打破关系:


    从共享最高total的每个组中选择具有最小id的行。


    如果total可以为NULL,则很可能希望具有最大非空值的行。像演示一样添加NULLS LAST。细节:



    • PostgreSQL按日期时间排序asc,null first?


  • SELECT列表不受任何方式的DISTINCT ONORDER BY中的表达式约束。 (在上面的简单案例中不需要):



    • 您不必包含DISTINCT ONORDER BY中的任何表达。

    • 您可以在SELECT列表中包含任何其他表达式。这有助于用子查询和聚合/窗口函数替换更复杂的查询。


  • 我测试了版本8.3-10。但是至少从版本7.1开始,该功能一直存在,所以基本上总是如此。



索引



上述查询的完美索引将是一个多列索引,它跨越匹配顺序中的所有三列并具有匹配的排序顺序:[115]


CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);


对于真实世界的应用程序可能过于专业化但如果读取性能至关重要,请使用它。如果查询中有DESC NULLS LAST,请在索引中使用相同的内容,以便Postgres知道排序顺序匹配。


有效性/性能优化



在为每个查询创建定制索引之前,您必须权衡成本和收益。上述指数的潜力很大程度上取决于数据分布


使用索引是因为它提供了预先排序的数据,而在Postgres 9.2或更高版本中,如果索引小于基础表,查询也可以从仅索引扫描中受益。不过,索引必须全部扫描。[116]



  • 对于每个客户的 少数行,这非常有效(如果您还需要排序输出,则更是如此)。每个客户的行数越来越多,收益越来越大
    理想情况下,您有足够的 work_mem 来处理RAM中涉及的排序步骤而不会溢出到磁盘。通常将work_mem 设置为可能会产生不良影响。考虑SET LOCAL对大集合的单一查询。
    EXPLAIN ANALYZE找出你需要多少钱。在排序步骤中提到磁盘:表示需要更多:[117]



    • Linux上PostgreSQL中的配置参数work_mem

    • 使用ORDER BY日期和文本优化简单查询


  • 对于每个客户的 许多行松散索引扫描会更高效,但目前尚未实现Postgres(最高为v10)。

    更快的查询技术来替代它。特别是如果您有一个单独的表来容纳唯一的客户,这是典型的用例。但如果你不这样做: [119] [120]



    • 优化GROUP BY查询以检索每个用户的最新记录

    • 优化分组最大查询

    • 查询每行最后N个相关行




基准



我在Postgres 9.1中有一个简单的基准测试,到2016年已经过时了。所以我为Postgres 9.4和 9.5 运行了一个更好,可重现的新设置,并在另一个中添加了详细结果回答即可。

其它参考2


基准



使用Postgres 9.4 9.5 测试最有趣的候选人,并在purchases 10k中使用 200k行的中间表不同customer_id (每个客户20行)。


对于Postgres 9.5,我有效地为86446个不同的客户进行了第二次测试。请参阅下文(平均每个客户2.3行)。


设置



主表


CREATE TABLE purchases (
  id          serial
, customer_id int  -- REFERENCES customer
, total       int  -- could be amount of money in Cent
, some_column text -- to make the row bigger, more realistic
);


我使用serial(下面添加PK约束)和整数customer_id,因为这是一个更典型的设置。还添加了some_column来弥补通常更多的列。


虚拟数据,PK,索引 - 一个典型的表也有一些死元组:


INSERT INTO purchases (customer_id, total, some_column)    -- insert 200k rows
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,200000) g;

ALTER TABLE purchases ADD CONSTRAINT purchases_id_pkey PRIMARY KEY (id);

DELETE FROM purchases WHERE random() > 0.9; -- some dead rows

INSERT INTO purchases (customer_id, total, some_column)
SELECT (random() * 10000)::int             AS customer_id  -- 10k customers
     , (random() * random() * 100000)::int AS total     
     , 'note: ' || repeat('x', (random()^2 * random() * random() * 500)::int)
FROM   generate_series(1,20000) g;  -- add 20k to make it ~ 200k

CREATE INDEX purchases_3c_idx ON purchases (customer_id, total DESC, id);

VACUUM ANALYZE purchases;


customer表 - 用于高级查询


CREATE TABLE customer AS
SELECT customer_id, 'customer_' || customer_id AS customer
FROM   purchases
GROUP  BY 1
ORDER  BY 1;

ALTER TABLE customer ADD CONSTRAINT customer_customer_id_pkey PRIMARY KEY (customer_id);

VACUUM ANALYZE customer;


在我的第二次测试中,我使用了相同的设置,但是random() * 100000生成customer_id,每customer_id只获得几行。


purchases

的对象大小

使用此查询生成。[125]


               what                | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 20496384 | 20 MB        |           102
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    24576 | 24 kB        |             0
 table_size_incl_toast             | 20529152 | 20 MB        |           102
 indexes_size                      | 10977280 | 10 MB        |            54
 total_size_incl_toast_and_indexes | 31506432 | 30 MB        |           157
 live_rows_in_text_representation  | 13729802 | 13 MB        |            68
 ------------------------------    |          |              |
 row_count                         |   200045 |              |
 live_tuples                       |   200045 |              |
 dead_tuples                       |    19955 |              |


查询



1。 row_number()在CTE中,(见其他答案)



WITH cte AS (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   )
SELECT id, customer_id, total
FROM   cte
WHERE  rn = 1;


2。 row_number()在子查询中(我的优化)



SELECT id, customer_id, total
FROM   (
   SELECT id, customer_id, total
        , row_number() OVER(PARTITION BY customer_id ORDER BY total DESC) AS rn
   FROM   purchases
   ) sub
WHERE  rn = 1;


3。 DISTINCT ON(见其他答案)



SELECT DISTINCT ON (customer_id)
       id, customer_id, total
FROM   purchases
ORDER  BY customer_id, total DESC, id;


4。带LATERAL子查询的rCTE(见这里)



WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT id, customer_id, total
   FROM   purchases
   ORDER  BY customer_id, total DESC
   LIMIT  1
   )
   UNION ALL
   SELECT u.*
   FROM   cte c
   ,      LATERAL (
      SELECT id, customer_id, total
      FROM   purchases
      WHERE  customer_id > c.customer_id  -- lateral reference
      ORDER  BY customer_id, total DESC
      LIMIT  1
      ) u
   )
SELECT id, customer_id, total
FROM   cte
ORDER  BY customer_id;


5。 customer表与LATERAL(见这里)



SELECT l.*
FROM   customer c
,      LATERAL (
   SELECT id, customer_id, total
   FROM   purchases
   WHERE  customer_id = c.customer_id  -- lateral reference
   ORDER  BY total DESC
   LIMIT  1
   ) l;


6。 array_agg() ORDER BY(见其他答案)



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


结果



上述查询的执行时间为EXPLAIN ANALYZE(所有选项关闭),最好5次。


所有查询在purchases2_3c_idx上使用仅索引扫描(以及其他步骤)。其中一些只是针对较小的索引,其他更有效。


一种。 Postgres 9.4有200k行和~20 [[/93]]



1. 273.274 ms  
2. 194.572 ms  
3. 111.067 ms  
4.  92.922 ms  
5.  37.679 ms  -- winner
6. 189.495 ms


B中。与Postgres 9.5相同



1. 288.006 ms
2. 223.032 ms  
3. 107.074 ms  
4.  78.032 ms  
5.  33.944 ms  -- winner
6. 211.540 ms  


℃。与B相同,但每customer_id

约2.3行

1. 381.573 ms
2. 311.976 ms
3. 124.074 ms  -- winner
4. 710.631 ms
5. 311.976 ms
6. 421.679 ms





2011年的原始(过时)基准



我在一个65579行的实际生命表上运行了PostgreSQL 9.1 的三个测试,并在所涉及的三个列中的每一列上运行了单列btree索引,并且最佳执行时间为5运行。结果
比较@OMGPonies第一个查询( A )与上述DISTINCT ON解决方案( B ): [[



  1. 选择整个表格,在这种情况下结果为5958行。


    A: 567.218 ms
    B: 386.673 ms
    

  2. 使用条件WHERE customer BETWEEN x AND y产生1000行。


    A: 249.136 ms
    B:  55.111 ms
    

  3. 使用WHERE customer = x选择单个客户。


    A:   0.143 ms
    B:   0.072 ms
    



用另一个答案中描述的索引重复相同的测试


CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);






1A: 277.953 ms  
1B: 193.547 ms

2A: 249.796 ms -- special index not used  
2B:  28.679 ms

3A:   0.120 ms  
3B:   0.048 ms

其它参考3


这是常见的每组最大问题,已经有经过充分测试和高度优化的解决方案。我个人更喜欢Bill Karwin的左连接解决方​​案(原始帖子有很多其他解决方案)。/questions/tagged/greatest-n-per-group


请注意,对于这个常见问题的大量解决方案可以在大多数官方来源中找到, MySQL手册!请参阅常见查询示例::保持某一列的分组最大值的行。[137]

其它参考4


在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

  • array_agg允许你指定一个作用于它自己的排序,所以它不会约束整个查询的结构。如果你需要做一些不同的事情,还有如何排序NULL的语法。默认值。

  • 构建数组后,我们采用第一个元素。 (Postgres数组是1索引的,而不是0索引的。)

  • 您可以以类似的方式使用array_agg作为第三个输出列,但max(total)更简单。

  • DISTINCT ON不同,使用array_agg可以保留GROUP BY,以防出于其他原因。


其它参考5


由于存在SubQ,Erwin指出的解决方案效率不高


select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;

其它参考6


我用这种方式(仅限postgresql):https://wiki.postgresql.org/wiki/First/last_%28aggregate%29 [138]


-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.last (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);


那么你的例子应该按原样运行 :


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


CAVEAT:忽略s NULL行





编辑1 - 改为使用postgres扩展



现在我用这种方式:http://pgxn.org/dist/first_last_agg/[139]


要在ubuntu 14.04上安装:


apt-get install postgresql-server-dev-9.3 git build-essential -y
git clone git://github.com/wulczer/first_last_agg.git
cd first_last_app
make && sudo make install
psql -c 'create extension first_last_agg'


这是一个postgres扩展,为您提供第一个和最后一个功能;显然比上述方式更快。





编辑2 - 订购和过滤



如果使用聚合函数(如这些),则可以对结果进行排序,而无需已经订购数据:


http://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-AGGREGATES


所以等效的例子,有了排序,就像这样:


SELECT first(id order by id), customer, first(total order by id)
  FROM purchases
 GROUP BY customer
 ORDER BY first(total);


当然,您可以按照您认为适合的方式订购和过滤;它的语法非常强大。

其它参考7


很快的解决方案


SELECT a.* 
FROM
    purchases a 
    JOIN ( 
        SELECT customer, min( id ) as id 
        FROM purchases 
        GROUP BY customer 
    ) b USING ( id );


如果表由id索引,那么非常快


create index purchases_id on purchases (id);

其它参考8


查询:


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),返回给定name和id的值
为了证明给定的总数(T(客户,身份))是最高的,我们必须证明这一点
我们想要证明



  • ∀xT(客户,身份证)> T(客户,x)(此总额高于其他所有人
    该客户的总数)



要么



  • ¬∃xT(customer,id)< T(客户,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


这就是我们需要的答案。

其它参考9


接受的OMG小马由任何数据库支持解决方案具有良好的测试速度。


在这里,我提供了一个相同的方法,但更完整,更干净的任何数据库解决方案。考虑关系(假设希望每个客户只获得一行,甚至每个客户的最大总数为多个记录),并且将为购买表中的实际匹配行选择其他购买字段(例如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


此查询相当快,尤其是在购买表上存在类似(客户,总计)的复合索引时。


备注:



  1. t1,t2是子查询别名,可根据数据库删除。

  2. 警告:2017年1月编辑时,MS-SQL和Oracle数据库中不支持using (...)子句。您必须自己将其扩展为例如on t2.id = purchase.id等USING语法适用于SQLite,MySQL和PostgreSQL。