sql - 根据 SQL 中的连续标志对行进行分组 (Redshift)

Question

我有一个棘手的问题，我想在这里解决，但到目前为止我还无法理解。

所以问题是这样的：我有跟踪数据，其中有随着时间的推移产生的记录。假设您有一个机器人四处行驶，并且您每秒记录一次它的位置。这些位置中的每一个都在数据库中记录为一条记录（我们使用 AWS Redshift）。

每个记录都有一个 tracking_id，它在属于同一跟踪源的所有记录中是唯一的，即对于机器人来说是唯一的。然后我有一个全局唯一的 record_id、一个时间戳和一个标志，该标志指示记录是在机器人位于定义区域内部还是外部时创建的。然后还有一些额外的数据，比如坐标。

这是一个小插图。粉色框是区域，绿线是机器人的路径，蓝点是产生的记录。

所以现在我想根据区域标志对记录进行分组（看看下面的截图）。所以我想将区域内的子路径隔离到一个记录中，并获取开始和结束时间戳和位置。ID 无关紧要，因此即使我将它们列在所需的结果中，我也不一定需要保留跟踪或记录 ID。

感谢您的帮助，我将不胜感激！同样，仅解决部分问题，例如如何根据标志进行分组而不获取子路径中的第一个和最后一个值已经有所帮助。

标签： sqlamazon-redshift

Answer 1

这是一个空白和孤岛问题。在这种情况下，您想要in_zone恰好位于的岛屿TRUE（其中有两个）。我们可以在这里使用行号方法的差异：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY tracking_id ORDER BY timestamp) rn1,
        ROW_NUMBER() OVER (PARTITION BY tracking_id, in_zone ORDER BY timestamp) rn2
    FROM yourTable
)

SELECT
    tracking_id,
    MIN(record_id) AS record_id,
    MIN(timestamp) AS start_timestamp,
    MAX(timestamp) AS end_timestamp,
    (SELECT t2.coordinates FROM yourTable t2
     WHERE t2.record_id = MIN(t1.record_id) AND t2.tracking_id = t1.tracking_id) AS entry_coordinates,
    (SELECT t2.coordinates FROM yourTable t2
     WHERE t2.record_id = MAX(t1.record_id) AND t2.tracking_id = t1.tracking_id) AS exit_coordinates
FROM cte t1
WHERE
    in_zone = 'TRUE'
GROUP BY
    tracking_id,
    rn1 - rn2,
    in_zone
ORDER BY
    tracking_id,
    record_id DESC;

演示