我有一表
int_id, begin_time, end_time, duration
1 2110720853 2008-1-14 23:50:00 2008-1-15 10
2 2110720853 2008-1-15 2008-1-16 1440
3 2110720853 2008-1-16 2008-1-16 0:05:00 5
4 2110720853 2008-1-16 23:55:00 2008-1-17 5
5 2110720853 2008-1-17 2008-1-17 0:05:00 5想得到如下汇总结果
2110720853 2008-1-14 23:50:00 2008-1-16 0:05:00 1455
2110720853 2008-1-16 23:55:00 2008-1-17 0:05:00 10
就是将时间连续的几条数据中duration就和 得到的合并结果中(也可体现begin_time,end_time )
万分感激 谢谢 在线等候佳音!
int_id, begin_time, end_time, duration
1 2110720853 2008-1-14 23:50:00 2008-1-15 10
2 2110720853 2008-1-15 2008-1-16 1440
3 2110720853 2008-1-16 2008-1-16 0:05:00 5
4 2110720853 2008-1-16 23:55:00 2008-1-17 5
5 2110720853 2008-1-17 2008-1-17 0:05:00 5想得到如下汇总结果
2110720853 2008-1-14 23:50:00 2008-1-16 0:05:00 1455
2110720853 2008-1-16 23:55:00 2008-1-17 0:05:00 10
就是将时间连续的几条数据中duration就和 得到的合并结果中(也可体现begin_time,end_time )
万分感激 谢谢 在线等候佳音!
SELECT aaa.int_id, MIN (aaa.begin_time) begin_time, MAX (aaa.end_time),
SUM (aaa.DURATION) DURATION
FROM (SELECT aa.*,
SUM (aa.temp_group_id) OVER (PARTITION BY aa.int_id ORDER BY aa.begin_time)
GROUP_ID
FROM (SELECT a.*,
DECODE
( begin_time
- LAG (end_time, 1, begin_time - 1) OVER (PARTITION BY int_id ORDER BY begin_time),
0, 0,
1
) temp_group_id
FROM a)) aaa
GROUP BY aaa.int_id, aaa.GROUP_ID
ORDER BY 1, 2
WITH a AS
(SELECT 2110720853 int_id,
TO_DATE ('2008-1-14 23:50:00',
'yyyy-mm-dd hh24:mi:ss'
) begin_time,
TO_DATE ('2008-1-15', 'yyyy-mm-dd hh24:mi:ss') end_time,
10 DURATION
FROM DUAL
UNION ALL
SELECT 2110720853, TO_DATE ('2008-1-15', 'yyyy-mm-dd hh24:mi:ss'),
TO_DATE ('2008-1-16', 'yyyy-mm-dd hh24:mi:ss'), 1440
FROM DUAL
UNION ALL
SELECT 2110720853, TO_DATE ('2008-1-16', 'yyyy-mm-dd hh24:mi:ss'),
TO_DATE ('2008-1-16 0:05:00', 'yyyy-mm-dd hh24:mi:ss'), 5
FROM DUAL
UNION ALL
SELECT 2110720853,
TO_DATE ('2008-1-16 23:55:00', 'yyyy-mm-dd hh24:mi:ss'),
TO_DATE ('2008-1-17', 'yyyy-mm-dd hh24:mi:ss'), 5
FROM DUAL
UNION ALL
SELECT 2110720853, TO_DATE ('2008-1-17', 'yyyy-mm-dd hh24:mi:ss'),
TO_DATE ('2008-1-17 0:05:00', 'yyyy-mm-dd hh24:mi:ss'), 5
FROM DUAL)
以上为插入测试数据
=============================
SELECT aaa.int_id, MIN (aaa.begin_time) begin_time, MAX (aaa.end_time),
SUM (aaa.DURATION) DURATION
FROM (SELECT aa.*,
SUM (aa.temp_group_id) OVER (PARTITION BY aa.int_id ORDER BY aa.begin_time)
GROUP_ID
FROM (SELECT a.*,
DECODE
( begin_time
- LAG (end_time, 1, begin_time - 1) OVER (PARTITION BY int_id ORDER BY begin_time),
0, 0,
1
) temp_group_id
FROM a)aa) aaa
GROUP BY aaa.int_id, aaa.GROUP_ID
ORDER BY 1, 2=============================
以下为结果
Row# INT_ID BEGIN_TIME MAX(AAA.END_TIME) DURATION1 2110720853 2008/1/14 23:50:00 2008/1/16 0:05:00 1455
2 2110720853 2008/1/16 23:55:00 2008/1/17 0:05:00 10
SELECT aaa.int_id, MIN (aaa.begin_time) begin_time, MAX (aaa.end_time) end_time,
SUM (aaa.DURATION) DURATION
FROM (SELECT aa.*,
SUM (aa.temp_group_id) OVER (PARTITION BY aa.int_id ORDER BY aa.begin_time)
GROUP_ID
FROM (SELECT a.*,
DECODE
( begin_time
- LAG (end_time, 1, begin_time - 1) OVER (PARTITION BY int_id ORDER BY begin_time),
0, 0,
1
) temp_group_id
FROM a)aa) aaa
GROUP BY aaa.int_id, aaa.GROUP_ID
ORDER BY 1, 2