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

概述几天来我一直在努力解决这个问题,现在我转向群众寻求帮助. 我的问题与此网站上的先前解决方案类似,但不完全相同： PL/SQL Split, separate a date into new dates according to black out dates! 这个解决方案相当布尔(包含/排除),而我的问题涉及其中的一些以及合并. 虽然我觉得我对SQL PL / SQL有中级/高级的掌握…但Ora 几天来我一直在努力解决这个问题,现在我转向群众寻求帮助.

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

虽然我觉得我对sql PL / sql有中级/高级的掌握…但Oracle Analytic函数显然让我感到困惑.我一直在努力阅读/学习,但我已经没时间了.

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

关于问题：
我有一张包含客户活动历史记录的表格.客户可以来去,因此我们可能在此表中有多行(每个客户).

CREATE table activity ASSELECT 1 AS cust_ID,TO_DATE('01-JAN-2010') AS start_dt,TO_DATE('31-Jul-2010') AS end_dt,'EAST' AS regionFROM DUALUNIONSELECT 1 AS cust_ID,TO_DATE('01-FEB-2011') AS start_dt,TO_DATE('31-DEC-2011') AS end_dt,'EAST' AS regionFROM DUAL;

我还有一个表格,其中包含跨度的属性信息.客户可以同时拥有多个属性类型,每种类型可以多次使用不同的时间跨度.

CREATE table attrib ASSELECT 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_galFROM DUALUNIONSELECT 1 AS cust_ID,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,TO_DATE('01-JUN-2010') AS atb_start_dt,TO_DATE('30-SEP-2010') AS atb_end_dt,1.70 AS per_kwh,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,'WATER' AS atb_cd,'GlacialGold' AS provIDer,0 AS per_kwh,0.60 AS per_galFROM 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     01        01-FEB-2010  28-FEB-2010  EAST                                    0        01        01-MAR-2010  31-MAR-2010  EAST    CoalGuys        GlacialGold     1.60     0.601        01-APR-2010  31-MAY-2010  EAST                    GlacialGold     0        0.601        01-JUN-2010  31-Jul-2010  EAST    LocalNuke       GlacialGold     1.70     0.601        01-FEB-2011  28-FEB-2011  EAST                    GlacialGold     0        0.601        01-MAR-2011  31-DEC-2011  EAST    GeoHeat         GlacialGold     1.10     0.60

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

虽然性能很重要,但我试图找到直接/批量sql解决方案的最大原因是维护.我的原始解决方案的if / else光标嵌套已经难以遵循,并且至少还有2个“属性”跨度要分裂,指数会更糟.

如果您能提供任何帮助,我将不胜感激.

解决方法 这可能有效.它不会将连续的区域合并在一起,但它仍然应该完成工作.

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_galFROM  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

总结

以上是内存溢出为你收集整理的如何将多个日期跨度合并/拆分为一个时间轴(Oracle 11g)？全部内容，希望文章能够帮你解决如何将多个日期跨度合并/拆分为一个时间轴(Oracle 11g)？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。