常用聚合函數(shù)

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

select
  vn_char,
  vn_number 
from kk_test;

kk_test有10條數(shù)據(jù)

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

差不多明白o(hù)ver(partition by ...)是干什么的了吧，這兒說一下over(partition by ...)和group by的區(qū)別，請自行體會(huì)

--受限與group by 我們刷選的字段是有限的，且會(huì)改變記錄數(shù)據(jù)，因?yàn)槲覀兎纸M了，那么同一組只有一條數(shù)據(jù)
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】的那個(gè)例子，主要區(qū)別在于我們篩選的字段是不會(huì)收到限制的，記錄條數(shù)也不會(huì)改變還是10條，現(xiàn)在這樣是不是明白多了...

聚合函數(shù)() over(partition by ... order by ...)

• 開窗函數(shù)：指定聚合函數(shù)的工作窗口，即聚合函數(shù)的作用域
  一個(gè)面試屢試不爽的問題，累計(jì)求和：

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

累計(jì)求和

其實(shí)這個(gè)語句完整的寫法是這樣的：

select
  vn_number,
  --累計(jì)求和
  sum(vn_number) over(order by vn_number) as accu_number1,
  --preceding:往前聚合窗口的大小，unbounded無窮大
  --current row：到當(dāng)前行
  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

所以我們自由發(fā)揮一下，出現(xiàn)了如下的語句：

select
  vn_char,
  vn_number,
  --累計(jì)求和
  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;

人笨，只能多寫寫，多練練

常用的分析函數(shù)

• row_number() over(partition by ... order by ...)
• rank() over(partition by ... order by ...) 第一名有兩個(gè)人，那么就沒有第二名，1 1 3 4 5
• dense_rank() over(partition by ... order by ...)第一名有兩個(gè)人，呵呵，不存在的，我照樣還是有第二名 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 ...) 嗯！這個(gè)函數(shù)不是全貌，容易讓人誤解（PS：所以說話要表達(dá)清楚意思，不要讓別人猜來猜去，女孩的心思豈是猜得透的），其實(shí)它是長這樣的：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 ...)
  轉(zhuǎn)自：https://www.cnblogs.com/dongyj/p/5992083.html