当一个的结束时间是另一个的开始时间时合并行(Oracle) [英] Combine rows when the end time of one is the start time of another (Oracle)

问题描述

我似乎无法弄清楚这个查询.我需要将时间连续状态的行合并为一个状态.

I just can't seem to get this query figured out. I need to combine rows of time-consecutive states into a single state.

除了我使用的是Oracle 10而不是SQL Server，此问题与此处找到的问题类似:

This question is similar to the question found here except I am working with Oracle 10 not SQL Server: Combine rows when the end time of one is the start time of another

示例数据:

name      start_inst         end_inst            code     subcode
Person1 9/12/2011 10:55 9/12/2011 11:49           161   50
Person1 9/12/2011 11:49 9/12/2011 11:55           107   28
Person1 9/12/2011 11:55 9/12/2011 12:07           161   50
Person1 9/12/2011 12:07 9/12/2011 12:26           161   50
Person1 9/12/2011 12:26 9/12/2011 12:57           161   71
Person1 9/12/2011 12:57 9/12/2011 13:07           161   71
Person1 9/12/2011 13:07 9/12/2011 13:20            52   50

我想获得以下输出:

name       start_inst       end_inst            code     subcode
Person1 9/12/2011 10:55     9/12/2011 11:49     161     50
Person1 9/12/2011 11:49     9/12/2011 11:55     107     28
Person1 9/12/2011 11:55     9/12/2011 12:26     161     50
Person1 9/12/2011 12:26     9/12/2011 13:07     161     71
Person1 9/12/2011 13:07     9/12/2011 13:20     52      50

以下是示例SQL:

CREATE TABLE Data (
    name varchar2(132 BYTE) not null,
    start_inst DATE not null,
    end_inst DATE not null,    
code number(3) not null,
subcode number(3) not null
);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 10:55','9/12/2011 11:49',161, 50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 11:49','9/12/2011 11:55',107,28);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 11:55','9/12/2011 12:07',161,50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:07','9/12/2011 12:26',161,50);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:26','9/12/2011 12:57',161,71);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 12:57','9/12/2011 13:07',161,71);
INSERT INTO Data(name,start_inst,end_inst, code, code2) VALUES('Person1','9/12/2011 13:07','9/12/2011 13:20',52,50);

提前谢谢！

推荐答案

也许是吗? (我没有运行它的SQL机器)

Maybe this? (I don't have a SQL machine to run it on)

WITH
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY name                ORDER BY start_inst) NameSequenceID,
    ROW_NUMBER() OVER (PARTITION BY name, code, subcode ORDER BY start_inst) NameStateSequenceID,
    *
  FROM
    data
)
SELECT
  name,
  MIN(start_inst) start_inst,
  MAX(end_inst)   end_inst,
  code,
  subcode
FROM
  sequenced_data
GROUP BY
  name,
  code,
  subcode,
  NameSequenceID - NameStateSequenceID

这篇关于当一个的结束时间是另一个的开始时间时合并行(Oracle)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！