sql中的分析函数

一 ，分析函数和聚合函数的区别

聚合函数：
1 聚合函数是能够直接使用的。 max() ，min(),avg(),count()…
2 聚合函数的计算结果返回一个值。
3 GROUP BY 分组之后，聚合函数会根据分组的字段，每一个组返回一个计算结果。
4 聚合函数若是要和其余的字段一块儿查询，那么其余的字段必须是分组的字段。
5 GROUP BY 之后，在 SELECT 后面 只能查询 被分组的字段，和聚合函数，其余的未分组的字段不容许直接查询。
6 若是表中不存在的数据，不受以上条件的约束。

分析函数
1 分析函数几乎没有任何限制。
2 分组的方式不一样，分析函数 使用 PARTITION BY 进行分组。
3 返回值数量不一样：分析函数会根据表中的行数，每行返回一个计算结果。
4 ORDER BY 在聚合函数中，只是单纯的排序，在分析函数中除了能够排序，还能够累计求值。

二，分析函数是什么？

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

          

三，分析函数和聚合函数的不同之处是什么？

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

              

四，分析函数的形式

分析函数带有一个开窗函数：over()

包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) 

他们的使用形式如下：

over(partition by xxx order by yyy)

over(partition by xxx order by yyy rows between unbounded preceding and current row)                   注：标红为窗口范围子句

五，常用分析函数

1，排序分析函数

求全局TopN时可使用order by limit 实现，但求每组TopN就得使用排序分析函数来快速实现了

• row_number  同一组内, 序号不重复, 序号绝对连续
• rand  同一组内, 序号有重复，重复序号的下一个序号会跳号
• dense_rank 同一组内, 序号有重复, 序号绝对连续

1）综合案例

表数据
create table TEST_ROW_NUMBER_OVER(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
 
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);

 2）对查询结果进行排序（无分组）

select id,name,age,salary,row_number()over(order by salary desc) rank
from TEST_ROW_NUMBER_OVER t

 

3)  根据id分组排序

select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t

 4）找出每一组中序号为一的数据

select * from(select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t)
where rank <2

2，聚合分析函数

3，移位分析函数

1)  WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。

更多内容with  as 见下面的博客：

https://blog.csdn.net/jia718/article/details/88253918

with 
cr as 
( 
    select CountryRegionCode from person.CountryRegion where Name like 'C%' 
)

select * from person.StateProvince where CountryRegionCode in (select * from cr)





查询结果同下：


select * from person.StateProvince where CountryRegionCode in 
         (select CountryRegionCode from person.CountryRegion where Name like 'C%')

2）  CTE  

3） lead ()

https://blog.csdn.net/a5276957/article/details/103808997

4) ·lag（）

 https://blog.csdn.net/a5276957/article/details/103808997

4，presto array_agg 分析函数

array_agg(expression):将表达式转成数组

 

5，窗口范围子句

https://www.cnblogs.com/Brambling/p/6721078.html