oracleconnectby和分析函數(shù)總結(jié)(編輯修改稿)
2025-07-22 07:15 本頁面
【文章內(nèi)容簡介】 total,2rank()over(orderbysum(customer_sales)desc)rank,3dense_rank()over(orderbysum(customer_sales)desc)dense_rank,4row_number()over(orderbysum(customer_sales)desc)row_number5fromuser_order6groupbyregion_id,customer_id。REGION_IDCUSTOMER_IDTOTALRANKDENSE_RANKROW_NUMBER 818125384011111152122499212121292312249921212139241224992121214103012168581513 15(Top/Bottom N、First/Last、NTile)一、Top/Bottom N查詢：找出所有訂單總額排名前3的大客戶：SQLselect*SQLfrom(selectregion_id,SQLcustomer_id,SQLsum(customer_sales)cust_total,SQLrank()over(orderbysum(customer_sales)descNULLSLAST)rankSQLfromuser_orderSQLgroupbyregion_id,customer_id)SQLwhererank=3。REGION_IDCUSTOMER_IDCUST_TOTALRANK925223270318171944281271419297743 First/Last排名查詢：找出訂單總額最多、最少的客戶。SQLselectmin(customer_id)2keep(dense_rankfirstorderbysum(customer_sales)desc)first,3min(customer_id)4keep(dense_ranklastorderbysum(customer_sales)desc)last5fromuser_order6groupbycustomer_id。FIRSTLAST311Min：函數(shù)的作用是用于當存在多個First/Last情況下保證返回唯一的記錄。（去掉會出錯）Keep：從上面的結(jié)果我們已經(jīng)知道Oracle對排名的結(jié)果只“保留”2條數(shù)據(jù)，這就是keep的作用。告訴Oracle只保留符合keep條件的記錄。dense_rank：是告訴Oracle排列的策略，first/last則告訴最終篩選的條件。三、按層次查詢（NTile）找出訂單總額排名前1/5的客戶。SQLselectregion_id,2customer_id,3ntile(5)over(orderbysum(customer_sales)desc)til4fromuser_order5groupbyregion_id,customer_id。REGION_IDCUSTOMER_IDTILE1031192511026161
數(shù)學相關(guān)推薦
鄂ICP備17016276號-1