Oracle Analytic Functions 3

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.