常用聚合函数

min()
max()
count()
sum()
avg()

select
  vn_char,
  vn_number 
from kk_test;

kk_test有10条数据

select
  vn_char,
  vn_number,
  --1+2+3+...+9+10=55
  sum(vn_number) over(partition by null) as total_number
from kk_test;

patition by null

select
  vn_char,
  vn_number,
  --partition by vn_char
  sum(vn_number) over(partition by vn_char) as total_partition_by_char
from kk_test;

partition by char

差不多明白over(partition by ...)是干什么的了吧，这儿说一下over(partition by ...)和group by的区别，请自行体会

--受限与group by 我们刷选的字段是有限的，且会改变记录数据，因为我们分组了，那么同一组只有一条数据
select
  vn_char,
  sum(vn_number) as total_by_char
from kk_test
group by vn_char
order by vn_char;

group by example

可以对比一下【partition by char】的那个例子，主要区别在于我们筛选的字段是不会收到限制的，记录条数也不会改变还是10条，现在这样是不是明白多了...

聚合函数() over(partition by ... order by ...)

开窗函数：指定聚合函数的工作窗口，即聚合函数的作用域
一个面试屡试不爽的问题，累计求和：

select
  vn_number,
  --累计求和
  sum(vn_number) over(order by vn_number) as accu_number
from kk_test;

累计求和

其实这个语句完整的写法是这样的：

select
  vn_number,
  --累计求和
  sum(vn_number) over(order by vn_number) as accu_number1,
  --preceding:往前聚合窗口的大小，unbounded无穷大
  --current row：到当前行
  sum(vn_number) over(order by vn_number range between unbounded preceding and current row) as accu_number2,
  --following：往后聚合窗口的大小，0等于current row
  sum(vn_number) over(order by vn_number range between unbounded preceding and 0 following) as accu_number3  
from kk_test;

this is interesting

所以我们自由发挥一下，出现了如下的语句：

select
  vn_char,
  vn_number,
  --累计求和
  sum(vn_number) over(order by vn_number) as accu_number,
  --往前1行，往后1行
  sum(vn_number) over(order by vn_number range between 1 preceding and 1 following) as accu_number_1_1,
  --往前1行,往后无穷大
  sum(vn_number) over(order by vn_number range between 1 preceding and unbounded following) as accu_number_1_42,
  --按照vn_char分组一下
  sum(vn_number) over(partition by vn_char order by vn_number range between 1 preceding and 1 following) as accu_number_1_1_p
from kk_test;

人笨，只能多写写，多练练

常用的分析函数

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...) 第一名有两个人，那么就没有第二名，1 1 3 4 5
dense_rank() over(partition by ... order by ...)第一名有两个人，呵呵，不存在的，我照样还是有第二名 1 1 2 3 4 5
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...) 嗯！这个函数不是全貌，容易让人误解（PS：所以说话要表达清楚意思，不要让别人猜来猜去，女孩的心思岂是猜得透的），其实它是长这样的：last_value() over(partition by ... order by ... range unbounded preceding and current row) 但是我们以为它是这样：last_value() over(partition by ... order by ... range unbounded preceding and unbounded following)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
转自：https://www.cnblogs.com/dongyj/p/5992083.html