sql取第一个值 - sql最新时间




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

正如标题所示,我想选择用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

基准

使用Postgres 9.49.5测试最有趣的候选人, purchases中包含200k行的中间真实表格, 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. 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.使用ORDER BY 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 (以及其他步骤)。 其中一些仅用于索引的较小尺寸,另一些则更有效。

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

我在包含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

在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中,你可以像这样使用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 ,以防其他原因需要使用。

快速解决方案

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

如果table被id索引,真的非常快:

create index purchases_id on purchases (id);

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

此查询速度相当快,特别是在购买表上存在像(客户,总计)这样的复合索引时。

备注:

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

  2. 注意 :截至2017年1月,在此编辑中, using (...)子句目前不支持MS-SQL和Oracle数据库。您必须on t2.id = purchase.id将其扩展到例如on t2.id = purchase.idon t2.id = purchase.id语法适用于SQLite,MySQL和PostgreSQL。


查询:

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)(该总数高于该客户的所有其他总数)

要么

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

这就是我们需要的答案。


这是常见的greatest-n-per-group问题,它已经经过了充分测试和高度优化的解决方案 。 就个人而言,我更喜欢Bill Karwin的左连接解决方​​案 ( 带有许多其他解决方案的原始文章 )。

请注意,针对这个常见问题的一堆解决方案可以在MySQL官方最常见的源代码中找到。 查看常见查询的示例::行保存特定列的最大组别数





greatest-n-per-group