- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个包含 From
和 To
日期的 SQL 表,如下所示:
Row From To
--------------------------------------------------
1 2017-10-28 00:00:00 2017-10-30 00:00:00
2 2017-10-30 00:00:00 2017-10-31 00:00:00
3 2017-10-31 00:00:00 2017-10-31 07:30:00
4 2017-10-31 14:41:00 2017-10-31 15:14:00
5 2017-10-31 17:13:00 2017-11-01 00:00:00
6 2017-11-01 00:00:00 2017-11-01 23:45:00
7 2017-11-02 03:13:00 2017-11-02 07:56:00
我需要将连续的数据分组到孤岛中。数据不重叠。使用此查询可以轻松完成此操作:
;with Islands as
(
SELECT
min([From]) as [From]
,max([To]) as [To]
FROM
(
select
[From],
[To],
sum(startGroup) over (order by [From]) StartGroup
from
(
SELECT
[From],
[To],
(case when [From] <= lag([To]) over (order by [From])
then 0
else 1
end) as StartGroup
FROM dbo.DateTable
) IsNewIsland
) GroupedIsland
group by StartGroup
)
select *
from Islands
然后给我这些结果:
From To Rows
-----------------------------------------------------
2017-10-28 00:00:00 2017-10-31 07:30:00 1-3
2017-10-31 14:41:00 2017-10-31 15:14:00 4
2017-10-31 17:13:00 2017-11-01 23:45:00 5-6
2017-11-02 03:13:00 2017-11-02 07:56:00 7
我遇到的问题是,一旦岛屿获得足够的记录以达到一定的总持续时间,我就需要修改查询以限制/拆分岛屿。这是一个输入/硬编码值。拆分包括整个记录,而不是在记录的 From-To
范围中间进行拆分。例如,我需要将岛屿拆分为 27 小时。这将给出以下结果:
From To Rows
-----------------------------------------------------
2017-10-29 00:00:00 2017-10-30 00:00:00 1
2017-10-30 00:00:00 2017-10-31 07:30:00 2-3
2017-10-31 17:13:00 2017-11-01 23:45:00 5-6
第一个岛被拆分是因为第 1 行和第 2 行单独创建了 27 小时的时间段。第 4 行和第 7 行不足以创建一个岛,因此将被忽略。
我尝试通过内部选择中的 lag
函数提取此信息以计算跨行的“滚动持续时间”,但它不适用于跨越超过 2 行的孤岛,因为它只会跟踪最后一行的持续时间,我无法“继续”计算。
SELECT
[From],
[To],
(case when [From] <= lag([To]) over (order by [From]
then (datediff(minute, [From], [To]) + lag(datediff(minute, [From], [To])) over (order by [From]))
else datediff(minute, [From], [To])
end) as RollingDuration,
(case when [From] <= lag([To]) over (order by [From])
then 0
else 1
end) as StartGroup
FROM dbo.DateTable
最佳答案
我能想到的“最不糟糕”的方式是“古怪的更新”。 (Google 一下,老实说我没有编造它。)
UPDATE
和用户变量遍历行并存储计算结果如果有差距,或者累计达到 27 小时,我可以使用它来开始一个新组。然后照常进行。
-- New table to work through
----------------------------------------------------------------------
-- Addition [group_start] field (identifies groups, and useful data)
-- PRIMARY KEY CLUSTERED to enforce the order rows will be processed
----------------------------------------------------------------------
CREATE TABLE sample (
id INT,
start DATETIME,
cease DATETIME,
group_start DATETIME DEFAULT(0),
PRIMARY KEY CLUSTERED (group_start, start) -- To force the order we will iterate the rows, and is useful in last step
);
INSERT INTO
sample (
id,
start,
cease
)
VALUES
(1, '2017-10-28 00:00:00', '2017-10-30 00:00:00'),
(2, '2017-10-30 00:00:00', '2017-10-31 00:00:00'),
(3, '2017-10-31 00:00:00', '2017-10-31 07:30:00'),
(4, '2017-10-31 14:41:00', '2017-10-31 15:14:00'),
(5, '2017-10-31 17:13:00', '2017-11-01 00:00:00'),
(6, '2017-11-01 00:00:00', '2017-11-01 23:45:00'),
(7, '2017-11-02 03:13:00', '2017-11-02 07:56:00')
;
-- Quirky Update
----------------------------------------------------------------------
-- Update [group_start] to the start of the current group
-- -> new group if gap since previous row
-- -> new group if previous row took group to 27 hours
-- -> else same group as previous row
----------------------------------------------------------------------
DECLARE @grp_start DATETIME = 0;
WITH
lagged AS
(
SELECT *, LAG(cease) OVER (ORDER BY group_start, start) AS lag_cease FROM sample
)
UPDATE
lagged
SET
@grp_start
= group_start
= CASE WHEN start <> lag_cease THEN start
WHEN start >= DATEADD(hour, 27, @grp_start) THEN start
ELSE @grp_start END
OPTION
(MAXDOP 1)
;
-- Standard SQL to apply other logic
----------------------------------------------------------------------
-- MAX() OVER () to find end time of each group
-- WHERE to filter out any groups under 12 hours long
----------------------------------------------------------------------
SELECT
*
FROM
(
SELECT
*,
MAX(cease) OVER (PARTITION BY group_start) AS group_cease
FROM
sample
)
bounded_groups
WHERE
group_cease >= DATEADD(hour, 12, group_start)
;
http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1bec5b3fe920c1affd58f23a11e280a0
关于SQL Split Island On Criteria,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48014405/
题目地址:https://leetcode.com/problems/number-of-islands/description/ 题目描述 Given a 2d grid map of '1's
我有一个如下表,其中 X 列是给定开始和结束期间的商品价格。 X START_DATE END_DATE ------------------------------ 1 0
我有一个包含 From 和 To 日期的 SQL 表,如下所示: Row From To ---------------------------------
我正在研究如何将 silverlight 合并到我现有的应用程序中,并且不断遇到最新的流行语“富人岛”。什么意思? 谢谢! 最佳答案 安island of richness是页面上的(相对)小区域,包
问题 我有一个 java.awt.Rectangle 数组。对于那些不熟悉此类的人,重要的信息是它们提供了一个 .intersects(Rectangle b) 函数。 我想编写一个函数,它接受这个
谁能解释一下垃圾收集的孤立岛的概念吗? 最佳答案 对象 A 引用对象 B。对象 B 引用对象 A。对象 A 和对象 B 都没有被任何其他对象引用。那是一座孤岛。 基本上,隔离岛是一组相互引用但不被应用
假设我有一个列表列表(或者概念上更准确的二维数组): list = [[1,1,0,0,0], [1,1,2,0,0], [0,2,2,2,0], [0
假设我有一个列表列表(或者概念上更准确的二维数组): list = [[1,1,0,0,0], [1,1,2,0,0], [0,2,2,2,0], [0
我在 Postgres 数据库中有一个这样的表: person | eventdate | type -------------------------------------
按照目前的情况,这个问题不适合我们的问答形式。我们希望答案得到事实、引用或专业知识的支持,但这个问题可能会引发辩论、争论、投票或扩展讨论。如果您觉得这个问题可以改进并可能重新打开,visit the
我有一个待处理的请求,要为我编写的温度日志应用程序编写报告,但我似乎无法确定查询。 应用程序存储温度日志。这些日志每天要在几个不同的地点进行 3 次。每个日志包含一个或多个区域,其中包含一个或多个设备
考虑以下图像,以numpy数组形式存储: a = [[0,0,0,0,0,1,1,0,0,0], [0,0,0,0,1,1,1,1,0,0], [0,0,0,0,0,1,1,0,0
我正在尝试使用 Python 库 Pygmo2 ( https://esa.github.io/pagmo2/index.html ) 来并行化优化问题。 据我了解,并行化可以通过岛屿(在本例中为mp
我有一张由六边形字段组成的 map 。在这张 map 上,每个字段(我们称之为十六进制)都属于某个玩家并由坐标定义。 我需要一个 List>某个玩家的魔咒。这意味着,如果我输入玩家拥有的所有六角形数组
我有一个交替包含 0 和 1 的向量,我想将每个“1”转换为它前面的零的长度。例如,我有 x 并想到达 y: x = [0 0 1 0 0 0 0 1 0 0 0 1 1 0 0 1] y = [0
我的场景一开始类似于孤岛和差距问题,我需要找到连续的工作天数。我当前的 SQL 查询回答“ProductA 是在 LocationA 从 DateA 到 DateB 生产的,总共 X 数量”。 但是,
Rust 如何处理 Rc 和 Arc 的“隔离岛”场景? “隔离岛”是这样一种情况,其中对象 A 包含指向对象 B 的指针,而对象 B 包含指向对象的指针A,但在其他任何地方都没有指向这两个对象的指针
我在优化从网上下载的“岛屿数量”程序时遇到问题。我尝试按照下面的说明对其进行优化,但无法 100% 正确。 什么是岛屿数? http://www.geeksforgeeks.org/find-numb
我一直在阅读有关垃圾收集的文章,并开始了解“隔离岛”一词,例如当 ObjectA 引用 ObjectB 和 ObjectB 同时引用 ObjectA 时。 有人可以用 C# 给我一个例子吗?另外,您能
我的问题与 How to group by continuous records in SQL 相同,只有我需要 Pandas 中的解决方案。 给定一个df like ID Colour -----
我是一名优秀的程序员,十分优秀!