C＃SQL问题，基于时间间隔存在进行分组 [英] C# SQL issue, grouping based on time interval presence
本文介绍了C＃SQL问题，基于时间间隔存在进行分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好。
我有问题...
表格中有表格：
事件ID |事件类别|事件开始|活动结束
_________________________________________
1 | 1 | 2012-12-16 02:02:55 | 2012-12-16 02:05:55
2 | 2 | 2012-12-16 02:05:56 | 2012-12-16 02:55:25
3 | 1 | 2012-12-16 02:55:26 | 2012-12-16 03:02:55
4 | 2 | 2012-12-16 03:02:56 | 2012-12-16 03:07:26
5 | 1 | 2012-12-16 03:07:27 | 2012-12-16 04:02:55
我需要做的是：
我需要以某种方式得到reult说：
时间间隔|类别|期限
_________________________________________________________________________
2012-12-16 02:00:00 -03：00：00 | 1 |（180 + 34 + 240）454s
2012-12-16 02:00:00 -03：00：00 | 2 | 2969s
..........
请给我一个提示如何制作这个...谢谢很多前期....
解决方案
好的，我明白了：
DECLARE @ tmp TABLE （event_ID INT IDENTITY （ 1 ， 1 ），event_category INT ，event_start DATETIME ，event_end DATETIME ）
INSERT INTO @ tmp （event_category，event_start，event_end）
VALUES （ 1 ，' 2012-12-16 02:02:55'，' 2012-12-16 02 ：05：55'），
（ 2 ，' 2012-12-16 02:05:56'，' 2012- 12-16 02:55:25'），
（ 1 ，' 2012-12-16 02:55:26'，' 2012-12-16 03:02:55'），
（ 2 ， ' 2012-12-16 03:02:56'，' 2012-12-16 03:07:26'），
（ 1 ，' 2012-12-16 03:07:27'，' 2012-12-16 04:02:55'）
- 取消注释以下行以查看详细信息
- SELECT event_id，event_category，event_start， event_end，interval_start，interval_end，
- CASE
< span class =code-comment> - WHEN event_end> interval_end THEN DATEDIFF（ss，event_start，interval_end）
- WHEN event_end< interval_end then =mode =hold/> - ELSE 0
- END AS持续时间
- FROM（
- SELECT event_id，event_category，event_start，event_end，DATEADD（hh ，DATEDIFF（hh，0，event_start），0）AS interval_start，DATEADD（hh，DATEDIFF（hh，0，event_start）+1,0）AS interval_end
- FROM @tmp
- ）AS T
- ORDER BY event_category，event_start，event_end
SELECT event_category，interval_start，interval_end，
SUM（ CASE
WHEN event_end> interval_end 那么 DATEDIFF（ss，event_start，interval_end）
WHEN event_end< interval_end 那么 DATEDIFF（ss，event_start，event_end）
ELSE 0
END ） AS 持续时间
FROM （
SELECT event_category，event_start，event_end，DATEADD（hh，DATEDIFF（hh， 0 ，event_start）， 0 ） AS interval_start，DATEADD（hh，DATEDIFF（hh， 0 ， event_start）+1， 0 ） AS interval_end
FROM @ tmp
） AS T
GROUP BY event_category，interval_start，interval_end
ORDER BY event_category
结果：
cat interval_start interval_end持续时间
1 20 12-12-16 02：00：00.000 2012-12-16 03：00：00.000 454
1 2012-12-16 03：00：00.000 2012-12-16 04：00：00.000 3153
2 2012-12-16 02：00：00.000 2012-12-16 03：00：00.000 2969
2 2012-12-16 03：00：00.000 2012-12-16 04：00：00.000 270
注意：某些值已被拒绝！
例如：
event_id = 3：2min.55sec
event_id = 5：2min.55sec
以上值尚未移至下一个时间间隔！如果您想添加它，那么您需要使用 CTE [ ^ ]
有关CTE的更多信息，请参阅：
SQL Server中的CTE [ ^ ]
普通SQL SERVER 2008中的表表达式（CTE） [ ^ ]
SQL Server CTE基础知识 [ ^ ]
CTE（公用表格表达式） [ ^ ]
优化递归CTE查询 [ ^ ]
以下CTE：
; WITH MyIntervals AS
（
SELECT event_id， event_category，event_start，event_end，DATEADD（hh，DATEDIFF（hh， 0 ，event_start）， 0 ） AS interval_start，DATEADD（hh，DATEDIFF（hh， 0 ，event_start）+ 1， 0 ） AS interval_end
FROM @ tmp
UNION ALL
SELECT event_id，event_category，interval_end AS event_start，event_end， interval_end AS interval_start，DATEADD（hh， 1 ，interval_end） AS interval_end
FROM MyIntervals
WHERE event_end> interval_end
）
SELECT event_category，interval_start，interval_end，
SUM（ CASE
WHEN event_end> interval_end 那么 DATEDIFF（ss， event_start，interval_end）
WHEN event_end< interval_end 那么 DATEDIFF（ss，event_start，event_end）
ELSE 0
END ） AS 持续时间
FROM （
SELECT event_category，event_start，event_end，interval_start，interval_end
FROM MyIntervals
） AS T
GROUP BY event_category，interval_start，interval_end
产生以下结果：
1 2012-12-16 02：00：00.000 2012-12-16 03：00：00.000 454
1 2012-12-16 03：00：00.000 2012-12-16 04：00：00.000 3328
1 2012-12 -16 04：00：00.000 2012-12-16 05：00：00.000 175
2 2012-12-16 02：00：00.000 2012-12-16 03：00：00.000 2969
2 2012 -12-16 03：00：00.000 2012-12-16 04：00：00.000 270
你看到了区别吗？ / BLOCKQUOTE>
Hello everybody
I have an issue...
I have table in the form:
event ID | event category | event start | event end _________________________________________ 1 | 1 | 2012-12-16 02:02:55 | 2012-12-16 02:05:55 2 | 2 | 2012-12-16 02:05:56 | 2012-12-16 02:55:25 3 | 1 | 2012-12-16 02:55:26 | 2012-12-16 03:02:55 4 | 2 | 2012-12-16 03:02:56 | 2012-12-16 03:07:26 5 | 1 | 2012-12-16 03:07:27 | 2012-12-16 04:02:55
What I need to achieve is following:
I need somehow get reult saying:
time interval | category | duration _________________________________________________________________________ 2012-12-16 02:00:00 -03:00:00 | 1 |(180+34+240) 454s 2012-12-16 02:00:00 -03:00:00 | 2 |2969s ..........
please, give me a hint how to make this...thanks a lot upfront....解决方案OK, i got it:
DECLARE @tmp TABLE (event_ID INT IDENTITY(1,1) , event_category INT, event_start DATETIME, event_end DATETIME) INSERT INTO @tmp (event_category, event_start, event_end) VALUES(1, '2012-12-16 02:02:55', '2012-12-16 02:05:55'), (2, '2012-12-16 02:05:56', '2012-12-16 02:55:25'), (1, '2012-12-16 02:55:26', '2012-12-16 03:02:55'), (2, '2012-12-16 03:02:56', '2012-12-16 03:07:26'), (1, '2012-12-16 03:07:27', '2012-12-16 04:02:55') --uncomment below lines to see details --SELECT event_id, event_category, event_start, event_end, interval_start, interval_end, -- CASE -- WHEN event_end>interval_end THEN DATEDIFF(ss,event_start, interval_end) -- WHEN event_end<interval_end then="" mode="hold" />-- ELSE 0 -- END AS Duration --FROM( -- SELECT event_id, event_category, event_start, event_end, DATEADD(hh, DATEDIFF(hh, 0, event_start), 0) AS interval_start, DATEADD(hh, DATEDIFF(hh, 0, event_start)+1, 0) AS interval_end -- FROM @tmp --) AS T --ORDER BY event_category, event_start, event_end SELECT event_category, interval_start, interval_end, SUM(CASE WHEN event_end>interval_end THEN DATEDIFF(ss,event_start, interval_end) WHEN event_end<interval_end THEN DATEDIFF(ss,event_start, event_end) ELSE 0 END) AS Duration FROM( SELECT event_category, event_start, event_end, DATEADD(hh, DATEDIFF(hh, 0, event_start), 0) AS interval_start, DATEADD(hh, DATEDIFF(hh, 0, event_start)+1, 0) AS interval_end FROM @tmp ) AS T GROUP BY event_category, interval_start , interval_end ORDER BY event_category
Result:
cat interval_start interval_end Duration 1 2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 454 1 2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 3153 2 2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 2969 2 2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 270
NOTE: Some values have been rejected!
For example:
event_id = 3: 2min.55sec
event_id = 5: 2min.55sec
Above values haven't been moved to the next time interval! If you would like to add it, then you need to use CTE[^]
For further information about CTE, please see:
CTE In SQL Server[^]
Common Table Expressions(CTE) in SQL SERVER 2008[^]
SQL Server CTE Basics[^]
CTEs (Common Table Expressions)[^]
Optimize Recursive CTE Query[^]
Below CTE:
;WITH MyIntervals AS ( SELECT event_id, event_category, event_start, event_end, DATEADD(hh, DATEDIFF(hh, 0, event_start), 0) AS interval_start, DATEADD(hh, DATEDIFF(hh, 0, event_start)+1, 0) AS interval_end FROM @tmp UNION ALL SELECT event_id, event_category, interval_end AS event_start, event_end, interval_end AS interval_start, DATEADD(hh, 1, interval_end) AS interval_end FROM MyIntervals WHERE event_end>interval_end ) SELECT event_category, interval_start, interval_end, SUM(CASE WHEN event_end>interval_end THEN DATEDIFF(ss,event_start, interval_end) WHEN event_end<interval_end THEN DATEDIFF(ss,event_start, event_end) ELSE 0 END) AS Duration FROM( SELECT event_category, event_start, event_end, interval_start, interval_end FROM MyIntervals ) AS T GROUP BY event_category, interval_start, interval_end
produces the following result:
1 2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 454 1 2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 3328 1 2012-12-16 04:00:00.000 2012-12-16 05:00:00.000 175 2 2012-12-16 02:00:00.000 2012-12-16 03:00:00.000 2969 2 2012-12-16 03:00:00.000 2012-12-16 04:00:00.000 270
Do you see the difference?
这篇关于C＃SQL问题，基于时间间隔存在进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
