小白学hadoop日记day12——hive常见函数和窗口函数

  常见函数

-- 随机函数
select rand();
select rand(8);  --在某范围内随机
-- round()  保留小数点位数
select round(rand()*100,2);
-- floor() 向下取整
select floor(rand()*100);
-- ceil() 向上取整
select ceil(rand()*100);
-- split()  切分
select split(rand()*100,'\\.')[0];
-- substr() 或者 substring()  截取
select substr(rand()*100,0,2);
select substring(rand()*100,0,2);
-- cast(value as 类型) 类型转换
select cast(rand()*100 as int);
-- concat(col1,col2,col3,....) 字符串拼接
select concat("1.0","2.0");
select concat(1,2,3,4,5,6);
-- concat_ws() 带分隔符拼接
select concat_ws('_',"1","2","3","4","5","6");
-- if(条件表达式,满足true执行,满足false执行)
select if(1==1,'true','false');
select if(1!=2,if(2==2,'女','妖'),'false');
-- case when end
--when后面写条件
select
case 
when sex=1 then '男'
when sex=2 then '女'
else '妖' end
from t_stu
;
--case后面写字段
select
case sex
when 1 then '男'
when 2 then '女'
else '妖' end
from t_stu
;
-- length() 字符串长度
select length("abcde");
-- size() 数组和map的长度
select size(array(1,2,5,6));
select size(map(1,2,5,6));
-- lower()  转小写
select lower("Acc");
-- upper() 转大写
-- nvl(值1,值2)  判空，如果值1为空值2；如果不为空值1
select nvl(123,666);
select nvl(NULL,666);
-- ifnull()  或者 ifnotnull() 判空
select isnull(NULL);

窗口函数

窗口是将真个数据集（表）划分成多个小数据集进行统计分析。分为：物理窗口和逻辑窗口。
关键字：over(分组 排序 窗口) 中的order by后的语法:
1、物理窗口（真实往上下移动多少行rows between）

2、 逻辑窗口(满足条件上下多少行)

注意:窗口函数一般不和group by搭配使用

窗口函数和聚合函数的区别：
聚合函数是一个分组返回一个值；而窗口函数是每一行都要返回一个聚合值。
举例：
id price
1  12
1 15
1 22
2 23
2 13
按照id分组sum:
1 12+15+22
2 36
窗口函数id分组截止当前行sum：
1 12
1 27
1 49
2 23
2 36
默认的数据库的查询都是要么详细记录,要么聚合分析,如果要查询详细记录和聚合数据,必须要经过两次查询
简单的说窗口函数对每条详细记录开一个窗口,进行聚合统计的查询
默认mysql老版本没有支持,在最新的8.0版本中支持, Oracle和Hive中都支持窗口函数
同时整合查询聚集前的结果和聚集后的结果

-- 数据准备order.txt
-- 格式为:
-- 姓名,购买日期,购买数量
-- 具体数据请看不做举例
-- 创建order表:
create table if not exists t_order
(
    name      string,
    orderdate string,
    cost      int
)  row format delimited fields terminated by ',';

-- 加载数据:
load data local inpath "/home/order.txt" into table t_order;
-- over 开窗语法
-- 使用窗口函数之前一般要通过over()进行开窗,简单可以写成==函数+over==简单的写法如下:
-- 一般搭配：
sum(col) over()
count() over()
-- 示例
-- 1.不使用窗口函数 
-- 查询所有明细
select * from t_order;
# 查询总量
select name,orderdate,count(*) from t_order;
-- 2.使用窗口函数
select *, count(*) over() from t_order;
-- 注意:
--   窗口函数是针对每一行数据的.
--    如果over中没有参数,默认的是全部结果集
-- 需求:查询在2018年1月份购买过的顾客购买明细及总人数
select 
*,
count(*) over ()
from t_order
where substring(orderdate,1,7) = '2018-01'
;
-- partition by子句
-- 在over窗口中进行分区,对某一列进行分区统计,窗口的大小就是分区的大小
-- 需求:查看顾客的购买明细及月购买总额
select 
*,
sum(tor.cost) over(partition by month(tor.orderdate))
from t_order tor
;
-- order by
-- order by子句会让输入的数据强制排序 ;;建议sort by更好一些。
select 
*,
sum(tor.cost) over(distribute by month(tor.orderdate) sort by tor.orderdate asc)
from t_order tor
;
-- Window子句
-- 如果要对窗口的结果做更细粒度的划分,那么就使用window字句,常见的有下面几个
--    PRECEDING：往前
--    FOLLOWING：往后
--    CURRENT ROW：当前行
--    UNBOUNDED：无限制；UNBOUNDED PRECEDING 无上限；UNBOUNDED following 无下限
-- 一般window子句都是==rows==开头
-- 案例:
select name,orderdate,cost,
       sum(cost) over() as sample1,--所有行相加
       sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加
       sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加
       sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一样,由起点到当前行的聚合
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, --当前行和前面一行做聚合
       sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,--当前行和前边一行及后面一行
       sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from t_order;

ntil

select 
name,orderdate,cost,
ntile(3) over(partition by name) -- 按照name进行分组,在分组内将数据切成3份
from t_order;

 LAG和LEAD函数
    lag返回当前数据行的上一行数据
    lead返回当前数据行的下一行数据
    语法：

lag语法：
LAG  (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause); The LAG function is used to access data from a previous row.
scalar_expression：所取字段(列)，必填
[,offset] ： 向上所取行数，省略时默认值为1
[,default]： 如所取值为空时的默认值，默认为NULL

lead语法：
LEAD (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause); The LEAD function is used to return data from the next row.
scalar_expression：所取字段(列)，必填
[,offset] ： 向下所取行数，省略时默认值为1
[,default]： 如所取值为空时的默认值，默认为NULL

案例：

select 
name,orderdate,cost,
lag(cost,1) over(partition by name order by orderdate asc) up
from t_order;

select 
name,orderdate,cost,
lead(cost,1) over(partition by name order by orderdate asc) down
from t_order;

first_value和last_value
    first_value  取分组内排序后，截止到当前行，第一个值
    last_value  分组内排序后，截止到当前行，最后一个值

select 
name,orderdate,cost,
first_value(cost) over(partition by name order by orderdate asc) small,
last_value(cost) over(partition by name order by orderdate asc) big
from t_order;

排名函数
场景：
1、能做分组排名
2、能做自增的主键
    row_number从1开始，按照顺序，生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
    RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位
    DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

row_number()：没有并列，相同名次依顺序排
rank()：有并列，相同名次空位
dense_rank()：有并列，相同名次不空位

案例：

-- 查询每天考试前3名
select
tmp.*
from (select 
dt,name,score,
row_number() over(partition by dt order by score desc) rn_cnt,
rank() over(partition by dt order by score desc) ra_cnt,
dense_rank() over(partition by dt order by score desc) dr_cnt
from stu_score) tmp
where tmp.rn_cnt < 4
;