Sql-从million+行中获取特定条件的时间范围
Sql-从million+行中获取特定条件的时间范围
提问于 2018-05-31 19:13:13
我正在使用SQL Server2012，我有一个表，大约有35列和10+百万行。我需要从数据中查找时间范围，其中任何特定列的值都是匹配的。例如，示例数据如下
Datetime col1 col2 col3
2018-05-31 0:00 1 2 1
2018-05-31 13:00 2 2 2
2018-05-31 14:30 3 2 1
2018-05-31 15:00 4 3 1
2018-05-31 16:00 4 5 1
2018-05-31 17:00 3 2 2
2018-05-31 17:30 3 2 4
2018-05-31 18:00 2 2 4
2018-05-31 20:00 1 2 6
2018-05-31 21:00 2 2 3
2018-05-31 21:10 2 2 1
2018-05-31 22:00 1 6 3
2018-05-31 22:00 4 5 1
2018-05-31 23:59 4 7 2从col2值为=< 2的数据中找到时间范围，因此我的预期结果集如下所示
Start Time End time Time Diff
2018-05-31 0:00 2018-05-31 14:30 14:30:00
2018-05-31 17:00 2018-05-31 21:10 4:10:00我可以用下面的逻辑实现同样的效果，但它非常慢，我得到所有行，然后
- Order by date_Time
- 扫描各行，获取值完全匹配的第一行，并将该时间戳记录为开始时间。
- 扫描更多的行，直到我得到条件正在被破坏的行，并将该时间戳记录为结束时间。
但当我不得不面对巨大的拒绝时。总体而言，这会使我的操作变慢，任何输入或伪代码的改进都是一样的。
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-05-31 19:29:14
我们可以在这里使用略微修改的行数差值方法。第一个标记为cte1的CTE的目的是添加一个计算列，该列标记我们想要的岛，其col2值为2，<=为1，其他值均为0。然后，我们可以计算两个行数的差，并在岛上聚合以找到开始和结束时间，以及这两个时间之间的差异。
WITH cte1 AS (
SELECT *,
CASE WHEN col2 <= 2 THEN 1 ELSE 0 END AS class
FROM yourTable
),
cte2 AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY Datetime) -
ROW_NUMBER() OVER (PARTITION BY class ORDER BY Datetime) rn
FROM cte1
)
SELECT
MIN(Datetime) AS [Start Time],
MAX(Datetime) AS [End Time],
CONVERT(TIME, MAX(Datetime) - MIN(Datetime)) AS [Time Diff]
FROM cte2
WHERE class = 1
GROUP BY rn
ORDER BY MIN(Datetime);
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50622995
复制相关文章
相似问题
腾讯云开发者
