2、函数

时间函数

当前日期

select current_date();
 2018-11-14      

当前时间戳

select current_timestamp();
2018-11-14 21:35:16.237  

date_format()

select date_format(current_date(),'yyyyMMdd');
20181114 select date_format(current_timestamp(),'yyyyMMdd');
20181114                    

unix_timestamp()

select unix_timestamp();
+-----------------------------------------------------------+--+
| unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)  |
+-----------------------------------------------------------+--+
| 1542202845                                                |
+-----------------------------------------------------------+--+

from_unixtime()

select from_unixtime(unix_timestamp(),'yyyyMMdd HH:mm:ss');
+---------------------------------------------------------------------------------------------
| from_unixtime(unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss), yyyyMMdd HH:mm:ss)  
+---------------------------------------------------------------------------------------------
| 20181114 21:43:14

日期差值

0: jdbc:hive2://s101:10000/lx> select datediff('2019-08-02','2019-08-06');
+---------------------------------------------------------------+--+
| datediff(CAST(2019-08-02 AS DATE), CAST(2019-08-06 AS DATE))  |
+---------------------------------------------------------------+--+
| -4                                                            |
+---------------------------------------------------------------+--+

字符串函数

split

select explode(split('hello',''));

substr

select substr('hello',1,3);

trim去除前后空格

select trim(' hello ');

format_number

select format_number(1234.345,1);

concat

length

条件语句

if

select if(w1 >2 ,w1,w2) from www;
true则w1，false则w2

case when then

select case when w1 > 2 then w1 when w1 <= 2 then w2 end from www;

窗口函数

lead

lead(input[, offset[, default]]) 上提
    input：     上提列
    offset： 上提行数，可选，默认是 1 行
    default：填充值，可选，默认是null
    使用：select id,name,lead(id,2,'qq')over(partition by id order by id) lad from www;
    注意：2可选，不写默认1
          ‘qq’可选，不写默认null,类型需要对应，此处id是int，所以还是为null
          partition by id：可选，分组后对每个组进行lead
          order by id：必须写

lag

lag(input[, offset[, default]]) 下拉
    input：     下拉列
    offset： 下拉行数，可选，默认是 1 行
    default：填充值，可选，默认是null
    使用：select id,name,lag(id,2,11)over(partition by id order by id) lag from www;
    注意：2可选，不写默认1
          11可选，不写默认null
          partition by id：可选，分组后对每个组进行lag
          order by id：必须写

first_value

first_value(expr[, isIgnoreNull])
    expr：列名或一个表达式
    isIgnoreNull：true或false，如果是true将跳过null值，可选，默认false
    select id,name,first_value(concat(cast(id as string), name),true)over(partition by name order by id) lag from www;

last_value

select id,name,last_value(concat(cast(id as string), name),true)over(order by id ) lag from www;
+-----+-------+--------+--+
| id  | name  |  lag   |
+-----+-------+--------+--+
| 1   | a     | 1a     |
| 2   | b     | 2b     |
| 3   | c     | 3c     |
| 4   | c     | 4c     |
| 5   | c     | 5c     |
| 6   | d     | 6d     |
| 7   | b     | 7b     |
| 8   | a     | 8a     |
| 9   | a     | 9a     |
| 12  | eee   | 12eee  |
+-----+-------+--------+--+select id,name from www;
+-----+-------+--+
| id  | name  |
+-----+-------+--+
| 12  | eee   |
| 1   | a     |
| 2   | b     |
| 3   | c     |
| 4   | c     |
| 5   | c     |
| 6   | d     |
| 7   | b     |
| 8   | a     |
| 9   | a     |
+-----+-------+--+
select id,name,last_value(concat(cast(id as string), name),true)over() lag from www;
+-----+-------+------+--+
| id  | name  | lag  |
+-----+-------+------+--+
| 12  | eee   | 6d   |
| 7   | b     | 6d   |
| 8   | a     | 6d   |
| 9   | a     | 6d   |
| 1   | a     | 6d   |
| 2   | b     | 6d   |
| 3   | c     | 6d   |
| 4   | c     | 6d   |
| 5   | c     | 6d   |
| 6   | d     | 6d   |
+-----+-------+------+--+

over和标准聚合函数

select distinct name,count(name)over(partition by name) s from www;
求分区个数并去重
select distinct name,sum(id)over(partition by name) s from www;
分区id和并去重
select id,name,max(length(name))over() from www;
总体的最大长度
select name,min(id)over(partition by name) s from www;
每个分区最小id
select name,max(id)over(partition by name) s from www;
每个分区最大id
select name,avg(id)over(partition by name) s from www;
每个分区平均id

over和partiton by

over和partition by order by

select first_value(id)over(partition by id,name) from www;
select first_value(id)over(partition by id,name order by id,name) from www;

以行限定窗口范围

select * ,sum(grade)over(order by grade desc rows between current row and 1 following) from sg;

select * ,sum(grade)over(order by grade desc rows between current row and unbounded following) from sg;

select * ,sum(grade)over(order by grade desc rows between unbounded preceding and current row) from sg;

select * ,sum(grade)over(order by grade desc rows between unbounded preceding and unbounded following) from sg;

以值限定窗口范围

select * ,sum(grade)over(order by grade desc range between unbounded preceding and current row) from sg;

排名函数

rank（）：并列跳跃

dense_rank()：并列连续，不跳跃

row_number（）：连续

Assigns a unique,sequential number to each row, 
starting with one,
according to the ordering of rows within the window partition

//商家内用户访问次数倒序排列，取前三个
select * from (
    select * ,row_number()over(partition by id order by count desc)b from (
        select id,uu,count(*) as count from shangjia where uu is not null group by id,uu)a)c 
where b <= 3;

cume_dist（）

select cume_dist()over(order by mid ) from t1;
//小于等于当前值的行数/分组内总行数    
select cume_dist()over(order by mid desc) from t1;
//大于等于当前值的行数/分组内总行数        

percent_rank

ntile（n）：每个分区按一定顺序分成n份

高级聚合函数

grouping sets

select *,count(1),grouping_id（） from pv group by t1,t2,s3 grouping sets(t1,t2,s3);

grouping__id    //分组的组号（可选）
grouping sets(t1,t2,s3,()) //相当于分别对t1,t2,s3,null进行分组并用union all连接

group by   ... with cube

select *,count(1),grouping_id() from pv group by t1,t2,s3 with cube order by grouping_id();
八种

rollup

select *,count(1),grouping_id() from pv group by t1,t2,s3 with rollup order by grouping_id();
null、t1、t1和t2、t1和t2和t3

排序函数

order by age　　

全排序，一个reduce；需要加limit，在map阶段在每个分区中取出前n个元素，交给r处理

sort by age

部分排序

distribute by age

哈希分区

cluster by

distribute by + sort by

select age from user_order distribute by age sort by age;