TSQL - 带条件的时间总和列
需要创建一个查询,该查询会获取位设置为ON/OFF的时间摘要。TSQL - 带条件的时间总和列
例子:
╔═══════════════════════════╗
║ TABLE ║
╠════╦════════╦═════╦═══════╣
║ ID ║ TIME ║ BIT ║ VALUE ║
╠════╬════════╬═════╬═══════╣
║ 1 ║ 13:40 ║ 1 ║ 5 ║
║ 2 ║ 13:45 ║ 1 ║ 3 ║
║ 3 ║ 13:50 ║ 1 ║ 1 ║
║ 4 ║ 13:55 ║ 0 ║ 2 ║
║ 5 ║ 14:00 ║ 0 ║ 7 ║
║ 6 ║ 14:05 ║ 1 ║ 3 ║
║ 7 ║ 14:10 ║ 1 ║ 4 ║
║ 8 ║ 14:15 ║ 0 ║ 2 ║
║ 9 ║ 14:20 ║ 1 ║ 2 ║
╚════╩════════╩═════╩═══════╝
我想有TIME
(和VALUE
- 简单的一个)总总结时BIT
是SET ON
:
13:40 - 13:50 = 10 mins
14:05 - 14:10 = 5 mins
14:20 = no end time, 0 mins
-----------------------------------------
15 mins
有没有发现:
- How to sum up time field in SQL Server =很好的问题,但有静态的时间开始(0:00:00),这将不会在这种情况下
- Aggregate function over a given time interval工作=有聚集而不是空调,并适用于所有数据
我想,这可能是作为递归函数完成(传递最后处理的日期时间),它将通过处理的最后日期,并且计算自BIT
为ON
以来的日期时间。
SQL查询求和值(容易的):
SELECT SUM(Value)
FROM Table
WHERE Bit = 1
我应该如何获得分钟(时间)的总价值,在此期间是BIT
设置ON
?
编辑:查询,可用于测试:
DECLARE @Table TABLE(
ID INT Identity(1,1) PRIMARY KEY,
[TIME] DATETIME NOT NULL,
[BIT] BIT NOT NULL,
[VALUE] INT NOT NULL
);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:40',1,5);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:45',1,3);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:50',1,1);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('13:55',0,2);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:00',0,7);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:05',1,3);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:10',1,4);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:15',0,2);
INSERT INTO @Table([TIME],[BIT],[VALUE]) VALUES('14:20',1,2);
SELECT * FROM @Table;
使用LEAD函数获取下一行的时间并计算时间间隔。然后只是组结果由[位]
WITH t AS(
SELECT
[time],
DATEDIFF(minute, [time], LEAD([time], 1, null) OVER (ORDER BY [time])) AS interval,
[bit],
[value]
FROM table1)
SELECT [bit], CAST(DATEADD(MINUTE, SUM(interval), '00:00') AS TIME), SUM([value]) FROM t
GROUP BY [bit]
你有两个问题:总结的时间,并确定相邻值。你可以用行号的方法处理第二个。您可以通过转换为分钟处理前:
select bit, min(time), max(time),
sum(datediff(minute, 0, time)) as minutes,
sum(value)
from (select t.*,
row_number() over (order by id) as seqnum,
row_number() over (partition by bit order by id) as seqnum_b
from t
) t
group by (seqnum - seqnum_b), bit;
感谢您的回答!然而,当距离(Seqnum - seqnum_b)对于更多情况相同时,我发现有一个问题,对于聚合函数,您会得到错误的结果 - 'MIN'首先发生,'MAX'将最后一次发生。 – Tatranskymedved
@Transranskymedved。 。 。你试过这个吗?这可以找到相邻的行。也许你在'group by'中省略了'bit'。 –
TBH还没有尝试过,但我现在已经测试过了。这是一段聪明的代码!感谢这个想法,我永远不会想象这可以通过这种方式完成。无论如何,这不是对主题的完整回答,它只涉及其中的一部分。感谢您的时间! =) – Tatranskymedved
这是一个“空白和孤岛”的问题,有一个非常标准的解决方案。我想出了这个,这与Gordon的几乎相同,但是有一个额外的步骤来计算间隔。这是我发布基本上是重复答案的唯一原因,我不确定在零分钟之内取得差异实际上有效吗?
DECLARE @table TABLE (id int, [time] TIME, [bit] BIT, value INT);
INSERT INTO @table SELECT 1, '13:40', 1, 5;
INSERT INTO @table SELECT 2, '13:45', 1, 3;
INSERT INTO @table SELECT 3, '13:50', 1, 1;
INSERT INTO @table SELECT 4, '13:55', 0, 2;
INSERT INTO @table SELECT 5, '14:00', 0, 7;
INSERT INTO @table SELECT 6, '14:05', 1, 3;
INSERT INTO @table SELECT 7, '14:10', 1, 4;
INSERT INTO @table SELECT 8, '14:15', 0, 2;
INSERT INTO @table SELECT 9, '14:20', 1, 2;
WITH x AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [bit] ORDER BY id) AS a_id, ROW_NUMBER() OVER (ORDER BY id) AS b_id FROM @table),
y AS (
SELECT [bit], MIN([time]) AS min_time, MAX([time]) AS max_time, SUM(value) AS value FROM x GROUP BY a_id - b_id, [bit])
SELECT [bit], SUM(value) AS total_value, SUM(DATEDIFF(MINUTE, min_time, max_time)) AS total_minutes FROM y GROUP BY [bit];
结果:
bit total_value total_minutes
0 11 5
1 18 15
作为奖金这里是一个解决方案,既解决了实际问题,即有多少经过的时间是在那里当该位被设置为1:
WITH x AS (SELECT id, id - DENSE_RANK() OVER(ORDER BY id) AS grp FROM @table WHERE [bit] = 1), y AS (SELECT MIN(id) AS range_start, MAX(id) AS range_end FROM x GROUP BY grp)
SELECT SUM(DATEDIFF(MINUTE, t1.[time], t2.[time])) AS minutes_elapsed FROM y INNER JOIN @table t1 ON t1.id = y.range_start INNER JOIN @table t2 ON t2.id = y.range_end;
更新后,我可以清楚地看到它是如何工作的,我有问题了解最初的查询= /谢谢你的答案,这应该很好! – Tatranskymedved
这真的很容易阅读,也很容易理解。感谢你的回答! – Tatranskymedved
更新:使用OVER(ORDER BY [时间])而不是(BY [id])。因为ID不一定是连续的。它可能是guid或自然的关键 – usart