mysql - 问：有效地获取大表中每组的最旧值、最新值和计数？

Question

我希望提高我的查询性能/或更有效的查询设计，以获取 mysql 表中每个组的最小值、最大值和计数。

我需要的输出是：

+---------+----------+----------+-----+
|      id | f_amount | r_amount | cnt |
+---------+----------+----------+-----+
|       1 |     1000 |      200 |   3 |
|       2 |      300 |      300 |   1 |
|       3 |      450 |      600 |   2 |
+---------+----------+----------+-----+

其中 f_amount 是最旧的金额，r_amount 是最近的金额，cnt 是该特定 ID 的交易数量。

我的查询[得到了预期的结果，但速度极慢]。我的表有近 10 亿条记录，每个 id 本身都有数千个事务，所有数据都在 MySQL 中。

我无法使用公用表表达式来实现相同的功能。

SELECT     x.fund_id AS id, 
           min_amt AS f_amount, 
           max_amt AS r_amount, 
           z.cnt 
FROM       ( 
                  SELECT fund_id, 
                         amount AS min_amt, 
                         dt 
                  FROM   trans
                  WHERE  dt = 
                         ( 
                                SELECT Min(dt) 
                                FROM   trans g 
                                WHERE  g.fund_id = trans.fund_id)) x 
INNER JOIN 
           ( 
                  SELECT fund_id, 
                         amount AS max_amt, 
                         dt 
                  FROM   trans
                  WHERE  dt = 
                         ( 
                                SELECT Max(dt) 
                                FROM   trans g 
                                WHERE  g.fund_id = trans.fund_id)) y 
INNER JOIN 
           ( 
                    SELECT   fund_id, 
                             Count(fund_id) AS cnt 
                    FROM     trans g 
                    GROUP BY 1) z 
where      x.fund_id = y.fund_id 
AND        x.fund_id = z.fund_id 
ORDER BY   x.fund_id;

表创建和示例数据插入：

CREATE TABLE trans (
  fund_id int,
  amount int,
  dt date);



insert into trans values(1,1000,'2019-02-01');
insert into trans values(1,500,'2019-02-02');
insert into trans values(1,200,'2019-02-03');
insert into trans values(2,300,'2019-02-15');
insert into trans values(3,450,'2019-02-17');
insert into trans values(3,600,'2019-02-20');

标签： mysqlsql

Answer 1

查看您的代码和数据 .. 似乎您需要

SELECT fund_id, Max(amount) , min(amount), count(*)
FROM   trans
group by fund_id