我有这个工作的SQL代码(MySql),它返回一个MState值的秒数,例如。4.
SELECT SUM(Seconds_In_State) From
(SELECT
Time_Stamp,
MState,
(-1 * TIMESTAMPDIFF(Minute, LEAD(Time_Stamp) OVER(ORDER BY Time_Stamp), Time_Stamp))
AS Seconds_In_State
FROM Mstate
WHERE DATE(`Time_Stamp`) = CURDATE()
AND TIME(`Time_Stamp`) >= '08:05' /*ShiftStart input tag*/
ORDER BY Time_Stamp) AS T
Where MState = 4;结果将不包括从08:05到08:10的300秒,其中MState为4。因为我查看的是时间戳> 08:05。但我想包括从08:05到08:10的时间,其中MState为4。
所以它会找出从MState为4的Time_Stamp到下一个时间戳的秒数。从08:05开始,并将它们相加。如果MState 4有时间戳08:00,则不包括在内。但我希望包括08:05到08:10之间的秒数,其中MState为4。因此,如果Time_Stamp >= 08:05之前的MState的值为4,则将08:05到08:10之间的秒数添加到Seconds_In_State中。
数据
|Time_Stamp | MState |
|------------------------|------- |
|2021-04-23 07:50:00 | 3 |
|2021-04-23 08:00:00 | 4 |
|2021-04-23 08:10:00 | 1 |
|2021-04-23 08:22:00 | 2 |
|2021-04-23 08:30:00 | 3 |
|2021-04-23 08:40:00 | 4 |
|2021-04-23 08:50:00 | 1 |
|2021-04-23 09:01:00 | 2 |
|2021-04-23 09:10:00 | 3 |当前代码结果:|SUM(Seconds_In_State) | |600
我想得到的结果是:
SUM(Seconds_In_State)| |900
发布于 2021-04-26 18:42:45
您似乎希望使用lag()和比较来强制执行时间框架:
SELECT SUM(Seconds_In_State)
FROM (SELECT s.*,
TIMESTAMPDIFF(Minute,
GREATEST(LAG(Time_Stamp) OVER (ORDER BY Time_Stamp), addtime(cast(curdate() as datetime), '08:05')),
Time_Stamp
) AS Seconds_In_State
FROM Mstate s
WHERE DATE(Time_Stamp) = CURDATE() AND
TIME(Time_Stamp) >= '08:05' /*ShiftStart input tag*/
) s
WHERE MState = 4
ORDER BY Time_Stamphttps://stackoverflow.com/questions/67264703
复制相似问题