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

Question

我已经与这个问题搏斗了几天，现在我转向群众寻求帮助。

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

虽然我喜欢认为我对 SQL+PL/SQL 有中级/高级掌握……Oracle 分析功能显然让我难以置信。我一直在尝试阅读/学习，但时间不够了。

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

问题来了:
我有一张表，其中包含客户的事件历史记录。客户可以来来去去，所以我们在这个表中可能有多行(每个客户)。

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;

我还有一个表，按跨度存储属性信息。一个客户可以同时拥有多种属性类型，并且每种类型可以在不同的时间跨度内多次使用。

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 年前，我使用 2 个异步游标处理缓慢(逐行)写了一些东西(当时要求类似于事件/功率)。

虽然性能很重要，但我试图找到直接/批量 sql 解决方案的最大原因是维护。我的原始解决方案的 if/else 游标嵌套已经很难遵循，并且至少有 2 个要拆分的“属性”跨度会呈指数级恶化。

我将不胜感激你们中的任何人能够提供的任何和所有帮助。

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