目录
一、函数学习
1、concat拼接函数
将多个字符串连接成一个字符串。
concat(str1, str2,…) 返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。
但是输入sql语句麻烦了许多,三个字段需要输入两次逗号,如果10个字段,要输入九次逗号…麻烦死了啦,有没有什么简便方法呢?——于是可以指定参数之间的分隔符的concat_ws()来了!!!
2、concat_ws()
和concat()一样,将多个字符串连接成一个字符串,但是可以一次性指定分隔符~(concat_ws就是concat with separator)
concat_ws(separator, str1, str2, …)
说明:第一个参数指定分隔符。需要注意的是分隔符不能为null,如果为null,则返回结果为null。
3、FROM_UNIXTIME函数
FROM_UNIXTIME(unix_timestamp,format)
- unix_timestamp为需要处理的时间戳(该参数是Unix 时间戳),可以是字段名,也可以直接是Unix 时间戳字符串。
- format为需要转换的格式
mysql官方手册的解释为:返回’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS 格式值的unix_timestamp参数表示,具体格式取决于该函数是否用在字符串中或是数字语境中。若format 已经给出,则结果的格式是根据format 字符串而定。format的格式和日常的编程语言的时间格式是保持一致的。
mysql> select FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S');
+-----------------------------------------------+
| FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S') |
+-----------------------------------------------+
| 2012-08-14 22:28:35 |
+-----------------------------------------------+
1 row in set (0.00 sec)
4、UNIX_TIMESTAMP()函数
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
- 参数调用,则返回一个 Unix timestamp (‘1970-01-01 00:00:00’ GMT 之后的秒数) 作为无符号整数。
- 若用date 来调用 UNIX_TIMESTAMP(),它会将参数值以’1970-01-01 00:00:00’ GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。
mysql> select UNIX_TIMESTAMP('2012-09-04 18:17:23');
+---------------------------------------+
| UNIX_TIMESTAMP('2012-09-04 18:17:23') |
+---------------------------------------+
| 1346753843 |
+---------------------------------------+
1 row in set (0.00 sec)
实际应用中,我们可以将格式转换为毫秒的与数据库中进行比较,也可以将数据库的Int格式转换为普通格式进行比较。
例如:查询所有创建日期大于2012-09-03 18:00:00之后的博客数据
方法一:将日期转换为int比较
select * from blog where createdTime > UNIX_TIMESTAMP('2012-09-03 18:00:00' );
方法二:将int转换为时间格式比较
select * from blog where FROM_UNIXTIME(createdTime, '%Y-%m-%d %H:%i:%S') > '2012-09-03 18:00:00';
5、NVL()函数
- hive中的nvl函数为判断是否为空值,nvl叫做空值转换函数。
- NVL函数的格式如下:NVL(expr1,expr2)
备注:
1、如果expr1为NULL,返回值为 expr2,否则返回expr1。
2、适用于数字型、字符型和日期型,但是 expr1和expr2的数据类型必须为同类型。
6、group by分组
- 在 Group by 子句中,Select 查询的列,要么需要是 Group by 中的列,要么得是用聚合函数(比如 sum、count等)加工过的列。不支持直接引用非 Group by 的列。这一点和 MySQL 有所区别;
- 对于非group by字段,可以用Hive的collect_set函数收集这些字段,返回一个数组,使用数字下标,可以直接访问数组中的元素。
select collect_set(d.dname)[0],count(*) num from emp e
join dept d on e.deptno=d.deptno
where e.sal<=2500
group by e.deptno
order by num desc limit 1
7、collect_set函数
8、size函数
计算数组的大小
size(collect_set(compant_name))
9、sumif函数的语法格式
- =sumif(range,criteria,sum_range)
- Sumif(条件区域,求和条件,实际求和区域),第二个求和条件参数在第一个条件区域里。
二、SQL正则表达式
1、元字符
当我们要进行一些简单的糊涂查询时用百分号(%),通配符(_)
就可以了.其中%表达任意长度的字符串,_表示任意的某一个字符。但如果在一些复杂的查询中关用这两个符号sql语句就会非常复杂,而且也不一定能实现.从Oracle 10g开始引入了在其他程序语言中普通使用的正则表达式。主要有regexp_like(匹配),regexp_replace(替换),regexp_substr(提取),regexp_instr(包含)四个正则表达式函数。
2、正则表达函数
2.1 regexp_like(x,pattern[,match_option])
含义:查看x是否与pattern相匹配,该函数还可以提供一个可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下:
- ‘c’ 说明在进行匹配时区分大小写(缺省值);
- ‘i’ 说明在进行匹配时不区分大小写;
- ‘n’ (.)点号能表示所有单个字符;
- ‘m’ 字符串存在换行的时候当作多行处理.这样 就 可 匹 配 每 行 的 结 尾 . 不 然 的 话 就可匹配每行的结尾.不然的话 就可匹配每行的结尾.不然的话只匹配字符串最后的位置。
select * from emp where regexp_like(ename,'^a[a-z]*n$')
解析:
从emp表中匹配ename字段,要求以a开头,a-z任意多个字符(包括0个),n结尾。
例如ename为arwen或arwin或anden.但Arwen不能被匹配.因为默认是区分大小写。
select * from emp where regexp_like(ename,'^a[a-z]*n$','i')
可以查找ename为Arwen的行记录。
2.2 regexp_instr(x,pattern[,start[,occurrence[,return_option[, match_option]]]])
含义:用于在x中查找pattern。返回pattern在x中出现的位置。匹配位置从1开始。可以参考字符串函数 INSTR(),参数相关:
- ‘start’ 开始查找的位置;
- ‘occurrence’ 说明应该返回第几次出现pattern的位置;
- ‘return_option’ 说明应该返回什么整数。若该参数为0,则说明要返回的整数是x中的一个字符的位置;若该参数为非0的整数,则说明要返回的整数为x中出现在pattern之后的字符的位置;
- ‘match_option’ 修改默认的匹配设置。与regexp_like里面的相同。
SELECT REGEXP_INSTR('hello world','o',1,1,0);
==》5
解析:
查找o在hello word中第一次出现的位置。
start=1,occurrence=1,return_option=0
SELECT REGEXP_INSTR('hello world','o',1,1,1);
==》6
查找o在hello word中第一次出现后的字符的位置,这里return_option=1也可以等于其他非0整数。
SELECT REGEXP_INSTR('hello world','o',1,2,0);
==》8
查找o在hello word中第二次出现的位置(空格也属于一个字符位置)。
2.3 REGEXP_SUBSTR(x,pattern[,start[,occurrence[, match_option]]])
**含义:**用于在x中查找pattern并返回。可以参考字符串函数 SUBSTR(),参数同REGEXP_INSTR函数。
SELECT REGEXP_SUBSTR('hello world','l{2}');
==》ll
解析:
查询到匹配的字符串才返回匹配的字符.没查到就返回空。
2.4 REGEXP_REPLACE(x,pattern[,replace_string[,start[,occurrence[, match_option]]]])
含义:用于在x中查找pattern,并将其替换为replae_string。可以参考字符串函数 REPLACE(),参数同REGEXP_INSTR函数。
REPLACE(String,from_str,to_str) 即:将String中所有出现的from_str替换为to_str。注意:from_str不能是空字符串 ('')。
SELECT REGEXP_REPLACE('hello world','o','x',1,1);
==》hellx world
解析:
start=1,occurrence=1。
SELECT REGEXP_REPLACE('hello world','o','x');
==》hellx wxrld
SELECT REGEXP_REPLACE('hello world','o','x',1,2);
==》hello wxrld
三、hive数仓数据重复性检查
1、如何快速确认某一字段是否有重复值
因为Hive中没有主键,mysql严格要求主键,可以将待确认字段作为主键。
1.1 cnt 没有去重,order_cnt 去重, 如果得到结果相同,则表是没有重复
select
count(*) cnt ,count(distinct order_id) order_cnt
from orders
//结果:
cnt order_cnt
3421083 3421083
1.2 根据待确认字段进行分组,订单号不能有大于1的情况,就代表没有重复
select
order_id,count(1)
from orders
group by order_id
having count(1)>1
//结果:
order_id count(1)
# 表示没有重复值
1.3 如果数据有重复,如何查找出来重复的字段信息呢?
select
pripid,count(1)
from orders
group by pripid
having count(1)>1
2、如何去除重复值
https://blog.csdn.net/ltliyue/article/details/52292819
2.1 hive表中某些数据是整行的重复,而且字段过多时:
insert overwrite table stu_tab
select distinct * from stu_tab;
通过过滤不同的行,将原表进行覆盖。
2.2 两行数据只有一个字段不一样:
根据情况我们将含有2的那行数据进行删除;
insert overwrite table sys_cus_hz_20210127
select distinct * from sys_cus_hz_20210127 where consumlevel != 2;
2.3 当表中字段不多,而且数据是某些字段重复,可通过row_number():
insert overwrite table emp_tab
select id,name,sal from (
select * ,
row_number() over(partition by id order by sal desc) rank
from emp_tab)t1
where t1.rank=1;
2.4 在某些情况中我们不是需要全部字段,而是仅仅过滤出某些不重复的字段:
select distinct id from stu_tab;
#单个字段去重
select distinct id,name from stu_tab;
#多个字段之间通过逗号去重
3、row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
这行代码的意思是先对COL1列进行分组,然后按照COL2进行排序,row_number()函数是对分组后的每个组内记录按照COL2排序标号,我们最后取的时候就拿标号为1的一条记录,即达到我的需求。
SELECT
*,
Row_Number() OVER (partition by deptid ORDER BY salary desc) rank
FROM employee
SELECT *
from( select
*,
Row_Number() OVER (partition by deptid ORDER BY salary desc) as rank
FROM employee) t1
where t1.rank=1
只保留rank=1的数据。
四、常见Shell快捷操作
快捷组合 | 含义 |
---|---|
i | vim状态下的插入 |
esc+wq | vim状态下保存退出 |
shift+z+z | vim状态下强制保存退出 |
ctrl+c | 强制停止运行 |
ctrl+l | 清空终端返回最上面 |
scp | 拷贝 |
rsync | 同步更新 |