分析函式之視窗子句
關於分析函式，可能大家基本都是從row_number()開始瞭解到的。分析函式的使用在某種程度上可以避免自連線，使得原本較為繁瑣複雜的查詢一下子變得精簡起來。
分析函式分為分割槽子句，排序子句，和視窗子句，對於視窗子句來說，可能開始比較難懂，這部分的使用也尤為重要。
還是先舉個例子，然後基於例子再來簡單分析一下分析函式。
我們建立一個測試表sales_fact
create table sales_fact(
product varchar2(200) not null,
country varchar2(100),
region varchar2(100),
year number,
week number,
sale number(10,2)
);
然後使用以下的pl/sql插入一部分資料，我們來針對美國的牛肉貿易來做一個簡單的分析。
declare
tmp_sql varchar2(1000);
begin
for tmp_year in 2012..2014 loop
for i in 1..50 loop
insert into sales_fact values('BEEF','USA','NOUTH',tmp_year,i,abs(mod(dbms_random.random,12)*100));
end loop;
end loop;
end;
/
使用分析函式中的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
輸出結果會大大不同。原因在於rows between unbounded preceding and current row是一種視窗函式，是相關分析函式的預設值，如果知道那個為unbounded following就會統計自1月份到12月份的銷售額。
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的使用，情況也是類似。我們可以根據需要來選擇資料的範圍來得到最大值。
####### 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
比如2012年第1周的銷售額是400，最高銷售額是的當年的1100.
YEAR WEEK SALE MAX_SALE
---------- ---------- ---------- ----------
2012 1 400 1100
再來看看另外一個Max的使用。不同之處在於視窗函式的部分。
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
比如2012年第2周，相比於第2周來說，最高銷售額是第1周的400。第3周的時候相比第1周，第2周，最高銷售額是第3周的600.
YEAR WEEK SALE MAX_SALE
---------- ---------- ---------- ----------
2012 1 400 400
2012 2 100 400
2012 3 600 600
從實際的使用角度來說，使用rows between unbounded preceding and current row 得到的資料是截止到指定時間的最大值，而rows between unbounded preceding and unbounded following得到的是歷史資料最大值。
對於視窗函式的使用不限於此，我們還可以指定更細粒度的資料區間。
像rows between 2 preceding and 2 following 比較的資料就是當前行的前2行和後2行對應的區間的資料。
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
比如說對於2012年第6中，銷售額為0，但是在前2周和後2周的區間範圍內，銷售額最大值為600.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1405507/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle分析函式與視窗函式Oracle函式
- SQL視窗分析函式使用詳解系列三之偏移量類視窗函式SQL函式
- 分析函式視窗子句 RANGE/ROWS 差別函式
- hive視窗分析函式使用詳解系列二之分組排序視窗函式Hive函式排序
- 分析函式用法及視窗子句 range/rows差別函式
- 視窗函式函式
- Oracle分析函式之開窗函式over()詳解Oracle函式
- PostgreSQL：視窗函式SQL函式
- SQL 視窗函式SQL函式
- Mysql視窗函式MySql函式
- Hive視窗函式Hive函式
- hive視窗分析函式使用詳解系列一Hive函式
- hive視窗函式使用Hive函式
- 【函式】oracle視窗函式over()的理解函式Oracle
- 與SQL視窗函式相同SQL函式
- PostgreSQL>視窗函式的用法SQL函式
- MySQL視窗函式彙總MySql函式
- hive05_視窗函式Hive函式
- mysql視窗函式中的滑動視窗MySql函式
- oracle的分析函式over 及開窗函式Oracle函式
- 【SQL】Lag/Rank/Over視窗函式揭秘，資料分析之旅SQL函式
- MySQL 8.0 視窗函式-筆記MySql函式筆記
- Hive視窗函式保姆級教程Hive函式
- Hive 視窗函式（Windowing Functions）Hive函式Function
- 用函式控制彈出視窗函式
- MySQL視窗函式用法總結MySql函式
- 常用分析函式開窗講解函式
- oracle的sql查詢分析函式-高階部分-分析函授over()子句OracleSQL函式
- oracle的分析函式over(Partition by...) 及開窗函式Oracle函式
- Flink處理函式實戰之四：視窗處理函式
- [clickhouse] Clickhouse之開窗函式篇函式
- PostgreSQL 視窗函式 ( Window Functions ) 如何使用？SQL函式Function
- 通俗易懂：視窗函式 | 全是案例函式
- 詳解SQL操作的視窗函式SQL函式
- 視窗屬性的獲取函式函式
- Oracle NTH_VALUE分析函式及ROWS BETWEEN UNBOUNDED PRECEDING AND子句Oracle函式
- 【Analytic】分析函式之MIN函式函式
- 【Analytic】分析函式之MAX函式函式