hive sum函数 hive 函数大全

转载

mob6454cc6553fc 2023-05-22 10:05:31

文章标签 hive sql 数据分析数据仓库 hadoop 文章分类 Hive 大数据

HIVE SQL函数实例讲解

一、SQL函数汇总

1.数学函数
2.日期函数
3.字符函数
4.聚合函数
5.取数常用函数

二、表操作相关

1.建表
2.表删除
3.表结构查询
4.视图与物化视图
5.索引

三、注意事项
四、练习

HIVE是一个建立在Hadoop上的数据仓库平台，它提供了一系列的工具，可进行数据提取、转化、加载(ETL)，这是一种可以存储、查询、和分析存储在Hodoop中的大规模数据的机制。HIVE定义了简单的类SQL查询语言即HQL，可以将SQL语句转换为MapReduce任务进行运行。

一、SQL函数汇总

1.数学函数

1. ceil()： 向上取最接近的整数；

select ceil(3.1415)
--返回结果：4

2. floor()： 向下取最近的整数；

select floor(3.1415)
--返回结果：3

3. round()： 四舍五入，默认按取整四舍五入，即保留一位小数的浮点型数据，也可指定需保留的小数位；

select round(3.1415)
--返回结果：3.0
select round(3.1415, 3)
--返回结果：3.142

4. abs()： 取绝对值；

select abs( - 3.1415)
--返回结果：3.1415

5. negative()： 取相反数；

select negative(3.1415)
--返回结果：-3.1415

6. sqrt()： 取平方根；

select sqrt(4)
--返回结果：2.0

7. log()： 取对数；

select log(2, 16)
--返回结果(取以2为底16的对数)：4.0

8. pow()、power()： 计算次方；

select pow(2, 4)
--返回结果(计算2的4次方)：16.0
select power(2, 4)
--返回结果(计算2的4次方)：16.0

9. bin()： 进制转换，十进制转换为二进制；

select bin(100)
--返回结果：1100100

10. hex()： 进制转换，十进制转换为十六进制；

select hex(100)
--返回结果：64

11. conv()： 进制转换，将某进制转换为其他进制；

select conv(1100100, 2, 16)
--返回结果(将二进制的1100100转换为十六进制)：64

12. pi()： 圆周率常数；

select pi()
--返回结果：3.141592653589793

13. degrees()： 弧度化为角度；.

select degrees(pi())
--返回结果(圆周率Pi弧度对应的角度为180度)：180.0

14. radians()： 角度化为弧度；

select radians(180)
--返回结果(180度角对应的弧度为圆周率Pi)：3.141592653589793

15. sin()、cos()、tan()、asin()、acos()、atan()： 分别取正弦、余弦、正切、反正弦、反余弦、反正切值，注意参数值为弧度，非角度值；

select sin(radians(30))
--返回结果：0.49999999999999994
select cos(radians(30))
--返回结果：0.8660254037844387
select tan(radians(30))
--返回结果：0.5773502691896257
select asin(radians(30))
--返回结果：0.5510695830994463
select acos(radians(30))
--返回结果：1.0197267436954502
select atan(radians(30))
--返回结果：0.48234790710102493

16. sign()： 正数返回1.0，负数返回-1.0，否则返回0.0；

select sign(3.1415)
--返回结果：1.0
select sign( - 3.1415)
--返回结果：-1.0
select sign(0)
--返回结果：0.0

17. percentile()、percentile_approx()： 分位数函数，取表某列的分位数，用法为percentile(col_name，p)，percentile_approx(col_name，p)，其中p取值为[0, 1]，如p=0、0.25、0.5、0.75、1等，分别代表列中的最小值、下四分位数、中位数、上四分位数、最大值等，也可传入数组即同时取出多个分位数；

--percentile函数入参的列值必须为int类型，对于非int类型的列可通过int()或cast()函数将其数据类型转换成int型；
select
	percentile(col_name, 0.5)
from
	table_name
--返回结果(结果为double类型)：表中col_name列的中位数

--percentile_approx函数入参的列值为数值类型即可，该函数还可以传入一个精度参数B，不传默认B为10000，即形式为percentile_approx(col_name,p,B),当所求分位数的列值去重数小于精度B时，返回的分位数为准确值，当去重数大于精度时，返回的分位数为按精度取舍得出的分位数，非准确值；
select
	percentile_approx(col_name, 0.5)
from
	table_name
--返回结果(结果为double类型)：表中col_name列的中位数

select
	percentile(col_name, array(0.25, 0.5, 0.75))
from
	table_name
--返回结果：以数组形式呈现表中col_name列的下四分位数、中位数、上四分位数，如[25.0,50.0,75.0]
select
	percentile_approx(col_name, array(0.25, 0.5, 0.75),9999)
from
	table_name
--返回结果：控制精度为9999，以数组形式呈现表中col_name列的下四分位数、中位数、上四分位数，如[25.0,50.0,75.0]

2.日期函数

1. to_date()： 返回时间中至日期的部分，格式为YYYY-MM-DD，类似用法还有to_month()、to_year()、to_quarter()，分别返回日期中至月份(YYYY-MM)、年份(YYYY)、季节(YYYYQ1、YYYYQ2等)部分的数据；

select to_date('2020-12-02 20:20:40')
--返回结果：2020-12-02
select to_date('2020-12-02')
--返回结果：2020-12-02

2. datediff()： 返回两个时间之间的天数差；

select datediff('2020-12-02 20:20:30', '2019-06-20 18:20:30')
--返回结果：531
select datediff('2020-12-02', '2019-06-20')
--返回结果：531
select datediff('2019-06-20 18:20:30', '2020-12-02 20:20:30')
--返回结果：-531

3. date_add()： 返回指定日期增加某些天数之后的日期，类似用法还有month_add()，返回指定月份差的日期；

select date_add('2020-12-02 20:20:30', 10)
--返回结果：2020-12-12
select date_add('2020-12-02 20:20:30', - 10)
--返回结果：2020-11-22
select date_add('2020-12-02', 10)
--返回结果：2020-12-12

4. date_sub()： 返回指定日期减少某些天数的日期；

select date_sub('2020-12-02 20:20:30', 10)
--返回结果：2020-11-22
select date_sub('2020-12-02 20:20:30', - 10)
--返回结果：2020-12-12
select date_sub('2020-12-02', 10)
--返回结果：2020-11-22

5. second()、minute()、hour()、day()、week()、month()、year()： 返回给定时间的秒、分、时、天、周、月、年相关信息；

select second('2020-12-02 20:20:30')
--返回结果：30
select day('2020-12-02 20:20:30')
--返回结果：2
select year('2020-12-02 20:20:30')
--返回结果：2020

6. weekofyear()： 返回指定时间在一年中的周数；

select weekofyear('2020-12-02 20:20:30')
--返回结果(2020-12-02属于2020年的第49周)：49
select weekofyear('2020-12-02')
--返回结果(2020-12-02属于2020年的第49周)：49

7. unix_timestamp()： 将时间转换成时间戳，若不传参返回当前时间戳；

select unix_timestamp('2020-12-02 20:20:20')
--返回结果：1606911620
select unix_timestamp()
--返回结果：1606904577

8. from_unixtime()： 将时间戳转换成日期形式；

select from_unixtime(1606911620)
--返回结果：2020-12-02 20:20:20
select from_unixtime(unix_timestamp())
--返回结果(获取当前时间)：2020-12-02 18:26:20

9. current_date()： 获取当前的日期，格式为YYYY-MM-DD；

select current_date()
--返回结果：2020-12-02

10. current_timestamp()： 获取当前系统时间，精确到毫秒；

select current_timestamp()
--返回结果：2020-12-02 18:29:19.161

3.字符函数

1. length()： 返回字符串长度；

select length('abcde')
--返回结果：5

2. concat()： 字符串合并函数，将多个字符串合并成一个字符串；

select concat('ab', 'cd', 'ef')
--返回结果：abcdef
select concat('ab', NULL, 'cd', 'ef')
--返回结果(NULL为未知，拼接后的结果仍然未知，因此结果为NULL)：NULL
select concat('ab', '', 'cd', 'ef')
--返回结果(空就是没有，等于没有拼接什么，因此ab,cd直接拼接，结果为abcdef)：abcdef
select concat('ab', ' ', 'cd', 'ef')
--返回结果(空格是实实在在的东西，不是空(没东西)和NULL(未知，即有没有或者有的话是什么，均不得知)，因此相当于ab,cd之间拼接了一个空格，结果为ab cdef)：ab cdef

3. concat_ws()： 字符串合并函数，concat_ws(char, str1, str2, str3, …)将多个字符串按照指定拼接符合并在一起；

select concat_ws('#', 'ab', 'cd', 'ef')
--返回结果：ab#cd#ef

4. reverse()： 字符串反转；

select reverse('abcdef')
--返回结果：fedcba

5. lpad()： 返回指定长度的字符串，给定字符串长度小于指定长度时，由指定字符从左侧填补，用法为lpad(string str1, int len, string str2)，当len小于等于str1的长度时直接返回str1对应长度的字符串，当len大于str1长度时，str1左侧填补str2至满足len的长度再返回填补后的字符串；

select lpad('abcdef', 10, 'q')
--返回结果：qqqqabcdef
select lpad('abcdef', 4, 'q')
--返回结果：abcd

6. rpad()： 返回指定长度的字符串，给定字符串长度小于指定长度时，由指定字符从右侧填补，用法为rpad(string str1, int len, string str2)，当len小于等于str1的长度时直接返回str1对应长度的字符串，当len大于str1长度时，str1右侧填补str2至满足len的长度再返回填补后的字符串；

select rpad('abcdef', 10, 'q')
--返回结果：abcdefqqqq
select rpad('abcdef', 4, 'q')
--返回结果：abcd

7. upper()： 大写转换，将字符串转换成大写；

select upper('abcdef')
--返回结果：ABCDEF
select upper('abcdef12#')
--返回结果：ABCDEF12#

8. lower()： 小写转换，将字符串转换成小写；

select lower('ABCDEF')
--返回结果：abcdef
select lower('ABCDEF12#')
--返回结果：abcdef12#

9. trim()： 删除字符串两端的空格，字符之间的空格保留；

select trim(' abc def ')
--返回结果：abc def

10. ltrim()： 删除字符串左边的空格，其他的空格保留；

select ltrim(' abc def ')
--返回结果：abc def
select length(ltrim(' abc def '))
--返回结果：8

11. rtrim()： 删除字符串右边的空格，其他的空格保留；

select rtrim(' abc def ')
--返回结果： abc def
select length(rtrim(' abc def '))
--返回结果：8

12. substr()、substring()： 字符串截取函数，二者用法一致，substr(string str , int start , int len)，将表达式从start位置开始（位置从1起，负值表示从末尾倒数），截取长度为len的字符串，不指定len则默认从指定位置截取到字符串末尾；

select substr('abcdefg',3)
--返回结果：cdefg
select substr('abcdefg',3,3)
--结果返回：cde
select substring('abcdefg', - 3)
--返回结果：efg
select substring('abcdefg', - 3,2)
--结果返回：ef

13. substring_index： 字符串截取函数，sub_string_index(string str1，string str2，index)，以某字符为分隔符，返回索引指定数量分隔符前后的部分，若索引为正数N，表示返回表达式中从左往右数第N个分隔符左边的全部内容，若为负数N，则表示返回表达式中从右往左数第N个分隔符右边的全部内容；

select substring_index('www.baidu.com', '.', 2)
--返回结果：www.baidu
select substring_index('www.baidu.com', '.', -2)
--结果返回：baidu.com
select substring_index(substring_index('www.baidu.com', '.', 2), '.', - 1)
--返回结果(可通过左右两边截取中间字符)：baidu

14. instr()： 索引函数，instr(string str1 , string str2)返回表达式中指定字符第一次出现的位置(位置号从1起)；

select instr('abcdef', 'c')
--返回结果：3

15. cast()： 类型转换，cast(express as type)将表达式转换成某种类型类型；

select cast(123 as int)
--返回结果：123

16. coalesce()： 非空查找函数，在多个表达式中，取第一个不为null的值，若均为null，则返回null；

select coalesce(NULL, 'abc', '123')
--返回结果：abc

17. nvl()： 空值替换函数，nvl(str1 , str2)，当str1为空时返回str2，str1不为空时返回str1；

select nvl(NULL, 'abc')
--返回结果：abc
select nvl('def', 'abc')
--返回结果：def

4.聚合函数

1. count()： 计算总行数，注意count()计算行数会忽略空行，可与distinct联用计算去重行数；

select count(1) from table_name
--计算表中总行数，空行会忽略
select count(*) from table_name
--计算表中总行数，空行会忽略
select count(col_name) from table_name
--计算表中col_name列的总行数，空行会忽略
select count(DISTINCT col_name) from table_name
--计算表中col_name列的去重总行数，空行会忽略

2. sum()： 求和函数，返回某列数据的总和，可与distinct联用，返回某列去重后数据的总和；

SELECT SUM(col_name) FROM table_name
SELECT SUM(DISTINCT col_name) FROM table_name

3. avg()： 平均值函数，返回某列中数据的平均值，可与distinct联用返回某列去重后数据的平均值；

SELECT AVG(col_name) FROM table_name
SELECT AVG(DISTINCT col_name) FROM table_name

4. max()、min()： 最大最小值函数，返回某列中最大、最小值；

SELECT MAX(col_name) FROM table_name
SELECT MIN(col_name) FROM table_name

5. variance()、var_pop()： 方差函数，返回某列数据的方差，可与distinct联用返回某列去重后数据的方差；

SELECT VARIANCE (col_name) FROM table_name
SELECT VAR_POP (DISTINCT col_name) FROM table_name

6. stddev()、stddev_pop()： 标准差函数，返回某列数据的标准差，可与distinct联用返回某列去重后数据的标准差；

SELECT STDDEV (col_name) FROM table_name
SELECT STDDEV_POP (DISTINCT col_name) FROM table_name

5.取数常用函数

1. split()： 切分，字符串切割函数，split(str，char)将字符串str按照字符char进行切分；

select split('abc_def', '_')
--返回结果：["abc","def"]
select split('abc_def', '_')[0]
--返回结果：abc
select split('abc_def', '_')[1]
--返回结果：def

2. explode()： 转置，行转列函数，接受array或map类型参数，将数组中每个元素生成一行或将map类型中的每个key-value生成一行，key和value各一列；

select explode(split('abc_def', '_'))
--返回结果：
--1	abc
--2	def

3. lateral view()： 侧视图，配合explode、split等函数一起使用，用于呈现将单行数据拆解成多行数据后的结果集，相当于生成一个虚拟表，该语句可多重使用。结合1、2、3举例：如下表table_name有col1、col2、col3三列数据

col1	col2	col3
1	[a,b]	1_2_3
2	[c,d]	4_5_6
1）按照col3的下划线进行行转列：

select
	col1,
	col2,
	col3,
	new_col
from
	table_name lateral VIEW explode(split(col3, '_')) new_table AS new_col

即生成新的虚拟表（new_table）其中包含新列（new_col），结果如下表：

col1	col2	col3	new_col
1	[a,b]	1_2_3	1
1	[a,b]	1_2_3	2
1	[a,b]	1_2_3	3
2	[c,d]	4_5_6	4
2	[c,d]	4_5_6	5
2	[c,d]	4_5_6	6
2）按照col2和col3两列进行行转列，col2数组直接用explode行转列，col3先按照下划线切分再行转列，两个lateral view连用：

select
	col1,
	col2,
	col3,
	new_col1,
	new_col2
from
	table_name lateral VIEW explode(col2) new_table1 AS new_col1 lateral view explode(split(col3, '_')) new_table2 as new_col2

即生成两张虚拟表（new_table1和new_table2）和两个新列（new_col1和new_col2），其最终展示的结果为原表和两张虚拟表的笛卡尔积，结果如下表：

col1	col2	col3	new_col1	new_col2
1	[a,b]	1_2_3	a	1
1	[a,b]	1_2_3	a	2
1	[a,b]	1_2_3	a	3
1	[a,b]	1_2_3	b	1
1	[a,b]	1_2_3	b	2
1	[a,b]	1_2_3	b	3
2	[c,d]	4_5_6	c	4
2	[c,d]	4_5_6	c	5
2	[c,d]	4_5_6	c	6
2	[c,d]	4_5_6	d	4
2	[c,d]	4_5_6	d	5
2	[c,d]	4_5_6	d	6

4. collect_all()、collect_list()、collect_set()： 列转行函数，需搭配group by使用，将分组中某列对应的字段值以数组的形式返回， collect_all()和collect_list()返回所有值不去重，collect_set()做去重处理；
举例： 如下表table_name，有col1、col2、col3三列：

col1	col2	col3
1	a	A
1	a	A
1	a	B
2	b	A
2	b	A
2	b	B

select
	col1,
	collect_all(col2) new_col--或者collect_list(col2)
from
	table_name
group by
	col1

返回结果如下表：

col1	new_col
1	[“a”,“a”,“a”]
2	[“b”,“b”,“b”]

select
	col1,
	collect_set(col2) new_col
from
	table_name
group by
	col1

返回结果如下表：

col1	new_col
1	[“a”]
2	[“b”]

select
	col1,
	col2,
	collect_all(col3) new_col--或者collect_set(col3)
from
	table_name
group by
	col1,
	col2

返回结果如下表：

col1	col2	new_col
1	a	[“A”,“A”,“B”]
2	b	[“A”,“A”,“B”]

select
	col1,
	col2,
	collect_set(col3) new_col
from
	table_name
group by
	col1,
	col2

返回结果如下表：

col1	col2	new_col
1	a	[“A”,“B”]
2	b	[“A”,“B”]

5.struct(value1 as key1, value2 as key2,…)： 给定值建立struct结构，即json形式数据结构，可对数据表某列应用构建json结构数据；
举例： select struct(‘a’ as key, ‘8’ as value) as json from table 结果如下：

json
{“key”:“a”,“value”:“8”}

6.count()、group by、having count()： 分组去重统计，group by进行分组去重，count()对分组进行统计，having count()将分组统计后满足一定条件的组展示出来，起到筛选分组的作用； 举例： 如下表table_name，有col1、col2、col3三列：

col1	col2	col3
1	a	90
1	a	95
2	B	98
2	B	98
3	C	100
3	C	100
3	C	100
1）按col1列进行分组计数；

select
	col1,
	COUNT(1) num
from
	table_name
group by
	col1
having
	count(1) > 1;

返回结果如下表：

col1	num
1	2
2	2
3	3
2）按照col1、col2、col3三列进行分组计数，将分组后col2列大于1次和col3列小于3次的分组展示出来；

select
	col1,
	col2,
	COL3
	COUNT(1) num
from
	table_name
group by
	col1,
	col2,
	col3
having
	count(col2) > 1
	AND count(col3) < 3;

返回结果如下表：

col2	col3	num
2	B	2

7.join（inner join）、full join（full outer join）、left join（left outer join）、right join（right outer join）： 表关联函数，分别为两表之间的内连接、全连接、左连接、右链接；
举例： 如下两个表table_name1和table_name2：
table_name1：

col1	col2
Damon	a
Stefan	b
Ella	c
table_name2：
col1_1	col2_2
–	–
Damon	a
Stefan	b
Lisa	c
1）join（inner join）：根据关联条件，两表之间能匹配上的行被保留，匹配不上的行被丢弃，即取两表的交集；

SELECT
	*
FROM
	table_name1 
JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

返回结果如下表：

col1	col2	col1_1	col2_2
Damon	a	Damon	a
Stefan	b	Stefan	b
2）full join（full outer join）：返回两表中的所有行，根据关联条件两表之间匹配不上的行其对应的字段值为空，即取两表的并集；

SELECT
	*
FROM
	table_name1 
FULL JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

返回结果如下表：

col1	col2	col1_1	col2_2
Damon	a	Damon	a
Stefan	b	Stefan	b
Ella	c	NULL	NULL
NULL	NULL	Lisa	c
3）left join（left outer join）：以左表为基准，返回左表的所有行，右表根据关联条件与左表匹配不上的行，字段值为空；

SELECT
	*
FROM
	table_name1 
LEFT JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

返回结果如下表：

col1	col2	col1_1	col2_2
Damon	a	Damon	a
Stefan	b	Stefan	b
Ella	c	NULL	NULL
4）right join（right outer join）：以右表为基准，返回右表的所有行，左表根据关联条件与右表表匹配不上的行，字段值为空；

SELECT
	*
FROM
	table_name1 
RIGHT JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

返回结果如下表：

col1	col2	col1_1	col2_2
Damon	a	Damon	a
Stefan	b	Stefan	b
NULL	NULL	Lisa	c

8. regexp_replace： 正则替换，regexp_replace(str, ‘str1’, ‘str2’)，将表达式str中的str1全部替换成str2；

select regexp_replace('case1_case2_case3', '_', '#')
--返回结果：case1#case2#case3

9. regexp_extract： 正则匹配函数，regexp_extract(str, regexp, index)，将表达式str按照正则部分regexp进行拆分，返回指定索引的部分；index=0：返回与正则表达式匹配的整个字符串，index=1、2、3、4…：分别返回正则表达式中第一、二、三、四…个括号内的字符串，不写index参数则默认为1；

select regexp_extract('form&source=a&flag=login', '&(source=.+)&(.*)')
--返回结果(index默认为1)：source=a
select regexp_extract('form&source=a&flag=login', '&(source=.+)&(.*)', 0)
--返回结果：&source=a&flag=login
select regexp_extract('form&source=a&flag=login', '&(source=.+)&(.*)', 1)
--返回结果：source=a
select regexp_extract('form&source=a&flag=login', '&(source=.+)&(.*)', 2)
--返回结果：flag=login

10. get_json_object()： json类型解析函数，get_json_object(json, ‘$.kye’)，解析json中指定key对应的value；

select get_json_object('{"name":"Lisa","score":"99"}', '$.name')
--返回结果：Lisa

11. parse_url()： url截取函数，用法为parse_url(url, key)，其中key可为HOST、PATH、QUERY、REF、PROTOCOL、FILE、AUTHORITY、USERINFO]；

select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL')
--返回结果：http
select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST')
--返回结果：facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'REF')
--返回结果：NULL
select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH')
--返回结果：/path/p1.php
select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY')
--返回结果：query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE')
--返回结果：/path/p1.php?query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY')
--返回结果：facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO')
--返回结果：NULL

12. if()： 条件判断函数，if(condition, true_value, false_value)，根据判断条件为true或false返回对应的值；

select if(1 > 2, 'abc', 'def')
--返回结果：def

13. case when三种用法： 根据某列值返回符合条件的对应值。举例： 如下表table_name，有id，col1，col2三列数据：

id	col1	col2
1	a	A
2	b	B
3	c	C

select
	id,
	col1,
	col2,
	case
		when id = 1
		then 'test1'
		when id = 2
		then 'test2'
		else 'test3'
	end as new_col1,
	case id
		when 1
		then 'test1'
		when 2
		then 'test2'
		else 'test3'
	end as new_col2,
	case
		when id = 1
		then col1
		else col2
	end as new_col3,
	case
		when id = 1
		then 'test1'
		when id = 2
		then 'test2'
	end as new_col4,
	case id
		when 1
		then 'test1'
		when 2
		then 'test2'
	end as new_col5,
	case
		when id = 1
		then col1
		when id = 2
		then col2
	end as new_col6
from
	table_name

返回结果如下表：

id	col1	col2	new_col1	new_col2	new_col3	new_col4	new_col5	new_col6
1	a	A	test1	test1	a	test1	test1	a
2	b	B	test2	test2	B	test2	test2	B
3	c	C	test3	test3	B	NULL	NULL	NULL

14. row_number()over()、dense_rank()over()、rank()over()： 分组排序，row_number()over(partition by col1 order by col2 desc)表示按照col1列分组，分组后组内按照col2列进行降序排列，dense_rank、rank用法与row_number一致。注意：row_number排序，序号依次递增不跳跃，组内数据相同序号不同，如1、2、3、4…；dense_rank排序，序号递增不跳跃，组内数据相同序号相同，如1、1、2、3…；rank排序，序号不连续存在跳跃，组内数据相同序号相同，如1、1、3、4…
举例： 如下表table_name，有name、subject、score三列；

name	subject	score
Stefan	Math	98
Stefan	Chinese	93
Damon	Math	100
Damon	Chinese	90
Ella	Math	98
Ella	Chinese	93
Lisa	Math	95
Lisa	Chinese	96

select
	name,
	subject,
	score,
	row_number() over(partition by subject order by score desc) row_number,
	dense_rank() over(partition by subject order by score desc) dense_rank,
	rank() over(partition by subject order by score desc) rank
from
	table_name

返回结果如下表：

name	subject	score	row_number	dense_rank	rank
Lisa	Chinese	96	1	1	1
Ella	Chinese	93	2	2	2
Stefan	Chinese	93	3	2	2
Damon	Chinese	90	3	3	4
Damon	Math	100	1	1	1
Ella	Math	98	2	2	2
Stefan	Math	98	3	2	2
Lisa	Math	95	4	3	4

二、表操作相关

1.建表

1）直接建表法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name      
 [(col_name data_type [COMMENT col_comment], ...)]      
 [COMMENT table_comment]                                 
 [PARTITIONED BY(col_name data_type [COMMENT col_comment], ...)]
 [CLUSTERED BY (col_name, col_name, ...)
 [SORTED BY(col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
 [ROW FORMAT row_format] 
 [STORED AS file_format]
 [LOCATION hdfs_path]  
 --关键词解释如下：
 --CREATE TABLE：创建一个指定名字的表，若同名表已存在会报错，加上IF NOT EXISTS若存在同名表不会报错，但建表不会成功，表结构依然是原来已存在的同名表；
--EXTERNAL：创建外部表，默认是内部表；
--COMMENT：给表字段或者表内容添加注释说明；
--PARTITIONED BY：给表做分区，分区字段不可与create建表时指定的表字段重复，即分区字段只出现在partition by中，不可重复出现在table_name后面指定的字段中；
--CLUSTERED BY：对表或分区进行分桶，桶为更细粒度的数据范围；
--ROW FORMAT DELIMITED FIELDS TERMINATED BY ','：指定表存储中列的分隔符，默认是 \001，此处指定以逗号分隔；
--STORED AS SEQUENCEFILE|TEXTFILE|RCFILE：指定数据文件的格式，STORED AS TEXTFILE表示数据文件为纯文本，如果数据需要压缩，可用STORED AS SEQUENCEFILE；
--LOCATION：指定hive表中数据在hdfs上的存储路径.

举例： 以下以dt字段为分区创建一张名为table_name的分区表：

CREATE
	TABLE IF NOT EXISTS table_name
	(
		name    VARCHAR(20) COMMENT '表第一列',
		subject VARCHAR(20) COMMENT'表第二列'
	)
	COMMENT '测试表' PARTITIONED BY
	(
		dt string
	)
	ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

表创建成功后，导入一行数据，结果如下：

name	subject	dt
Lisa	Math	2020-12-02
2）查询建表法：将子查询的结果（table_name中的查询结果）存在新表new_table中，通过as查询语句完成建表：

CREATE TABLE IF NOT EXISTS new_table AS
SELECT name,dt FROM table_name

new_table如下：

name	dt
Lisa	Math
3）like建表法：创建结构完全相同的表，仅创建表结构，无数据：

CREATE TABLE IF NOT EXISTS new_table LIKE table_name

new_table如下：

name	subject	dt

2.表删除

1）truncate table： 删除表数据，保留表结构，用法为语句后直接加表名；

TRUNCATE TABLE table_case

2）drop table： 删除整个表，表结构和表数据全部清除，用法为语句后直接加表名；

DROP TABLE table_case

3）delete： 删除表中特定条件下的行；

DELETE FROM table_case WHERE name = 'Lisa'
--删除table_case中name为Lisa的数据，若指定WHERE 1=1,则删除所有行

3.表结构查询

1）show create table： 查看建表语句；

show
CREATE TABLE table_name

2）show partitions： 查看表分区；

show partitions table_name

3）desc： 查看表结构，即查看表字段、字段类型、字段注释以及表分区；

DESC table_name

4）desc formatted： 查看表详细属性，除表字段、字段类型、字段注释以及表分区外还会展示表详细信息如所属数据库、负责人、创建时间等；

DESC formatted table_name

5）dfs -du： 查看表某个分区下文件大小（文件路径可从查看表详细或建表语句的LOCATION中获取）：

dfs -du hdfs://snd10101/user/mobile_market/dev.db/table_case/dt=2020-12-02/name_partition=L
---格式为dfs - du ***(表路径)/***(分区，可指定多个分区)

4.视图与物化视图

1）视图： 视图是一张虚表，只存储sql语句，不存储数据；
举例：如表table_name，有name、subject、dt三列数据；

name	subject	dt
Lisa	Math	2020-12-02

create view view_name as
select * from table_name

创建视图（view_name）如下：

name	subject	dt
Lisa	Math	2020-12-02
2）物化视图：物化视图可理解为一张表，其真实存储数据；
举例：如表table_name，有name、subject、dt三列数据；
name	subject	dt
–	–	–
Lisa	Math	2020-12-02

create materialized view view_name
as
	select * from table_name

创建物化视图（view_name）如下：

name	subject	dt
Lisa	Math	2020-12-02

5.索引

索引是为了提高表中数据行的检索效率而创建的数据结构，索引可以大大提高数据的查询速度。
索引是将表的某列值及其值对应数据行的物理地址进行存储，查询时直接根据索引对应的地址返回相关数据行，极大提高了查询速度。根据索引中包含的列数可将索引分为单列索引（索引中只包含一个列）和组合索引（索引中包含多个列）；
举例： 如下表table_name，有id、name、score三列，其中斜体的磁盘地址表示每行数据的存储地址，非表本身数据。以id列建立索引示意图如下：
table_name：

磁盘地址	id	name	score
0x1234	1	Lisa	95
0x1235	2	Ella	98
0x1236	3	Damon	90
…	…	…	…
以id列建立的索引：
1	0x1234
–	–
2	0x1235
3	0x1236
…	…
假如表table_name中有100万条数据，要取出id=10000的数据行，在无索引的情况下，需要将100万条数据全部遍历完，然后返回id=10000的数据行，查询速度较慢；而在以id列建立索引的情况下，则无需遍历数据，直接根据索引找到id=10000所在行的地址然后返回数据，极大提高了查询速度，二者的查询效率可想而知；
索引的优点显而易见，它可以极大提高数据的查询速度，同时索引的缺点也很明显，主要是占用磁盘空间（因为索引的数据结构需要存储）和降低表更新的速度（因为对于数据表的增、删、改操作索引也要随之变动）；

三、注意事项

1.group by 后字段必须全部包含select 中的字段： group by执行顺序在select前，由于group by对字段进行分组去重，分组以外的字段将被合并，若select后选择分组去重字段以外的字段，则无法明确选中字段的值，因为这些字段已经被合并，因此程序会报错；
2.order by实现排序的同时也实现了分组： order by按照某字段升序或降序（desc），由于相同值会排在一起，因此排序的同时也实现了分组，如order by name, score desc即先按照姓名升序排列，同名的排在一起，然后同组之间再按照分数降序排列，这样也就实现了先分组然后组内再排序的功能；

四、练习

1.引流： 提取3个月内未访问过客户端，由活动再次访问客户端的用户数，活动周期2020-12-19至2021-01-05；
1）第一种方法： 先取出3个月内访问过客户端且访问了活动的用户，再将这部分用户从访问过活动的总用户中剔除；

SELECT
	d.dt,
	COUNT(DISTINCT d.user_log_acct) num
FROM
	(
		SELECT
			dt,
			user_log_acct
		FROM
			user_table
		WHERE
			dt BETWEEN'2020-12-19' AND '2021-01-05'
			AND 活动页条件限定
		GROUP BY
			dt,
			user_log_acct
	)
	d
LEFT JOIN
	(
		SELECT
			a.dt,
			a.user_log_acct
		FROM
			(
				SELECT
					dt,
					user_log_acct
				FROM
					user_table
				WHERE
					dt BETWEEN'2020-12-19' AND '2021-01-05'
					AND 活动页条件限定
				GROUP BY
					dt,
					user_log_acct
			)
			a
		LEFT JOIN
			(
				SELECT
					dt,
					user_log_acct
				FROM
					user_table
				WHERE
					dt BETWEEN '2020-09-19' AND '2021-01-04'/*注意，总用户的时间范围为活动周期开始时间向前推3个月至活动最后一天向前推一天*/
				GROUP BY
					dt,
					user_log_acct
			)
			b
		ON
			a.user_log_acct = b.user_log_acct
		WHERE
			DATEDIFF(a.dt, b.dt) BETWEEN '1' AND '90'
		GROUP BY
			a.dt,
			a.user_log_acct
	)
	c ON d.dt = c.dt
	AND d.user_log_acct = c.user_log_acct
WHERE
	c.user_log_acct IS NULL
GROUP BY
	d.dt
ORDER BY
	d.dt ;

2）第二种方法： 对用户进行打标，将3个月内访问过客户端且访问过活动的用户记成sort=1，三个月内未访问过客户端然后通过活动再次访问了客户端的用户记为sort=0，sum（sort）=0的就是满足三个月内未访问过客户端通过活动再次访问客户端的用户；

SELECT
	d.dt,
	COUNT(DISTINCT user_log_acct) num
FROM
	(
		SELECT
			c.dt,
			c.user_log_acct
		FROM
			(
				SELECT
					a.dt,
					a.user_log_acct,
					CASE
						WHEN DATEDIFF(a.dt, b.dt) BETWEEN '1'AND '90'
						THEN 1
						ELSE 0
					END AS sort
				FROM
					(
						SELECT
							dt,
							user_log_acct
						FROM
							user_table
						WHERE
							dt BETWEEN'2020-12-19' AND '2021-01-05'
							AND 活动页条件限定
						GROUP BY
							dt,
							user_log_acct
					)
					a
				LEFT JOIN
					(
						SELECT
							dt,
							user_log_acct
						FROM
							user_table
						WHERE
							dt BETWEEN '2020-09-19' AND '2021-01-04'/*注意，总用户的时间范围为活动周期开始时间向前推3个月至活动最后一天向前推一天*/
						GROUP BY
							dt,
							user_log_acct
					)
					b
				ON
					a.user_log_acct = b.user_log_acct
			)
			c
		GROUP BY
			c.dt,
			c.user_log_acct
		HAVING
			SUM(sort) = 0
	)
	d
GROUP BY
	d.dt
ORDER BY
	dt;

2.连签：取近7天连续签到的用户数

SELECT
	COUNT(DISTINCT a.user_log_acct) AS num
FROM
	(
		SELECT
			user_log_acct,
			COUNT(DISTINCT dt) AS uniq_dt
		FROM
			user_table
		WHERE
			dt >= sysdate( - 7)
			AND dt <= sysdate( - 1)
			AND 用户签到限定条件
		GROUP BY
			user_log_acct
	)
	a
WHERE
	a.uniq_dt = '7';

3.复访：
4.留存
5.假设表有id和value两列，取满足条件的value值： 当id字段中最大值>=6时取id=6对应的value字段值，当id中最大值<6则取最大的id值对应的value值，sql如下：

SELECT
	value
FROM
	table_name
WHERE
	id IN
	(
		SELECT
			CASE
				WHEN MAX(id) >= 6
				THEN 6
				ELSE MAX(id)
			END AS NEW_column
		FROM
			tablename
	)
--根据某列值的情况返回另一列对应的值，与case when功能一致，此sql case when产生的新列只有一个数据，可将其嵌套在where中使用，做到根据一列的值取另一列的值;

6.解析json中指定的key： 从如下json中解析出shopid；
{ “ordernum”: “111”, “shopnumber”: “222”, “shop”: [{ “shopid”: “666”, “shoptype”: “777”}, { “shopid”: “222”, “shoptype”: “111”}] }

SELECT
	explode(split(regexp_replace(regexp_replace(get_json_object('{"ordernum": "111", "shopnumber": "222", "shop": [{ "shopid": "666", "shoptype": "777"},{ "shopid": "222", "shoptype": "111"}]}', '$.shop'), '\\[|\\]', ''), '\\}\\,\\{', '}#{'), '#'))
--sql解析：
--1.先get_json_object()解析出shop，解析出的值为数组格式；
--2.连用两个正则替换regexp_replace()先将数组中的前后中括号[]替换成空，即去掉中括号，再将各json中的},{替换成}#{，便于后续使用split()按照#切分出各个单独的json；
--3.用行转列函数explode将切分后的各个json转成行，每个json单独成一行。
--返回结果：
--1	{"shopid":"666","shoptype":"777"}
--2	{"shopid":"222","shoptype":"111"}

7.不用分位数函数求表table_name中score列的中位数：

select
	avg(tem_table.score)
from
	(
		select
			score,
			row_number() over(order by score) rank
		from
			table_name
	)
	tem_table
where
	tem_table.rank in
	(
		select ceil((count(1) + 1) / 2) from table_name
		
		union all
		
		select floor((count(1) + 1) / 2) from table_name
	)
--1.通过row_number()over()函数将原表score列按数据大小进行升序排列并给对应数据添加序号，形成rank列；
--2.根据中位数的定义，用count()求出数据总个数即数据排序后的最大编号，利用ceil()和floor()获取总数加一除以二后的向上和向下整数(结果能除尽，则向上向下取整皆为其本身)，此步可定位中位数位置所在，再将对应向上和向下整数位置对应的score求均值即为中位数；
--3.where子查询中是一张一列两行的数据表，数据正是中位数前后的位置号；

8.连续登录异常用户排查： 假设数据表有三个字段，分别是user_id、login_time（登陆时间）、quit_time（登出时间），且表中只有一天的数据，定义一分钟内连续登录三次的用户为异常用户，找出所有的异常用户

SELECT DISTINCT
	a.user_id
FROM
	(
		SELECT
			user_id,
			login_time,
			row_number() over(partition BY user_id order by login_time) rank
		FROM
			dev.dev_zwj_b_test
	)
	a
LEFT JOIN
	(
		SELECT
			user_id,
			login_time,
			row_number() over(partition BY user_id order by login_time) rank
		FROM
			dev.dev_zwj_b_test
	)
	b
ON
	a.user_id = b.user_id
	AND a.rank + 2 = b.rank --此处改成a.rank = b.rank + 2，后续where条件改为a的时间减b的时间，道理相同
WHERE
	unix_timestamp(b.login_time) - unix_timestamp(a.login_time) <= 60 ;
 

  **本文为个人工作、学习所得，内容会持续更新。如有问题，欢迎交流指正~**

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。