我有一表
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 )
万分感激 谢谢 在线等候佳音!
解决方案 »
- utl_file.put_line问题求助
- 如果根据表的某个字段来动态改变记录条数?
- 免安装的客户端无法运行sqlldr
- 求oralce 10g下正则表达式
- 100分!求快速学习PL/SQL
- 请大侠问数据库会话异常是什么原因?急问!!!
- vc连接oracle,用ole驱动,连接串应该怎么些?
- 郁闷!那位有在win2000专业版下,安装oracle9i的成功经历???
- 大量使用存储过程或函数会不会给服务器太大的压力、VC如何调用存储过程?
- SUM(xx ) OVER (PARTITION BY xx) 在下面这个例子中怎么用,请教大神!
- 看到一种数据库设计的方式,说是目前比较先进的方式,但是我不能理解,请高手解释
- 请教数据库高手: 为何我能看到其他用户建的包头却看不到包体
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