mysql - 使用 mySQL 查找列中的运行差异

Question

我正在尝试获得以下结果:

     HOUR  Throughput     Difference
       0           298          0
       1           209         -89
       6           1          -208
       7           8            -7
       8            9           1
       9           175          166
       10         223        and so on
       11         260
       12         246
       13         195
       14         170
       15         230
       16         200
       17         301
       18         316
       19         282
       20         293
       21         281
       22         175
       23         244

我目前的sql语句是

    SELECT DISTINCT
    HOUR(p_datetime) as HOUR,
    Count(p_type) as Throughput
    FROM
    product_log
    WHERE
    DATE(p_datetime) = '2013-11-30' AND
    p_type='STACK'
    GROUP BY HOUR;

我已经尝试了一段时间，但找不到解决方案这是在 mysql 中完成的。谢谢!

Answer 1

一种方法

SELECT hour, throughput, difference
  FROM
(
  SELECT hour, throughput, 
         throughput - @p difference, @p := throughput
    FROM
  (
    SELECT HOUR(p_datetime) hour,
           COUNT(*) throughput
      FROM product_log l
     WHERE p_datetime >= '2013-11-30'
       AND p_datetime < '2013-11-31'
       AND p_type = 'stack'
     GROUP BY HOUR(p_datetime)
  ) a CROSS JOIN (SELECT @p := 0) i
) b

这是 SQLFiddle 演示