常用聚合函数

• 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