下面只做备份:
目录 SQL> select region_id, customer_id,
2 sum(customer_sales) cust_sales, 3 sum(sum(customer_sales)) over(partition by region_id) ran_total, 4 rank() over(partition by region_id 5 order by sum(customer_sales) desc) rank 6 from user_order 7 group by region_id, customer_id; REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK ---------- ----------- ---------- ---------- ---------- 10 31 6238901 1 10 26 1808949 6238901 2 10 27 1322747 6238901 3 10 30 1216858 6238901 4 10 28 986964 6238901 5 10 29 903383 6238901 6 我们看到这里有一条记录的CUST_TOTAL字段值为NULL,但居然排在第一名了!显然这不符合情理。所以我们重新调整完善一下我们的排名策略,看看下面的语句: SQL> select region_id, customer_id,
2 sum(customer_sales) cust_total, 3 sum(sum(customer_sales)) over(partition by region_id) reg_total, 4 rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank 5 from user_order 6 group by region_id, customer_id; REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK ---------- ----------- ---------- ---------- ---------- 10 26 1808949 6238901 1 10 27 1322747 6238901 2 10 30 1216858 6238901 3 10 28 986964 6238901 4 10 29 903383 6238901 5 10 31 6238901 6 绿色高亮处,NULLS
LAST/FIRST告诉Oracle让空值排名最后后第一。 SQL> select * SQL> from (select region_id, SQL> customer_id, SQL> sum(customer_sales) cust_total, SQL> rank() over(order by sum(customer_sales) desc NULLS LAST) rank SQL> from user_order SQL> group by region_id, customer_id) SQL> where rank <= 3; REGION_ID CUSTOMER_ID CUST_TOTAL RANK ---------- ----------- ---------- ---------- 9 25 2232703 1 8 17 1944281 2 7 14 1929774 3 SQL> 【2】找出每个区域订单总额排名前3的大客户: SQL> select * 2 from (select region_id, 3 customer_id, 4 sum(customer_sales) cust_total, 5 sum(sum(customer_sales)) over(partition by region_id) reg_total, 6 rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST) rank 7 from user_order 8 group by region_id, customer_id) 9 where rank <= 3; REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK ---------- ----------- ---------- ---------- ---------- 5 4 1878275 5585641 1 5 2 1224992 5585641 2 5 5 1169926 5585641 3 6 6 1788836 6307766 1 6 9 1208959 6307766 2 6 10 1196748 6307766 3 7 14 1929774 6868495 1 7 13 1310434 6868495 2 7 15 1255591 6868495 3 8 17 1944281 6854731 1 8 20 1413722 6854731 2 8 18 1253840 6854731 3 9 25 2232703 6739374 1 9 23 1224992 6739374 2 9 24 1224992 6739374 2 10 26 1808949 6238901 1 10 27 1322747 6238901 2 10 30 1216858 6238901 3 18 rows selected. 三、First/Last排名查询: 想象一下下面的情形:找出订单总额最多、最少的客户。按照前面我们学到的知识,这个至少需要2个查询。第一个查询按照订单总额降序排列以期拿到第一名,第二个查询按照订单总额升序排列以期拿到最后一名。是不是很烦?因为Rank函数只告诉我们排名的结果,却无法自动替我们从中筛选结果。 幸好Oracle为我们在排列函数之外提供了两个额外的函数:first、last函数,专门用来解决这种问题。还是用实例说话: SQL> select min(customer_id) 2 keep (dense_rank first order by sum(customer_sales) desc) first, 3 min(customer_id) 4 keep (dense_rank last order by sum(customer_sales) desc) last 5 from user_order 6 group by customer_id; FIRST LAST ---------- ---------- 31 1 这里有几个看起来比较疑惑的地方: ①为什么这里要用min函数 ②Keep这个东西是干什么的 ③fist/last是干什么的 ④dense_rank和dense_rank()有什么不同,能换成rank吗? 首先解答一下第一个问题:min函数的作用是用于当存在多个First/Last情况下保证返回唯一的记录。假如我们去掉会有什么样的后果呢? SQL> select keep (dense_rank first order by sum(customer_sales) desc) first, 2 keep (dense_rank last order by sum(customer_sales) desc) last 3 from user_order 4 group by customer_id; select keep (dense_rank first order by sum(customer_sales) desc) first, * ERROR at line 1: ORA-00907: missing right parenthesis 接下来看看第2个问题:keep是干什么用的?从上面的结果我们已经知道Oracle对排名的结果只“保留”2条数据,这就是keep的作用。告诉Oracle只保留符合keep条件的记录。 那么什么才是符合条件的记录呢?这就是第3个问题了。dense_rank是告诉Oracle排列的策略,first/last则告诉最终筛选的条件。 第4个问题:如果我们把dense_rank换成rank呢? SQL> select min(region_id) 2 keep(rank first order by sum(customer_sales) desc) first, 3 min(region_id) 4 keep(rank last order by sum(customer_sales) desc) last 5 from user_order 6 group by region_id; select min(region_id) * ERROR at line 1: ORA-02000: missing DENSE_RANK 四、按层次查询: 现在我们已经见识了如何通过Oracle的分析函数来获取Top/Bottom
N,第一个,最后一个记录。有时我们会收到类似下面这样的需求:找出订单总额排名前1/5的客户。 SQL> select region_id, 2 customer_id, 3 ntile(5) over(order by sum(customer_sales) desc) til 4 from user_order 5 group by region_id, customer_id; REGION_ID CUSTOMER_ID TILE ---------- ----------- ---------- 10 31 1 9 25 1 10 26 1 6 6 1 8 18 2 5 2 2 9 23 3 6 9 3 7 11 3 5 3 4 6 8 4 8 16 4 6 7 5 10 29 5 5 1 5 Ntil函数为各个记录在记录集中的排名计算比例,我们看到所有的记录被分成5个等级,那么假如我们只需要前1/5的记录则只需要截取TILE的值为1的记录就可以了。假如我们需要排名前25%的记录(也就是1/4)那么我们只需要设置ntile(4)就可以了。 参考资料:《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2) ------------------------------------------------------------- 生活就像打牌,不是要抓一手好牌,而是要尽力打好一手烂牌。 |
|