Oracle Analytic Functions 3

Top/Bottom Nfirst/Lastntile--① Ranking of all customers by order total--② ranking by region and customer order totals--③ Find the top 13 customers for the total order--④ Find the customer with the highest and lowest order total--⑤ Find the top 25% of total orders--here Null is ranked first, you can add nulls last to put null data in the endSelectregion_id, customer_id,sum(customer_sales) cust_sales,sum(sum(Customer_sales)) Over(Partition byregion_id) Ran_total,rank () Over(Partition byregion_idOrder  by sum(Customer_sales)desc /*nulls Last*/) Rank fromUser_orderGroup  byregion_id, customer_id;--Find out the top 3 big customers for all order totalsSelect *  from (Selectregion_id, customer_id,sum(customer_sales) cust_total, rank () Over(Order  by sum(Customer_sales)descNULLS last) rank fromUser_orderGroup  byregion_id, customer_id)whereRank<= 3;--identify the top 3 major customers for each region's order totalsSelect * from(Selectregion_id, customer_id,sum(customer_sales) cust_total,sum(sum(Customer_sales)) Over(Partition byregion_id) Reg_total, rank () Over(Partition byregion_idOrder  by sum(Customer_sales)descNULLS last) rank fromUser_orderGroup  byregion_id, customer_id)whereRank<= 3;--min Keep First last find customers with the highest and lowest order total--min can only be used for Dense_rank--the Min function is used to guarantee that a unique record is returned when there are multiple first/last cases, and the error is removed .--the role of Keep. Tells Oracle to keep only records that meet the keep criteria. Select min(customer_id) Keep (Dense_rank firstOrder  by sum(Customer_sales)desc) First,min(customer_id) Keep (Dense_rank lastOrder  by sum(Customer_sales)desc) Last fromUser_orderGroup  bycustomer_id;--Top 1/5 Customer ntile of total order--1. Divide the data into 5 piecesSelectregion_id,customer_id,sum(customer_sales) sales,ntile (5) Over(Order  by sum(Customer_sales)descnulls last) tile fromUser_orderGroup  byregion_id, customer_id;--2. Extracting data from tile=1Select *  from (Selectregion_id,customer_id,sum(customer_sales) sales,ntile (5) Over(Order  by sum(Customer_sales)descnulls last) tile fromUser_orderGroup  byregion_id, customer_id)whereTile= 1;--Cust_nbr,month primary key, go to heavy, leaving only the largest record of month--find CUST_NBR the same, month's largest recordSelectCUST_NBR,Max(Month) Keep (Dense_rank firstOrder  by Month desc) Max_month fromOrders_tmpGroup  byCUST_NBR;--go to Heavy, cust_nbr,month primary key, CUST_NBR same, leave only month biggest recordDelete  fromOrders_tmp2where(CUST_NBR,Month) not inch (SelectCUST_NBR,Max(Month) Keep (Dense_rank firstOrder  by Month desc) Max_month fromORDERS_TMP2 TBGroup  byCUST_NBR)

Oracle Analytic Functions 3