# 分析函数之窗口子句(r4笔记第3天)

```使用分析函数中的sum,这个sum和平时使用的sum还是有很大的不同。这个sum会按照年份来统计自1月份到当前月份的销售额。比如2012年2月的累计销售额就是100+400=500

#######  sum #############
select year,week,sale,
sum(sale) over(
partition by product,country,region,year
order by  week
rows between  unbounded preceding and current row
)  running_sum_ytd
from sales_fact
where country='USA' and product='BEEF' and  year in (2012,2013)
order by product,country,year,week;
YEAR        WEEK       SALE RUNNING_SUM_YTD
---------- ---------- ----------  ---------------
2012          1        400             400
2012          2         100             500
2012          3        600            1100
2012          4         100            1200
2012          5        200            1400
2012          6          0            1400
2012          7         100            1500
2012          8        600            2100
2012          9        300            2400
2012         10         700            3100
2012         11        400             3500
......
YEAR       WEEK       SALE RUNNING_SUM_YTD
----------  ---------- ---------- ---------------
2012         45         300           22900
2012         46        900           23800
2012         47        300           24100
2012         48         800           24900
2012         49        400           25300
2012         50        100           25400
2013          1         100             100
2013          2        600             700
2013          3        800            1500
2013          4        1000            2500
2013          5       1000            3500```
```对于上面的查询我们只修改一处。把rows between  unbounded preceding and current row修改为rows between unbounded preceding and unbounded  following

select year,week,sale,
sum(sale)  over(
partition by product,country,region,year
order by week
rows between unbounded preceding and unbounded  following
) running_sum_ytd
from  sales_fact
where country='USA' and product='BEEF'  and year in  (2012,2013)
order by product,country,year,week;
YEAR       WEEK       SALE RUNNING_SUM_YTD
---------- ----------  ---------- ---------------
2012          1        400           25400
2012          2         100           25400
2012          3        600           25400
2012          4        100           25400
2012          5         200           25400
2012          6          0           25400
2012          7        100           25400
2012          8         600           25400
2012          9        300           25400
2012         10        700           25400
2012         11         400           25400
...
YEAR       WEEK       SALE  RUNNING_SUM_YTD
---------- ---------- ---------- ---------------
2012         45        300           25400
2012         46         900           25400
2012         47        300           25400
2012         48        800           25400
2012         49         400           25400
2012         50        100           25400
2013          1        100           28700
2013          2         600           28700
2013          3        800           28700
2013          4       1000           28700
2013          5        1000           28700

####### max  ############
select year,week,sale,
max(sale) over(
partition by product,country,region,year
order by  week
rows between  unbounded preceding and unbounded following
) max_sale
from sales_fact
where country='USA' and product='BEEF' and year in  (2012,2013)
order by product,country,year,week;
YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
2012          1        400       1100
2012          2        100       1100
2012          3        600        1100
2012          4        100       1100
2012           5        200       1100
2012          6          0       1100
2012          7        100       1100
2012          8        600        1100
2012          9        300       1100
2012          10        700       1100
2012         11        400        1100
...
YEAR       WEEK       SALE   MAX_SALE
----------  ---------- ---------- ----------
2012         45        300        1100
2012         46        900       1100
2012          47        300       1100
2012         48        800       1100
2012         49        400       1100
2012         50        100        1100
2013          1        100       1100
2013           2        600       1100
2013          3        800       1100
2013          4       1000       1100
2013          5       1000        1100```

```select  year,week,sale,
max(sale) over(
partition by  product,country,region,year
order by  week
rows between  unbounded preceding and current row
) max_sale
from sales_fact
where country='USA' and product='BEEF'  and year in  (2012,2013)
order by product,country,year,week;
YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
2012          1        400        400
2012          2        100        400
2012          3        600         600
2012          4        100        600
2012           5        200        600
2012          6          0        600
2012          7        100        600
2012          8        600         600
2012          9        300        600
2012          10        700        700
2012         11        400         700
...
YEAR       WEEK       SALE   MAX_SALE
----------  ---------- ---------- ----------
2012         45        300        1100
2012         46        900       1100
2012          47        300       1100
2012         48        800       1100
2012         49        400       1100
2012         50        100        1100
2013          1        100        100
2013           2        600        600
2013          3        800        800
2013          4       1000       1000
2013          5       1000        1000```

```     YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
2012          1        400        400
2012          2        100        400
2012          3        600         600```

```select  year,week,sale,
max(sale) over(
partition by  product,country,region,year
order by  week
rows between 2  preceding and 2 following
) max_sale
from  sales_fact
where country='USA' and product='BEEF'  and year in  (2012,2013)
order by product,country,year,week;
YEAR       WEEK       SALE   MAX_SALE
---------- ----------  ---------- ----------
2012          1        400        600
2012          2        100        600
2012          3        600         600
2012          4         100        600
2012          5        200        600
2012          6          0         600
2012           7        100        600
2012          8        600        700
2012          9         300        700
2012         10        700        700
2012         11        400        700
...
YEAR       WEEK        SALE   MAX_SALE
---------- ---------- ---------- ----------
2012         45        300       1000
2012         46        900        1000
2012         47        300        900
2012          48        800        900
2012         49        400        800
2012         50        100        800
2013          1        100         800
2013          2        600       1000
2013           3        800       1000
2013          4       1000       1000
2013          5       1000       1000```

1466 篇文章163 人订阅

0 条评论

## 相关文章

42670

37290

27440

35360

29980

622100

86880

### 返璞归真：如何判断一个初始化参数是否来自默认设置

? 杨廷琨（yangtingkun） 云和恩墨 CTO 高级咨询顾问，Oracle ACE 总监，ITPUB Oracle 数据库管理版版主 Oracle初...

29970

32860

427100