oracle 分组排序 update,求助：百万级数据分组排序，求时间间隔进行批量修改标志...

第一步先生成一个临时表，这个表中只包含将要把TYPE设成1的记录,如下：

WITH TEMP AS

(

SELECT 88557262 ID,46751776 USER_ID,393 SERVICE_ID,59360    SORT_ID,56393    MATE_ID,TO_DATE('2009-2-1 0:00:03','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557280 ID,46947635 USER_ID,199 SERVICE_ID,50009835 SORT_ID,50009835 MATE_ID,TO_DATE('2009-2-1 0:00:13','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557298 ID,47089905 USER_ID,361 SERVICE_ID,50009835 SORT_ID,50009835 MATE_ID,TO_DATE('2009-2-1 0:00:29','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557275 ID,47166365 USER_ID,367 SERVICE_ID,50009835 SORT_ID,50009835 MATE_ID,TO_DATE('2009-2-1 0:00:10','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557296 ID,47309337 USER_ID,328 SERVICE_ID,50009835 SORT_ID,50009835 MATE_ID,TO_DATE('2009-2-1 0:00:29','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557285 ID,47609878 USER_ID,347 SERVICE_ID,50009835 SORT_ID,50009835 MATE_ID,TO_DATE('2009-2-1 0:00:17','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557268 ID,48142364 USER_ID,247 SERVICE_ID,50009835 SORT_ID,50009835 MATE_ID,TO_DATE('2009-2-1 0:00:05','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557277 ID,48304440 USER_ID,265 SERVICE_ID,57857    SORT_ID,57845    MATE_ID,TO_DATE('2009-2-1 0:00:12','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557273 ID,48312498 USER_ID,148 SERVICE_ID,50009835 SORT_ID,50009835 MATE_ID,TO_DATE('2009-2-1 0:00:08','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557278 ID,48321229 USER_ID,265 SERVICE_ID,59360    SORT_ID,51591    MATE_ID,TO_DATE('2009-2-1 0:00:13','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557264 ID,48325739 USER_ID,353 SERVICE_ID,50009840 SORT_ID,41577    MATE_ID,TO_DATE('2009-2-1 0:00:03','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557286 ID,48325739 USER_ID,353 SERVICE_ID,50009840 SORT_ID,41577    MATE_ID,TO_DATE('2009-2-1 0:00:17','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

UNION

SELECT 88557292 ID,48325739 USER_ID,353 SERVICE_ID,50009840 SORT_ID,41577    MATE_ID,TO_DATE('2009-2-1 0:00:25','YYYY-MM-DD HH24:MI:SS') YDATE FROM DUAL

)

CREATE TABLE TEMP1 AS

(SELECT T.*,TRUNC((YDATE-XDATE)*1440) CZ FROM

(SELECT T.*,LAG(YDATE,1,NULL) OVER(PARTITION BY USER_ID,SERVICE_ID ORDER BY YDATE) XDATE FROM TEMP T) T WHERE XDATE IS NOT NULL

) WHERE CZ >= 2

第二步利用上面的临时表来更新源表，假设源表为SOURCETAB,并且ID为唯一值。

UPDATE SOURCETAB SET TYPE = 1 WHERE ROWID IN

(

SELECT ROWID FROM SOURCETAB T1,TEMP1 WHERE T1.ID = TEMP1.ID

)

当然也可以不建临时表直接更新，不过我担心效率可能会慢。