mysql - 如何对MySQL表进行分组计算每个表的总行进距离之间的差异

Question

我在每一行都有一个(非常大的)表格，存储了总行程和当前日期。所以我需要一个适合大量记录的解决方案。例如在 2014-10-03 09:00:00 我走了 1 公里，然后我将当前日期和 2101.00km 插入到 totalTrip 字段(totalTrip 从 2100 开始，可以从任何数字开始)。下一个小时，我又走了 5 公里，并将 totalTrip 存储为 2105.00 公里。第二天，我又走了 10 公里，并将日期 2014-10-04 05:00:00 和总行程插入数据库 2115.00 公里。因此，我需要按天对行进行分组，并且只获取每个特定日期的公里数。我怎样才能以最短和有效的方式做到这一点，并且可以通过使用“group by”来做到这一点？我希望我能解释一下我的情况。

这里(样本)我有什么:

id     date                    totalTrip
1      2014-10-03 09:00:00     2100.23
2      2014-10-03 10:00:00     2102.33
3      2014-10-04 05:00:00     2112.35
4      2014-10-05 02:00:00     2120.37
5      2014-10-05 03:00:00     2140.41
6      2014-10-05 05:00:00     2155.45

这是我想要的(示例):

date         traveledDistance
2014-10-03   2.10
2014-10-04   10.03
2014-10-05   43.10

Answer 1

  DROP TABLE IF EXISTS my_table;

  CREATE TABLE my_table
  (id     INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,date                    DATETIME NOT NULL
  ,totalTrip DECIMAL(7,2) NOT NULL
  );

  INSERT INTO my_table VALUES
  (1      ,'2014-10-03 09:00:00',     2100.23),
  (2      ,'2014-10-03 10:00:00',     2102.33),
  (3      ,'2014-10-04 05:00:00',     2112.35),
  (4      ,'2014-10-05 02:00:00',     2120.37),
  (5      ,'2014-10-05 03:00:00',     2140.41),
  (6      ,'2014-10-05 05:00:00',     2155.45);

  SELECT DATE(a.date) date
       , SUM(a.diff) total 
    FROM 
       ( SELECT y.*
              , MIN(y.totaltrip)-x.totaltrip diff 
           FROM my_table x 
           JOIN my_table y 
             ON y.id > x.id 
          GROUP 
             BY x.id
       ) a 
   GROUP 
      BY DATE(a.date);
  +--------------+-------+
  | date         | total |
  +--------------+-------+
  | 2014-10-03   |  2.10 |
  | 2014-10-04   | 10.02 |
  | 2014-10-05   | 43.10 |
  +--------------+-------+

解释:每行的行进距离从其最近的后续行的行进距离中减去 MIN(y.totaltrip)-x.totaltrip。之后，所有这些中间结果按日期汇总。就这样。

请注意，如果“非常大”实际上是指“非常、非常大”(非常不可能)，那么使用变量的解决方案会明显更快。