标签：总结 pv 函数 cookieid json SQL createtime select

HiveSQL篇

SerDe相关语法

SerDe相关语法(多用,记清楚):   #SerDe主要用于序列化和反序列化的
	在Hive的建表语句中，和SerDe相关的语法为:
		row format delimited/serde
		delimited:	//使用默认的LazySimpleSerDe类来处理数据, 这个是我们用的最多的.
		
			fields terminated by char				//字段分隔符
			
			collection items terminated by char		//列表分隔符
			
			map keys terminated by char				//映射分隔符
			
			lines terminated by char				//行分隔符,用的少
			
		serde:		//如果要用其它的SerDe类, 甚至是用户自定义的SerDe类规则, 则使用这种方式.
		
			with serdeproperties	//允许用户自定义SerDe规则, 目前用不到, 后期NoSQL阶段会用到.

例子:

		create table t_hot_hero_skin_price(
			id int,
			name string,
			win_rate int,
			skin_price map<string,int>
		)
		row format delimited fields terminated by ',' 
		collection items terminated by '-' 
		map keys terminated by ':';

HQL建表规则详解

1661688015562

详见笔记 11.HiveSQL(一)_DDL

案例参考:

1661739698420

HQL查询规则

传统(MySQL)的SQL语句 和 HQL语句关于 单表查询语句格式对比如下:
	传统(MySQL)的SQL语句:
		select distinct/all 列1, 列2... from 表名
		where 组前筛选
		group by 分组字段1, 分组字段2...
		having 组后筛选
		order by 排序的字段1 asc/desc, 排序的字段2 asc/desc          -- 全局排序.
		limit 起始索引, 每页的数据条数;

   HQL语句:
		[with CTE公共表达式]   Common Table Expression公共表达式的意思.  例如:with 名 as ()
		select distinct/all 列1, 列2... from 表名
		where 组前筛选
		group by 分组字段1, 分组字段2...
		having 组后筛选
		order by 排序的字段1 asc/desc, 排序的字段2 asc/desc           -- 全局排序.
		cluster by 字段 / distribute by 字段 sort by 字段  --桶内排序           
		-- 分桶查询  cluster by =  distribute by +  sort by   
																									  //cluster by表示根据A字段分根据A排序,distribute by可以做到根据A字段分根据B字段排序
		limit 起始索引, 每页的数据条数;             distribute  分发，分配
		
		总结:HQL比传统SQL多了:公共表达式(with CTE公共表达式)和分桶(cluster by )
		
		:分桶的本质:分文件,而在MR中,分文件的本质是reduces的个数,每个reduces输出一个文件,默认reduces是-1，也就是由我们语句(计算量)来自动划分.所以,分桶和reduces密不可分.
		
		另外,对于group by,不写它,例如:select * from student;
		则等于:select * from student group by ();
		即所有数据分一组,所以,为什么不分组也能直接使用聚合函数,如:select *,sum(id) from student;
		因为,默认时,已经分组,即所有的都是一组!

HQL插入

单次插入

	insert into table student_insert1
	       select num,name from student;
	       
	//格式:insert into table 表名 select 字段... from 数据源的表名   
	//相当于吧select列出的字段数据一个个往上面给,所以字段顺序和个数一定要一致!

多重插入(一插多)

2.重点重点 从student表中, 插入数据到 student_insert2, student_insert3 两张表中.

		方式: //多重插入, 即: 一次扫描, 多次插入
			from student
			insert into student_insert2
			select name     //多个insert之间没有逗号,加逗号相当于逗号后面有字段名没写,肯定报格式错
			insert into student_insert3
			select num, sex;
			
			//格式相当于单次插入中的from 数据源表提到前面

overwrite

2.导出查询结果到HDFS指定目录下
insert overwrite directory '/tmp/hive_export/e1' select num,name,age from student 
limit 2;   //默认导出数据字段之间的分隔符是\001

3. 导出时指定分隔符和文件存储格式
	-- 格式: insert overwrite directory 'hdfs路径' select * from 表名;

	insert overwrite directory '/tmp/hive_export/e2' 
	row format delimited fields terminated by ','
	stored as orc			//这里表示设置存储方式为列式存储(二进制),    默认: textfile(行式存储)
	select * from student;

4.覆盖写入表中
	insert overwrite 表名 select * from 源数据表名;
	
注意:		   1. 目的地目录(aa)可以不存在, 会自动创建.    
			2. 导出后, HDFS文件的分隔符是 默认分隔符'\001'
			3. 我们可以在导出的时候, 指定: 导出文件的 字段分隔符.      
			4. 若文件存在,则操作是:覆盖.
			5.若文件已经分区,则只覆盖对应的分区.

联合查询

即 union distinct 和 union all

它们的原理分别是去重合并和直接合并,底层是使用MR(以后在回来补)

hive1.2.0之后默认为union distinct ,即默认去重合并

例子:

5.重点 如果要将order by，sort by，cluster by，distribute by或limit应用于整个select, 请将子句放在整个select查询的最后
	select * from student_local
	union all
	select * from student_hdfs limit 2;  //对整个数据取前两条

6.重点 如果要将order by，sort by，cluster by，distribute by或limit应用于单个select, 请将子句放在括住select的括号内
	select * from (select * from student_local limit 2) t1
	union all
	select * from (select * from student_hdfs limit 3) t2;
	//第一个数据取2条,第二个取3条,一共5条数据

CTE表达式:with 名 as ()

1.重点 select 语句中的CTE功能.
	with t1 as (select * from student where sno in (95001, 95002, 95003, 95004, 95005, 95006))
	select * from t1;

	//重点:with t1 as(查询到的数据)  字面意思为用t1 作为 ()里的东西,也就是()里的数据被t1这个临时表存储下来了
	//即: 相当于创建一张临时表, 该表的数据可以被整个SQL语句中使用, 且可以重复使用.
	
2.注意哦 from格式(相当于前面的多重插入的那种格式,把from调到前面去了)
	with t1 as (select * from student where sno in (95001, 95002, 95003, 95004, 95005))
	from t1
	select *;

3.重点 CTE chars, 链式   (相当于在t1的基础上再做过滤时,还要用临时表存储,此时with可省略)
	with t1 as ( select * from student),    -- 使用CTE链式编程的时候, 记得要写 逗号.
		   t2 as ( select sno, sname, sage from t1)
	select * from t2 limit 0, 5;
	
	-- 上述写法等价于, 子查询写法之:
	select * from (select sno, sname, sage from (select * from student limit 0, 5) t2) t1;
	
4. union 联合查询.
	with t1 as (select * from student where sno = 95001),
		 t2 as (select * from student where sno = 95002)
	select * from t1
	union
	select * from t2;

5. ctas(creat table as select) 创建一张表来自于后面的查询语句
	//表的字段个数 名字 顺序和数据行数都取决于查询
	create table tmp1 as
	with t1 as (select * from student where sno in (95001, 95002, 95003, 95004, 95005, 95006))
	select * from t1;

join查询

1.(内)连接查询

4.1 第1种: (内)连接查询, 查询的是: 两张表的交集. inner join

			select e1.*, e2.* from employee e1
			inner join
			employee_address e2 on e1.id = e2.id;
			
		//显式内连接,上述语句可以省略inner,如下: 
		select e1.*, e2.* from employee e1 join employee_address e2 on e1.id = e2.id;

		//隐式内连接,上述语句可以改为如下:   (直接逗号隔开两个表,然后用where 感觉这个格式就是懒得写inner哈哈哈)
		select e1.*, e2.* from employee e1, employee_address e2 where e1.id = e2.id;

2.左外连接

4.2 第2种: 左外连接:  左表的全集 + 表的交集.

    select e1.*, e2.* from employee e1
    left outer join 	//outer可以省略
    employee_address e2 on e1.id = e2.id;

3.右外连接

4.3 第3种: 右外连接:  右表的全集 + 表的交集.

    select e1.*, e2.* from employee e1
    right outer join 	// outer可以省略
    employee_address e2 on e1.id = e2.id;

满外连接查询

4.4 第4种: 满外连接查询全外连接 full join == full outer join

	//full outer join 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
	//full outer join 关键字结合了 left join 和 right join 的结果。
	//简单的说,满外连接的结果就是左外连接的结果加上右外连接的结果中 左外连接没有的部分.
	
	select e1.*, e2.* from employee e1
	full outer join 	//outer可以省略
	employee_address e2 on e1.id = e2.id;

5.左半连接

4.5 第5种: 左半连接, left semi join
	//相当于 inner join, 但是只返回左表全部数据，右表数据全部抛弃, 只不过效率高一些
	应用场景: 
		统计每天的新用户或者老用户.
	select * from employee e left semi join employee_address e_addr on e.id = e_addr.id;

6.交叉查询,即笛卡尔积

4.6 第6种: cross join 交叉查询, 相当于查询表的笛卡尔积, 无意义, 一般不用.
	select * from employee e cross join employee_address e_addr;

重点在前三种.

内置运算符及常用函数(当表查,有空多看看)

Apache Hive-内置运算符(多看看)
官方链接：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

一些Hive命令:
show functions; //显示所有的函数和运算符
describe function +; //查看运算符或者函数的使用说明
describe function extended +; //使用extended 可以查看更加详细的使用说明

具体分类:

1. 关系运算符
2. 算术运算符 
3. 逻辑运算符

参考代码:
-- 1、创建表dual
create table dual(id string);

-- 2、加载一个文件dual.txt到dual表中
-- dual.txt只有一行内容：内容为一个空格

load data local inpath '/root/hivedata/dual.txt' into table dual;

-- 3、在select查询语句中使用dual表完成运算符、函数功能测试
select 1+1 from dual;
select 1+1;

//----------------Hive中关系运算符--------------------------
// is null空值判断
select 1 from dual where 'itcast' is null;

// is not null 非空值判断
select 1 from dual where 'itcast' is not null;

// like比较： _表示任意单个字符 %表示任意数量字符
// 否定比较： NOT A like B
select 1 from dual where 'itcast' like 'it_';
select 1 from dual where 'itcast' like 'it%';
select 1 from dual where  'itcast' not like 'hadoo_';
select 1 from dual where  not 'itcast' like 'hadoo_';

// rlike：确定字符串是否匹配正则表达式，是regexp_like()的同义词。
select 1 from dual where 'itcast' rlike '^i.*t$';
select 1 from dual where '123456' rlike '^\\d+$';  // 判断是否全为数字
select 1 from dual where '123456aa' rlike '^\\d+$';

// regexp：功能与rlike相同 用于判断字符串是否匹配正则表达式
//手机号是否合法.  规则: 1.必须是11位.  2.必须是纯数字.  3.第1个数字必须是1.  4.第二位数字可以是 3 ~ 9
select '13112345678' regexp '^1[3-9]\\d{9}$';

// Hive中算术运算符----------------
// 取整操作: div  给出将A除以B所得的整数部分。例如17 div 3得出5。
select 17 div 3;

// 取余操作: %  也叫做取模mod  A除以B所得的余数部分
select 17 % 3;

// 位与操作: &  A和B按位进行与操作的结果。 与表示两个都为1则结果为1
select 4 & 8 from dual;  // 4转换二进制：0100 8转换二进制：1000
select 6 & 4 from dual;  // 4转换二进制：0100 6转换二进制：0110

// 位或操作: |  A和B按位进行或操作的结果  或表示有一个为1则结果为1
select 4 | 8 from dual;
select 6 | 4 from dual;

// 位异或操作: ^ A和B按位进行异或操作的结果 异或表示两者的值不同,则结果为1
select 4 ^ 8 from dual;
select 6 ^ 4 from dual;

	// 3、Hive逻辑运算符
	// 与操作: A AND B   如果A和B均为TRUE，则为TRUE，否则为FALSE。如果A或B为NULL，则为NULL。
	select 1 from dual where 3>1 and 2>1;

	// 或操作: A OR B   如果A或B或两者均为TRUE，则为TRUE，否则为FALSE。
	select 1 from dual where 3>1 or 2!=2;

	// 非操作: NOT A 、!A   如果A为FALSE，则为TRUE；如果A为NULL，则为NULL。否则为FALSE。
	select 1 from dual where not 2>1;
	select 1 from dual where !2=1;

	// 在:A IN (val1, val2, ...)  如果A等于任何值，则为TRUE。
	select 1 from dual where 11  in(11,22,33);

	// 不在:A NOT IN (val1, val2, ...) 如果A不等于任何值，则为TRUE
	select 1 from dual where 11 not in(22,33,44);

扩展:
	/*
	二进制 和 十进制快速转换法, 8421码表:
			 二进制数据: 0   0   0   0   0   0   0   0
		对应的十进制数据: 128 64  32  16  8   4   2   1

	二进制转十进制案例, 求: 101011的十进制, 其实相当于求 0010 1011的十进制 = 32 + 8 + 2 + 1 = 43
	十进制转二进制案例, 求: 56的二进制, 56 = 32 + 16 + 8 = 0011 1000

	细节: 第1位是符号位, 0:正数, 1:负数,  其它的是数值位.

	0100        4的二进制
	0110        6的二进制
& -----------------
	0100        4
 */

Apache Hive-常用的内置函数详解
字符串函数:
// 字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
select substr("angelababy",2,2);

//正则表达式替换函数：regexp_replace(str, regexp, rep)
select regexp_replace('100-200', '(\\d+)', 'num'); --正则分组

//URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST');

//分割字符串函数: split(str, regex)
select split('apache hive', '\\s+');	//匹配一个或者多个空白符

//json解析函数：get_json_object(json_txt, path)
//$表示json对象
select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"}, {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '$.[1].website');

日期函数
// 获取当前日期: current_date
select current_date();
// 获取当前时间戳: current_timestamp
// 同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();
// 获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
// 日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
// UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1620723323);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
// 日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
// 日期增加函数: date_add
select date_add('2012-02-28',10);
// 日期减少函数: date_sub
select date_sub('2012-01-1',10);

数字函数:
// 取整函数: round 返回double类型的整数值部分（遵循四舍五入）
select round(3.1415926);
// 指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
// 向下取整函数: floor
select floor(3.1415926);
select floor(-3.1415926);
// 向上取整函数: ceil
select ceil(3.1415926);
select ceil(-3.1415926);
// 取随机数函数: rand 每次执行都不一样返回一个0到1范围内的随机数
select rand();
// 指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(5);

条件函数:
// if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

// 空判断函数: isnull( a )
select isnull("allen");
select isnull(null);

// 非空判断函数: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);

// 空值转换函数: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");

// 非空查找函数: COALESCE(T v1, T v2, ...)
// 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null,null,33);
select COALESCE(null,null,null);

// 条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

Type Conversion Functions 类型转换函数:
前置知识: Hive中支持类型的隐式转换有限制自动转换不保证成功就显示null
//cast显示类型转换函数

--任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);
+-------+
|  _c0  |
+-------+
| NULL  |
+-------+

Data Masking Functions 数据脱敏函数
//mask脱敏掩码处理, 数据脱敏：让敏感数据不敏感, 13455667788 --->134****7788
// mask, 将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); // 自定义替换的字母

// mask_first_n(string str[, int n]
// 对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);

// mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);

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

// mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);

// mask_hash(string|char|varchar str)
// 返回字符串的hash编码。
select mask_hash("abc123DEF");

Misc. Functions 其他杂项函数、加密函数
// 如果你要调用的java方法所在的jar包不是hive自带的可以使用add jar添加进来
// hive调用java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22);

// 反射函数: reflect(class, method[, arg1[, arg2..]])
select reflect("java.lang.Math","max",11,22);

// 取哈希值函数:hash
select hash("allen");

// current_user()、logged_in_user()、current_database()、version()

// SHA-1加密: sha1(string/binary)
select sha1("allen");

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

// crc32加密:
select crc32("allen");

// MD5加密: md5(string/binary)
select md5("allen");

Apache Hive-函数的分类(UDF, UDAF, UDTF)

关于Hive的函数的分类.

Hive中的函数主要分为: 内置函数和自定义函数, 但是整个是早期的分发, 为了更好的划分用户自定义函数, Hive又把函数分为三大类, 分别如下:
内置函数:
用户自定义函数:
UDF: 普通函数, 一进一出. 即: 输入1行, 输出1行.
select split('aa,bb,cc,dd', ','); -- 按照,切割, ["aa","bb","cc","dd"]

		UDAF:   聚合函数, 多进一出.     即: 输入多行, 输出1行.
			select count(1) from student;

		UDTF:   表生成函数, 一进多出.   即: 输入1行, 输出多行.
			select explode(array('aa', 'bb', 'cc'));    -- explode()炸裂函数.

后来Hive发现用 UDF, UDAF, UDTF来划分函数非常方便, 于是有了 函数扩大化的概念, 即: 本来UDF, UDAF, UDTF是用来划分 用户自定义函数的,
现在 UDF, UDAF, UDTF 是用来划分Hive中所有函数的, 包括内置函数 和 用户自定义函数.

UDTF函数:explode函数

概述:
		explode属于UDTF函数，表生成函数，输入一行数据输出多行数据。
	功能:
		//explode接收map array类型的参数 ,用于行 转--> 列的.
		//它只能处理: Array, map类型的数据.

	explode(array(11,22,33))         
		11
		22
		33																		
	select explode(`array`(11,22,33,44,55));
	select explode(`map`("id",10086,"name","allen","age",18));	  
	//一般函数名,关键字会用``标记.

如果数据不是map或者array 如何使用explode函数呢？
//想法设法使用split subsrt regex_replace等函数组合使用把数据变成array或者map.

	//参考代码
	select explode(split(champion_year, '|')) from the_nba_championship;
	//split()会将函数切成array类型返回

UDTF函数使用限制、lateral View侧视图功能

UDTF函数生成的结果可以当成一张虚拟的表，但是无法和原始表进行组合查询,简单的说就是炸裂函数炸裂出来的相当于新表,要么用join联合查,要么侧视图,很显然,为什么有侧视图?它比join方便呀!

功能:
	把UDTF函数生成的结果和原始表进行关联，便于用户在select时间组合查询, lateral view是UDTf的好基友好搭档，实际中经常配合使用。

语法:
	1. lateral view侧视图基本语法如下
		select …… from tabelA lateral view UDTF(xxx) 表别名 as 字段别名;

	2. 针对上述NBA冠军球队年份排名案例，使用explode函数+lateral view侧视图，可以完美解决
		select a.team_name ,b.year from the_nba_championship a 
		lateral view explode(champion_year) b as year;

	3. 根据年份倒序排序
		select a.team_name ,b.year from the_nba_championship a 
		lateral view explode(champion_year) b as year
		order by b.year desc;

	4. 统计每个球队获取总冠军的次数 并且根据倒序排序
		select a.team_name ,count(*) as nums from the_nba_championship a 
		lateral view explode(champion_year) b as year
		group by a.team_name
		order by nums desc;

通常来说,侧视图结合炸裂函数,相当于加一列哈哈哈

行列转换

数据收集函数
collect_set //把多行数据收集为一行返回set集合去重无序
collect_list //把多行数据收集为一行返回list集合不去重有序
1. 字符串拼接函数
  concat //直接拼接字符串,采用默认拼接符(即啥都没有,直接拼),拼接什么类型的数据都可以
  concat_ws //指定分隔符拼接,第1个值是分隔符, 之后的都是数据, 必须是string类型或者是
array

select concat("it","cast","And","heima");
select concat("it","cast","And",null);

select concat_ws("-","itcast","And","heima");
select concat_ws("-","itcast","And",null);
多行转单列

3. 案例:
	//原表
	+----------------+----------------+----------------+--+
	| row2col2.col1  | row2col2.col2  | row2col2.col3  |
	+----------------+----------------+----------------+--+
	| a              | b              | 1              |
	| a              | b              | 2              |
	| a              | b              | 3              |
	| c              | d              | 4              |
	| c              | d              | 5              |
	| c              | d              | 6              |
	+----------------+----------------+----------------+--+

	//目标表
	+-------+-------+--------+--+
	| col1  | col2  |  col3  |
	+-------+-------+--------+--+
	| a     | b     | 1-2-3  |
	| c     | d     | 4-5-6  |
	+-------+-------+--------+--+

4. 具体实现步骤.
	4.1 建表
		create table row2col2(
			 col1 string,
			 col2 string,
			 col3 int
		)row format delimited fields terminated by '\t';

	4.2 加载数据到表中
		load data local inpath '/root/hivedata/r2c2.txt' into table row2col2;
		select * from row2col2;

	4.3 最终SQL实现    #cast(字段 as 类型)  转化数据类型
		select
			col1,
			col2,
			concat_ws('-', collect_list(cast(col3 as string))) as col3
		from
			row2col2
		group by
			col1, col2;

单列转多行

需求:
	1. 原表
		+-------+-------+--------+--+
		| col1  | col2  |  col3  |
		+-------+-------+--------+--+
		| a     | b     | 1,2,3  |
		| c     | d     | 4,5,6  |
		+-------+-------+--------+--+

	2. 目标表
		+----------------+----------------+----------------+--+
		| row2col2.col1  | row2col2.col2  | row2col2.col3  |
		+----------------+----------------+----------------+--+
		| a              | b              | 1              |
		| a              | b              | 2              |
		| a              | b              | 3              |
		| c              | d              | 4              |
		| c              | d              | 5              |
		| c              | d              | 6              |
		+----------------+----------------+----------------+--+

具体实现步骤:
	1. 创建表
		create table col2row2(
			col1 string,
			col2 string,
			col3 string
		)row format delimited fields terminated by '\t';

	2. 加载数据
		load data local inpath '/root/hivedata/c2r2.txt' into table col2row2;
		//select * from col2row2;
		//select explode(split(col3,',')) from col2row2;
	
	3. SQL最终实现  
	    -- split(字段, 分隔符)拆出来的时候返回一个array
		select
			col1,
			col2,
			lv.col3 as col3
		from
			col2row2
		lateral view explode(split(col3, ',')) lv as col3;  -- 先表名再字段名

json格式数据处理

解释:
在hive中，没有json类的存在，一般使用 string类型来修饰，叫做json字符串，简称 json串。

在hive中，处理json数据的两种方式	//hive内置了两个用于解析json的函数
	json_tuple			//是UDTF 表生成函数  输入一行，输出多行  一次提取读个值  可以单独使用 也可以配合lateral view侧视图使用
	get_json_object		//是UDF普通函数，输入一行 输出一行 一次只能提取一个值 多次提取多次使用
	//使用[JsonSerDe] 类解析，在加载json数据到表中的时候完成解析动作

案例:
	1. 创建表
		create table tb_json_test1 (
			json string
		);

	2. 加载数据
		load data local inpath '/root/hivedata/device.json' into table tb_json_test1;
		//select * from tb_json_test1;

	3. get_json_object UDF函数 最大弊端是一次只能解析提取一个字段
		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;

	4. son_tuple 这是一个UDTF函数 可以一次解析提取多个字段
		//单独使用 解析所有字段
		select
			json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
		from tb_json_test1;

	5. 搭配侧视图使用
		select
			json,device,deviceType,signal,stime
		from tb_json_test1
		lateral view json_tuple(json,"device","deviceType","signal","time") b as device,deviceType,signal,stime;

	6. 方式2： 使用JsonSerDe类在建表的时候解析数据
		//建表的时候直接使用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 local inpath '/root/hivedata/device.json' into table tb_json_test2;

		select * from tb_json_test2;	//查询数据.

窗口函数

OVER开窗函数必须与聚合函数或排序函数一起使用

位置:在 select * from student 中* ,也就是字段的位置.

	窗口函数(下面的背下来):
		概述:
		   特指的是over(), 它一般是用来描述范围的, 即: 局部操作, 从哪里开始, 到哪里结束.
		   如果不用窗口函数, 则默认操作的是表中所有的数据.
		格式:
			可以和窗口函数结合使用的函数 over(partition by 分区字段 order by 排序的列 asc/desc rows between ** and **)
		细节:
			1. 窗口函数一般可以结合 聚合函数 或者 排序函数, 以及其它函数一起使用.
			2. 这里的聚合和排序分别指的是:
				聚合函数: count(), sum()/求和, max(), min(), avg()//平均值
				排序函数: row_number(), rank(), dense_rank(), ntile(几分之几)
						  1234         1224       1223
				其它函数: lag(), lead(), first_value(), last_value()
			3. 如果不写 partition by, 则: 全局聚合, 如果写了, 则: 局部聚合(类似分组聚合)
			4. 如果不写 order by, 则: 统计组内所有的数据, 如果写了, 则: 累计统计, 即: 统计从第一行截止到当前行的总数.
			5. rows后边的between and 的值可以是如下的这些格式:
				preceding：往前
				following：往后
				current row：当前行
				unbounded：起点
				unbounded preceding 表示从前面的起点  第一行
				unbounded following：表示到后面的终点  最后一行
			6. ntile(数字,表示分成几份)  采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2

初体验案例:
	-- 需求0: 准备数据.
	create database day11;
	use day11;
	-- 1. 创建数据表.
	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,
	   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 ',';

	-- 2. 上传源文件到目标表路径下.
	-- load data local inpath 'Linux的路径' into table 表名 partition(分区字段1, 分区字段2=值);

	-- 3. 查看表数据(源数据)
	explain select * from employee;
	select * from website_pv_info;
	select * from website_url_info;

	-- 需求1: 窗口函数 + 聚合函数一起使用, 窗口函数初体验.
	-- 需求: 统计所有员工的工资, 并将该数值显示到每个员工信息的后边.
	-- 写法1: sum()聚合函数 + group by, 只是看看效果, 不是我们要的数据.
	select name, sum(salary) totcal_salary from employee group by name;

	-- 写法2: sum()聚合函数 + over()窗口函数
	select *, sum(salary) over() totcal_salary from employee;   -- 窗口函数相当于给原表"新增一列", 至于该列的内容是什么, 取决于 窗口函数前边写的是啥.

窗口+聚合 案例:
		1. 求出每个用户总pv数  sum+group by普通常规聚合操作
			select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;
			+-----------+-----------+
			| cookieid  | total_pv  |
			+-----------+-----------+
			| cookie1   | 26        |
			| cookie2   | 35        |
			+-----------+-----------+

		2. sum+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
			//sum(...) over( )对表所有行求和
			//sum(...) over( order by ... ) 连续累积求和
			//sum(...) over( partition by... ) 同组内所有行求和
			//sum(...) over( partition by... order by ... ) 在每个分组内，连续累积求和

			2.1 需求：求出网站总的pv数 所有用户所有访问加起来
				//sum(...) over( )对表所有行求和
				select cookieid,createtime,pv,
					   sum(pv) over() as total_pv  
				from website_pv_info;

			2.2 需求：求出每个用户总pv数
				//sum(...) over( partition by... )，同组内所行求和
				select cookieid,createtime,pv,
					   sum(pv) over(partition by cookieid) as total_pv
				from website_pv_info;

			2.3 需求：求出每个用户截止到当天，累积的总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;
				+-----------+-------------+-----+-------------------+
				| cookieid  | createtime  | pv  | current_total_pv  |
				+-----------+-------------+-----+-------------------+
				| cookie1   | 2018-04-10  | 1   | 1                 |
				| cookie1   | 2018-04-11  | 5   | 6                 |
				| cookie1   | 2018-04-12  | 7   | 13                |
				| cookie1   | 2018-04-13  | 3   | 16                |
				| cookie1   | 2018-04-14  | 2   | 18                |
				| cookie1   | 2018-04-15  | 4   | 22                |
				| cookie1   | 2018-04-16  | 4   | 26                |
				| cookie2   | 2018-04-10  | 2   | 2                 |
				| cookie2   | 2018-04-11  | 3   | 5                 |
				| cookie2   | 2018-04-12  | 5   | 10                |
				| cookie2   | 2018-04-13  | 6   | 16                |
				| cookie2   | 2018-04-14  | 3   | 19                |
				| cookie2   | 2018-04-15  | 9   | 28                |
				| cookie2   | 2018-04-16  | 7   | 35                |
				+-----------+-------------+-----+-------------------+

窗口+聚合 案例续:
	1. 默认从第一行到当前行
		select cookieid,createtime,pv,
			   sum(pv) over(partition by cookieid order by createtime) as pv1  
		from website_pv_info;

	2. 第一行到当前行 等效于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. 向前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;

	4. 向前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;

	5. 当前行至最后一行
		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;

	6. 第一行到最后一行 也就是分组内的所有行
		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;

窗口+排序 案例:
功能：
	主要对数据分组排序之后，组内顺序标号。
核心函数：
	row_number, rank、dense_rank 
	

案例:
	SELECT
		cookieid,
		createtime,
		pv,
		RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
		DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
		ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
	FROM website_pv_info;

需求：找出每个用户访问pv最多的Top3 重复并列的不考虑
	SELECT * from
	(SELECT
		cookieid,
		createtime,
		pv,
		ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS seq
	FROM website_pv_info) tmp where tmp.seq <4;

ntile函数
	功能：
		将分组排序之后的数据分成指定的若干个部分（若干个桶）
		
	规则：
		尽量平均分配 ，优先满足最小的桶，彼此最多不相差1个。
	
	案例:
		//把每个分组内的数据分为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(), lead(), first_value(), last_value()

//1. LAG 用于统计窗口内往上第n行值
SELECT cookieid,
	   createtime,
	   url,
	   ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
	   LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
	   LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM website_url_info;

//2. LEAD 用于统计窗口内往下第n行值
SELECT cookieid,
	   createtime,
	   url,
	   ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
	   LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
	   LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
FROM website_url_info;

//3. FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
SELECT cookieid,
	   createtime,
	   url,
	   ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
	   FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM website_url_info;

//4. LAST_VALUE  取分组内排序后，截止到当前行，最后一个值
SELECT cookieid,
	   createtime,
	   url,
	   ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
	   LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM website_url_info;

SQL篇

1.SQL语言分类

1. 数据定义语言

DDL: (Data Definition Language)定义 数据库,数据表,数据字段  

常见命令:  create  drop  alter

2. 数据操作语言

DML: (Data Manipulation Language) 操作数据表中的记录

常见命令:  insert  delete  update

3. 数据查询语言

DQL: (Data Query Language) 查询数据表中记录

常见命令:  select  from  where

4. 数据控制语言

DCL: (Data Control Language) 定义数据库的权限,创建用户等(了解)

2.SQL基本语法

传统(MySQL)的SQL语句:
		select distinct/all 列1, 列2... from 表名
		where 组前筛选
		group by 分组字段1, 分组字段2...
		having 组后筛选
		order by 排序的字段1 asc/desc, 排序的字段2 asc/desc          -- 全局排序.
		limit 起始索引, 每页的数据条数;

其实SQL篇的笔记写的特别好,没必要在这里赘述了,忘了回去翻翻就好.

分支函数

case语句:

1.简单case语句

语法:

case  <col_name>
   when <value1> then <result1>
   when <value2> then <result2>
   ...
   else <result>
end

例如:

select  day
when  1  then  "星期一"
when  2  then  "星期二"
when  3  then  "星期三"
when  4  then  "星期四"
when  5  then  "星期五"
when  6  then  "星期六"
else  "星期天"
end
--只要满足一条when,直接结束,所有when都不满足,则执行else

2.case搜索函数,即case后参数可省略.

语法:

case
    when <条件1> then <结果1>
    when <条件2> then <结果2>
    ...
    else <结果>
end

例：判断一个人的成绩是属于哪个等级,并输出该
注：及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

case 
when  score<60  then"不及格"
when  score <=70  then "及格"
when  score<=80   then "中等"
when  score<=90   then "优良"
else   "优秀"
end

SQL编写思路(最重要)

1.对于复杂数据来源,我们主要分两步:

第0步:确定写SQL的目的,你要干嘛,你要查什么东西,你要算哪些指标?

第一步: 准备数据

with t1 as (

//在这里面将我们的select , where , group by , having , order by 所用到的字段都取到数据.

)

第二步: 根据需求,通过维度,使用上面的数据计算出需要的指标

select

各种指标

from t1...

2.对于给定指标如何分析?

1.分析分组字段有哪些(即根据哪些维度),来源于哪张表

2.需要的字段有哪些? 即数据来源于哪个字段,哪个表

3.普通SQL写法:三步走

1662364588603

通常来说,三步走能解决很多SQL题,但还是要具体问题具体分析.

SQL注意事项

1.group by 后的字段, 要考虑在select 后面要补上去,不然可能报错,原因是可能行数不对,具体要自己想想.

标签：总结,pv,函数,cookieid,json,SQL,createtime,select
From： https://www.cnblogs.com/nanguyhz/p/16837522.html

SQL总结