确定期限的开始和结束 - oracle 10g sql [英] Identifying start and end of period covered - oracle 10g sql

问题描述

我的数据如下所示：

        ID      FROM        TO          START_CODE      END_CODE    TYPE
        A       01/01/2012  02/02/2012  P               E           1
        A       12/03/2011  01/01/2012  P               X           1
        A       01/01/2011  12/03/2011  S               X           2

        A       01/01/2010  02/02/2010  P               E           2
        A       12/03/2010  01/01/2010  P               X           4
        A       01/01/2009  12/03/2009  S               X           1

基本上，这是一个客户端的信息。 end_code'e'表示护理期的结束，start_code''表示护理期的开始。起始代码p表示日期是延续，终止代码x也是如此。我想要返回的数据如下：

Basically, this is information for one client. The end_code 'e' denotes the end of a period of care, the start_code 's' denotes a start of a period of care. A start code of 'p' denotes the dates are a continuation, as does an end code of 'x'. What I would like to be able to return is data that looks like the following:

        ID      START       END             Types
        A       01/01/2011  02/02/2012      2,1,1
        A       01/01/2009  02/02/2010      1,4,2

第一个表的查询看起来与此类似（仅适用于示例），但是，我有兴趣为多个客户端返回，而不仅仅是A。

The query for the first table looks similar to this (example only), however, I would be interested in returning for a number of clients, and not just 'A'.

        SELECT
        A.ID,
        A.FROM,
        A.TO,
        A.START_CODE,
        A.END_CODE,
                       A.Type

        WHERE
        A.ID = 'A'

过去我已经使用xmlagg来实现类似的功能（即在一个单元格中列出信息），但实际上它确定了单独的开始和每个客户端的结束日期，然后返回我正在努力的类型序列。为了参考，我的oracle版本是10g，10.2.0.5.0。

I've used xmlagg in the past to achieve similar things (i.e. to list information in one cell), but it's actually identifying seperate start and end dates for each client, and then returning the sequence of types that I'm struggling with. For reference, my oracle version is 10g, 10.2.0.5.0.

感谢您的时间，任何指针或帮助都不胜感激。

Thanks for your time, any pointers or assistance is appreciated.

编辑以包含以下ABCade的建议：

Edited to include suggestion by A.B.Cade below:

        SELECT
        t3.MOV_PER_GRO_ID,
        t3.f,
        t3.MOV_END_DATE,
        t3.types,
        LENGTH(REGEXP_REPLACE(t3.types,'[^,]')) as "Count"
        FROM(

        SELECT 
        sys_connect_by_path(t2.MOV_2000_PLACEMENT_TYPE,',') types,
        connect_by_root(t2.MOV_START_DATE) f, 
        t2.MOV_START_DATE,        
        t2.MOV_END_DATE,        
        connect_by_isleaf is_leaf, 
        t2.MOV_PER_GRO_ID 
        FROM (SELECT t.*,
        lag(t.MOV_START_DATE) over (ORDER BY t.MOV_PER_GRO_ID, t.MOV_START_DATE) nfrom
        FROM O_MOVEMENTS t
        WHERE t.MOV_PER_GRO_ID IN ('A','B'))t2 
        START 
        WITH 
        t2.MOV_2000_START_REASON = 'S'
        CONNECT BY   
        PRIOR t2.MOV_START_DATE = t2.nfrom
        AND PRIOR t2.MOV_PER_GRO_ID =  t2.MOV_PER_GRO_ID 
        AND t2.MOV_2000_START_REASON IN ('P'))t3
        where t3.is_leaf=1

根据ABCade的解决方案更新。经过几番调整（再次感谢ABCade），似乎正在运作。

Updated based on ABCade's solution. After a few tweaks (again thanks to ABCade) it appears to be working.

推荐答案

尝试：

SELECT  t3."ID", t3.f "start", t3."TO" "end", t3.types
FROM (
SELECT sys_connect_by_path(t2."TYPE",',') types,
       connect_by_root(t2."FROM") f,
       t2."FROM",
       t2."TO",
       connect_by_isleaf is_leaf ,
t2."ID"
FROM (
  SELECT t.*, lag(t."FROM") over (ORDER BY t."FROM") nfrom
    FROM table1 t
   WHERE t."ID" = 'A'
  ) t2
START WITH t2."START_CODE" = 'S'
CONNECT BY   PRIOR t2."FROM" = t2.nfrom  AND t2."START_CODE" = 'P') t3
WHERE is_leaf=1

这里是一个小提琴

这里是另一个小提琴（看到您的评论和更新后）

Here is a fiddle
Here is another fiddle (after seeing your comment and update)

这篇关于确定期限的开始和结束 - oracle 10g sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！