时间戳

首页 > 解决方案 > SQL - 行之间的时间差，有条件

mysql - SQL - 行之间的时间差，有条件

问题描述

我有一个用于记录小狗小便/便便时间的数据库。每行包含一个时间戳datetime、布尔值pee和poo。

我正在尝试为自上次小便/便便以来的时间添加一列。本专栏的详细版本是：

减去这一行的datetime

从datetime行的

最高datetime低于这一行的datetime

它的poo值为 1

基本查询是：

SELECT 
    d.`datetime`,
    d.`poo`,
    d.`pee`/*,
    column with time since last poo as `poo_diff`,
    column with time since last pee as `pee_diff` */
FROM 
    `diary` d
WHERE 
    d.`user_id`=3
    AND 
    (d.`poo`=1 OR d.`pee`=1)
    AND 
    d.`datetime` >= DATE_ADD(CURDATE(), INTERVAL - 7 DAY)
    AND 
    d.`datetime` <= CURDATE();

我尝试使用LAG，但它的条件是找到具有适用布尔值的最后一行。例如，如果这些是查询的结果：

查询结果示例

...然后LAG将不起作用，poo_diff因为差异应该来自最近有 apoo的行1。

如何添加此列？

标签： mysqlsqldatabase

解决方案

您应该能够使用滚动变量获得所需的值：

SET @last_poo = '';
SET @last_pee = '';

SELECT 
    d.`datetime`,
    d.`pee`,
    SEC_TO_TIME(IF(@last_poo AND d.`poo`, TIMESTAMPDIFF(SECOND, @last_poo, d.`datetime`), 0)) AS `poo_diff`,
    SEC_TO_TIME(IF(@last_pee AND d.`pee`, TIMESTAMPDIFF(SECOND, @last_pee, d.`datetime`), 0)) AS `pee_diff`,
    IF(d.`poo` AND @last_poo:= d.`datetime`, d.`poo`, d.`poo`) AS `poo`,
    IF(d.`pee` AND @last_pee:= d.`datetime`, d.`pee`, d.`pee`) AS `pee`
FROM 
    `diary` d
WHERE 
    d.`user_id`=3
    AND 
    (d.`poo`=1 OR d.`pee`=1)
    AND 
    d.`datetime` >= DATE_ADD(CURDATE(), INTERVAL - 7 DAY)
    AND 
    d.`datetime` <= CURDATE()
ORDER BY d.`datetime` ASC;

在这种情况下，@last_poo并@last_pee存储各自事件的最后一个日期时间，同时迭代选定的行。

请注意，如果您这样做是行不通ORDER BY ... DESC的，而且我还必须交换一些列，以便在计算差异后有地方更新变量。

最后，我正在使用，SEC_TO_TIME(... TIMESTAMPDIFF(...))因为否则它似乎会产生一些奇怪的值，例如03:00:00.00000

推荐阅读