如何将多个日期跨度合并/拆分为一个时间线(Oracle 11g)？

Question

这个问题我已经纠结了几天，现在我要向群众求助了。

我的问题与这个站点上的前一个解决方案类似，但不完全相同：PL/SQL Split, separate a date into new dates according to black out dates!这个解决方案相当布尔(包含/排除)，而我的问题涉及其中的一些以及合并。

虽然我认为我对SQL+PL/SQL有一个中级/高级的掌握...Oracle分析函数显然让我摸不着头脑。我一直在努力阅读/学习，但我的时间不多了。

由于我不确定共享表名( not )、业务线等的合法性，我将用一个模糊的场景/上下文来模拟我的问题。希望这能驱散律师的幽灵。

关于问题:我有一个表，其中包含客户的活动历史记录。客户可以来去自如，因此我们可能在此表中有多行(每个客户)。

CREATE TABLE activity AS
SELECT 1 AS cust_id,
       TO_DATE('01-JAN-2010') AS start_dt,
       TO_DATE('31-JUL-2010') AS end_dt,
       'EAST' AS region
FROM DUAL
UNION
SELECT 1 AS cust_id,
       TO_DATE('01-FEB-2011') AS start_dt,
       TO_DATE('31-DEC-2011') AS end_dt,
       'EAST' AS region
FROM DUAL;

我还有一个表，其中包含按span划分的属性信息。客户可以一次拥有多个属性类型，每种类型可以多次用于不同的时间跨度。

CREATE TABLE attrib AS
SELECT 1 AS cust_id,
       'POWER' AS atb_cd,
       TO_DATE('01-JAN-2009') AS atb_start_dt,
       TO_DATE('31-JAN-2010') AS atb_end_dt,
       'LocalNuke' AS provider,
       1.80 AS per_kwh,
       0 AS per_gal
FROM DUAL
UNION
SELECT 1 AS cust_id,
       'POWER' AS atb_cd,
       TO_DATE('01-MAR-2010') AS atb_start_dt,
       TO_DATE('31-MAR-2010') AS atb_end_dt,
       'CoalGuys' AS provider,
       1.60 AS per_kwh,
       0 AS per_gal
FROM DUAL
UNION
SELECT 1 AS cust_id,
       'POWER' AS atb_cd,
       TO_DATE('01-JUN-2010') AS atb_start_dt,
       TO_DATE('30-SEP-2010') AS atb_end_dt,
       'LocalNuke' AS provider,
       1.70 AS per_kwh,
       0 AS per_gal
FROM DUAL
UNION
SELECT 1 AS cust_id,
       'POWER' AS atb_cd,
       TO_DATE('01-MAR-2011') AS atb_start_dt,
       TO_DATE('31-DEC-9999') AS atb_end_dt,
       'GeoHeat' AS provider,
       1.10 AS per_kwh,
       0 AS per_gal
FROM DUAL
UNION
SELECT 1 AS cust_id,
       'WATER' AS atb_cd,
       TO_DATE('01-MAR-2010') AS atb_start_dt,
       TO_DATE('31-DEC-9999') AS atb_end_dt,
       'GlacialGold' AS provider,
       0 AS per_kwh,
       0.60 AS per_gal
FROM DUAL;

这些奇怪的数据是故意的，我试图让这个场景尽可能地真实，而不是与“真实世界”相关。

结果应该将跨度限制到这个虚构公司的客户活动，并拆分出所有重叠的日期以形成一个时间表。数据元素将需要合并在一起以进行报告。

视觉上：

Cust:
         |----------------------|             |------------------------|
Power:
|-------------|    |--|    |-------|               |---------------------->
Water:
                   |------------------------------------------------------>    
Expected Result:
         |----|----|--|----|----|             |----|-------------------|

该解决方案应该是可伸缩的，以包括其他属性。最后，我将这些非规范化信息放在一个表中，这样我就可以在任何时候报告客户的数据。例如，如果他们在特定的一天有活动、电力和水；我应该能够导出该天的per_kwh、per_gal和活动数据。

输出示例(表格)：

CUST_ID  FROM_DT      THRU_DT      REGION  POWER_PROVIDER  WATER_PROVIDER  PER_KWH  PER_GAL
-------  -----------  -----------  ------  --------------  --------------  -------  -------
1        01-JAN-2010  31-JAN-2010  EAST    LocalNuke                       1.80     0
1        01-FEB-2010  28-FEB-2010  EAST                                    0        0
1        01-MAR-2010  31-MAR-2010  EAST    CoalGuys        GlacialGold     1.60     0.60
1        01-APR-2010  31-MAY-2010  EAST                    GlacialGold     0        0.60
1        01-JUN-2010  31-JUL-2010  EAST    LocalNuke       GlacialGold     1.70     0.60
1        01-FEB-2011  28-FEB-2011  EAST                    GlacialGold     0        0.60
1        01-MAR-2011  31-DEC-2011  EAST    GeoHeat         GlacialGold     1.10     0.60

我在大约2年前写了一些东西(当时的需求类似于Activity/Power)，使用2个异步游标处理慢到慢(逐行)。

虽然性能很重要，但我尝试寻找直接/批量sql解决方案的最大原因是维护性。我的原始解决方案的if/else游标嵌套已经很难遵循了，而且如果要拆分至少两个“属性”跨度，情况会变得更糟。

我将非常感谢你们任何人所能提供的任何帮助。

Answer 1

这可能行得通。它不会将连续的区域合并在一起，但它仍然可以完成这项工作。

WITH

  milestone AS
  (
    SELECT cust_id, start_dt     AS point_in_time FROM ACTIVITY
  UNION
    SELECT cust_id, atb_start_dt AS point_in_time FROM ATTRIB
  UNION
    SELECT cust_id, LEAST(end_dt,     TO_DATE('30-DEC-9999')) + 1 AS point_in_time FROM ACTIVITY
  UNION
    SELECT cust_id, LEAST(atb_end_dt, TO_DATE('30-DEC-9999')) + 1 AS point_in_time FROM ATTRIB
  )

SELECT
  milestone.cust_id                 AS cust_id,
  milestone.point_in_time           AS from_dt,
  LEAD(point_in_time)
    OVER (PARTITION BY milestone.cust_id ORDER BY milestone.point_in_time) - 1
                                    AS thru_dt,
  activity.region                   AS region,
  power_attrib.provider             AS power_provider,
  water_attrib.provider             AS water_provider,
  COALESCE(power_attrib.per_kwh, 0) AS per_kwh,
  COALESCE(water_attrib.per_gal, 0) AS per_gal
FROM
  MILESTONE

  LEFT OUTER JOIN ACTIVITY
    ON milestone.cust_id = activity.cust_id
       AND milestone.point_in_time BETWEEN activity.start_dt AND activity.end_dt

  LEFT OUTER JOIN ATTRIB power_attrib
    ON milestone.cust_id = power_attrib.cust_id
       AND power_attrib.atb_cd = 'POWER'
       AND milestone.point_in_time BETWEEN power_attrib.atb_start_dt AND power_attrib.atb_end_dt

  LEFT OUTER JOIN ATTRIB water_attrib
    ON milestone.cust_id = water_attrib.cust_id
       AND water_attrib.atb_cd = 'WATER'
       AND milestone.point_in_time BETWEEN water_attrib.atb_start_dt AND water_attrib.atb_end_dt

Answer 2

这确实是一个非常棘手的问题，我希望您最终会得到一个又大又乱的查询。核心问题是您需要为attrib表中的间隙生成"psudeo“行。这是有问题的。

我对你的问题做了一个简化的版本，只是想为能量属性制造一些空隙。我认为每个属性行的前面都可以有一个空格。想出了这个

SELECT  PS.cust_id
    ,   G.is_gap
    ,   DECODE( G.is_gap, 'Y', PS.prev_start, PS.atb_start_dt ) AS start_date
    ,   DECODE( G.is_gap, 'Y', PS.prev_end, PS.atb_end_dt ) AS end_date
    ,   DECODE( G.is_gap, 'Y', NULL, PS.provider ) AS provider
    ,   DECODE( G.is_gap, 'Y', NULL, PS.per_kwh ) AS per_kwh
    ,   DECODE( G.is_gap, 'Y', NULL, PS.per_gal ) AS per_gal
FROM
    (   SELECT  P.cust_id
            ,   P.atb_start_dt
            ,   P.atb_end_dt
            ,   P.provider
            ,   P.per_kwh
            ,   P.per_gal
            ,   P.atb_start_dt - 1      AS prev_end
            ,   NVL( MAX( P.atb_end_dt ) OVER ( ORDER BY P.atb_end_dt
                        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) + 1
                   , '01-JAN-1900' )    AS prev_start
        FROM    attrib      P
        WHERE   P.atb_cd    = 'POWER'
    ) PS
,   (   SELECT  DECODE(LEVEL,1,'Y','N') AS is_gap
        FROM    DUAL
        CONNECT BY LEVEL <= 2
    ) G
WHERE   (   PS.prev_end > PS.prev_start
        OR  G.is_gap    = 'N' )
ORDER BY 3
/

给了我这些结果

CUST_ID I START_DATE END_DATE   PROVIDER    PER_KWH PER_GAL
------- - ---------- ---------- ----------- ------- -------
      1 Y 01-JAN-00  31-DEC-08
      1 N 01-JAN-09  31-JAN-10  LocalNuke   1.8     0
      1 N 01-FEB-10  31-MAR-10  CoalGuys    1.6     0
      1 Y 01-APR-10  31-MAY-10
      1 N 01-JUN-10  30-SEP-10  LocalNuke   1.7     0
      1 Y 01-OCT-10  28-FEB-11
      1 N 01-MAR-11  31-DEC-99  GeoHeat     1.1     0

一些注意事项：

• 我认为您样本结果的第5行的结束日期不正确。它应该是31-JUL-2010，因为那是activity结束的时候？
• I将CoalGuys开始日期更新为01-FEB-2010，以测试何时没有差距如果没有活动运行到遥远的未来，则
• 将失败，因为它不会生成尾随差距，而只是一个先行差距。and UNION one in我猜
• 最好不要使用9999作为一年，因为如果你尝试添加任何东西，你会得到错误。最终并不重要，但如果你去寻找落后的差距，那就太匆忙了。

现在，这距离完整的解决方案还有很长的路要走，一旦你加入了客户和日期，它就会变得更加混乱。但是，您可能需要将上述内容作为内联视图包含在主查询中。那么你将不得不对水做同样的事情。然后，您必须使用日期范围检查将两者结合在一起，然后使用LEAST和GREATEST来获得最终的日期结果。

对不起，在我花了大约40分钟之后，它从一个有趣的问题变成了工作的感觉，所以我的答案是不完整的。希望能有所帮助。