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

几天来我一向在全力办理这个题目,此刻我转向群众寻求辅佐.

我的题目与此网站上的先前办理方案相同,但不完全沟通：
PL/SQL Split,separate a date into new dates according to black out dates!
这个办理方案相等布尔(包括/解除),而我的题目涉及个中的一些以及归并.

固然我认为我对SQL PL / SQL有中级/高级的把握…但Oracle Analytic函数显然让我感想狐疑.我一向在全力阅读/进修,但我已经没时刻了.

因为我不确定共享表名(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,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_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光标嵌套已经难以遵循,而且至少尚有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_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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!