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

顾名思义,我想选择以GROUP BY分组的每组行的第一行。

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

SELECT * FROM purchases;

我的输出:

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

我想查询每个customer购买的最大商品的idtotal )。像这样:

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

答案

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

SELECT <b>DISTINCT ON</b> (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 <b>NULLS LAST</b>, id;

要点

  • DISTINCT ON是该标准的 PostgreSQL 扩展(仅在整个SELECT列表上定义了DISTINCT )。

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

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

    大胆强调我的。

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

    “从每个组中选择id最小的行,共享total最大的行。”

    要以与确定每个组第一个排序顺序不同的排序方式来对结果进行排序,可以将上面的查询嵌套在另一个ORDER BY的外部查询中。喜欢:

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

  • SELECT列表不受DISTINCT ONORDER BY中的表达式的任何限制。 (在上面的简单情况下不需要):

    • 不必DISTINCT ONORDER BY包含任何表达式。

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

  • 我使用 Postgres 8.3 – 12 版进行了测试。但是至少从 7.1 版开始,该功能就存在了,因此基本上总是如此。

指数

上面查询的理想索引是一个多列索引,它以匹配顺序和匹配的排序顺序跨越所有三列:

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

可能太专业了。但是,如果特定查询的读取性能至关重要,请使用它。如果查询中具有DESC NULLS LAST ,则在索引中使用相同的字符,以便排序顺序匹配并且索引适用。

效果 / 性能优化

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

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

基准测试

我这里有一个简单的基准,现在已经过时了。 在这个单独的答案中,我用详细的基准代替了它。

在 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

基准测试

使用 Postgres 9.49.5测试中最有趣的候选者,该过程中包含一张purchases200k 行10k 个不同的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;

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

purchases对象大小

使用此查询生成。

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. CTE 中的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 customer表( 请参阅此处

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 (其它步骤之间)。其中一些只是针对较小的索引大小,而其他一些则更有效。

A. Postgres 9.4,具有 20 万行,每个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

相关基准

这是在Postgres 11.5 (截至 2019 年 9 月)上通过1000 万行和 6 万个唯一 “客户”进行的 “ogr” 测试得出的新结果。结果仍然符合我们到目前为止所看到的:

2011 年的原始(过时)基准

我使用 PostgreSQL 9.1在实际的 65579 行表和涉及的三列中的每一列上的单列 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

这是常见问题,该问题已经过测试和高度优化 。就我个人而言,我更喜欢Bill Karwin左联接解决方案带有许多其他解决方案原始帖子 )。

注意,在大多数官方资料之一MySQL 手册中 ,可以惊奇地找到许多针对这个常见问题的解决方案!请参阅常见查询的示例:: 持有特定列的按组最大值的行

在 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允许您指定array_agg于自身的排序,因此它不会限制整个查询的结构。如果需要执行一些与默认值不同的操作,则还提供了有关如何对 NULL 进行排序的语法。
  • 构建数组后,我们将获取第一个元素。 (Postgres 数组是 1 索引的,而不是 0 索引的)。
  • 您可以以类似的方式将array_agg用于第三输出列,但max(total)更简单。
  • DISTINCT ON不同,使用array_agg可使您保留GROUP BY ,以防其他原因。

由于存在 SubQ,该解决方案不是十分有效,正如 Erwin 指出的那样

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

快速解决方案

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

我使用这种方式(仅适用于 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);

当然,您可以按自己认为合适的顺序进行排序和过滤。这是非常强大的语法。

查询:

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))是最高的,我们必须证明我们想证明

  • ∀xT(客户,id)> T(客户,x)(此总和高于该客户的所有其他总和)

要么

  • ¬∃T(客户,id)

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

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


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

这就是我们需要的答案。

PostgreSQLU-SQLIBM DB2Google BigQuery SQL使用ARRAY_AGG函数:

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