HIVE SQL函数实例讲解

已于 2022-12-21 15:54:57 修改
阅读量2.6k 收藏 35
点赞数 1
分类专栏： 数据分析必备技能 数据产品经理知识库 大数据知识库 文章标签： hive sql 数据分析 数据仓库 hadoop
于 2020-12-10 18:49:47 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Jun2613/article/details/96887220
版权

HIVE SQL函数实例讲解

  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三列数据

col1col2col3
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），结果如下表：

col1col2col3new_col
1[a,b]1_2_31
1[a,b]1_2_32
1[a,b]1_2_33
2[c,d]4_5_64
2[c,d]4_5_65
2[c,d]4_5_66
  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），其最终展示的结果为原表和两张虚拟表的笛卡尔积，结果如下表：

col1col2col3new_col1new_col2
1[a,b]1_2_3a1
1[a,b]1_2_3a2
1[a,b]1_2_3a3
1[a,b]1_2_3b1
1[a,b]1_2_3b2
1[a,b]1_2_3b3
2[c,d]4_5_6c4
2[c,d]4_5_6c5
2[c,d]4_5_6c6
2[c,d]4_5_6d4
2[c,d]4_5_6d5
2[c,d]4_5_6d6

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

col1col2col3
1aA
1aA
1aB
2bA
2bA
2bB
select
	col1,
	collect_all(col2) new_col--或者collect_list(col2)
from
	table_name
group by
	col1

  返回结果如下表：

col1new_col
1[“a”,“a”,“a”]
2[“b”,“b”,“b”]
select
	col1,
	collect_set(col2) new_col
from
	table_name
group by
	col1

  返回结果如下表：

col1new_col
1[“a”]
2[“b”]
select
	col1,
	col2,
	collect_all(col3) new_col--或者collect_set(col3)
from
	table_name
group by
	col1,
	col2

  返回结果如下表：

col1col2new_col
1a[“A”,“A”,“B”]
2b[“A”,“A”,“B”]
select
	col1,
	col2,
	collect_set(col3) new_col
from
	table_name
group by
	col1,
	col2

  返回结果如下表：

col1col2new_col
1a[“A”,“B”]
2b[“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三列：

col1col2col3
1a90
1a95
2B98
2B98
3C100
3C100
3C100
  1） 按col1列进行分组计数；
select
	col1,
	COUNT(1) num
from
	table_name
group by
	col1
having
	count(1) > 1;

  返回结果如下表：

col1num
12
22
33
  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;

  返回结果如下表：

col2col3num
2B2

  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：

col1col2
Damona
Stefanb
Ellac
  table_name2：
col1_1col2_2
––
Damona
Stefanb
Lisac
  1）join（inner join）： 根据关联条件，两表之间能匹配上的行被保留，匹配不上的行被丢弃，即取两表的交集；
SELECT
	*
FROM
	table_name1 
JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

  返回结果如下表：

col1col2col1_1col2_2
DamonaDamona
StefanbStefanb
  2）full join（full outer join）： 返回两表中的所有行，根据关联条件两表之间匹配不上的行其对应的字段值为空，即取两表的并集；
SELECT
	*
FROM
	table_name1 
FULL JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

  返回结果如下表：

col1col2col1_1col2_2
DamonaDamona
StefanbStefanb
EllacNULLNULL
NULLNULLLisac
  3）left join（left outer join）： 以左表为基准，返回左表的所有行，右表根据关联条件与左表匹配不上的行，字段值为空；
SELECT
	*
FROM
	table_name1 
LEFT JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

  返回结果如下表：

col1col2col1_1col2_2
DamonaDamona
StefanbStefanb
EllacNULLNULL
  4）right join（right outer join）： 以右表为基准，返回右表的所有行，左表根据关联条件与右表表匹配不上的行，字段值为空；
SELECT
	*
FROM
	table_name1 
RIGHT JOIN table_name2
ON
	table_name1.col1 = table_name2.col1_1 ;

  返回结果如下表：

col1col2col1_1col2_2
DamonaDamona
StefanbStefanb
NULLNULLLisac

  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三列数据：

idcol1col2
1aA
2bB
3cC
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

  返回结果如下表：

idcol1col2new_col1new_col2new_col3new_col4new_col5new_col6
1aAtest1test1atest1test1a
2bBtest2test2Btest2test2B
3cCtest3test3BNULLNULLNULL

  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三列；

namesubjectscore
StefanMath98
StefanChinese93
DamonMath100
DamonChinese90
EllaMath98
EllaChinese93
LisaMath95
LisaChinese96
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

  返回结果如下表：

namesubjectscorerow_numberdense_rankrank
LisaChinese96111
EllaChinese93222
StefanChinese93322
DamonChinese90334
DamonMath100111
EllaMath98222
StefanMath98322
LisaMath95434

二、表操作相关

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 ',';

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

namesubjectdt
LisaMath2020-12-02
  2）查询建表法： 将子查询的结果（table_name中的查询结果）存在新表new_table中，通过as查询语句完成建表：
CREATE TABLE IF NOT EXISTS new_table AS
SELECT name,dt FROM table_name

  new_table如下：

namedt
LisaMath
  3）like建表法： 创建结构完全相同的表，仅创建表结构，无数据：
CREATE TABLE IF NOT EXISTS new_table LIKE table_name

  new_table如下：

namesubjectdt

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三列数据；

namesubjectdt
LisaMath2020-12-02
create view view_name as
select * from table_name

  创建视图（view_name）如下：

namesubjectdt
LisaMath2020-12-02
  2）物化视图： 物化视图可理解为一张表，其真实存储数据；
举例：如表table_name，有name、subject、dt三列数据；
namesubjectdt
–––
LisaMath2020-12-02
create materialized view view_name
as
	select * from table_name

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

namesubjectdt
LisaMath2020-12-02

5.索引

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

磁盘地址idnamescore
0x12341Lisa95
0x12352Ella98
0x12363Damon90
…………
  以id列建立的索引：
10x1234
––
20x1235
30x1236
……
  假如表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 ;
 

&emsp;&emsp;**本文为个人工作、学习所得，内容会持续更新。如有问题，欢迎交流指正~**
Felix、J
关注
  • 1
    点赞
  • 踩
  • 35
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive Sql 大全(1)
ZP1008yy的博客
03-20 765
一、DDL语句（数据定义语句）： 对数据库的操作：包含创建、修改数据库 对数据表的操作：分为内部表及外部表，分区表和分桶表 二、DQL语句（数据查询语句）： 单表查询、关联查询 hive函数：包含聚合函数，条件函数，日期函数，字符串函数等 行转列及列转行：lateral view 与 explode 以及 reflect 窗口函数与分析函数 其他一些窗口函数
HiveSQL
qq_43528451的博客
05-06 1万+
HiveSQL Hive Sql 入门教程基础关键字用法 前置教程 hive库表知识 hive库是表的一个集合，一个库拥有多个表，hive整个数据库拥有多个库。 hive表代表一个对象，比如一个人设计为一个表就有身高体重等属性，在hive 中实际存储表现为每张表会有一个存储地址，比如表名为people，实际存储会有 hive中的分区概念 /xx/xx/people,hive中引用了一个很重要的概念分区，分区实际上也是这个表的字段，反映在 实际存储中通常以天为分区，存储在址为/xx/xx/peopl
一文学完所有的Hive SQL（两万字最全详解）
大数据星球-浪尖
11-07 2954
Hive SQL 大全本文基本涵盖了Hive日常使用的所有SQL，因为SQL太多，所以将SQL进行了如下分类：一、DDL语句（数据定义语句）：对数据库的操作：包含创建、修改数据库对数据表的操作：分为内部表及外部表，分区表和分桶表二、DQL语句（数据查询语句）：单表查询、关联查询hive函数：包含聚合函数，条件函数，日期函数，字符串函数等行转列及列转行：lateral view 与 explode ...
方差，标准差，CPK指标数据分析 (使用SQL做数据分析）
最新发布
quyunde的博客
06-07 528
CASE WHEN [标准差] >= 0 AND (([上限]-[平均值])/(3*[标准差])) > (([平均值]-[下限])/(3*[标准差]))'MIN((上限-平均值)/(3*标准差), (平均值-下限)/(3*标准差))' AS 'CPK公式',ELSE ([上限]-[平均值])/(3*[标准差])THEN ([平均值]-[下限])/(3*[标准差])([上限]-[平均值])/(3*[标准差]) AS [上],([平均值]-[下限])/(3*[标准差]) AS [下],0 AS [下限],
Hive SQL（一）
currify的博客
07-11 1831
数据库是长期存放在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按照一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易拓展性，并可为各种用户共享。主要分为关系型数据库和非关系型数据库。
大数据学习之HiveSQL
weixin_48205015的博客
03-08 2455
HiveSQL
Hive Sql 大全(hive函数，hive表)
asdsdwuwang的博客
03-04 2181
本节基本涵盖了Hive日常使用的所有SQL，因为SQL太多，所以将SQL进行了如下分类： 一、DDL语句（数据定义语句）：对数据库的操作：包含创建、修改数据库对数据表的操作：分为内部表及外部表，分区表和分桶表二、DQL语句（数据查询语句）：单表查询、关联查询hive函数：包含聚合函数，条件函数，日期函数，字符串函数等行转列及列转行：lateral view 与 explode 以及 reflect窗口函数与分析函数其他一些窗口函数UD。
用于hive的实例
10-23
本文将通过一个具体的Java与Hive结合的实例，深入讲解如何利用Java API进行Hive的操作，以加深对Hive语句的理解。 首先，要使用Java与Hive交互，我们需要引入Hive的JDBC驱动。在项目中，可以通过Maven或Gradle添加...
Spark_SQL大数据实例开发教程.pdf by Spark_SQL大数据实例开发教程.pdf (z-lib.org)1
08-04
5. **Spark SQL内置函数与窗口函数**：深入讲解Spark SQL的内置函数，包括聚合、转换、统计等，以及如何使用窗口函数进行复杂的时间序列分析。 6. **Spark SQL UDF与UDAF**：用户定义的函数（UDF）和用户定义的聚合...
Hive编程指南（带书签）
05-20
Hive作为大数据处理领域中的重要工具，是基于Hadoop生态系统的数据仓库解决方案，它允许用户使用SQL-like查询语言（HQL）来处理大规模分布式数据集。随着大数据时代的到来，Hive在处理海量数据时的高效性和易用性使...
《Spark SQL编程指南》
01-23
在SQL查询方面，书中有详尽的SQL语法介绍，包括创建、查询和管理Spark SQL数据库及表，以及使用JOIN、GROUP BY、窗口函数等复杂查询。同时，还会探讨如何使用Spark SQL进行数据分析和数据挖掘，例如时间序列分析、...
hive简单使用共13页.pdf.zip
10-30
这份13页的PDF教程应该会详细讲解这些概念，并通过实例演示如何在Hive中操作数据，帮助初学者快速掌握Hive的基本使用。对于想要了解或提升Hive技能的人来说，这是一份非常实用的学习资料。在实际工作中，结合Hadoop...
Hive基本SQL操作(图文并茂）
热门推荐
gaofeng的博客
06-21 1万+
Hive基本SQL操作 Hive DDL（数据库定义语言） 1、数据库的基本操作 --展示所有数据库 show databases; --切换数据库 use database_name; /*创建数据库 CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=pro
4.HiveSQL查询语句方法大全
m0_63953077的博客
05-03 1623
HiveSQL查询语句方法归纳与实战
hiveSQL常见函数及用法（持续收集）
xukaiqiang123的博客
07-13 4759
注意：聚合函数常与 SELECT 语句的 GROUP BY 子句一块儿使用。换句话说使用聚合函数时，一个列字段要不在group by里，要没必要须在聚合函数里面，不能单独出现，不然报错。 不支持等值连接，一般使用left join、right join 或者inner join替代。 2、分号字符 分号是sql语句的结束符号，在hive中也是，但是hive对分号的识别没有那么智能，有时需要进行转义 “；” --> “\073”sql中null代表空值，但是在Hive中，String类型的字段若是空（empt
hive中sql操作
qq_52772669的博客
05-20 474
comment:是对数据库的注释，是字符串location: 设置数据库在hdfs中的路径 ，默认路径是 /user/hive/warehouse/数据库名.db,指定路径使用单引号。with dbproperties 给数据库设置一些键值。
hiveSQL/SQL常用句式整理（不定时更新）
我的心曾悲伤7次
06-28 1444
1.sql中的 IF 条件语句的用法 if(bool , expr1, expr2) 如果bool为True,则取expr1，否则取expr2.
hive常用SQL函数及案例
Appreciate(欣赏)
12-14 1346
Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。好处：避免用户反复写逻辑，可以直接拿来使用。重点：用户需要知道函数叫什么，能做什么。Hive提供了大量的内置函数，按照其特点可大致分为如下几类：单行函数、聚合函数、炸裂函数、窗口函数。以下命令可用于查询所有内置函数的相关信息。
【Hive】SQL
高高兴兴绊倒，灰头土脸填坑
07-06 1109
【Hive】 SQL 简单语法总结
hive SQL函数大全
05-26
Hive SQL支持许多内置函数，包括以下类型： 1. 聚合函数（Aggregate Functions） - AVG()：计算平均值。 - COUNT()：返回输入行数或非 NULL 值的个数。 - MAX()：返回最大值。 - MIN()：返回最小值。 - SUM()：返回总和。 2. 数学函数（Mathematical Functions） - ABS()：返回绝对值。 - CEIL()：返回大于等于给定数的最小整数。 - EXP()：返回 e 的幂次方。 - FLOOR()：返回小于等于给定数的最大整数。 - LN()：返回指定数字的自然对数。 - LOG()：返回指定数字的对数。 - MOD()：返回给定数的余数。 - POWER()：返回指定数字的幂次方。 - RAND()：返回一个随机数。 - ROUND()：返回指定数字的四舍五入值。 - SIGN()：返回数字的符号。 - SQRT()：返回指定数字的平方根。 3. 日期和时间函数（Date and Time Functions） - ADD_MONTHS()：给定日期加上月份。 - CURRENT_DATE()：返回当前日期。 - CURRENT_TIMESTAMP()：返回当前时间戳。 - DATE_ADD()：给定日期加上指定天数。 - DATE_SUB()：给定日期减去指定天数。 - DATEDIFF()：返回两个日期之间的天数。 - FROM_UNIXTIME()：将 UNIX 时间戳转换为日期时间格式。 - HOUR()：返回指定时间的小时数。 - MONTH()：返回指定日期的月份。 - NOW()：返回当前日期和时间。 - SECOND()：返回指定时间的秒数。 - TO_DATE()：将时间戳转换为日期格式。 - UNIX_TIMESTAMP()：将日期时间格式转换为 UNIX 时间戳。 - YEAR()：返回指定日期的年份。 4. 字符串函数（String Functions） - CONCAT()：连接两个或多个字符串。 - LENGTH()：返回字符串长度。 - LOWER()：将字符串转换为小写。 - REGEXP_REPLACE()：替换与正则表达式匹配的字符串。 - REPLACE()：替换字符串。 - SPLIT()：将字符串拆分为数组。 - SUBSTR()：返回指定位置的子字符串。 - TRIM()：去除字符串两端的空格。 - UPPER()：将字符串转换为大写。 这只是 Hive SQL 内置函数的一部分，还有很多其他函数可以使用。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值