开窗函数

dense_rank : 1 1 2

rank : 1 1 3

row_number:  1 2 3

 

sum( if(a>b,1,0) ) over()

over() 指定聚合范围

 

select *,rank() over (partition by a order by b) rk where rk<3

先 from  再 where 最后 select

所以不能直接 where  可以用 temp

 

行转列

select *,(case when sex='男' then 1 else 0 end) 男,(case when sex='女' then 1 else 0 end) 女 from socre;

 

炸裂

select explode( array ( 1,3,5));

1

3

5

select explode( map( 'a',1,'b',3,'c' ,5));

a 1

b 3

c 5

LATERAL VIEW udtf(expression) tableAlias AS columnAlias

列转行

SELECT * FROM exampleTable

LATERAL VIEW explode(col1) myTable1 AS myCol1

LATERAL VIEW explode(Col2) myTable2 AS myCol2;

Array<int> col1	Array<string> col2
[1, 2]	[a", "b", "c"]
[3, 4]	[d", "e", "f"]

Will produce:

int myCol1	string myCol2
1	"a"
1	"b"
1	"c"
2	"a"
2	"b"
2	"c"
3	"d"
3	"e"
3	"f"
4	"d"
4	"e"
4	"f"