首页 文章

选择每个GROUP BY组中的第一行?

提问于
浏览
976

正如 Headers 所示,我想选择与 GROUP BY 分组的每组行的第一行 .

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

SELECT * FROM purchases;

My Output:

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

我想查询每个 customer 所做的最大购买( total )的 id . 像这样的东西:

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

Expected Output:

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

11 回答

  • 42

    在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
    
  • 878

    PostgreSQL 中,这通常是 simpler and faster (下面有更多的性能优化):

    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(赢得't hurt either way, but you' ll想要匹配现有索引):

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

    重点

    • DISTINCT ON是该标准的PostgreSQL扩展(其中只定义了整个 SELECT 列表中的 DISTINCT ) .

    • 列出 DISTINCT ON 子句中的任意数量的表达式,组合的行值定义重复项 . The manual:

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

    大胆强调我的 .

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

    “从共享最高 total 的每个组中选择最小 id 的行 . ”

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

    • PostgreSQL sort by datetime asc, null first?

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

    • 您不必在 DISTINCT ONORDER BY 中包含任何表达式 .

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

    • 我测试了Postgres版本8.3 - 11.但是至少从版本7.1开始,该功能已经存在,所以基本上总是如此 .

    索引

    上述查询的完美索引是multi-column index,它匹配匹配序列中的所有三列并具有匹配的排序顺序:

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

    可能太专业了 . 但是,如果特定查询的读取性能至关重要,请使用它 . 如果查询中包含 DESC NULLS LAST ,请在索引中使用相同的内容,以便排序顺序匹配且索引适用 .

    有效性/性能优化

    在为每个查询创建定制索引之前,权衡成本和收益 . 上述指数的潜力很大程度上取决于 data distribution .

    使用索引是因为它提供了预先排序的数据 . 在Postgres 9.2或更高版本中,如果索引小于底层,则查询也可以从 index only scan 中受益表 . 但是,索引必须完整扫描 .

    • 对于 few rows per customer (列 customer 中的高基数),这非常有效 . 如果你还需要分类输出,那就更是如此了 . 随着每个客户的行数越来越多,收益越来越大 .
      理想情况下,您有足够的work_mem来处理RAM中涉及的排序步骤而不会溢出到磁盘 . 但通常设置太高可能会产生不利影响 . 考虑 SET LOCAL 用于异常大的查询 . 通过 EXPLAIN ANALYZE 查找您需要多少 . 在排序步骤中提到“磁盘:”表示需要更多:

    • Configuration parameter work_mem in PostgreSQL on Linux

    • Optimize simple query using ORDER BY date and text

    • 对于 many rows per customer (列 customer 中的低基数),loose index scan(又名"skip scan")会更高效,但是直到Postgres 11还没有实现 . (Postgres 12计划实现仅索引扫描 . 请参阅herehere . )
      现在,有 faster query techniques 替代这个 . 特别是如果您有一个单独的表来容纳唯一的客户,这是典型的用例 . 但如果你不这样做:

    • Optimize GROUP BY query to retrieve latest record per user

    • Optimize groupwise maximum query

    • Query last N related rows per row

    基准

    我在这里有一个简单的基准,现在已经过时了 . 我用detailed benchmark in this separate answer替换了它 .

  • 93

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

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

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

    -- 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:忽略NULL行


    编辑1 - 改为使用postgres扩展名

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

    要在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);
    

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

  • 21

    基准

    使用Postgres 9.49.5 测试最有趣的候选人,在 purchases10k distinct customer_id (每个客户平均20行)中使用 200k rows 的中间表 .

    对于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;
    

    在我的 second test for 9.5中,我使用相同的设置,但使用 random() * 100000 生成 customer_id ,每个 customer_id 只获得几行 .

    表购买的对象大小

    生成this query .

    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 |              |
    

    查询

    CTE中的

    1. row_number(),(参见其他答案)

    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.与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;
    

    带有ORDER BY的

    6. array_agg()(参见其他答案)

    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 上使用 Index Only Scan (以及其他步骤) . 其中一些只是针对较小的索引,其他更有效 .

    A. Postgres 9.4有200k行,每个customer_id约20

    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
    

    C.与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年的原始(过时)基准

    我在PostgreSQL 9.1 上运行了三个测试,在一个包含65579行的实际表中,并且在所涉及的三列中的每一列上都有单列btree索引,并且执行了5次运行的最佳执行时间 .
    @OMGPonies'第一个查询( A )与above DISTINCT ON solutionB )进行比较:

    • 选择整个表,在这种情况下产生5958行 .
    A: 567.218 ms
    B: 386.673 ms
    
    • 使用条件 WHERE customer BETWEEN x AND y 产生1000行 .
    A: 249.136 ms
    B:  55.111 ms
    
    • 使用 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
    
  • 2

    这是常见的greatest-n-per-group问题,已经过很好的测试和高度optimized solutions . 我个人更喜欢left join solution by Bill Karwinoriginal post with lots of other solutions) .

    请注意,对于这个常见问题的一堆解决方案可以在大多数官方消息来源中找到令人惊讶的结果, MySQL manual !见Examples of Common Queries :: The Rows Holding the Group-wise Maximum of a Certain Column .

  • 5

    在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 ,以防出于其他原因 .

  • 11
    • 如果要从聚合行集中选择任何(通过某些特定条件)行 .

    • 如果除了 max/min 之外还想使用另一个( sum/avg )聚合函数 . 所以你不能用 DISTINCT ON 的线索

    您可以使用下一个子查询:

    SELECT  
        (  
           SELECT **id** FROM t2   
           WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount )   
        ) id,  
        name,   
        MAX(amount) ma,  
        SUM( ratio )  
    FROM t2  tf  
    GROUP BY name
    

    您可以使用一个限制将 amount = MAX( tf.amount ) 替换为您想要的任何条件:此子查询不得返回不止一排

    但如果你想做这样的事情你可能正在寻找window functions

  • 0

    查询:

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

    HOW DOES THAT WORK! (我去过那里)

    我们希望确保每次购买的总数最高 .


    Some Theoretical Stuff (如果您只想了解查询,请跳过此部分)

    设Total为函数T(customer,id),返回给定名称和id的值为了证明给定的总数(T(customer,id))是最高的,我们必须证明我们要证明

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

    要么

    • ¬∃xT(客户,身份证)<T(客户,x)(该客户没有更高的总额)

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

    第二个将需要一个聪明的方式来说没有比这个更高的记录 .


    Back to 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
    

    这就是我们需要的答案 .

  • 856

    被接受的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
    

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

    备注:

    • t1,t2是子查询别名,可以根据数据库删除它们 .

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

  • 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);
    

相关问题