postgresql SQL如何根据当前事件和下一个事件确定动作的持续时间？

qpgpyjmq 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(123)

我有一个包含两列的表：动作开始时间和动作名称。
| 动作| action |
| --| ------------ |
| 运行| running |
| 行走| walking |
| 行走| walking |
| 坐着| sitting |
| 行走| walking |
| 运行| running |
我需要知道行动的持续时间：开始和结束时间。开始时间是动作的时间，这是已知的。
但如何知道结束的时间呢？也就是说，下一个动作的开始与当前动作不同？
动作“行走”的示例
| 时间结束| time_end |
| --| ------------ |
| 2023-07-27 05：01：00.000| 2023-07-27 05:01:00.000 |
| 2023-07-27 05：10：00.000| 2023-07-27 05:10:00.000 |

postgresql

来源：https://stackoverflow.com/questions/76778105/sql-how-to-determine-the-duration-of-the-action-based-on-the-current-and-next-ev

2条答案

按热度按时间

3bygqnnd1#

请看下面的修正版本。这个假设每个动作都是唯一的。
我认为你可以使用滞后函数来实现你的目标。

select 
    action,
    time as time_start,
    lag(time, -1) over(order by time) as time_end
from <table_name>

字符串
在这种情况下，最后一行的 time_end 将等于 NULL，我认为这是正确的，因为它还不知道。

**编辑：**在@Tim Biegeleisen的评论之后，这是基于他的工作的更新答案。但是，它可以正确地找到相同的连续操作组的time_end值。

WITH data AS (
    SELECT '2023-07-27 04:52:00.000' AS time, 'running' AS action UNION ALL
    SELECT '2023-07-27 04:53:00.000', 'running' UNION ALL
    SELECT '2023-07-27 04:55:00.000', 'walking' UNION ALL
    SELECT '2023-07-27 04:59:00.000', 'walking' UNION ALL
    SELECT '2023-07-27 05:01:00.000', 'sitting' UNION ALL
    SELECT '2023-07-27 05:06:00.000', 'walking' UNION ALL
    SELECT '2023-07-27 05:10:00.000', 'walking' UNION ALL
    SELECT '2023-07-27 05:12:00.000', 'walking' UNION ALL
    SELECT '2023-07-27 05:15:00.000', 'walking' UNION ALL
    SELECT '2023-07-27 05:20:00.000', 'running' 
),
withLag AS (
    SELECT 
        action,
        time AS start_time,
        LAG(time) OVER (ORDER BY time desc) AS next_time,
        ROW_NUMBER() OVER (ORDER BY time) AS row_num,
        ROW_NUMBER() OVER (PARTITION BY action ORDER BY time) AS group_num
    FROM data
)
SELECT action, min(start_time) as time_start, max(next_time) as time_end
FROM withLag
GROUP BY action, row_num - group_num
order by time_start

型
因此，我们实现了预期的结果：
x1c 0d1x的数据

赞(0）回复(0）举报 2023-08-04

xwbd5t1u2#

使用行号差异方法，我们可以尝试以下操作：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY time) rn1,
              ROW_NUMBER() OVER (PARTITION BY action ORDER BY time) rn2
    FROM yourTable
)

SELECT MIN(time) AS time_start,
       MAX(time) AS time_end,
       action
FROM cte
GROUP BY rn1 - rn2, action
ORDER BY MIN(time);

字符串

的数据