窗口函数 select makedate from (select makedate, sum(decode('雨',1,0)) over(order by makedate rows between 0 preceding and 2 following) dd from TA) where dd>=2
少了个参数 select makedate from (select makedate, sum(decode(weather,'雨',1,0)) over(order by makedate rows between 0 preceding and 2 following) dd from TA) where dd>=2
sum() over(),结构的写法中,over是sum函数的开窗函数,sum是按照over中指定的分区进行统计的。 over(order by makedate rows between 0 preceding and 2 following) 上面的开窗函数,是按照makedate进行排序,分区范围是以当前行为基准的,前0行(0 preceding )到后2行(2 following)的数据,这里是按行号进行的分区。 按你的需求应当是按照值进行分区,那么就要更换一个关键词,把rows换成range,函数如下: over(order by makedate range between 0 preceding and 2 following) 更改后的函数是按照当前行的makedate的值进行分区,分区条件是从当前记录相等值的记录到大于两天的记录。
内容如:
2011-10-1 ,晴;
2011-10-2 ,雨;
2011-10-3 ,晴;
2011-10-4 ,雨
select makedate from
(select makedate,
sum(decode('雨',1,0))
over(order by makedate rows between 0 preceding and 2 following) dd
from TA) where dd>=2
select makedate from
(select makedate,
sum(decode(weather,'雨',1,0))
over(order by makedate rows between 0 preceding and 2 following) dd
from TA) where dd>=2
能详细解释一下吗?还没有用过窗口函数。preceding、following,用在这里是什么意思。
range函数的用法,能讲一下吗?
sum() over(),结构的写法中,over是sum函数的开窗函数,sum是按照over中指定的分区进行统计的。
over(order by makedate rows between 0 preceding and 2 following)
上面的开窗函数,是按照makedate进行排序,分区范围是以当前行为基准的,前0行(0 preceding )到后2行(2 following)的数据,这里是按行号进行的分区。
按你的需求应当是按照值进行分区,那么就要更换一个关键词,把rows换成range,函数如下:
over(order by makedate range between 0 preceding and 2 following)
更改后的函数是按照当前行的makedate的值进行分区,分区条件是从当前记录相等值的记录到大于两天的记录。
讲的很详细,受教了。