甲骨文GRoup 7天

Question

我试着为每个约会安排七天的时间，但这似乎行不通。

 select 
 START_DATE-7 FROM_DATES,
 SUM(EVENT_B),
 SUM(EVENT_B)  
  FROM (
  select 
  nvl(ENT1.DATE_FIELD,ENT2.DATE_FIELD)as START_DATE,
  nvl(ENT1.EVENT_A,0) as EVENT_A,
  nvl(ENT2.EVENT_B,0) as EVENT_B
  from 
 (select 
  DATE_FIELD, SUM(NR_EVENTS) as EVENT_A
  from TABLE_A
  where  EVENT_NAME in ('CALL',  'EDIT','INSERT') 
  group by START_DATE,START_HOUR,SUBSCRIBER_TYPE,CO_ID
) ENT1 
 FULL OUTER JOIN
(select 
 DATE_FIELD,
 SUM(NR_EVENTS) as EVENT_B
 from TABLE_B
 where  EVENT_NAME in ('CALL',  'EDIT','INSERT') 
 group by DATE_FIELD
) ENT2 
on ( 
ENT1.DATE_FIELD=ENT2.DATE_FIELD)
)
WHERE 
START_DATE >= START_DATE-7
AND START_DATE <= START_DATE-1
gROUP BY 
START_DATE-7

得到的输出：

9/8/2014    262187  280365
9/7/2014    509405  478245
9/6/2014    564663  537996
9/5/2014    661871  632689
9/4/2014    669788  638839

只有一天而不是7天

输出应该是

2014-09-15，(A方在7天内的事件之和- 2014-09-15-7到2014-09-15)，(B方7天的事件之和- 2014-09-15-7到2014-09-15)

2014-09-14，(A方在7天内的事件之和- 2014-09-14-7到2014-09-14)，(B方7天的事件之和- 2014-09-14-7到2014-09-14)

2014-09-13，(A方在7天内的事件之和- 2014-09-13-7到2014-09-13)，(B方7天的事件之和- 2014-09-13-7到2014-09-13)

等等。

有人能帮我吗？

Answer 1

with event_dates as (
  select d_field from table_a
  union
  select d_field from table_b
)
select
  d_field from_date,
  d_field to_date,
  (
    select nvl(sum(nr_events),0) from table_a
    where event_name in ('CALL', 'EDIT', 'INSERT')
    and d_field between e.d_field and e.d_field+6
  ) nr_events_a,
  (
    select nvl(sum(nr_events),0) from table_b
    where event_name in ('CALL', 'EDIT', 'INSERT')
    and d_field between e.d_field and e.d_field+6
  ) nr_events_b
from event_dates e;

我根据你最初的文章和拉利特库马尔B的笔记制作了拉利特库马尔B。上面的答案包括event_name字段过滤器，这是后来添加的。

Answer 2

您的查询不正确。

首先，您的输出与所选列不匹配。如果第一列是D_FIELD1+7，那么在输出中第一列值如何是D_FIELD1？

第二，谓词AND D_FIELD1 BETWEEN D_FIELD1+7 AND D_FIELD1是模糊的。

第三，BETWEEN将包括上、下界，因此您将得到8天的窗口，而不是7天。

Answer 3

您可以使用一个具有间隔期的Analytic函数。

SELECT nvl(ent1.date_field, ent2.date_field) - 6 start_date
     , nvl(ent1.date_field, ent2.date_field) end_date
     , sum(event_a) OVER
         (ORDER BY nvl(ent1.date_field, ent2.date_field)
          RANGE numtodsinterval(6, 'day') PRECEDING
         ) sum_event_a
     , sum(event_b) OVER
         (ORDER BY nvl(ent1.date_field, ent2.date_field)
          RANGE numtodsinterval(6, 'day') PRECEDING
         ) sum_event_b
FROM (SELECT date_field,
             sum(nr_events) AS event_a
      FROM table_a
      WHERE event_name IN ('CALL', 'EDIT', 'INSERT') 
      GROUP BY date_field
     ) ent1 
     FULL OUTER JOIN
     (SELECT date_field,
             sum(nr_events) AS event_b
      FROM table_b
      WHERE event_name IN ('CALL', 'EDIT', 'INSERT') 
      GROUP BY date_field
     ) ent2
       ON (ent1.date_field = ent2.date_field)