over 之rows 与range窗口区别

select * from t order by t.area_code,t.bill_month;
-- 逻辑范围  根据order by 中的字段计算逻辑上的范围
/*
如下表中数据实际只有5,6,8月分的
月分   计算范围  实际
5      4+5+6     5+6
6      5+6+7     5+6
8      7+8+9     8
*/
select area_code,bill_month,sum(countId) over(partition by area_code order by bill_month range between 1 preceding and 1 following)
 from(
select 
       t.area_code,t.bill_month,count(area_code) as countId
       from t group by t.area_code,t.bill_month
       
       );
 -- 物理范围，根据行找      
select area_code,bill_month,sum(countId) over(partition by area_code order by bill_month rows between 1 preceding and 1 following)
 from(
select 
       t.area_code,t.bill_month,count(area_code) as countId
       from t group by t.area_code,t.bill_month
       
       );

 

create table t(
       bill_month number, 
       area_code number,
       net_type char, 
       local_fare number
);


declare
  i      number;
  period number := 200405;
  a_code number := 5060;
  v_type char(1) := 'G';
  num    number;
  k      number;
begin
  
  for k in 0 .. 3 loop
    period := period+k;
    a_code := 5060;
    for i in 0 .. 9 loop
      if mod(i, 2) = 0 then
        a_code := a_code + 1;
      end if;
      if (mod((i + 1), 2) != 0) then
        v_type := 'J';
      end if;
      if (mod((i + 1), 2) = 0) then
        v_type := 'G';
      end if;
      num := DBMS_RANDOM.RANDOM;
      num := num / 100;
      if num < 0 then
        num := -1 * num;
      end if;
      insert into t
        (bill_month, area_code, net_type, local_fare)
      values
        (period, a_code, v_type, num);
    end loop;
  end loop;
  commit;

end;

 

range前的order by 字段只能是数字吗？
转成日期比较会出错，那要统计如“201001”时，就统计不到200912了，这种情况该如何解决