在某单车公司实习时常用的presto、hive函数（残缺，够用）

1、明文手机号转MD5码

SQL：UPDATE ‘表名‘ SET ‘新字段’=MD5(手机号字段)

Python：

import hashlib
def getStrAsMD5(parmStr):
if isinstance(parmStr,str):
parmStr=parmStr.encode('utf-8')
m=hashlib.md5()
m.update(parmStr)
return m.hexdigest()

pandas可使用map()函数批量完成转换，如dt['md5_mobile']=dt['encrypt_mobile_phone'].map(getStrAsMD5)

注意：明文手机号必须是字符，且要小心手机号在excel里如果不是字符，如123344544，直接使用str()函数转成字符可能是123344544.0。

2、随机抽取

ORDER BY RAND() LIMIT 100 表示先随机排序后取100个数

hive环境下limit用法: limit 1,100表示从索引为1的数据开始往后取100个数据；limit 10表示随机取10个数

3、排序函数ROW_NUMBER()

（1）、ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的每条xlh记录返回一个序号

参考格式：ROW_NUMBER() OVER(ORDER BY COLUMN)  其中COLUMN是排序的列表，先按列名排序，再为排序以后的每条记录返回一个编号

（2）、ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示先根据COL1分组，在分组内部根据 COL2排序（组内排序）

参考格式：ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2 desc)   先根据column1分组，每组根据column2降序排列

（3）、其他类似用法的排序函数

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

4、类型强转cast()函数

Cast(字段名 as 转换的类型 )

其中类型可以为：

• CHAR[(N)] 字符型
• DATE 日期型
• DATETIME 日期和时间型
• DECIMAL float型
• SIGNED int
• TIME 时间型

常用于时间戳转成字符，

例如：presto:cast(d_date as varchar)     hive:cast(d_date as string)

5、正则模糊查询regexp函数

presto 正则 

REGEXP_LIKE  (create_user,'sunrong|haiya|zhangzhongwen|shennan|wangshiming|menglu')

hive  正则 create_user  REGEXP 'sunrong|haiya|zhangzhongwen|shennan|wangshiming|menglu'

6、日期函数

presto时间运算符:

示例	解释	返回类型
date '2012-08-08' + interval '2'  day/month/year	2012/8/8加或者减两天/月/年	date
time '01:00' + interval '2'  second/minute/hour	1:00加或者减两秒、分钟、小时	time
timestamp '2012-08-08 01:00' + interval '2'  second/minute/hour/day/month/year	时间戳2012-08-08 01:00 加或者减两秒、分、小时、天、月、年	timestamp
select interval '2' day/hour/minute/second + interval '3' day/hour/minute/second	day可以换成hour、minute、second等day以下的单位，返回值如{milliSeconds=432000000}	milliSeconds
select interval '2' year/month + interval '3' year/month	year可以换成month，即day以上的单位，返回值如{months=60}	month

注：presto运算时date类型的加减必须是day/month/year，time类型的加减必须是second/minute/hour，timestamp类型则是可用于任何时间单位的加减。

hive时间运算符:

示例	解释	返回类型
date '2012-10-31'	返回date日期，只能date不能是其他时间类型如时间戳'2012-10-31 01:00:00'	date
date '2012-10-31' -interval '1' second/minute/hour	会将'2022-10-31'默认为时间戳'2022-10-31 00:00:00'，再减去1秒、分、小时（天以下的单位）	timestamp
date '2012-10-31' -interval '1' day/month/year	2012-10-31 减去1天、月、年（天及天以上的单位）	date
from_unixtime(unix_timestamp('2012-08-08 01:00:00')+3600*2 )	将时间戳转为秒值进行运算再转成时间戳，此处是加2个小时	timestamp

hive日期函数：

返回值类型	函数名称（参数）	说明
string	from_unixtime(bigint unixtime[, string format])	将时间的秒值转换成format格式（format可为"yyyy-MM-dd hh:mm:ss","yyyy-MM-dd hh","yyyy-MM-dd hh:mm"等等）如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
bigint	unix_timestamp()	获取本地时区下的时间戳(秒值)
bigint	unix_timestamp(string date)	将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳(秒值)
bigint	unix_timestamp(string date, string pattern)	将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0 .如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400；unix_timestamp('20090320', 'yyyyMMdd') = 1237532400
string	to_date(string timestamp)	返回时间字符串的日期部分
int	year(string date)	返回时间字符串的年份部分
int	quarter(date/timestamp/string a)	返回季度(从Hive 1.3.0开始)
int	month(string date)	返回时间字符串的月份部分
int	day(string date) , dayofmonth(date)	返回时间字符串的天
int	hour(string date)	返回时间字符串的小时
int	minute(string date)	返回时间字符串的分钟
int	second(string date)	返回时间字符串的秒
int	weekofyear(string date)	返回时间字符串位于一年中的第几个周内.  如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
int	datediff(string enddate, string startdate)	计算开始时间startdate到结束时间enddate相差的天数
string	date_add(string startdate, int days)	从开始时间startdate加上days
string	date_sub(string startdate, int days)	从开始时间startdate减去days
string	date_format(date/timestamp/string ts, string fmt)	按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22
timestamp	from_utc_timestamp(timestamp, string timezone)	如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳
timestamp	to_utc_timestamp(timestamp, string timezone)	如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
date	current_date()	返回当前时间日期
timestamp	current_timestamp()	返回当前时间戳
string	add_months(string dt, int n)	返回当前时间dt下再增加n个月的日期
string	next_day(string start_date, string day_of_week)	返回当前时间的下一个星期X所对应的日期 如：next_day('2015-01-14', 'TU') = 2015-01-20，以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20
string	last_day(string date)	返回这个月的最后一天的日期，忽略时分秒部分
string	trunc(string date, string format)或trunc(n1, n2)	截断函数,可截断日期或数字。当第一个参数为string类型的时间字段时返回时间的最开始年份或月份。此时第二个参数format所支持的格式(不分大小写)为:YEAR/YYYY/YY/Y(截取本年第一天);MONTH/MON/MM(截取本月第一天);DD(截取本周第一天);D(截取到今天);HH(截取到小时); MI(截取到分钟); SS(截取到秒); 如trunc("2016-06-26","MM")=2016-06-01,trunc("2016-06-26","YY")=2016-01-01,trunc("2016-06-26 12:30:45","DD")=2016-06-26 00:00:00。 当第一个参数是数字类型的数值时第二个参数取整数,省略时从小数点处开始截断。例如: trunc(12345.6789)=12345，trunc(12345.6789,2)=12345.67trunc(12345.6789,-2)=12300
double	months_between(date1, date2)	返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0  如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677  1997-02-28 10:30:00与1996-10-30相差3.94959677个月

presto日期函数:

string	current_date()	获取当前日期
string	current_time()	获取当前时间
timestamp	current_timestamp()	获取当前日期和时间
timestamp	now()	获取当前日期和时间
string	current_timezone()	获取当前时区
timestamp	cast('2022-11-22 00:00:00 ' as timestamp)	字符串转时间戳
timestamp	date_parse('2022-03-17 00:00:00', '%Y-%m-%d %H:%i:%S')	字符串转时间戳
timestamp	from_unixtime（）	bigint秒值转时间戳
bigint	to_unixtime（）	时间戳转秒值
string	format_datetime(cast('2022-03-17' as timestamp),'yyyy-MM-dd HH')	时间戳格式化
string	date_trunc('second', current_timestamp())	时间戳格式化
string	date_format(current_date,'%Y-%m-%d')	时间戳取年月日
string	date(current_date)	时间戳取年月日
string	cast(current_date as date)	时间戳取年月日
string	date(cast('2021-03-17 10:28:00' as TIMESTAMP))	字符串转年月日
string	date('2021-03-17')	字符串转年月日
string	date_format(cast('2021-03-17 10:28:00' as TIMESTAMP),'%Y-%m-%d')	字符串转年月日
string	to_date('2021-03-17','yyyy-mm-dd')	字符串转年月日
string	date(from_unixtime(1647500800))	bight转年月日
string	date_format(from_unixtime(1647500800),'%Y-%m-%d')	bight转年月日
int	date_diff(unit, date start_day, date end_day)	unit包括second、minute、hour、day、week、month、quarter、year
string	current_date - interval '7' month	几天前几天后
string	date_add('day', -7, current_date)	几天前几天后
timestamp	date_trunc(unit, timestamp)	月初、年初、周一、季度第一天，unit包括second、minute、hour、day、week、month、quarter、year
int	extract（unit from date）	时间提取函数，unit包括year、month、day
int	day_of_week(date)	日期是周几

7、连结函数union

presto:

      select *from table1

      union

      select *from table2

hive:

      select *from table1

      union all

      select *from table2

注意：表1和表2必须字段一致，另外如果子查询中有limit、order by等函数会报语法错误，所以可以先把子查询的结果存到表中再用union

8、case when

        case when  条件1 then 返回1

               when  条件2 then 返回2

              ...

               else 返回n

              end 字段名

注：如果字段名已经存在返回值将代替该字段里的值，如果字段名不存在，将插入该字段，字段里的值就是返回值。

9、简单计算函数

• Avg()：平均数
• Count()：返回行数，一般用于计算次数
• Max() ：最大值
• Min() ：最小值
• Sum() ：总和
• First() ：第一个
• Last()：最后一个

10、截断函数

• left(str, length)  从左边第一位开始截取指定长度字符串

• right(str, length)  从右边第一位开始截取指定长度字符串

• substring(str, index, length) 从指定开始位置截取指定长度字符串，length 默认截取到最后一位

• substr()、mid() 函数与substring()函数功能类似

11、hive创建表

insert into table account  select  id,age,name   from account_tmp

insert overwrite table account2 select id,age,name  from account_tmp 

overwrite 会覆盖现有的数据，而 into 是直接将数据写入库，

如果需要的是去重的数据，那么应该选择 overwrite 作为插入的方式

（整理的不全，不过这些都会了应该够面试用了）