Hive函数

油头少年_w

已于 2024-11-11 00:21:00 修改

阅读量1k

点赞数 28

文章标签： hive hadoop 数据仓库

于 2024-11-10 23:55:06 首次发布

本文链接：https://blog.csdn.net/m0_63845988/article/details/143608445

版权

官网网站有函数文档:

LanguageManual UDF - Apache Hive - Apache Software Foundation

sql代码中查看函数:

查询所有hive函数名称: show functions;
查看某函数使用帮助文档: desc function [extended] 函数名;

注意: 加上extended关键字能查看详细信息示例

函数分类标准

知识点:

原生分类标准: 内置函数和用户定义函数(UDF,UDAF,UDTF)

分类标准扩大化: 用户定义函数分类标准的扩大化,

本来，UDF 、UDAF、UDTF这3个标准是针对用户自定义函数分类的；

但是，现在可以将这个分类标准扩大到hive中所有的函数，包括内置函数和自定义函数；

目前hive三大标准
UDF:（User-Defined-Function）普通函数: 特点是一进一出(输入一行数据输出一行数据)

举例: split
UDAF:（User-Defined Aggregation Function）聚合函数: 特点是多进一出(输入多行输出一行)

举例: count sum max min avg
UDTF:（User-Defined Table-Generating Functions）表生成函数: 特点是一进多出(输入一行输出多行)

举例: explode

示例:

查看所有hive函数

show functions;

查看指定函数基本信息

desc function split;

演示普通函数示例 (一进一出)
查看指定函数详细扩展信息(有对应示例)

desc function extended split;

切水果实战练习
已知字符串'苹果-香蕉-西瓜-哈密瓜-火龙果-榴莲'要求切割放到容器中

select '苹果-香蕉-西瓜-哈密瓜-火龙果-榴莲';
select split('苹果-香蕉-西瓜-哈密瓜-火龙果-榴莲', '-');

聚合函数(多进一出)

with t1 as (select explode(array(55,66,78,99,100,56)))
select avg(col) from t1;

演示炸裂函数示例(一进多出)
查看指定函数详细扩展信息(有对应示例)

desc function extended explode;

已知array容器中"苹果","香蕉","西瓜","哈密瓜","火龙果","榴莲"要求炸开

select explode(array("苹果", "香蕉", "西瓜", "哈密瓜", "火龙果", "榴莲"));

复杂类型函数

知识点:

hive复杂类型: array struct map

array类型: 又叫数组类型,存储同类型的单数据的集合

        取值: 字段名[索引] 注意: 索引从0开始

        获取长度的函数: size(array字段名) 常用

        判断是否包含某个数据的函数: array_contains(字段名,某数据) 常用

        对数组进行排序的函数: sort_array(数组)

struct类型: 又叫结构类型,可以存储不同类型单数据的集合

        取值: 字段名.子字段名n

map类型: 又叫映射类型,存储键值对数据的映射(根据key找value)

取值: 字段名[key] 获取长度的函数: size(字段名) 常用

        获取所有key的函数: map_keys() 常用

        获取所有value的函数: map_values() 常用

示例:

演示集合函数

array类型: 又叫数组类型,存储同类型的单数据的集合

取值: 字段名[索引] 注意: 索引从0开始

select array('binzi','666','888');

判断是否包含某个数据的函数: array_contains(字段名,某数据)

select array_contains(array('binzi','666','888'),'binzi');

对数组进行排序的函数: sort_array(数组)

select sort_array(array(3,1,5,2,4)); -- [1,2,3,4,5]

map类型: 又叫映射类型,存储键值对数据的映射(根据key找value)

取值: 字段名[key] 获取长度的函数: size(字段名)

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

获取长度的函数: size(array字段名)

select size(map('a',1,'b',2,'c',3));

获取所有key的函数: map_keys()

select map_keys(map('a',1,'b',2,'c',3));-- ["a","b","c"]

获取所有value的函数: map_values()

select map_values(map('a',1,'b',2,'c',3));-- [1,2,3]

字符串函数[练习]

知识点:

字符串常见的函数:

concat: 字符串紧凑拼接到一起生成新字符串

concat_ws: 字符串用指定分隔符拼接到一起生成新字符串常用

length: 获取字符串长度常用

lower: 把字符串中的字母全部变成小写

upper: 把字符串中的字母全部变成大写

trim: 把字符串两端的空白去除常用

拓展字符串函数

substr: 截取字符串常用

replace: 替换字符串常用

regexp_replace: 正则替换字符串

parse_url: 解析url(统一资源定位符) 组成: 协议/主机地址:端口号/资源路径?查询参数 get_json_object: 获取json对象解析对应数据

示例:

演示字符串常见的函数
concat: 字符串紧凑拼接到一起生成新字符串

select concat('binzi', '666', '888');

concat_ws: 字符串用指定分隔符拼接到一起生成新字符串

select concat_ws('-', 'binzi', '666', '888');

length: 获取字符串长度

select length('binzi-666');

lower: 把字符串中的字母全部变成小写

select lower('BINZI-666');

upper: 把字符串中的字母全部变成大写

select upper('binzi-666');

trim: 把字符串两端的空白去除

select '   binzi 666  ';
select trim('   binzi 666  ');

substr(字符串,开始索引,截取长度): 截取字符串
注意: 正索引从1开始正着数负索引从-1开始负着数

select substr('binzi666',1,2);

select substr('binzi666',1); -- 默认从1索引位置到最后

select substr('binzi666',-4);-- 默认从-4索引位置到最后

select `current_date`();

已知'2023-05-21'要求分别截取年月日

select substr('2023-05-21',1,4); -- 结果2023

select substr('2023-05-21',6,2); -- 结果05

select substr('2023-05-21',-2,2); -- 结果21

replace(大字符串,敏感词,替换后的内容):替换字符串

select replace('你TMD哦','TMD','真可爱');

正则表达式替换函数：regexp_replace(str, regexp, rep)

select regexp_replace('binzi-666', '\\d+', 'num');

正则表达式解析函数：regexp_extract(str, regexp[, idx])
正则中()代表分组,自动从1开始生成编号,提取正则匹配到的指定组内容

select regexp_extract('binzi-666-888', '(\\d+)-(\\d+)', 1);

URL解析函数：parse_url 注意要想一次解析出多个可以使用parse_url_tuple这个UDTF函数
URL: 统一资源定位符也就是咱们常说的网址组成: 协议主机地址:端口号资源路径查询参数

select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'HOST');

select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'PATH');

select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY');

select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY', 'user');

select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY', 'pwd');

json解析函数：get_json_object(json_txt, path), 细节: 整个json字符串用单引号'包裹, json字符串中的键, 值用双引号"包裹.
json字符串的格式: {键:值, 键: 值}
json数组的格式: [{键:值, 键: 值}, {键:值, 键: 值}, {键:值, 键: 值}] -- 索引从 0 开始.

select get_json_object('{"name":"杨过", "age":"18"}', '$.name');      -- 杨过, $表示json对象

select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[0]'); -- {"name":"杨过", "age":"18"}

select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name'); -- 小龙女,   $表示json对象

日期时间函数[练习]

知识点:

current_timestamp: 获取时间原点到现在的秒/毫秒,底层自动转换方便查看的日期格式    常用
to_date: 字符串格式时间戳转日期(年月日)
current_date: 获取当前日期(年月日)    常用

year: 获取指定日期时间中的年    常用
quarter: 获取指定日期所属的季度
month:获取指定日期时间中的月    常用
day:获取指定日期时间中的日    常用
hour:获取指定日期时间中的时
minute:获取指定日期时间中的分
second:获取指定日期时间中的秒

dayofmonth: 获取指定日期时间中的月中第几天
dayofweek:获取指定日期时间中的周中第几天
quarter:获取指定日期时间中的所属季度
weekofyear:获取指定日期时间中的年中第几周

datediff: 获取两个指定时间的差值    常用
date_add: 在指定日期时间上加几天    常用
date_sub: 在指定日期时间上减几天

unix_timestamp: 获取unix时间戳(时间原点到现在的秒/毫秒)    注意: 可以使用yyyyMMdd HH:mm:ss进行格式化转换
from_unixtime: 把unix时间戳转换为日期格式的时间    注意: 如果传入的参数是0,获取的是时间原点1970-01-01 00:00:00

示例:

日期时间函数
获取当前时间戳(时间原点到现在的秒/毫秒)

select unix_timestamp();

select current_timestamp(); -- 自动转换

获取当前日期

select current_date();

字符串格式时间戳转日期

select to_date('2023-05-21 11:19:31.222000000');

select to_date(current_timestamp());

依次获取年月日时分秒

select year('2023-05-21 11:19:31.222000000');

select month('2023-05-21 11:19:31.222000000');

select day('2023-05-21 11:19:31.222000000');

select hour('2023-05-21 11:19:31.222000000');

select minute('2023-05-21 11:19:31.222000000');

select second('2023-05-21 11:19:31.222000000');

依次获取月中第几天,周中第几天,季度,年中第几周

select dayofmonth('2023-05-21 11:19:31.222000000');

select dayofweek('2023-05-21 11:19:31.222000000');

select quarter('2023-05-21 11:19:31.222000000');

select weekofyear('2023-05-21 11:19:31.222000000');

计算时间差

select datediff('2023-05-21','2023-05-09');

获取明天的日期

select date_add(current_timestamp(),1);
select date_sub(current_timestamp(),-1);

获取昨天的日期

select date_sub(current_timestamp(),1);
select date_add(current_timestamp(),-1);

拓展
获取当前UNIX时间戳函数: unix_timestamp

select unix_timestamp();

字符串日期转UNIX时间戳函数: unix_timestamp

select unix_timestamp("2023-5-21 11:38:56"); -- 1684669136

指定格式日期转UNIX时间戳函数: unix_timestamp

select unix_timestamp('20230521 11:38:56','yyyyMMdd HH:mm:ss'); --1684669136

UNIX时间戳转日期函数: from_unixtime

select from_unixtime(1684669136); -- 2023-05-21 11:38:56

获取时间原点日期

select from_unixtime(0); -- 1970-01-01 00:00:00

数学函数

pi: 生成π结果

round: 指定小数保留位数常用

rand: 生成0-1的随机数

ceil: 向上取整

floor: 向下取整

随机数 -- 完全随机0.0-1.0之间随机取值

select rand();					-- 完全随机0.0-1.0之间随机取值

获取π值

select pi();

四舍五入设置保留位数

select round(pi(),4);

round 取整

select round(pi());

向上取整

select ceil(pi());

向下取整

select floor(pi());

条件函数[练习]

知识点:

if(参数1,参数2,参数3): 如果参数1表达式结果为true,就执行参数2内容,否则执行参数3的内容 case...when.then...end: 条件判断类似于编程语言中的if..else if ...else... 常用

isnull(数据) : 为空null: true 不为空:false

isnotnull(数据): 不为空: true 为空null:false

nvl(数据,参数2): 如果数据不为空就保留数据本身,如果数据为空null那就用第二个参数替换常用

coalesce(参数1,参数2...): 从左到右依次查找,返回第一个不是null的值,如果找到最后都是null,就返回null

示例:

演示条件函数
if(条件判断,true的时候执行此处,false的时候执行此处)

select if(10 > 5, '真', '假');

select if(10 < 5, '真', '假');

条件转换函数格式1: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

select
      case 7
          when 1 then '周一上班'
          when 2 then '周二上班'
          when 3 then '周三上班'
          when 4 then '周四上班'
          when 5 then '周五上班'
          when 6 then '周六休息'
          when 7 then '周日休息'
          else '老弟啊,你是外星人吗?'
      end;

条件转换函数格式2:CASE WHEN a==b THEN a==c [WHEN a==d THEN a==e]* [ELSE f] END

select
      case
          when 7==1 then '周一上班'
          when 7==2 then '周二上班'
          when 7==3 then '周三上班'
          when 7==4 then '周四上班'
          when 7==5 then '周五上班'
          when 7==6 then '周六休息'
          when 7==7 then '周日休息'
          else '老弟啊,你是外星人吗?'
      end;

演示null相关函数
isnull(数据) 为空: true 不为空:false

select isnull(null); -- true

isnotnull(数据) 不为空: true 为空:false

select isnotnull('斌子'); -- true

nvl(数据,前面的数据是null的时候执行此处): 如果数据不为空打印数据,为空打印第二个参数

select nvl('binzi','666');

select nvl(null,'666');

coalesce(v1,v2...): 从左到右依次查找,返回第一个不是null的值,如果找到最后都是null,就返回null

select COALESCE(null,11,22,33);-- 11

select COALESCE(null,null,22,33);--22

select COALESCE(null,null,null,33);--33

select COALESCE(null,null,null,0);--0

select COALESCE(null,null,null,null);--null

注意: null不能和其他数值相加,结果会返回null,这时候就需要用nvl或者coalesce把null转为0

select 10+null;

select 10+nvl(null,0);

select 10+coalesce(null,0);

类型转换函数

类型转换: cast(数据 as 要转换的类型) 常用

演示类型转换函数
cast: 主要用于类型转换注意: 转换失败返回null

select cast(3.14 as int); -- 3

select cast(3.14 as string) ; -- '3.14'

select cast('3.14' as float); -- 3.14

select cast('3.14' as int); -- 3

select cast('binzi' as int); -- null

注意: 很多时候底层都默认做了自动转换

select '3'+3; -- 6

实际应用场景:concat_ws要求被连接的必须是字符串,如果直接用666就报错

select concat_ws('_','binzi',666,'888'); --此行报错,因为concat_ws只能拼接字符串类型

select concat_ws('_','binzi',cast(666 as string),'888'); -- binzi_666_888

数据脱敏函数

演示数据脱敏函数[了解]

mask_hash: 返回指定字符串的hash编码
select mask_hash('binzi');

拓展
将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。

select mask("abc123DEF"); -- xxxnnnXXX

自定义替换的字母: 依次为大写小写数字

select mask("abc123DEF",'大','小','数');

select mask("abc123DEF",'/','.','%');

mask_first_n(string str[, int n]
对前n个进行脱敏替换大写字母转换为X，小写字母转换为x，数字转换为n。

select mask_first_n("abc123DEF",6);

mask_last_n(string str[, int n])
对后n个进行脱敏替换大写字母转换为X，小写字母转换为x，数字转换为n。

select mask_last_n("abc123DEF",6);

mask_show_first_n(string str[, int n])
除了前n个字符，其余进行掩码处理

select mask_show_first_n("abc123DEF",6);

除了后n个字符，其余进行掩码处理
mask_show_last_n(string str[, int n])

select mask_show_last_n("abc123DEF",6);

其他函数

演示其他函数
取哈希值函数:hash

select hash("binzi"); -- 93742710

MD5加密: md5(string/binary)

select md5("binzi"); -- 32位   072853027b387fcf891a610137f8dc1b

取长度:length

select length('072853027b387fcf891a610137f8dc1b');

SHA-1加密: sha1(string/binary)

select sha1("binzi"); -- 40位 66368c80ca9125f9a8a945aaf1e1ec3f8b21f7f9

SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)

select sha2("binzi",224);

select sha2("binzi",512);

crc32加密:

select crc32("binzi"); -- 3221865747

当前环境相关的

select current_user(),logged_in_user(),current_database(),version();

炸裂函数实战[练习]

知识点:

把一个容器的多个数据炸裂出单独展示: explode(容器)

炸裂函数配合侧视图使用格式:select 原表别名.字段名,侧视图名.字段名 from 原表原表别名 lateral view explode(要炸开的字段) 侧视图名 as 字段名 ;

示例:

UDTF: 一进多出

select explode(array('binzi', '666', '888'));

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

实战

将NBA总冠军球队数据使用explode进行拆分，并且根据夺冠年份进行倒序排序。
step1:建表

create table the_nba_championship(
          team_name string,
          champion_year array<string>
) row format delimited
fields terminated by ','
collection items terminated by '|';

step2:加载数据文件到表中先上传到hdfs/source目录

load data  inpath '/source/The_NBA_Championship.txt' into table the_nba_championship;

step3:验证

select * from the_nba_championship;

只查询冠军年份

select explode(champion_year) as year from the_nba_championship ;

配合侧视图完成需求

with tmp as(
   select  a.team_name,b.year
   from the_nba_championship a
   lateral view explode(champion_year) b as year
   )
select * from tmp;

JSON文件处理

知识点:

get_json_object: 获取json对象解析对应数据一次只能提取一个字段
json_tuple: 直接获取json对应数据这是一个UDTF函数可以一次解析提取多个字段
注意: 因为json_tuple是UDTF函数,所以也可以配合侧视图使用

示例:

演示json解析
需求: 把json解析后的数据保存成一个新表

--创建表
create table tb_json_test1 (
   json string
);

--加载数据
load data  inpath '/mnt/device.json' into table tb_json_test1;

-- 查看数据
select * from tb_json_test1;

方式1: 逐个(字段)处理, get_json_object UDF函数最大弊端是一次只能解析提取一个字段
-- get_json_object UDF函数最大弊端是一次只能解析提取一个字段

create table device1 as
select
   --获取设备名称
   get_json_object(json,"$.device") as device,
   --获取设备类型
   get_json_object(json,"$.deviceType") as deviceType,
   --获取设备信号强度
   get_json_object(json,"$.signal") as signal,
   --获取时间
   get_json_object(json,"$.time") as stime
from tb_json_test1;

select * from device1;

方式2: 逐条处理. json_tuple 这是一个UDTF函数可以一次解析提取多个字段
--json_tuple 这是一个UDTF函数可以一次解析提取多个字段
--单独使用解析所有字段

create table device2 as
select
   json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
from tb_json_test1;

select * from device2;

搭配侧视图使用(本次了解)

select
   device,deviceType,signal,stime
from tb_json_test1
        lateral view json_tuple(json,"device","deviceType","signal","time") b
        as device,deviceType,signal,stime;

方式3: 在建表时候, 直接处理json, row format SerDe '能处理Json的SerDe类'

--建表的时候直接使用JsonSerDe解析
create table tb_json_test2 (
                              device string,
                              deviceType string,
                              signal double,
                              `time` string
)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;

-- 加载数据
load data  inpath '/source/device.json' into table tb_json_test2;

-- 查看
select * from tb_json_test2;

开窗函数

基础知识点[重点]

开窗函数格式: select ... 开窗函数 over(partition by 分组字段名 order by 排序字段名 asc|desc) ... from 表名;

聚合开窗函数: 原来学的聚合函数(max,min,sum,count,avg)配合over()使用的时候,这些聚合函数也可以叫开窗函数

排序开窗函数: row_number dense_rank rank
                row_number: 巧记: 1234 特点: 唯一且连续
                dense_rank: 巧记: 1223 特点: 并列且连续
          rank: 巧记: 1224                 特点: 并列不连续

开窗函数: hive和mysql8都能使用
开窗函数本质在表后新增了一列

聚合开窗函数: max min sum avg count

-- 聚合函数配合over()使用,也可以叫开窗函数
select col1,
      max(col3) over()
from row2col2;

排序开窗函数: row_number rank dense_rank

-- 排序函数必须配合over(order by 排序字段 asc|desc)
/*
row_number: 巧记: 1234   特点: 唯一且连续
dense_rank: 巧记: 1223   特点: 并列且连续
  rank   : 巧记: 1224   特点: 并列不连续
*/

select *,
      row_number() over (order by signal desc),
      dense_rank() over (order by signal desc),
      rank() over (order by signal desc)
from device1;

开窗函数分组
注意不能用group by ,需要使用partition by,可以理解成partition by是group by的子句
演示排序函数和分组配合使用: 先分组再组内排序

select *,
      row_number() over (partition by deviceType order by signal desc),
      dense_rank() over (partition by deviceType order by signal desc),
      rank() over (partition by deviceType order by signal desc)
from device1;

演示聚合函数和分组配合使用

select *,
      max(signal) over(partition by deviceType)
from device1;

演示聚合函数同时和分组以及排序关键字配合使用
需求：求出每个用户截止到当天，累积的总pv数

---建表并且加载数据
create table website_pv_info(
  cookieid string,
  createtime string,   --day
  pv int
) row format delimited
fields terminated by ',';
-- 建表
create table website_url_info (
   cookieid string,
   createtime string,  --访问时间
   url string       --访问页面
) row format delimited
fields terminated by ',';

-- 加载数据  直接上传website_pv_info.txt和website_url_info.txt到hdfs中指定表路径中
-- 查询数据
select * from website_pv_info;
select * from website_url_info;

需求：求出每个用户截止到当天，累积的总pv数
sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和

select cookieid, createtime,  pv,
      sum(pv) over(partition by cookieid order by createtime) as current_total_pv
from website_pv_info;

开窗函数控制范围

开窗函数控制范围: rows between
               - x preceding:往前x行
- x following:往后x行
- current row:当前行
- unbounded: 起点
- unbounded preceding :表示从前面的起点第一行
                - unbounded following :表示到后面的终点最后一行

range : 范围(范围的数据)
                               range between
- n preceding：往前
- n following：往后
                               - current row：当前行
- unbounded：起点
- unbounded preceding 表示从前面的起点第一行
- unbounded following：表示到后面的终点最后一行

演示窗口范围的控制

--默认从第一行到当前行
select cookieid,createtime,pv,
      sum(pv) over(partition by cookieid order by createtime) as pv1
from website_pv_info;

--第一行到当前行 等效于rows between不写 默认就是第一行到当前行
select cookieid,createtime,pv,
      sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from website_pv_info;

--向前3行至当前行
select cookieid,createtime,pv,
      sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from website_pv_info;

--向前3行 向后1行
select cookieid,createtime,pv,
      sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from website_pv_info;

--当前行至最后一行
select cookieid,createtime,pv,
      sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from website_pv_info;

--第一行到最后一行 也就是分组内的所有行
select cookieid,createtime,pv,
      sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding  and unbounded following) as pv6
from website_pv_info;

range : 范围(范围的数据)

建表

create table website_pv_info_range(
    cookieid   string,
    createtime string, --day
    pv         int
) row format delimited
    fields terminated by ',';

按照时间范围求出范围内的pv数和当前行到往上无边界统计pv数

select *,
       sum(pv) over(partition by cookieid order by createtime
            range between unbounded preceding and current row) , ---  按照时间范围求出范围内的pv数
       sum(pv) over(partition by cookieid order by createtime
            rows between unbounded preceding and current row)--按照当前行到往上无边界统计pv数
from website_pv_info_range;

其他开窗函数

其他开窗函数: ntile lag和lead first_value和last_value

ntile(x)功能: 将分组排序之后的数据分成指定的x个部分（x个桶）
注意ntile规则:尽量平均分配，优先满足最小(编号1)的桶，彼此最多不相差1个。

lag: 用于统计窗口内往上第n行值
lead:用于统计窗口内往下第n行值

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

注意: 窗口函数结果都是单独生成一列存储对应数据

演示其他函数
演示ntile
把每个分组内的数据分为3桶

SELECT
   cookieid,
   createtime,
   pv,
   ntile(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;

需求：统计每个用户pv数最多的前3分之1天。

--理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
SELECT * from
(SELECT
    cookieid,
    createtime,
    pv,
    NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
FROM website_pv_info) tmp where rn =1;

lag 用于统计窗口内往上第n行值

select cookieid, createtime, url,
   row_number() over (partition by cookieid order by createtime) rn,
   lag(createtime, 1) over (partition by cookieid order by createtime) la1,
   lag(createtime, 2, '2000-01-01 00:00:00') over (partition by cookieid order by createtime) la2
from website_url_info;

lead 用于统计窗口内往下第n行值

select cookieid, createtime, url,
   row_number() over (partition by cookieid order by createtime) rn,
   lead(createtime, 1) over (partition by cookieid order by createtime) la1,
   lead(createtime, 2, '2000-01-01 00:00:00') over (partition by cookieid order by createtime) la2
from website_url_info;

FIRST_VALUE 取分组内排序后，从开始计算,截止到当前行，第一个值

select cookieid, createtime, url,
   row_number() over (partition by cookieid order by createtime) rn,
   first_value(url) over (partition by cookieid order by createtime) fv
from website_url_info;

LAST_VALUE 取分组内排序后，从末尾计算,截止到当前行，最后一个值

select cookieid, createtime, url,
   row_number() over (partition by cookieid order by createtime) rn,
   last_value(url) over (partition by cookieid order by createtime rows between unbounded preceding and unbounded following) fv
from website_url_info;