行转列列转行

1：行转列
将多行的数据合并到一列中

 select collect_list(empname ) from emp; --去重
 select collect_set(deptno) from emp;

这两个都是聚合函数 将收集的多行数据聚集成一个数组集合
2：concat 拼接 参数是可变参数 拼接字符串

 select concat(deptno,empno) from emp;

3：concat_ws 参数一 拼接符 参数二 可变个数的字符串 数组

select concat_ws('|',empname,job) from emp;

4：列转行
①：explode 炸裂函数将数组或map中的每一个元炸裂放到每一行中去
②：split切割 按照指定切割符切割

 select explode(split(categorys,',')) from tb_movie2;

5：侧窗口函数 类似隐式 join lateral view

select name,tp from tb_movie2 lateral view explode(split(categorys,',')) t as tp;

t是表明 tp 是虚拟表的字段名

窗口函数

1．相关函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行 current row
n PRECEDING：往前n行数据 n preceding
n FOLLOWING：往后n行数据 n following
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点 unbound preceding unbound following
LAG(col,n)：往前第n行数据 lag 参数一 字段 n
LEAD(col,n)：往后第n行数据 lead
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。 ntile(5)

指定聚合函数的执行范围 又可以操作聚合函数之前的数据
①：指定窗口范围 、窗口大小

 select *, count(9) over() --指定窗口函数的范围是整个表
  from tb_orders;

 select * ,count(9) over(partition by name) --指定窗口大小是一个人（name）
  from tb_orders;

②：求起始到当前行的累加数据 可以看出数据的变化趋势

select *,
sum(money) over (partition by name rows between unbounded preceding and current row ) 
from tb_orders;

select *,
sum(money) over (partition by name order by ctime) --起始行到当前行
 from tb_orders;

上一行到这行的累加

 select *,
 sum(money) over (partition by name order by ctime rows between 1 preceding and current row)
  from tb_orders;

上一行加到下一行

select *,
sum(money) over (partition by name order by ctime rows between 1 preceding and 1 following)
 from tb_orders;

LAG(col,n)：往前第n行数据

select * ,
lag(ctime,1) over(partition by name order by ctime)--返回前一条数据
 from tb_orders;

LAG(col,n,默认值)：往前第n行数据，如果没有数据则返回默认值

select * ,
lag(ctime,1,'00000') over(partition by name order by ctime)--返回前一条数据如果没有则给一个默认值
from tb_orders;

查前百分之20的订单数据

select name,ctime,money 
from (select * ,ntile(5) over(order by ctime) num from tb_orders ) t where num=1;