mysql - 如何得到减去日期的结果

Question

大家好，我想使用 SQL 查询当前日期减去上一个日期并返回长度的变化(当前长度 - 上一个长度)。请参阅下表中当前减去先前长度的更改示例

我想不出查询该怎么做从 length_data 中选择日期，长度

date                 length          changes
-------------------  --------------  ------------
2000-08-29 10:30:00          147.98  147.98
2000-08-30 00:00:00          147.98  0
2000-09-02 10:30:00          156.51  8.53
2000-09-04 00:00:00          156.51  ....
2000-09-04 04:30:00          156.51  ....
2000-09-04 06:30:00          156.51  ....
2000-09-05 21:00:00          156.51
2000-09-06 03:00:00          156.51
2000-09-07 09:30:00          204.06
2000-09-07 10:30:00          204.06
2000-09-08 00:00:00          339.09
2000-09-08 12:30:00          395.78
2000-09-09 09:30:00          477.77
2000-09-10 02:30:00          737.77

Answer 1

您可以使用变量来模拟 LAG 窗口函数:

SELECT `date`, `length`,
       `length` - @prev AS changes,
       @prev := `length`
FROM length_data, (SELECT @prev := 0) AS a
ORDER BY `date`;

SqlFiddleDemo

我假设 date 是 PRIMARY KEY/UNIQUE 否则它不会稳定。

编辑:

要删除额外的列，您可以使用子查询。

SELECT `date`, `length`, `changes`
FROM (SELECT `date`, `length`,
        `length` - @prev AS changes,
        @prev := `length`
      FROM length_data,(SELECT @prev := 0) as a
      ORDER BY `date`) AS sub
ORDER BY `date`;  

SqlFiddleDemo2

输出:

╔══════════════════════════════╦═════════╦═════════╗
║            date              ║ length  ║ changes ║
╠══════════════════════════════╬═════════╬═════════╣
║ August, 29 2000 10:30:00     ║ 147.98  ║ 147.98  ║
║ August, 30 2000 00:00:00     ║ 147.98  ║ 0       ║
║ September, 02 2000 10:30:00  ║ 156.51  ║ 8.53    ║
║ September, 04 2000 00:00:00  ║ 156.51  ║ 0       ║
║ September, 04 2000 04:30:00  ║ 156.51  ║ 0       ║
║ September, 04 2000 06:30:00  ║ 156.51  ║ 0       ║
║ September, 05 2000 21:00:00  ║ 156.51  ║ 0       ║
║ September, 06 2000 03:00:00  ║ 156.51  ║ 0       ║
║ September, 07 2000 09:30:00  ║ 204.06  ║ 47.55   ║
║ September, 07 2000 10:30:00  ║ 204.06  ║ 0       ║
║ September, 08 2000 00:00:00  ║ 339.09  ║ 135.03  ║
║ September, 08 2000 12:30:00  ║ 395.78  ║ 56.69   ║
║ September, 09 2000 09:30:00  ║ 477.77  ║ 81.99   ║
║ September, 10 2000 02:30:00  ║ 737.77  ║ 260     ║
╚══════════════════════════════╩═════════╩═════════╝

编辑 2:

使用 Jean Doux's idea仅使用一列来计算它:

SELECT `date`, length,
    CAST((-IF(@prev IS NULL,0,@prev) + (@prev := length)) AS DECIMAL(10,4)) AS changes 
FROM length_data,(SELECT @prev := NULL ) AS a
ORDER BY `date`;

SqlFiddleDemo2

或者最简单的方法是跳过应用层的附加列。