partition by :分组
①ROW_NUMBER:
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②DENSE_RANK:(稠密即连续)
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
③RANK:
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
如下图:
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
8 18 1253840 11 11 11
5 2 1224992 12 12 12
9 23 1224992 12 12 13
9 24 1224992 12 12 14
10 30 1216858 15 13 15
NULLS LAST:空值排最后
rank() over(partition by region_id order by sum(customer_sales) desc NULLS LAST
一、带空值的排列:
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,但居然排在第一名了!显然这不符合情理。所以我们重新调整完善一下我们的排名策略,看看下面的语句:
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让空值排名最后后第一。
二、Top/Bottom N查询:找出所有订单总额排名前3的大客户:
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
三、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
select min(t.citycode)keep(dense_rank first order by sum(t.quantity) desc) 出票数最高城市,
min(t.citycode) keep(dense_rank last order by sum(t.quantity) desc ) 出票数最低城市
from ticket_order t GROUP BY t.citycode
四、按层次查询:找出订单总额排名前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
GROUP BY ROLLUP(A, B, C):
首先会对(A、B、C)进行GROUP BY,
然后再对(A、B)进行GROUP BY,
其后再对(A)进行GROUP BY,
最后对全表进行汇总操作。
GROUP BY CUBE(A, B, C):
则首先会对(A、B、C)进行GROUP BY,
然后依次是(A、B),(A、C),(A),(B、C),(B),(C),
最后对全表进行汇总操作