移动端的网站怎么做的,网站页面设计模板,psd网站,自己做网站花多少钱我们来看看下面的几个典型例子#xff1a; ①查找上一年度各个销售区域排名前10的员工 ②按区域查找上一年度订单总额占区域订单总额20%以上的客户 ③查找上一年度销售最差的部门所在的区域 ④查找上一年度销售最好和最差的产品 我们看看上面的几个例子就可以感觉到这几个查询… 我们来看看下面的几个典型例子 ①查找上一年度各个销售区域排名前10的员工 ②按区域查找上一年度订单总额占区域订单总额20%以上的客户 ③查找上一年度销售最差的部门所在的区域 ④查找上一年度销售最好和最差的产品 我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同具体有 ①需要对同样的数据进行不同级别的聚合操作 ②需要在表内将多条数据和同一条数据进行多次的比较 ③需要在排序完的结果集上进行额外的过滤操作 Oracle分析函数简单实例 下面我们通过一个实际的例子按区域查找上一年度订单总额占区域订单总额20%以上的客户来看看分析函数的应用。 【1】测试环境 SQL desc orders_tmp; Name Null? Type ----------------------- -------- ---------------- CUST_NBR NOT NULL NUMBER(5) REGION_ID NOT NULL NUMBER(5) SALESPERSON_ID NOT NULL NUMBER(5) YEAR NOT NULL NUMBER(4) MONTH NOT NULL NUMBER(2) TOT_ORDERS NOT NULL NUMBER(7) TOT_SALES NOT NULL NUMBER(11,2) 【2】测试数据 SQL select * from orders_tmp; CUST_NBR REGION_ID SALESPERSON_ID YEAR MONTH TOT_ORDERS TOT_SALES ---------- ---------- -------------- ---------- ---------- ---------- ---------- 11 7 11 2001 7 2 12204 4 5 4 2001 10 2 37802 7 6 7 2001 2 3 3750 10 6 8 2001 1 2 21691 10 6 7 2001 2 3 42624 15 7 12 2000 5 6 24 12 7 9 2000 6 2 50658 1 5 2 2000 3 2 44494 1 5 1 2000 9 2 74864 2 5 4 2000 3 2 35060 2 5 4 2000 4 4 6454 2 5 1 2000 10 4 35580 4 5 4 2000 12 2 39190 13 rows selected. 【3】测试语句 SQL select o.cust_nbr customer, 2 o.region_id region, 3 sum(o.tot_sales) cust_sales, 4 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales 5 from orders_tmp o 6 where o.year 2001 7 group by o.region_id, o.cust_nbr; CUSTOMER REGION CUST_SALES REGION_SALES ---------- ---------- ---------- ------------ 4 5 37802 37802 7 6 3750 68065 10 6 64315 68065 11 7 12204 12204 分析函数OVER解析 请注意上面的绿色高亮部分group by的意图很明显将数据按区域ID客户进行分组那么Over这一部分有什么用呢假如我们只需要统计每个区域每个客户的订单总额那么我们只需要group by o.region_id,o.cust_nbr就够了。但我们还想在每一行显示该客户所在区域的订单总额这一点和前面的不同需要在前面分组的基础上按区域累加。很显然group by和sum是无法做到这一点的(因为聚集操作的级别不一样前者是对一个客户后者是对一批客户)。 这就是over函数的作用了它的作用是告诉SQL引擎按区域对数据进行分区然后累积每个区域每个客户的订单总额(sum(sum(o.tot_sales)))。 现在我们已经知道2001年度每个客户及其对应区域的订单总额那么下面就是筛选那些个人订单总额占到区域订单总额20%以上的大客户了 SQL select * 2 from (select o.cust_nbr customer, 3 o.region_id region, 4 sum(o.tot_sales) cust_sales, 5 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales 6 from orders_tmp o 7 where o.year 2001 8 group by o.region_id, o.cust_nbr) all_sales 9 where all_sales.cust_sales all_sales.region_sales * 0.2; CUSTOMER REGION CUST_SALES REGION_SALES ---------- ---------- ---------- ------------ 4 5 37802 37802 10 6 64315 68065 11 7 12204 12204 SQL 现在我们已经知道这些大客户是谁了哦不过这还不够如果我们想要知道每个大客户所占的订单比例呢看看下面的SQL语句只需要一个简单的Round函数就搞定了。 SQL select all_sales.*, 2 100 * round(cust_sales / region_sales, 2) || % Percent 3 from (select o.cust_nbr customer, 4 o.region_id region, 5 sum(o.tot_sales) cust_sales, 6 sum(sum(o.tot_sales)) over(partition by o.region_id) region_sales 7 from orders_tmp o 8 where o.year 2001 9 group by o.region_id, o.cust_nbr) all_sales 10 where all_sales.cust_sales all_sales.region_sales * 0.2; CUSTOMER REGION CUST_SALES REGION_SALES PERCENT ---------- ---------- ---------- ------------ ---------------------------------------- 4 5 37802 37802 100% 10 6 64315 68065 94% 11 7 12204 12204 100% SQL 总结 ①Over函数指明在那些字段上做分析其内跟Partition by表示对数据进行分组。注意Partition by可以有多个字段。 ②Over函数可以和其它聚集函数、分析函数搭配起到不同的作用。例如这里的SUM还有诸如RankDense_rank等。 分析函数2(Rank, Dense_rank, row_number) 目录 1.使用rownum为记录排名 2.使用分析函数来为记录排名 3.使用分析函数为记录进行分组排名 一、使用rownum为记录排名 在前面一篇《Oracle开发专题之分析函数》我们认识了分析函数的基本应用现在我们再来考虑下面几个问题 ①对所有客户按订单总额进行排名 ②按区域和客户订单总额进行排名 ③找出订单总额排名前13位的客户 ④找出订单总额最高、最低的客户 ⑤找出订单总额排名前25%的客户 按照前面第一篇文章的思路我们只能做到对各个分组的数据进行统计如果需要排名的话那么只需要简单地加上rownum不就行了吗事实情况是否如此想象般简单我们来实践一下。 【1】测试环境 SQL desc user_order; Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NUMBER(2) CUSTOMER_ID NUMBER(2) CUSTOMER_SALES NUMBER 【2】测试数据 SQL select * from user_order order by customer_sales; REGION_ID CUSTOMER_ID CUSTOMER_SALES ---------- ----------- -------------- 5 1 151162 10 29 903383 6 7 971585 10 28 986964 9 21 1020541 9 22 1036146 8 16 1068467 6 8 1141638 5 3 1161286 5 5 1169926 8 19 1174421 7 12 1182275 7 11 1190421 6 10 1196748 6 9 1208959 10 30 1216858 5 2 1224992 9 24 1224992 9 23 1224992 8 18 1253840 7 15 1255591 7 13 1310434 10 27 1322747 8 20 1413722 6 6 1788836 10 26 1808949 5 4 1878275 7 14 1929774 8 17 1944281 9 25 2232703 30 rows selected. 注意这里有3条记录的订单总额是一样的。假如我们现在需要筛选排名前12位的客户如果使用rownum会有什么样的后果呢 SQL select rownum, t.* 2 from (select * 3 from user_order 4 order by customer_sales desc) t 5 where rownum 12 6 order by customer_sales desc; ROWNUM REGION_ID CUSTOMER_ID CUSTOMER_SALES ---------- ---------- ----------- -------------- 1 9 25 2232703 2 8 17 1944281 3 7 14 1929774 4 5 4 1878275 5 10 26 1808949 6 6 6 1788836 7 8 20 1413722 8 10 27 1322747 9 7 13 1310434 10 7 15 1255591 11 8 18 1253840 12 5 2 1224992 12 rows selected. 很明显假如只是简单地按rownum进行排序的话我们漏掉了另外两条记录(参考上面的结果)。 二、使用分析函数来为记录排名 针对上面的情况Oracle从8i开始就提供了3个分析函数randdense_rankrow_number来解决诸如此类的问题下面我们来看看这3个分析函数的作用以及彼此之间的区别 RankDense_rankRow_number函数为每条记录产生一个从1开始至N的自然数N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。 ①ROW_NUMBER Row_number函数返回一个唯一的值当碰到相同数据时排名按照记录集中记录的顺序依次递增。 ②DENSE_RANK Dense_rank函数返回一个唯一的值除非当碰到相同数据时此时所有相同数据的排名都是一样的。 ③RANK Rank函数返回一个唯一的值除非遇到相同的数据时此时所有相同数据的排名是一样的同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。 这样的介绍有点难懂我们还是通过实例来说明吧下面的例子演示了3个不同函数在遇到相同数据时不同排名策略 SQL select region_id, customer_id, sum(customer_sales) total, 2 rank() over(order by sum(customer_sales) desc) rank, 3 dense_rank() over(order by sum(customer_sales) desc) dense_rank, 4 row_number() over(order by sum(customer_sales) desc) row_number 5 from user_order 6 group by region_id, customer_id; 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 30 rows selected. 请注意上面的绿色高亮部分这里生动的演示了3种不同的排名策略 ①对于第一条相同的记录3种函数的排名都是一样的12 ②当出现第二条相同的记录时Rank和Dense_rank依然给出同样的排名12而row_number则顺延递增为13依次类推至第三条相同的记录 ③当排名进行到下一条不同的记录时可以看到Rank函数在12和15之间空出了13,14的排名因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增 比较上面3种不同的策略我们在选择的时候就要根据客户的需求来定夺了 ①假如客户就只需要指定数目的记录那么采用row_number是最简单的但有漏掉的记录的危险 ②假如客户需要所有达到排名水平的记录那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要选择dense_rank或得到最大的记录 三、使用分析函数为记录进行分组排名 上面的排名是按订单总额来进行排列的现在跟进一步假如是为各个地区的订单总额进行排名呢这意味着又多了一次分组操作对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持我们所要做的仅仅是在over函数中order by的前面增加一个分组子句partition by region_id。 SQL select region_id, customer_id, sum(customer_sales) total, 2 rank() over(partition by region_id order by sum(customer_sales) desc) rank, 3 dense_rank() over(partition by region_id order by sum(customer_sales) desc) dense_rank, 4 row_number() over(partition by region_id order by sum(customer_sales) desc) row_number 5 from user_order 6 group by region_id, customer_id; REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER ---------- ----------- ---------- ---------- ---------- ---------- 5 4 1878275 1 1 1 5 2 1224992 2 2 2 5 5 1169926 3 3 3 6 6 1788836 1 1 1 6 9 1208959 2 2 2 6 10 1196748 3 3 3 30 rows selected. 现在我们看到的排名将是基于各个地区的而非所有区域的了Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分这样排列函数就能够应用于这各个子集。 前面我们提到的5个问题已经解决了2个了(第1,2)剩下的3个问题(Top/Bottom NFirst/Last, NTile)会在下一篇讲解。 分析函数3(Top/Bottom N、First/Last、NTile) 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 一、带空值的排列 假如被排列的数据中含有空值呢 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让空值排名最后后第一。 注意是NULLS不是NULL。 二、Top/Bottom N查询 在日常的工作生产中我们经常碰到这样的查询找出排名前5位的订单客户、找出排名前10位的销售人员等等。现在这个对我们来说已经是很简单的问题了。下面我们用一个实际的例子来演示 【1】找出所有订单总额排名前3的大客户 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的客户。 很熟悉是不我们马上会想到第二点中提到的方法可是rank函数只为我们做好了排名并不知道每个排名在总排名中的相对位置这时候就引入了另外一个分析函数NTile下面我们就以上面的需求为例来讲解一下 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)就可以了。
