sql - 如何从位置/时间戳 SQL 数据流创建 SQL 位置时间表

Question

我有一个在 SQL 中遇到的问题。

我目前有一系列位置和时间戳数据。它由位于不同时间戳位置的设备组成。这些位置是重复的，所以虽然它们是纬度/经度坐标，但有几个重复。时间戳以不规则的间隔出现（有时每秒多次，有时 30 秒内没有）。例如，请参见下面的代表性数据（在此示例中我按设备名称排序，但如果有帮助，可以按任何排序）：

Device     Location     Timestamp
X          A            1
X          A            1.7
X          A            2
X          A            3
X          B            4
X          B            5.2
X          B            6
X          A            7
X          A            8
Y          A            2
Y          A            4
Y          C            6
Y          C            7

我希望根据上述数据创建一个表格，该表格将显示每个位置的进入/退出或第一次/最后一次，以及该实例的总持续时间。IE：

Device     Location     EntryTime     ExitTime     Duration
X          A            1             3            2
X          B            4             6            2
X          A            7             8            1
Y          A            2             4            2
Y          C            6             7            1

例如，从这里我可以进一步处理它以计算出给定日期的总位置时间。

这是我可以在 Python 或其他语言中使用诸如 while 循环之类的东西来做的事情，但我真的不确定如何在 SQL 中完成此操作。可能值得注意的是，这是在 Azure SQL 中，我通过流分析查询创建此表到事件中心实例。我不想简单地在一个位置汇总所有内容的原因是因为它将是流数据并滚动显示，例如最后 24 小时。

任何关于我如何完成此任务的提示、提示或技巧将不胜感激。我已经看过并且无法完全找到我正在寻找的东西 - 我可以看到诸如 datediff 用于计算两个时间戳之间的持续时间，或者 max 和 min 用于查找第一个和最后一个日期，但似乎没有一个方框里打勾。我在这里遇到的挑战是设备在此期间多次移动并返回相同的位置。例如，将设备 X 在位置 A 的第一次出现/时间戳从最后一个中减去，并没有考虑到在这些时间戳之间它可能已经到达的其他位置。更复杂的是，时间戳是不规则的，所以我不能简单地计算每个位置的出现次数并将它们加起来。

也许我错过了一些简单或明显的东西，但这让我很难过！帮助将不胜感激:)

标签： sqltimestamplocationazure-sql-databaseazure-stream-analytics

Answer 1

我相信分组会起作用

SELECT Device, Location, [EntryTime] = MIN(Timestamp), [ExitTime] = Max(Timestamp), [Duration] = MAX(Timestamp)- MIN(Timestamp)
FROM <table>
GROUP BY Device, Location