mysql - 如何在mysql中获取前10行的总和？

Question

是否可以获取当前行的最后 10 行值的总和？

我为一家商店创建了一个数据库，其中包含一个名为 purchase_details 的表。该表的结构是:

+--------------------------------+---------------+-----+
| Field                          | Type          | Key |
+--------------------------------+---------------+-----+
| Trans_ID                       | int(11)       | PRI |
| Dealer_Name                    | varchar(40)   |     |
| Todays_Purchase                | double(18,10) |     |
| Total_Purchase_In_Last_10_Days | double(18,10) |     |
+--------------------------------+---------------+-----+

示例数据:

+----------+-------------+-----------------+--------------------------------+
| Trans_ID | Dealer_Name | Todays_Purchase | Total_Purchase_In_Last_10_Days |
+----------+-------------+-----------------+--------------------------------+
|        1 | Rahul       | 7769.1488285639 |                           NULL |
|        2 | Rahul       | 4158.5117578537 |                           NULL |
|        3 | Rahul       | 7200.1363099802 |                           NULL |
|        4 | Rahul       | 9338.8341269511 |                           NULL |
|        5 | Rahul       | 5897.7252866370 |                           NULL |
|        6 | Rahul       | 3266.6656585172 |                           NULL |
|        7 | Rahul       | 3188.0742696276 |                           NULL |
|        8 | Rahul       | 4270.5917314234 |                           NULL |
|        9 | Rahul       | 2604.3369713541 |                           NULL |
|       10 | Rahul       | 7908.6014441989 |                           NULL |
|       11 | Rahul       | 2693.4584823737 |                           NULL |
|       12 | Rahul       | 7945.7825034862 |                           NULL |
|       13 | Rahul       | 1904.1472157570 |                           NULL |
|       14 | Rajesh      | 7093.0478540344 |                           NULL |
|       15 | Rajesh      | 3219.3736989638 |                           NULL |
+----------+-------------+-----------------+--------------------------------+

我想获得最近 10 笔交易中完成的购买总和，条件是至少要总结 10 笔交易。

预期输出:

+----------+-------------+-----------------+--------------------------------+
| Trans_ID | Dealer_Name | Todays_Purchase | Total_Purchase_In_Last_10_Days |
+----------+-------------+-----------------+--------------------------------+
|        1 | Rahul       | 7769.1488285639 |                   0.0000000000 |
|        2 | Rahul       | 4158.5117578537 |                   0.0000000000 |
|        3 | Rahul       | 7200.1363099802 |                   0.0000000000 |
|        4 | Rahul       | 9338.8341269511 |                   0.0000000000 |
|        5 | Rahul       | 5897.7252866370 |                   0.0000000000 |
|        6 | Rahul       | 3266.6656585172 |                   0.0000000000 |
|        7 | Rahul       | 3188.0742696276 |                   0.0000000000 |
|        8 | Rahul       | 4270.5917314234 |                   0.0000000000 |
|        9 | Rahul       | 2604.3369713541 |                   0.0000000000 |
|       10 | Rahul       | 7908.6014441989 |               55602.6263900000 |
|       11 | Rahul       | 2693.4584823737 |               50526.9360400000 |
|       12 | Rahul       | 7945.7825034862 |               54314.2067800000 |
|       13 | Rahul       | 1904.1472157570 |               49018.2176900000 |
|       14 | Rajesh      | 7093.0478540344 |                   0.0000000000 |
|       15 | Rajesh      | 3219.3736989638 |                   0.0000000000 |
+----------+-------------+-----------------+--------------------------------+

为此，我创建了一个 mysql 函数，它将 Trans_ID 和 Dealer_Name 作为参数，并返回 Todays_Purchase 列的总和。

函数定义:

CREATE FUNCTION GET_TOTAL_PURCHASE(paramTransID INT, paramDealerName VARCHAR(40))
         RETURNS DOUBLE(18,10)
    READS SQL DATA
BEGIN
    DECLARE totalPurchase DOUBLE(18,10);
    SET totalPurchase = 0;
    SELECT SUM(Todays_Purchase)
        INTO totalPurchase
        FROM purchase_details
        WHERE Trans_ID > (paramTransID-10)
          AND Trans_ID <= paramTransID
          AND Dealer_Name = paramDealerName;
    RETURN totalPurchase;
END

更新 Total_Purchase_In_Last_10_Days 列的 SQL 查询是:

UPDATE purchase_details
SET Total_Purchase_In_Last_10_Days = GET_TOTAL_PURCHASE(Trans_ID, Dealer_Name);

以上SQL运行正常，但执行时间过长。表中有超过一百万条记录，所以查询需要5分钟多的时间。锄改善这个？

Answer 1

派生信息(例如您要求的信息)在 SELECTs 中正确完成，而不是通过在表中使用冗余代码。

如果一个用户提取他的信息，即时计算总和将相当便宜。而且您已经拥有执行此操作的 FUNCTION。

但是，您真的可以相信 Trans_ID 是连续的、没有间隙等吗？您的命名法不一致:“_Last_10_Days”与“前 10 行”与“Trans_”。如果有差距，“10 天”可能会很棘手。等等