本文目录
- 能说一下oracle中的开窗函数 聚合函数 分析函数都是什么吗
- Hive碎碎念（2）：分析函数和窗口函数
- oracle中的over函数怎么用的，什么意思
- 在oracle 中这个表user_tab_partitions 这个表的作用是什么，请高人指点一下，谁有分析函数资料啊
能说一下oracle中的开窗函数 聚合函数 分析函数都是什么吗
我也是用oracle数据库的，在实际开发中用到开窗函数和分析函数的机会还是很少的，用聚合函数的时候非常多，请LZ多关注聚合函数，下面是我上各大网站收集的，希望对楼主有所帮助。分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。下面通过几个例子来说明其应用。 1：统计某商店的营业额。 date sale 1 20 2 15 3 14 4 18 5 30 规则：按天统计：每天都统计前面几天的总额 得到的结果： DATE SALE SUM ----- -------- ------ 1 20 20 --1天 2 15 35 --1天＋2天 3 14 49 --1天＋2天＋3天 4 18 67 . 5 30 97 . 2:统计各班成绩第一名的同学信息 NAME CLASS S ----- ----- ---------------------- fda 1 80 ffd 1 78 dss 1 95 cfe 2 74 gds 2 92 gf 3 99 ddd 3 99 adf 3 45 asdf 3 55 3dd 3 78 通过： -- select * from ( select name,class,s,rank()over(partition by class order by s desc) mm from t2 ) where mm=1 -- 得到结果： NAME CLASS S MM ----- ----- ---------------------- ---------------------- dss 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1 注意： 1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果 2.rank()和dense_rank()的区别是： --rank()是跳跃排序，有两个第二名时接下来就是第四名 --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名3.分类统计 (并显示信息) A B C -- -- ---------------------- m a 2 n a 3 m a 2 n b 2 n b 1 x b 3 x b 2 x b 4 h b 3 select a,c,sum(c)over(partition by a) from t2 得到结果： A B C SUM(C)OVER(PARTITIONBYA) -- -- ------- ------------------------ h b 3 3 m a 2 4 m a 2 4 n a 3 6 n b 2 6 n b 1 6 x b 3 9 x b 2 9 x b 4 9 如果用sum，group by 则只能得到 A SUM(C) -- ---------------------- h 3 m 4 n 6 x 9 无法得到B列值 ＝＝＝＝＝select * from test数据:A B C1 1 11 2 21 3 32 2 53 4 6---将B栏位值相同的对应的C 栏位值加总select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sumfrom testA B C C_SUM1 1 1 11 2 2 72 2 5 71 3 3 33 4 6 6---如果不需要已某个栏位的值分割,那就要用 nulleg: 就是将C的栏位值summary 放在每行后面select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sumfrom testA B C C_SUM1 1 1 171 2 2 171 3 3 172 2 5 173 4 6 17 求个人工资占部门工资的百分比SQL》 select * from salary;NAME DEPT SAL---------- ---- -----a 10 2000b 10 3000c 10 5000d 20 4000SQL》 select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;NAME DEPT SAL PERCENT---------- ---- ----- ----------a 10 2000 20b 10 3000 30c 10 5000 50d 20 4000 100二：开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：1： over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数 over（partition by deptno）按照部门分区2： over（order by salary range between 5 preceding and 5 following） 每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5 例如:对于以下列 aa 1 2 2 2 3 4 5 6 7 9 sum(aa)over（order by aa range between 2 preceding and 2 following） 得出的结果是 AA SUM ---------------------- ------------------------------------------------------- 1 10 2 14 2 14 2 14 3 18 4 18 5 22 6 18 7 22 9 9 就是说，对于aa=5的一行，sum为 5-1《=aa《=5+2 的和 对于aa=2来说，sum=1+2+2+2+3+4=14 ； 又如 对于aa=9 ，9-1《=aa《=9+2 只有9一个数，所以sum=9 ； 3：其它： over（order by salary rows between 2 preceding and 4 following） 每行对应的数据窗口是之前2行，之后4行4：下面三条语句等效： over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： over（order by salary range between unbounded preceding and unbounded following） 等效 over(partition by null)other（数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。一、 over函数 over函数指定了分析函数工作的数据窗口的大小，这个数据窗口大小可能会随着行的变化而变化，例如：over(order by salary)按照salary排序进行累计，order by是个默认的开窗函数over(partition by deptno) 按照部门分区over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150的数据记录over(order by salary rows between 50 perceding and 150 following)前50行，后150行over（order by salary rows between unbounded preceding and unbounded following）所有行over（order by salary range between unbounded preceding and unbounded following）所有行二、 sum函数 功能描述：该函数计算组中表达式的累积和。SAMPLE：下例计算同一经理下员工的薪水累积值SELECT manager_id, last_name, salary,SUM (salary) OVER (PARTITION BY manager_id ORDER BY salaryRANGE UNBOUNDED PRECEDING) l_csumFROM employeesWHERE manager_id in (101,103,108);三、 应用实例1， 测试环境设置设有销售表t_sales (subcompany,branch,region,customer,sale_qty); 存储客户的销售明细，记录如下所示。Subcompany Branch Region Customer Sale_qty北京分公司 北京经营部 片区1 客户1 1北京分公司 北京经营部 片区1 客户1 1北京分公司 北京经营部 片区1 客户2 1北京分公司 北京经营部 片区1 客户2 1北京分公司 北京经营部 片区2 客户1 1北京分公司 北京经营部 片区2 客户1 1北京分公司 北京经营部 片区2 客户2 1北京分公司 北京经营部 片区2 客户2 1北京分公司 其他经营部 片区1 客户1 1北京分公司 其他经营部 片区1 客户1 1北京分公司 其他经营部 片区1 客户2 1北京分公司 其他经营部 片区1 客户2 1北京分公司 其他经营部 片区2 客户1 1北京分公司 其他经营部 片区2 客户1 1北京分公司 其他经营部 片区2 客户2 1北京分公司 其他经营部 片区2 客户2 1create table t_sales(subcompany varchar2(40),branch varchar2(40),region varchar2(40),customer varchar2(40),sale_qty numeric(18,4));comment on table t_sales is ’销售表，分析函数测试’;comment on column t_sales.subcompany is ’分公司’;comment on column t_sales.branch is ’经营部’;comment on column t_sales.region is ’片区’;comment on column t_sales.customer is ’客户’;comment on column t_sales.sale_qty is ’销售数量’;2，问题提出现在要求给出销售汇总报表，报表中需要提供的数据包括客户汇总，和客户在其上级机构中的销售比例。Subcompany Branch Region Customer Sale_qty Rate北京分公司 北京经营部 片区1 客户1 2 50％北京分公司 北京经营部 片区1 客户2 2 50％北京分公司 北京经营部 片区1 小计 4 50％北京分公司 北京经营部 片区2 客户1 2 50％北京分公司 北京经营部 片区2 客户2 2 50％北京分公司 北京经营部 片区2 小计 4 50％北京分公司 北京经营部 小计 小计 8 50％北京分公司 北京经营部 片区1 客户1 2 50％北京分公司 北京经营部 片区1 客户2 2 50％北京分公司 北京经营部 片区1 小计 4 50％北京分公司 北京经营部 片区2 客户1 2 50％北京分公司 北京经营部 片区2 客户2 2 50％北京分公司 北京经营部 片区2 小计 4 50％北京分公司 北京经营部 小计 小计 8 50％北京分公司 小计 小计 小计 16 100％3，解决方案（方案1）首先我们可以使用oracle对group by 的扩展功能rollup得到如下的聚合汇总结果。select subcompany,branch,region,customer,sum(sale_qty) sale_qty from t_sales group by rollup(subcompany,branch,region,customer);Subcompany Branch Region Customer Sale_qty北京分公司 北京经营部 片区1 客户1 2北京分公司 北京经营部 片区1 客户2 2北京分公司 北京经营部 片区1 4北京分公司 北京经营部 片区2 客户1 2北京分公司 北京经营部 片区2 客户2 2北京分公司 北京经营部 片区2 4北京分公司 北京经营部 8北京分公司 其他经营部 片区1 客户1 2北京分公司 其他经营部 片区1 客户2 2北京分公司 其他经营部 片区1 4北京分公司 其他经营部 片区2 客户1 2北京分公司 其他经营部 片区2 客户2 2北京分公司 其他经营部 片区2 4北京分公司 其他经营部 8北京分公司 16 16分析上面的临时结果，我们看到：明细到客户的汇总信息，其除数为当前的sum(sale_qty),被除数应该是到片区的小计信息。明细到片区的汇总信息，其除数为片区的sum(sale_qty),被除数为聚合到经营部的汇总数据。。。。考虑到上述因素，我们可以使用oracle的开窗函数over，将数据定位到我们需要定位的记录。如下代码中，我们利用开窗函数over直接将数据定位到其上次的小计位置。over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null)经整理后的查询语句如下。select subcompany, decode(f_branch, 1,subcompany||’(С¼Æ)’, branch), decode(f_region,1,branch||’(С¼Æ)’,region), decode(f_customer,1,region||’(С¼Æ)’, customer), sale_qty, trim(to_char(round(sale_qty/ sum(sale_qty) over(partition by decode(f_branch, 1, null, subcompany), decode(f_branch, 1, null, decode(f_region, 1, null, branch)), decode(f_branch, 1, null, decode(f_region, 1, null, decode(f_customer, 1, null, region))), null),2) *100,99990.99)) from (select grouping(branch) f_branch, grouping(region) f_region, grouping(customer) f_customer, subcompany, branch, region, customer, sum(sale_qty) sale_qty from t_sales group by subcompany, rollup(branch, region, customer))Subcompany Branch Region Customer Sale_qty Rate北京分公司 北京经营部 片区1 客户1 2 50.00北京分公司 北京经营部 片区1 客户2 2 50.00北京分公司 北京经营部 片区2 客户1 2 50.00北京分公司 北京经营部 片区2 客户2 2 50.00北京分公司 北京经营部 片区1 片区1(小计) 4 50.00北京分公司 北京经营部 片区2 片区2(小计) 4 50.00北京分公司 其他经营部 片区1 客户1 2 50.00北京分公司 其他经营部 片区1 客户2 2 50.00北京分公司 其他经营部 片区2 客户1 2 50.00北京分公司 其他经营部 片区2 客户2 2 50.00北京分公司 其他经营部 片区1 片区1(小计) 4 50.00北京分公司 其他经营部 片区2 片区2(小计) 4 50.00北京分公司 北京经营部 北京经营部(小计) (小计) 8 50.00北京分公司 其他经营部 其他经营部(小计) (小计) 8 50.00北京分公司 北京分公司(小计) (小计) (小计) 16 100.00北京分公司 北京经营部 片区1 客户1 2 50.004，可能的另外一种解决方式（方案2）select subcompany, decode(f_branch, 1,subcompany||’(С¼Æ)’, branch), decode(f_region,1,branch||’(С¼Æ)’,region), decode(f_customer,1,region||’(С¼Æ)’, customer), sale_qty, /* trim(to_char(round(sale_qty/*/ decode(f_branch+f_region+f_customer, 0, (sum(sale_qty) over(partition by subcompany,branch,region))/2, 1, (sum(sale_qty) over(partition by subcompany,branch))/3, 2, (sum(sale_qty) over(partition by subcompany))/4 , sum(sale_qty) over()/4 )/* ,2) *100,99990.99))*/ from (select grouping(branch) f_branch, grouping(region) f_region, grouping(customer) f_customer, subcompany, branch, region, customer, sum(sale_qty) sale_qty from t_sales group by subcompany, rollup(branch, region, customer))在上面的解决方式中，最大的问题在于开窗函数过大。导致每次计算涉及到的行数过多，影响到执行的速度和效率。并且需要额外的计算处理清除多余叠加进去的数值 。）
Hive碎碎念（2）：分析函数和窗口函数
在Hive 0.11之后支持的，扫描多个输入的行计算每行的结果。通常和OVER,PARTITION BY, ORDER BY, WINDOWING配合使用。和传统的分组结果不一样，传统的结果每组中只有一个结果。分析函数的结果会出现多次，和每条记录都连接输出。 语法形式如下： OVER从句 使用标准的聚合函数COUNT,SUM,MIN,MAX,AVG 使用PARTITION BY语句，使用一个或者多个原始数据类型的列 使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者拍序列 使用窗口规范，窗口规范支持一下格式： 当ORDER BY后面缺少窗口从句条件，窗口规范默认是 当ORDER BY和窗口从句都缺失，窗口规范默认是： 在聚合函数(sum, count, avg)中支持distinct，但是在order by或者 窗口限制中不支持。 conut(distinct a) over(partition by c) select rank() over(order by sum(b)) count(distinct a) over (partition by c order by d rows between 1 preceding and 1 following) 结果和ORDER BY相关,默认为升序 如果不指定ROWS BETWEEN,默认为从起点到当前行; 如果不指定ORDER BY，则将分组内所有值累加; PRECEDING：往前 FOLLOWING：往后 CURRENT ROW：当前行 UNBOUNDED：无界限（起点或终点） UNBOUNDED PRECEDING：表示从前面的起点 UNBOUNDED FOLLOWING：表示到后面的终点 其他COUNT、AVG，MIN，MAX，和SUM用法一样。 +----------+ | user_id | +----------+ | tom1 | | tom3 | +----------+ +----------+------------+--------+------+----------------------+--+ | user_id | user_type | sales | cd1 | cd2 | +----------+------------+--------+------+----------------------+--+ | liliu | new | 1 | 0.3 | 0.2857142857142857 | | tom | new | 1 | 0.3 | 0.2857142857142857 | | zhangsa | new | 2 | 0.5 | 0.42857142857142855 | | wanger | new | 3 | 0.7 | 0.5714285714285714 | | tom2 | new | 5 | 0.9 | 0.8571428571428571 | | tom3 | new | 5 | 0.9 | 0.8571428571428571 | | tom1 | new | 6 | 1.0 | 1.0 | | lisi | old | 1 | 0.3 | 0.3333333333333333 | | tomas | old | 2 | 0.5 | 0.6666666666666666 | | tomson | old | 3 | 0.7 | 1.0 | +----------+------------+--------+------+----------------------+--+ +------------+--------+----+-----+---------------------+---------------------+--+ | user_type | sales | s | r | pr | prg | +------------+--------+----+-----+---------------------+---------------------+--+ | new | 1 | 7 | 1 | 0.0 | 0.0 | | new | 1 | 7 | 1 | 0.0 | 0.0 | | new | 2 | 7 | 4 | 0.3333333333333333 | 0.3333333333333333 | | new | 3 | 7 | 6 | 0.5555555555555556 | 0.5 | | new | 5 | 7 | 8 | 0.7777777777777778 | 0.6666666666666666 | | new | 5 | 7 | 8 | 0.7777777777777778 | 0.6666666666666666 | | new | 6 | 7 | 10 | 1.0 | 1.0 | | old | 1 | 3 | 1 | 0.0 | 0.0 | | old | 2 | 3 | 4 | 0.3333333333333333 | 0.5 | | old | 3 | 3 | 6 | 0.5555555555555556 | 1.0 | +------------+--------+----+-----+---------------------+---------------------+--+
oracle中的over函数怎么用的，什么意思
over函数是oracle中的分析函数，分析函数是对行集组进行聚合计算，但是不像普通聚合仗函数那样每组只返回一个值，分析函数可以为每组返回多个值。
使用方法为：over(partition by排 列名1 order by 列名2 )，括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。
扩展资料
在SQL语句中，很多查询语句需要进行GROUP BY分组汇总，但是一旦经过分组，SELECT返回的记录孢数就会减少。为了保留所有原始行记录，并且仍可以进行分组数据分析，分析函数应运而生。
oracle数据库函数，分析函数用于为行定义一个窗口，对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号。
DENSE_RANK（）和RANK（）类似，不同的是如果有相同的序号，那么接下来的序号不会间断。也就是说如果两个相同的行生成序号，那么接下来生成的序号还是。
在oracle 中这个表user_tab_partitions 这个表的作用是什么，请高人指点一下，谁有分析函数资料啊
1)要查询创建分区的信息，可以通过查询user_part_tables,user_tab_partitions两个数据字典(索引分区、组织分区等信息也有对应的数据字典)。user_part_tables：记录分区的表的信息;user_tab_partitions：记录表的分区的信息。2)ORACLE函数介绍(详细需要自己搜索)注：N表示数字型，C表示字符型，D表示日期型，表示内中参数可被忽略，fmt表示格式。 分析函数计算基于group by的列，分组查询出的行被称为"比照(window)"，在根据over()执行过程中，针对每一行都会重新定义比照。比照为"当前行(current row)"确定执行计算的行的范围。这点一定要理解清楚。它是分析函数生成数据的原理。如果此处模糊，那么你在应用分析函数时恐就不会那么得心应手了。 分析函数与前面章节中讲到的聚合函数非常相似，不同于聚合函数的地方在于它们每个分组序列均返回多行。在本节示例中会同时应用两种函数做对比，以更好体现二者的差异。通过本章节练习相信大家就会注意到，部分聚合函数和分析函数是同一个命令，事实确实如此。如果从语法格式上区分的话，没加over()的即是聚合函数，加了over()即是分析函数：） 有一点需要注意哟，除了order by子句的运算外，分析函数在SQL语句中将会最后执行。因此，分析函数只能应用于select的列或order by子句中(记住喽，千万别扔到什么where、group by、having之类的地方了)。也正因此，同名的函数在做为聚合函数和分析函数时得出的结果可能不相同，就是因为此处运算逻辑不同造成的。 同时，部分分析函数在选择列时支持distinct，如果你指定了该参数，则over条件中就只能指定partition子句，而不能再指定order by 子句了。 分析函数的语法结构比较复杂，但多数函数都具有相同的语法结构，所以先在之前进行统一介绍，后续单个函数介绍时就不过多说明函数语法结构了。 基本上所有的分析函数均是这种格式： 函数名称 () OVER (analytic_clause)analytic_clause包含：Partition 子句：Partition by exp1...Partition没啥说的，功能强大参数少，主要用于分组，可以理解成select中的group by。不过它跟select语句后跟的group by 子句并不冲突。Order子句：Order by exp1。部分函数支持window子句。Order by的参数基本与select中的order by相同。大家按那个理解就是了。Nulls first|last是用来限定nulls在分组序列中的所在位置的，我们知道oracle中对于null的定义是未知，所以默认ordery的时候nulls总会被排在最前面。如果想控制值为null的列的话呢，nulls first|last参数就能派上用场了。
