窗口函数

==================================================

 

 

 

1,19,a,male

2,19,b,male

3,22,c,female

4,16,d,female

5,30,e,male

6,26,f,female

 

+---------------+----------------+-----------------+----------------+--+

| t_student.id | t_student.age | t_student.name | t_student.sex |

+---------------+----------------+-----------------+----------------+--+

| 1 | 19 | a | male |

| 2 | 19 | b | male |

| 5 | 30 | e | male |

 

| 3 | 22 | c | female |

| 4 | 16 | d | female |

| 6 | 26 | f | female |

 

 

 

+---------------+----------------+-----------------+----------------+--+

 

row_number() over()

 

 

 

select

id,age,name,sex,row_number() over(partition by sex order by age desc) rk

from t_student

 

select

tmp.*

from

(select

id,age,name,sex,row_number() over(partition by sex order by age desc) rk

from t_student) tmp

where tmp.rk = 1

 

 

 

 

select

id,age,name,sex,rank() over(partition by sex order by age desc) rk

from t_student

 

 

select

id,age,name,sex,dense_rank() over(partition by sex order by age desc) rk

from t_student

 

select

id,age,name,sex,ntile(2) over(partition by sex order by age desc) rk

from

t_student

 

=============================================================

 

https://www.jianshu.com/p/12eaf61cf6e1

 

 

A,2015-01,5

A,2015-01,15

B,2015-01,5

A,2015-01,8

B,2015-01,25

A,2015-01,5

C,2015-01,10

C,2015-01,20

A,2015-02,4

A,2015-02,6

C,2015-02,30

C,2015-02,10

B,2015-02,10

B,2015-02,5

A,2015-03,14

A,2015-03,6

B,2015-03,20

B,2015-03,25

C,2015-03,10

C,2015-03,20

 

--求每个销售员每个月的销售额和到当月为止的累计销售额

create table t_saller(name string,month string,amount int)

row format delimited fields terminated by ','

 

load data local inpath '/usr/local/chuzhigao/c4.txt' into table t_saller

 

create table t_accumulate

as

select name,month,sum(amount) samount

from t_saller

group by name,month

 

+--------------------+---------------------+-----------------------+--+

| t_accumulate.name | t_accumulate.month | t_accumulate.samount |

+--------------------+---------------------+-----------------------+--+

| A | 2015-01 | 33 |

| A | 2015-02 | 10 |

| A | 2015-03 | 20 |

| B | 2015-01 | 30 |

| B | 2015-02 | 15 |

| B | 2015-03 | 45 |

| C | 2015-01 | 30 |

| C | 2015-02 | 40 |

| C | 2015-03 | 30 |

+--------------------+---------------------+-----------------------+--+

--最前面一行

select

name,month,samount,sum(samount) over(partition by name order by month rows between unbounded preceding and current row) accumlateAmount

from

t_accumulate

 

| A | 2015-01 | 33 |

| A | 2015-02 | 10 |

| A | 2015-03 | 20 |

| A | 2015-04 | 30 |

| A | 2015-05 | 15 |

| A | 2015-06 | 45 |

| A | 2015-07 | 30 |

| A | 2015-08 | 40 |

| A | 2015-09 | 30 |

 

 

select

name,month,samount,sum(samount) over(partition by name order by month rows between 2 preceding and 1 following ) accumlateAmount

from

t_accumulate

 

 

preceding |

当前行 | 窗口长度

following |

 

unbounded preceding 最前一行

unbounded following 最后一行

 

min() over() ,max() over() , avg() over()

 

============================================================================================

 

1,zhangsan,化学:物理:数学:语文

2,lisi,化学:数学:生物:生理:卫生

3,wangwu,化学:语文:英语:体育:生物

 

create table t_stu_subject(id int,name string,subjects array<string>)

row format delimited fields terminated by ','

collection items terminated by ':'

 

explode()

 

select explode(subjects) from t_stu_subject;

 

 

select distinct tmp.subs from (select explode(subjects) subs from t_stu_subject) tmp

 

==========================================================

 

lateral view连接函数

 

select

id,name,sub

from

t_stu_subject lateral view explode(subjects) tmp as sub

 

+-----+-----------+------+--+

| id | name | sub |

+-----+-----------+------+--+

| 1 | zhangsan | 化学 |

| 1 | zhangsan | 物理 |

| 1 | zhangsan | 数学 |

| 1 | zhangsan | 语文 |

| 2 | lisi | 化学 |

| 2 | lisi | 数学 |

| 2 | lisi | 生物 |

| 2 | lisi | 生理 |

| 2 | lisi | 卫生 |

| 3 | wangwu | 化学 |

| 3 | wangwu | 语文 |

| 3 | wangwu | 英语 |

| 3 | wangwu | 体育 |

| 3 | wangwu | 生物 |

+-----+-----------+------+--+

 

============================================================

 

wordcount

 

words

 

create table words(line string)

 

line

hello world hi tom and jack

hello chentianxing qiaoyuan and shaoshuai

hello hello hi tom and shaoshuai

chentianxing love saoshuai

hello love what is love how love

 

split()

 

select

tmp.word,count(1) cnts

from

(select

explode(split(line,' ')) word

from words) tmp

group by tmp.word order by cnts desc

 

 

=========================================================

 

--炸map

select

id,name,key,value

from

t_people

lateral view explode(family) tmp as key,value

 

===================================================================