提取 MySQL 5.7 连续值的差异

Question

姓名日期小时计数米尔斯2022-07-172312米尔斯2022-07-180015米尔斯2022-07-180120米尔斯2022-07-180222米尔斯2022-07-180325米尔斯2022-07-180420米尔斯2022-07-180522米尔斯2022-07-180625迈克2022-07-180015迈克2022-07-180120迈克2022-07-180222迈克2022-0

P粉771233336 · Answer

尝试以下操作：

SET @count=(select Count_ from Hourly order by Name,Date_,Hours LIMIT 1);
Set @Name=(select Name from Hourly order by Name,Date_,Hours LIMIT 1);

select  Name,Date_,Hours,Count_,
ABS(curr_count-lag_count) as DiffCount
From
(
  select Name,Date_,Hours,Count_, 
  Case When @Name = Name Then @count Else Count_ End as lag_count
  , @count:=Count_ curr_count, @Name:=Name
  From Hourly order by Name,Date_,Hours
) D
Order By Name,Date_,Hours;

查看来自 db-fiddle 的演示。

P粉569205478 · Answer

在MySQL 5.7中，您可以内联更新变量，以包含更新后的“Count”值。由于当“Name”的值更改时，您需要重置变量，因此您可以使用包含先前“Name”值的另一个变量。然后使用 IF 函数检查：

如果您以前的名字与现在的名字相同
然后计算计数差
否则分配 0

它将与ABS a> 函数，对差值应用绝对值。

SET @cnt  := NULL;
SET @name := NULL;

SELECT Date,
       Hours,
       ABS(IF(@name = Name, 
              @cnt := @cnt - Count,
              0)                    ) AS DiffCount,
       (@name := Name)                AS Name,
       (@cnt := Count)                AS Count
FROM tab
ORDER BY Name DESC,
         Date, 
         Hours;

请在此处查看演示。

在MySQL 8.0中，您可以使用像LAG 顺利获得输出。它将与：

ABS 应用绝对值差异值，
COALESCE 用于删除第一个空值。

SELECT *, 
       COALESCE(ABS(
           Count - LAG(Count) OVER(
                       PARTITION BY Name
                       ORDER     BY Date, Hours
                   )
       ), 0) AS Diff 
FROM tab
ORDER BY Name DESC, 
         Date, 
         Hours

请在此处查看演示。

姓名	日期	小时	计数
米尔斯	2022-07-17	23	12
米尔斯	2022-07-18	00	15
米尔斯	2022-07-18	01	20
米尔斯	2022-07-18	02	22
米尔斯	2022-07-18	03	25
米尔斯	2022-07-18	04	20
米尔斯	2022-07-18	05	22
米尔斯	2022-07-18	06	25
迈克	2022-07-18	00	15
迈克	2022-07-18	01	20
迈克	2022-07-18	02	22
迈克	2022-07-18	03	25
迈克	2022-07-18	04	20