ORACLE 分析函数

首先必须说明，这是别人的文章的个人阅读总结版本，对此非常感谢，因为感觉很棒，实际工作中已经帮助了我很多！

	|-统计方面
			|-公式
					|-Sum( ) Over ([Partition by  ] [Order by  ])
					|-Sum( ) Over ([Partition by  ] [Order by  ]  Rows Between   Preceding And   Following)
					|-Sum( ) Over ([Partition by  ] [Order by  ] Rows Between   Preceding And Current Row)
					|-Sum( ) Over ([Partition by  ] [Order by  ] Range Between Interval ' ' 'Day' Preceding And Interval ' ' 'Day' Following )
			|-例子
					|-
						--创建订单表
						CREATE TABLE order_record (
						    who VARCHAR2(30) NOT NULL,
						    whe VARCHAR2(100) NOT NULL,
						    yer NUMBER(4),
						    moh NUMBER(2),
						    total_oraders NUMBER(7),
						    total_sales NUMBER(11, 2)
						);
						
						SELECT all_sales.*, round(all_sales.sales/ all_sales.region_sales, 4)* 100 ||'%'
						FROM (
						    SELECT 
						    		o.whe, o.who, 
						    		SUM(o.total_sales) sales, 
						    		SUM(SUM(o.total_sales)) over(PARTITION BY whe) region_sales
						    FROM order_record o
						    GROUP BY whe, who
						) all_sales
						WHERE all_sales.sales> all_sales.region_sales* 0.5;


	|-排列方面
			|-公式
					|-Rank() Over ([Partition by  ] [Order by  ] [Nulls First/Last])
							|-相同数据排名一致，但下一记录会空出其中的排名
							|-数据	排名
								A			1
								B			2
								B			2
								C			4
					|-Dense_rank() Over ([Patition by  ] [Order by  ] [Nulls First/Last])
							|-相同数据排名一致，之后数据接上一次序继续
							|-数据	排名
								A			1
								B			2
								B			2
								C			3
					|-Row_number() Over ([Partitionby  ] [Order by  ] [Nulls First/Last])
							|-碰到相同数据时不做处理依照记录顺序依次递增
							|-数据	排名
								A			1
								B			2
								B			3
								C			4
					|-Ntile( ) Over ([Partition by  ] [Order by  ])
			|-例子
					|-
						SELECT  
						    region_id, customer_id, 
						    sum(customer_sales) total,
						    --按照地区内进行销售总额的排序
						    rank() over(PARTITION BY region_id ORDER BY SUM(customer_sales) desc) rank,	
						    dense_rank() over(PARTITION BY region_id ORDER BY SUM(customer_sales) desc) dense_rank,
						    row_number() over(PARTITION BY region_id ORDER BY SUM(customer_sales) desc) row_number
						FROM user_order
						GROUP BY region_id, customer_id;


	|-最大值/最小值查找方面
			|-公式
					|-Min( )/Max( ) Keep (Dense_rank First/Last [Partition by  ] [Order by  ])
					|-Ratio_to_report(value)
							|-Ratio_to_report(value)= value/ sum(value)
							|-函数允许我们计算每条记录在其对应记录集或其子集中所占的比例
			|-例子
					|-	--获取各地区订单排名前三的客户，其中空值放置于最后
						SELECT *
						FROM(SELECT region_id,
						     customer_id,
						     SUM(customer_sales) cust_total,
						     SUM(SUM(customer_sales)) over(PARTITION BY region_id) reg_total,	--获取总订单信息
						     --按照区域进行分区，按照订单额度进行排序，其中空值置于最后
						     rank() over(PARTITION BY region_id ORDER BY SUM(customer_sales) desc NULLS LAST) rank
						FROM user_order
						GROUP BY region_id, customer_id)
						WHERE rank <= 3;
						
						--查询订单最多和最少的信息
						SELECT  
								--MIN用于确保返回唯一记录
								--KEEP用于告知Oracle保留符合KEEP条件的记录
								--dense_rank为固定写法，不可更改
						       MIN(customer_id) KEEP (dense_rank FIRST ORDER BY SUM(customer_sales) DESC) first,
						       MIN(customer_id) KEEP (dense_rank LAST ORDER BY SUM(customer_sales) DESC) last
						FROM user_order
						GROUP BY customer_id;
						
						--查询订单总额排名前1/5的客户
						SELECT t.*
						FROM (
						    SELECT 
						    		region_id, customer_id, 
						    		--ntile中参数为5表示将按照条件将符合的记录分为5份，内容即为1...5
						    		ntile(5) over(ORDER BY SUM(customer_sales) DESC) til
						    FROM user_order
						    GROUP BY region_id, customer_id;
						)t
						WHERE t.til= 1;
						
						--统计当天和五天内的平均销售额
						SELECT
						       trunc(order_dt) DAY,
						       SUM(sale_price) daily_sales,
						       AVG(SUM(sale_price)) over (ORDER BY trunc(order_dt)
						          	 RANGE BETWEEN INTERVAL '2' DAY preceding 
						                 	AND INTERVAL '2' DAY following) five_day_avg
						from cust_order
						where sale_price IS NOT NULL
						      AND order_dt BETWEEN to_date('01-jul-2001','dd-mon-yyyy')
						          	AND to_date('31-jul-2001','dd-mon-yyyy')
						 
						 
						 --查询上、本、下个月的销售信息，并计算平均值
						 SELECT 
						    	MONTH,
							    first_value(SUM(tot_sales)) over (ORDER BY MONTH
							        	ROWS BETWEEN 1 preceding AND 1 following) prev_month,
							    SUM(tot_sales) monthly_sales,
							    last_value(SUM(tot_sales)) over (ORDER BY MONTH
							        	ROWS BETWEEN 1 preceding AND 1 following) next_month,
							    avg(sum(tot_sales)) over (ORDER BY MONTH
							       		ROWS BETWEEN 1 preceding AND 1 following) rolling_avg
						FROM orders
						WHERE YEAR= 2001 
						AND region_id = 6
						GROUP BY MONTH
						ORDER BY MONTH;
						 
						 
						 --查询本月和上月的销售额
						 SELECT   
						      MONTH ,            
						      SUM (tot_sales) monthly_sales,
						      lag(SUM (tot_sales), 1) over (ORDER BY MONTH) prev_month_sales
						FROM orders
						WHERE YEAR= 2001 AND region_id = 6
						GROUP BY MONTH
						ORDER BY MONTH ;



	|-首记录/末记录查找方面
			|-公式
					|-First_value / Last_value(Sum( ) Over ([Patition by  ] [Order by  ] Rows Between   Preceding And   Following  ))
			|-例子
					|-
						--窗口函数实现数据统计
						SELECT  
							  month,
							  SUM(tot_sales) month_sales,
							  --第一层SUM是用于group by的按月份合并，第二次则是对于指定的月份记录的数据合并 
							  SUM(SUM(tot_sales)) over (ORDER BY month	
					  				--unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。
					  				--unbounded following
					  						--更改为current row即表示记录之前到现在的信息汇总，累计效果
					  						--更改为1 following则表示将下一条记录列入范围中
							  		ROWS BETWEEN unbounded preceding AND unbounded following) total_sales
						FROM orders
						GROUP BY month;


	|-相邻记录之间比较方面
			|-公式
					|-Lag(Sum( ), 1) Over([Patition by  ] [Order by  ])
			|-例子
					|-