Hive函数

---

一、Hive函数分类

1. Hive函数划分介绍

Hive函数划分介绍:
    函数介绍(大白话):
        函数就是开发者定义好的规则, 我们只要调用指定的函数, 传入对应的参数, 就能获取对应的结果, 我们无需关心函数内部是如何执行的, 只要会调用即可.
        可以把函数理解为是人们研发的机器, 不同的机器所需的原材料(参数)是不一样的, 传入不同的原材料(参数), 就会获取不同的结果.
    概述:
        最初Hive的函数分为 内置函数 和 用户自定义函数两大类, 而用户自定义函数又分为 UDF, UDAF, UDTF三种.
        后来人们(程序员)发现用 UDF, UDAF, UDTF来划分hive函数太方便精准了, 于是在Hive2.X版本中提出了1个概念: 函数标准扩大化.
        即: 从Hive2.4X开始, Hive中的函数就只有: UDF, UDAF, UDTF三种了.
    分类:
        UDF:    全称 user defined functions, 也叫: 普通函数.
            核心: 一进一出.  即: 给函数10行数据, 处理之后, 返回10行.
            例如: abs(), round()...
        UDAF:   全称 user defined Aggregate functions, 也叫: 聚合函数.
            核心: 多进一出, 即: 给函数10行数据, 处理之后, 返回1行.
            例如: count(), max(), sum(), avg(), min()
        UDTF:   全称 user defined Table Generating functions, 也叫: 表生成函数.
            核心: 一进多出, 即: 给函数1条, 它返回多条.
            例如: explode() 爆炸函数(炸裂函数)

2. 案例–Hive函数划分

-- ------------------------ 案例1: hive函数划分 ------------------------
use day08;
--- 1. UDF函数演示, 一进一出.
select abs(-10);    -- 10
select round(10.123, 2);    -- 10.12

-- 2. UDAF函数演示, 多进一出.
select count(sid) from student;     -- 22条

-- 3. UDTF函数演示, 一进多出.
select array('aa', 'bb', 'cc');     -- 演示数据源, 即: 一行数据.  这个不是 UDTF函数.
select explode(array('aa', 'bb', 'cc'));    -- 一进多出, 这个才是UDTF函数.

-- 4. 扩展, 如果某个函数不会用, 可以查看下它的说明文档, 具体如下.
-- 官网网址: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-MathematicalFunctions

-- 5. 函数基础语法.
show functions ;        -- 查看hive所有的函数, hive中符号也是函数, 只不过是: 函数名为符号而已.
describe function abs;              -- 查看函数的说明文档(简单信息)
describe function extended abs;     -- 查看函数的说明文档(详细信息).

二、Hive函数-字符串相关函数

1. 字符串相关函数

-- ------------------------ 案例2: Hive函数-字符串相关函数 ------------------------
-- 演示: split(参1, 参2)函数, 参1: 要切割的字符串, 参2: 切割符(支持正则)
select split('a1b2c3d', '1');           -- ["a","b2c3d"], 按照数字1对字符串切割
select split('a1b2c3d', '[0-9]');       -- ["a","b","c","d"], 按照数字对字符串切割
select split('a1b2c3d', '\\d+');      -- ["a","b","c","d"], 按照数字对字符串切割

-- 演示: concat()函数, 默认拼接符是: '', 可以拼接任意类型.
select concat('-', 'aa', 'bb', 11, 'false'); -- -aabb11false
-- 演示: concat_ws()函数, 可以指定拼接符. 参1: 拼接符号, 参2.... 要拼接的内容.
select concat_ws('-', 'aa', 'bb', 'cc', array('x', 'y', 'z')); -- 只能拼字符串, 或者字符串数组      aa-bb-cc-x-y-z
-- 演示: trim()函数, 移除两端空格.
select trim('    aa   bb     ');           -- 'aa   bb'
-- 演示: substr()函数, 参1: 要操作的字符串, 参2:从几开始截取,默认从1开始计数.  参3: 截取几个.
select substr('hello world hive', 7);    -- world hive, 从7开始, 截取到结尾.
select substr('hello world hive', 7, 5);    -- world hive, 从7开始, 截取5个
select substr('hello world hive', -4);    -- hive, 从-4索引(从右往左数, 从-1开始)
select substr('hello world hive', 13);    -- hive, 从13索引开始.

select substr('2024-01-14 10:16:19', 1, 10);       -- 年月日
select substr('2024-01-14 10:16:19', 6, 2);        -- 月
select substr('2024-01-14 10:16:19',9, 2);         -- 日

-- 演示: substring()函数, 效果同上.
select substring('2024-01-14 10:16:19', 1, 10);       -- 年月日

-- 演示: lower()函数,
select lower('ABC123');     -- abc123
-- 演示: upper()函数,
select upper('abc123');     -- ABC123

-- 演示: regexp_replace()函数, 正则替换, 参1: 要操作的字符串, 参2: 正则表达式, 参3: 替换后的内容.
select regexp_replace('aa11bb22cc33dd', '\\d+', '#');

-- 演示: parse_url()函数, 从URL地址中解析出指定的内容.  参1: url地址, 参2: 要解析出的内容, 例如: HOST, PATH, QUERY
--                          域名          文件路径     参数
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'HOST'); -- www.itcast.cn
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'PATH'); -- /index.html
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'QUERY'); -- username=root&password=123456'
select parse_url('https://www.itcast.cn/index.html?username=root&password=123456', 'QUERY', 'username'); -- root

2. json字符串

-- 演示: get_json_object(), 处理json字符串的.   参1: json字符串, 参2: 从json中解析出对应内容, 可以用 $代表整个json字符串 或者 json数组.

json字符串介绍:
    概述:
        它是一种特殊规则的字符串, 由键值对组成. 只要用双引号包裹.
    格式:
        {"键":"值", "键":"值"}
    作用:
        一般是前后端传输数据的, 跨域传输, 例如: 前端把数据封装好 然后传入到 后台代码中.
    细节:
        json字符串也有数组形式:  [{"键":"值", "键":"值"}, {"键":"值", "键":"值"}...]

3. 案例–json字符串

select get_json_object('{"name":"乔峰", "age":31}', '$.name');    -- 乔峰
select get_json_object("{'name':'乔峰', 'age':31}", '$.name');    -- null, json字符串用双引号包裹.
-- json数组
select get_json_object('[{"name":"乔峰", "age":31}, {"name":"虚竹", "age":29}]', '$.[1].name');    -- 虚竹, 索引从0开始.

三、Hive函数–日期相关函数

-- ------------------------ 案例3: Hive函数-字符串相关函数 ------------------------
-- 1. 获取当前的时间戳.
select `current_timestamp`();       -- 2024-01-14 10:57:23.763000000

-- 2. 获取当前的时间.
select `current_date`();    -- 2024-01-14

-- 3. 获取当前的秒值, 即: 从时间原点(1970年1月1日 00:00:00) 至 当前时间的秒值
select unix_timestamp();                        -- 1705201180
select unix_timestamp('1970-01-01 00:00:01');   -- 1, 获取时间原点 ~ 指定时间的秒值.

-- 4. 把秒值 转成对应的 时间.
select from_unixtime(1705201180);       -- 2024-01-14 02:59:40

-- 5. 根据时间戳, 获取指定的日期: 年-月-日
select to_date(`current_timestamp`());

-- 6. 计算下你活了多少天.
--         当前时间的秒值  -   你出生那天的秒值
select unix_timestamp() - unix_timestamp('2002-08-07 00:00:00');                    -- 676523149秒
select (unix_timestamp() - unix_timestamp('2002-08-07 00:00:00')) / 3600 / 24;      -- 7830.129537037036....

-- 7. 计算两个日期的差值.
select datediff('2024-01-10 00:00:00', '2024-01-05 00:00:00');  --  结果: 前 - 后, 5

-- 8. 从日期中获取对应的内容.
select year('2024-01-10 13:14:21');
select month('2024-01-10 13:14:21');
select day('2024-01-10 13:14:21');
select hour('2024-01-10 13:14:21');
select minute('2024-01-10 13:14:21');
select second('2024-01-10 13:14:21');
select quarter('2024-01-10 13:14:21');          -- 掌握, 季度.

-- 9. 偏移指定的时间.
select date_add('2024-01-10 13:14:21', 2);    -- 往后推2天, 2024-01-12
select date_sub('2024-01-10 13:14:21', -2);   -- 往后推2天, 2024-01-12,      subtract: 减法

select date_add('2024-01-10 13:14:21', -2);  -- 往前推2天, 2024-01-08
select date_sub('2024-01-10 13:14:21', 2);   -- 往前推2天, 2024-01-08,      subtract: 减法

四、Hive函数–数字相关

-- ------------------------ 案例4: Hive函数-数字相关函数 ------------------------
-- 1. 求绝对值.
select abs(-10);    -- 10

-- 2. 四舍五入. round
select round(10.312, 2);    -- 10.31
select round(10.315, 2);    -- 10.32

-- 3. 随机数
select rand();      -- 0.0 ~ 1.0之间, 包左不包右.
-- 新需求: 获取1个 1 ~ 100之间的随机数
select int(rand() * 100) + 1;   -- rand() * 100 获取 0.0 ~ 100.0 之间, 包左不包右的数字.

-- 4. ceil() 天花板数, 比这个数字大的所有数字中, 最小的那个整数.
select ceil(10.3);  -- 11
select ceil(10.0);  -- 10

-- 5. floor() 地板数.
select floor(10.3);  -- 10
select floor(10.0);  -- 10

-- 6. pow(a, b), 计平方值, a的b次幂
select pow(3, 2);   -- 9

-- 7. 面试题: 四舍五入的原理是什么?      +0.5, 然后求地板数.
select `floor`(10.3 + 0.5);     -- 10
select `floor`(10.4 + 0.5);     -- 10
select `floor`(10.5 + 0.5);     -- 11

五、Hive函数–转换相关

-- ------------------------ 案例5: Hive函数-判断, 转换相关函数 ------------------------
-- 1. if()函数, 做判断的, 条件成立返回值1, 条件不成立返回值2.  参1: 关系表达式, 参2: 值1, 参3: 值2
select if(5 > 3, '成立', '不成立');

-- 2. isnull() 判断是否为空.
select isnull('');      -- false
select isnull(null);    -- true

-- 3. isnotnull() 判断是否不为空.
select isnotnull('');      -- true
select isnotnull(null);    -- false

-- 4. nvl() 空值转换, 判断参1是否为空, 不为空就返回其值, 为空就返回值2.
select nvl('乔峰', '虚竹');     -- 乔峰
select nvl(null, '虚竹');      -- 虚竹

-- 5. coalesce(值1, 值2...)   获取众多参数中的第1个非空值.
select coalesce(null, null, null);              -- null
select coalesce(null, null, 1, 2, 3);           -- 1
select coalesce(null, 'y', null, 'a', '10');    -- y
select coalesce('a', '10', 'b', '20');          -- 'a'

-- 6. case when
select
    case
        when 5 > 3 then '大于'
        when 5 < 3 then '小于'
        else '等于'
    end as col1;

-- case when语法糖, 如果都是等于判断, 且是同一列, 可以简写为如下写法.
select
    case 1      -- 应该是: case 列名
        when 1 then '星期一'
        when 2 then '星期二'
        when 3 then '星期三'
        else '没有这样的时间'
    end as week;

-- 7. cast()函数, 类型转换.
select cast(10.3 as int);   -- 10
select cast(10.6 as int);   -- 10
select cast(10 as string);  -- '10'
select cast('123' as int);  -- 123, 字符串 -> 数字
select cast('123' as boolean);  -- true, 非空 '', 非0, 非null
select cast('' as boolean);  -- false

六、Hive函数–脱敏,加密相关

-- ------------------------ 案例6: Hive函数-加密, 脱敏相关函数 ------------------------
-- 1. 获取哈希值.
select hash('abc');     -- 96354

-- 2. 加密相关函数.
select md5('Aa1');    -- 698d51a19d8a121ce581499d7b701668
select sha1('Aa1');   -- f55cd2f8d3186c960aa86782452979118760e96d
select sha2('Aa1', 512);   -- 2907b40d2cb5527fe522b1c97f90dbf49423dd0cfd411f5d91528bc38c0c2ee9515d981816149d138ffa9d2ac80ef1d0435dc0213bdb85b2f85f446c4a035f3f
describe function extended sha2;
select crc32('Aa1');       -- 2736874553

-- 3. 脱敏相关.
select mask('xyzABC123');       --结果为: xxxXXXnnn,  默认顺序是: 大写字母X, 小写字母x, 数字n
-- 细节: 我想指定小写字母用 小 来替换.   参1: 要脱敏的字符串.  参2: 大写字母替换符, 参3: 小写字母替换符, 参4: 数字替换符.
select mask('xyzABC123', 'X', '小');  -- xxx小小小nnn

-- 4. 只脱敏前几个字符.
select mask_first_n('ABCabc123', 4);        -- XXXxbc123 只处理前 4 个字符.
select mask_show_first_n('ABCabc123', 4);   -- ABCaxxnnn 除了前 4 个字符, 其它都处理.
-- 自学: mask_last_n(),  mask_show_last_n()

-- 5. 需求: 把13112345678 => 131****5678
select concat(substr('13112345678', 1, 3), mask(substr('13112345678', 4, 4), 'X', 'x', '*'), substr('13112345678', -4));

-- 6. sort_array()函数, 对数组进行排序, 属于: 集合函数.
 select sort_array(array(11, 33, 22, 55));      -- [11,22,33,55]

七、Hive函数–其它函数

-- ------------------------ 案例7: Hive函数-其它函数 ------------------------
-- 1. 查看当前的用户
select current_user();      -- root

-- 2. 查看当前在使用的数据库.
select current_database();

-- 3. 查看当前的hive版本.
select version();       -- 3.1.2 r8190d2be7b7165effa62bd21b7d60ef81fb0e4af

八、Hive高阶函数–列转行(初体验)

-- ------------------------ 案例8: Hive高阶函数-行列转换 之初体验(列转行) ------------------------
-- 1. 铺垫知识. 列转行需要用到 explode()函数, 爆炸函数, 它属于UDTF(表生成函数), 即: 一进多出. 只能处理 数组或者字典.
select `array`(11, 22, 33);
select explode(array(11, 22, 33));      -- 一进多出,  列转行
select explode(map(11, 22, 33));        -- 报错, map是键值对类型, 元素个数必须成对出现.
select explode(map(11, 22, 33, 44));        -- 报错, map是键值对类型, 元素个数必须成对出现.

-- 2. 建表, NBA历年夺冠球队信息.
-- 数据格式为: Chicago Bulls,1991|1992|1993|1996|1997|1998
create table the_nba_championyear(
    team_name string comment '队名',
    champion_year array<string> comment '夺冠年份'
) row format delimited
    fields terminated by ','                -- 切割后, 数据为: "Chicago Bulls", "1991|1992|1993|1996|1997|1998"
    collection items terminated by '|';     -- 切割后, 数据为: "Chicago Bulls", ["1991", "1992", "1993", "1996", "1997", "1998"]

-- 3. 上传源文件, 然后查看数据.
select * from the_nba_championyear;

/*
至此, 数据已经加载成功了, 但是不符合我们的格式, 我要的数据格式为:
    team_name           champion_year
    Chicago Bulls         1991
    Chicago Bulls         1992
    Chicago Bulls         1993
    Chicago Bulls         1996
    Chicago Bulls         1997
    Chicago Bulls         1998
    San Antonio Spurs     1999
    San Antonio Spurs     2003
    ......
*/

-- 4. 思路很简单, 把 champion_year字段用 explode()爆炸函数, 炸开即可.
select explode(champion_year) as champion_year from the_nba_championyear;

-- 5. 走到这里, 就剩下最后1步了, 在上述查询基础上, 加入: 队名即可, 写法如下.
select team_name, explode(champion_year) as champion_year from the_nba_championyear;    -- 想法OK, 但是报错.

-- 6. 思考: 为啥报错?
-- 产生原因: 炸裂后的数据, 无法直接和原表查询, 因为对应的关联信息不精准(炸裂后的每一行, 具体对应的是炸裂前的哪一行数据, 对不上)
-- 解决方案: 通过侧视图(lateral view)实现, 它是Hive提供的一种专门用来记录 炸裂前后, 数据对应关系的函数.
-- 侧视图格式: lateral view 炸裂函数 侧视图的名字 as 炸裂后数据的列名
select a.team_name, b.champion_year from the_nba_championyear a
lateral view explode(champion_year) b as champion_year;

九、Hive高阶函数–行转列

1. 行列转换–行转列

行转列涉及到的函数:
    采集函数:
        collect_list()     采集, 不去重, 有序(不是排序, 而是元素的存取顺序).
        collect_set()      采集, 去重, 无序.
    拼接函数:
        concat()           默认拼接符 ''
        concat_ws()        可以指定拼接符.

2. 案例–行转列

-- ------------------------ 案例9: Hive高阶函数-行列转换 之 行转列 ------------------------
-- 1. 建表.
create table row2col2(
     col1 string,
     col2 string,
     col3 int
)row format delimited fields terminated by '\t';

-- 2. 上传源文件.
-- 3. 查看表数据.
select * from row2col2;

-- 4. HiveSQL语句--完成 行转列 代码实现.
select
    col1, col2,
    /*
     oncat_ws('-', collect_list(cast(col3 as string))) as col3 代码解释:
        cast(col3 as string):               把 col3 列的数据, 转成 string类型, 就能通过 concat_ws()拼接了.
        collect_list(cast(col3 as string)): 根据分组条件, 采集每组所有的信息, 有序, 可重复.
        collect_set(cast(col3 as string)): 根据分组条件, 采集每组所有的信息, 无序, 唯一.
        oncat_ws('-', collect_list(cast(col3 as string))):      用'-'作为连接符, 把上述采集到的数据, 拼接到一起.
     */
    concat_ws('-', collect_list(cast(col3 as string))) as col3,
    concat_ws('-', collect_set(cast(col3 as string))) as col4
from
    row2col2
group by
    col1, col2;

十、Hive高阶函数–列转行(面使用)

1. 注意点

explode()只能处理 数组array, 映射map数据. 可以通过split()函数把字符串转成 array数组.

2. 案例–列转行

-- ------------------------ 案例10: Hive高阶函数-行列转换 之 列转行 ------------------------
-- 1. 建表.
create table col2row2(
    col1 string,
    col2 string,
    col3 string
)row format delimited fields terminated by '\t';

-- 2. 上传源文件.
-- 3. 查看表数据.
select * from col2row2;

-- 4. HiveSQL语句--完成 列转行 代码实现.
select a.col1, a.col2, b.col3 from col2row2 a
lateral view explode(split(col3, ',')) b as col3;

-- 细节: explode()只能处理 数组array, 映射map数据. 可以通过split()函数把字符串转成 array数组.
select explode(split('1,2,3', ','));

十一、Hive高阶函数–处理json字符串

1. json字符串介绍

json字符串介绍:
    概述:
        它是一种特殊规则的字符串, 由键值对组成. 只要用双引号包裹.
    格式:
        {"键":"值", "键":"值"}
    作用:
        一般是前后端传输数据的, 跨域传输, 例如: 前端把数据封装好 然后传入到 后台代码中.
    细节:
        json字符串也有数组形式:  [{"键":"值", "键":"值"}, {"键":"值", "键":"值"}...]
    Hive中处理Json字符串主要有两种思路:
        思路1: 以普通字符串的形式存储json字符串, 然后解析.          简单说: 先存储, 再解析.
            方式1: get_json_object()      逐个解析.
            方式2: json_tuple()           批量解析.
        思路2: 在加载(建表)json文件的时候, 直接解析json字符串.      简单说: 存储时, 直接解.

2. 案例–处理json字符串

-- ------------------------ 案例11: Hive高阶函数-处理json字符串 ------------------------
-- 演示 思路1: 以普通字符串的形式存储json字符串, 然后解析.          
-- 简单说: 先存储, 再解析.
-- 1. 建表.
create table t_json1(
    json string
);
-- 2. 上传源文件.
-- 3. 查看源数据.
select * from t_json1;
-- 4. 解析json字符串.
-- 4.1 方式1: get_json_object()      逐个解析.
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 `time`    -- 数据采集时间
from
    t_json1;

-- 4.2 方式2: json_tuple()           批量解析.
select
    -- 格式: json_tuple(json字符串, '键名1', '键名2', '键名3'...) as (别名1, 别名2, 别名3...)
    json_tuple(json, 'device', 'deviceType', 'signal', 'time') as (device, deviceType, signal, `time`)
from t_json1;

-- 演示 思路2: 在加载(建表)json文件的时候, 直接解析json字符串.      简单说: 存储时, 直接解.
drop table t_json2;
-- 1. 建表, 直接解析 json字符串.
create table t_json2(
    device string,
    deviceType string,
    signal string,
    `time` string
)
-- delimited方式默认用的是LazySimpleSerDe类, 实现不了我们的需求, 所以我们用Hive专门处理Json字符串SerDe类, 即: JsonSerDe类
row format SerDe 'org.apache.hive.hcatalog.data.JsonSerDe';

-- 2. 上传源文件.
-- 3. 查看源数据(看到的就是 解析好的内容)
select * from t_json2;

十二、Hive高阶函数–窗口函数

1. 窗口函数

窗口函数解释:
    概述:
        窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数结合什么函数一起使用.
        窗口函数指的是 over()函数, 它可以限定操作数据的范围, 进行局部 或者 全局计算等...
    格式:
        可以结合窗口函数一起用的函数 over(partition by 分组字段 order by 排序字段 asc | desc rows between 起始行 and 结束行)
    可以结合窗口函数一起用的函数 分为3类:
        1. 聚合函数.
            count(), sum(), max(), min(), avg()
        2. 排序函数.
            row_number(), rank(), dense_rank(), ntile(n)
        3. 其它函数.
            lag(), lead(), first_value(), last_value()
    细节:
        1. 窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数结合什么函数一起使用.
        2. 如果不写partition by, 默认是全局统计, 如果写了, 则只统计组内的数据(局部统计).
        3. 如果不写order by, 默认是统计组内所有数据, 如果写了, 则统计组内第一行至当前行的数据.
        4. 通过rows between可以限定操作数据的范围, 常用关键字如下:
            unbounded preceding         起始行(第一行)
            n preceding                 向上n行
            current row                 当前行
            n following                 向下n行
            unbounded following         结束行(最后1行)
        5. ntile(n)是几分之几的意思, 表示把数据分成几份, 优先参考最小分区.
           例如: 7条数据, 分成3份, 则是:   1,1,1      2,2     3,3
        6. 常用的 可以结合窗口函数一起用的函数 主要有: count(), sum(), row_number(), rank(), lag()

2. 案例–窗口函数

-- ------------------------ 案例12: Hive高阶函数-窗口函数 ------------------------

--  ----------------------- 以下是窗口函数, 准备动作 -----------------------------------------
--  切换数据库.
create database day09;
use day09;
show tables ;

--  员工表
CREATE TABLE employee (
    id     int,         --  编号
    name   string, --  姓名
    deg    string, --  职位
    salary int,         --  工资
    dept   string  --  部门
) row format delimited fields terminated by ',';

--  网站点击量表
create table website_pv_info
(
    cookieid   string, --  用户id
    createtime string, --  访问时间
    pv         int          --  页面浏览量
) row format delimited fields terminated by ',';


--  网站访问记录表
create table website_url_info
(
    cookieid   string, --  用户id
    createtime string, --  访问时间
    url        string  --  访问的url页面
) row format delimited fields terminated by ',';

--  ----------------------- 以下是窗口函数案例 初体验-------------------------------------
--  案例1: 窗口函数初体验, 需求: 统计所有员工的工资.
--  不使用窗口函数, 直接用传统的 聚合函数.
select sum(salary) from employee;

--  需求: 统计所有员工的工资, 并将其展示在每个员工数据的最后.
select *, sum(salary) over() from employee;

3. 窗口函数–结合聚合函数

--  ------------------------------------- 以下是窗口函数案例 窗口 + 聚合 --------------------------------
--  补充概述: PV: Page View 页面浏览量, UV: user view, 用户访问数 IP: ip(独立访客)访问数
--  例如: 我通过我电脑的谷歌浏览器访问了京东的10个页面, 通过Edge浏览器访问了京东的7个页面, 请问: PV, UV, IP分别是多少?
--  答案: IP: 1个, UV: 2个, PV: 17个
select * from website_pv_info;

--  案例2: 演示 窗口函数 + 聚合函数一起使用.
--  需求：求出网站总的pv数 所有用户所有访问加起来
--  如果over()里边什么都不写, 默认操作的是: 表中 该列所有的数据.
select *, sum(pv) over() as total_pv from website_pv_info;

--  需求: 求出每个用户总pv数
--  方式1: sum() + group by 一起使用.
select cookieid, sum(pv) as total_pv from website_pv_info group by cookieid;

--  方式2: 聚合函数 + 窗口函数一起使用.
--  细节: 如果写了partition by(表示分组): 则默认操作 组内所有的数据.
select *, sum(pv) over(partition by cookieid) as total_pv from website_pv_info;

--  细节: 如果写了order by(表示排序):     则默认操作 组内第一行 至 当前行的数据.
select *, sum(pv) over(partition by cookieid order by createtime) as total_pv from website_pv_info;

--  上述的代码, 等价于如下的内容:
select *,                                                                 --   第1行             至    当前行
       sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row ) as total_pv
from
    website_pv_info;

--  需求: 统计每个cookieID的pv(访问量), 只统计: 当前行及 向前3行 向后1行
select *,                                                                 --   向前3行    至    向后1行
       sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following ) as total_pv
from
    website_pv_info;

4. 窗口函数–结合排序函数

--  ------------------------------------- 以下是窗口函数案例 窗口 + 排序 --------------------------------
--  需求: 根据点击量(pv)做排名, 组内排名.
--  这里的排序函数指的是: row_number(), rank(), dense_rank(), 它们都可以做排名, 不同的是, 对相同值的处理结果.
--  例如: 数据是100, 90, 90, 60, 则: row_number是: 1, 2, 3, 4,  rank: 1, 2, 2, 4,  dense_rank: 1, 2, 2, 3
select
    *,
    row_number() over (partition by cookieid order by pv desc ) as rn,      -- 根据用户id分组, 根据点击量, 降序排列.
    rank() over (partition by cookieid order by pv desc ) as rk,
    dense_rank() over (partition by cookieid order by pv desc ) as dr
from website_pv_info;
--  需求: 根据cookieID进行分组, 获取每组点击量最高的前4名数据, 这个就是经典的案例: 分组求TopN
--  Step1: 根据cookieID进行分组, 根据点击量进行排名.
select
    *,
    dense_rank() over (partition by cookieid order by pv desc ) as dr
from website_pv_info
where dr <= 4;          -- 报错, 思路没有问题, 但是: where只能筛选表中已经有的列(数据)
--  细节: where只能筛选表中已经有的列(数据)
--  Step2: 把上述的查询结果当做一张表, 然后从中获取我们要的数据即可.
with t1 as (
    select
        *,
        dense_rank() over (partition by cookieid order by pv desc ) as dr
    from website_pv_info
)
select * from t1 where dr <= 4;

--  ntile(数字,表示分成几份)  采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2
select
    *,
    ntile(3) over (partition by cookieid order by pv desc ) as nt      -- 根据用户id分组, 根据点击量, 降序排列. 分成3份
from website_pv_info;

-- 需求: 按照cookieid分组, 按照点击量降序排列, 只要每组前三分之一的数据.
with t1 as (
    select
        *,
        ntile(3) over (partition by cookieid order by pv desc ) as nt      -- 根据用户id分组, 根据点击量, 降序排列. 分成3份
    from website_pv_info
)
select * from t1 where nt = 1;

5. 窗口函数–结合其它函数

--  ---------------------- 以下是窗口函数案例 窗口 + 其它 --------------------------------
use day09;
--  1. LAG 用于统计窗口内往上第n行值
--  需求: 显示用户上一次的访问时间.
select
    *,
    -- 获取上1个createtime列的值,  根据cookieid分组   根据createtime升序排列
    lag(createtime) over(partition by cookieid order by createtime) lag_time
from
    website_url_info;

--  根据cookieID分组, createtime升序排序, 获取当前行 向上2行的createtime列的值, 找不到就用默认值(夯哥)填充.
select
    *,
    -- 获取createtime列向上第2个值, 如果没有就用 夯哥 作为默认值进行填充.
    -- 参1: 要操作的列,   参2: 向上第几个.  参3: 默认值.
    lag(createtime, 2, '夯哥') over(partition by cookieid order by createtime) lag_time2
from
    website_url_info;

--  2. LEAD 用于统计窗口内往下第n行值
select
    *,
    -- 获取createtime列向上第2个值, 如果没有就用 夯哥 作为默认值进行填充.
    -- 参1: 要操作的列,   参2: 向下第几个.  参3: 默认值.
    lead(createtime, 2, '夯哥') over(partition by cookieid order by createtime) lead_time
from
    website_url_info;

--  3. FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
select
    *,
    first_value(createtime) over(partition by cookieid order by createtime) first_time
from
    website_url_info;

--  4. LAST_VALUE  取分组内排序后，截止到当前行，最后一个值
select
    *,
    last_value(createtime) over(partition by cookieid order by createtime) last_time
from
    website_url_info;