PRESTO SQL总结

PRESTO SQL总结

否在当月的最后三天 cast(updatedat as timestamp) between date_add(‘day’,-3,date_trunc(‘month’,date_add(‘month’,1,cast(updated_at as timestamp)))) and date_add(‘day’,-1,date_trunc(‘month’,date_add(‘month’,1,cast(updated_at as timestamp))))
updated_at是varchar类型，dateadd与date_trunc函数都是需要date，time类型，不支持字符串格式。
date_trunc日期截取函数，输出结果也为日期型，非常方便与其他日期型函数结合参与运算，如date_diff，date_add，官方文档见图

附相关问题一枚，如何计算两日期间月份差，期望结果如下：

问题1，直接截取月份相减，id1结果为1
问题2，使用date_diff(‘month’,createtime,end_time),结果是先计算天差后与create_time的月份天数比较，看是否足月。id2结果为0
解法1，直接使用if/case..when,先比较年份再计算月差，如年份相差为N，则月份差=月差+N12
解法2，拉平日期月份比较，比如全部拉平至月初，2020-04-01 00:00:00
date_diff(‘month’,date_trunc(‘month’,create_time),date_trunc(‘month’,end_time)) as “月份差”
2，同一业务ID下，最后一次流转后员工ID在历史记录中出现过，则该业务ID不出现

表结构如上，业务ID=2即为所需，1需排除
select ** from table where dt = ‘2019-09-30’ and rank = 1 and carid not in —反向选取 (select distinct t01.carid from—找到同一carid下，最后一次员工ID出现在其他记录的carid (select * from table where dt = ‘2019-09-30’ )t01 inner join (select * from table where dt = ‘2019-09-30’ )t02 on t02.carid = t01.carid and t01.rank < t02.rank and t01.rank = 1 and (t01.after_id = t02.after_id or t01.after_id = t02.before_id))
经验：正向走不通时，反向处理可能是一种解法
3,python 向SQL中传递参数
conn = presto_client.connect(,,,) supplier= [‘江苏出版社’,’人民出版社’] sql = ‘’’ select distinct name as “名称” ,address as “地址” from table where name in %(supplier)s ‘’’ df = pd.read_sql_query(sql,conn,params = {‘supplier’:supplier[1]})
_read_sql_query :在sql引用%(_var_name)s，调用时使用params参数，格式为params = {‘name’：’value’}
4，presto SQL：多条件模糊匹配
多条件模糊匹配:regexp_like(字段名, ‘(str1|str2|…)’)
示例如下：
where talbe.name not like ‘%测试%’ and talbe.name not like ‘%test%’ ——————-可转换为——— _where not regexp_like(talbe.name, ‘(测试|test)’)
右模糊匹配:regexp_like(col, ‘^(str1|str2|…)’)
左模糊匹配:regexp_like(col, ‘(str1|str2|…)$’)

5，SQL函数总结
1.1 字符 substr(staffleave_date,1,10) concat(string1, …, stringN) → varchar—拼接多个字符串 length(string) → bigint length(cast(start_time as varchar)) replace(string, search, replace) → varchar trim(string) → varchar—删除左右两侧的空格 split_part(ip,’:’,1)—以冒号分割取第二部分 upper/lower(string) → varchar—大小写 ###mid(str,pos,len)—从指定位置截取指定长度的字符串 —错误 substr(str,start,length)—截取指定长度字符串 1.2 日期 current_date -> date now() → timestamp from_unixtime(create_time)—int/bigint→ timestamp substr(staff_leave_date,1,10) date_format(from_unixtime(status_update_time),’%Y-%m-%d’)—调整时期格式’2017-11-25’ date_format(cast(audit_time as timestamp),’%Y-%m-%d %H:%i:%s’)—2018-04-13 22:09:16 date_add(‘day’, -1, CURRENT_DATE)—当前日期-1 date_trunc(‘month’,from_unixtime(create_time))—当前月份的第一天，2001-08-22 03:04:05.321→2001-08-01 00:00:00.000 date_diff(unit, timestamp1, timestamp2) → bigint date_diff(‘day’,cast(substr(audit_at,1,10) as date),cast(current_timestamp as date)) as “库存时间” day_of_week(x) → bigint Returns the ISO day of the week from x. The value ranges from 1 (Monday) to 7 (Sunday). week_of_year(x) → bigint This is an alias for week() 1.3 json json_extract(json, json_path) → json json_extract_scalar(json, json_path) → varchar {“chang_status_remark”:[“ChangeApply.id = 56798765”,”Apply.id = 345876”],”source_status”:[3,0],”status_update_time”:[98765,234589]} json_extract(change_info,’$.source_status’) —[3,0] json_extract_scalar(change_info,’$.source_status[1]’)—0 json_extract_scalar(json, ‘$.store.book[0].author’) 1.4 关联 表间关联 t01 inner/left/right join t02 on t01.id = t02.id left/right 未关联到的字段置为null 多部分数据拼接 union:对结果去重且排序 union all:直接返回合并的结果,速度快 union中的每个查询必须包含相同的列，表达式及聚合函数； 只能使用一条order by字句，且必须放在最后一个查询后面 1.5 条件 两分类： if(staff_status =1,’在职’,’离职’) as “在职状态” 等价于 case when staff_status=1 then ‘在职’ else ‘离职’ end as “在职状态” 多分类： case when level=1 then ‘A’ when level=2 then ‘B’ when level=3 then ‘C’ else ‘D’ end as “等级” coalesce(value1, value2[, …])—返回第一个非NULL的值 Coalesce(vehicle_finance_loan_order_is_micro_loan, 0) 1.6 正则 store_main_store_name not like ‘%测试%’ and store_main_store_name not like ‘%test%’ 等价于 not regexp_like(store_main_store_name, ‘(测试|test)’)—“|”这个符号一定是英文状态下的 regexp_like(location_name, ‘(徐州|宿迁|盐城|苏州|南京|淮安|常州|无锡|南通|扬州|泰州|镇江|连云港)’) regexp_like(字段名, ‘(str1|str2|…)’)—全模糊匹配 regexp_like(字段名, ‘^(str1|str2|…)’)—右模糊匹配 regexp_like(字段名, ‘(str1|str2|…)$’)—左模糊匹配 regexp_extract_all(string, pattern) → array<varchar> regexp_extract_all(‘1a 2b 14m’, ‘\d+’); — [1, 2, 14] regexp_extract(string, pattern) → varchar regexp_extract(‘1a 2b 14m’, ‘\d+’); — 1 1.7 转换 cast(value AS type) → type cast(‘2017-01-01’ as timestamp)—2017-01-01 00:00:00.0 cast(from_unixtime(appoint_actual_time) as timestamp)—2018-04-13 15:55:55.0 cast(1.22222 as decimal(10,2))—1.22 cast(substr(audit_at,1,10) as date)—2018-04-13 cast(business_key as int)—32971656 cast(starttime as varchar) try_cast(value AS type) → type 将value转换为type指定的类型，如果转换错误，则返回NULL。 typeof(expr) → varchar 返回表达式expr数据类型的名称。 typeof(123); — integer 1.8 聚合统计 group by user_id having count()→ bigint sum() max() min() mean() —sql均值不是mean，也不是average，注意！！！python中是np.mean() avg() —均值 _ distinct id variance stddev(x) → double variance(x) → double 1.9 排序 order by score asc/desc dense_rank() → bigint percent_rank() → bigint rank() → bigint row_number row_number() over(partition by user_id order by score asc) sum(totalprice) over (partition by clerk order by orderdate) as rolling_sum

6，创建时间的下一个整点
datetrunc(‘hour’, date_add(‘hour’, 1, cast(created_at as timestamp)))—created_at为string
7,记录个问题，from_unixtime(create_time/1000)—单位为秒，如何取到毫秒？
create_time from_unixtime(create_time/1000)
1602381660211 2020-10-11 10:01:00.0
1602381660381 2020-10-11 10:01:00.0
找到了解决办法！！！format_datetime
select create_time as bigint ,from_unixtime(create_time/1000) as “秒”—ms与s进制是1000 _,format_datetime(from_unixtime(action_time/1000),’yyyy-MM-dd HH:mm:ss.mmm’) as “毫秒”

createtime 秒 毫秒
1602381660211 2020-10-11 10:01:00.0 2020-10-11 10:01:00.001
1602381660381 2020-10-11 10:01:00.0 2020-10-11 10:01:00.001
好像没起作用。。。
8，presto sql 如何将sql脚本作为where查询条件
select
from table
where query = ‘select aa from tt where name =’北京’ limit 1’
答：当作普通字符串处理就好，单引号记得用 ‘’转义一下,举例如下
select ** from table where query = ‘select aa from tt where name = ‘’北京’’ limit 1’
9，分组排序，选TOP
需求：如何在系统日志中找到每个员工访问量前三的IP，限定每个IP下访问量1K及以上
select user_id ,ip ,num from ( select user_id ,ip ,count(*) as num ,row_number () over(partition by user_id order by count(*) desc) rank from table table where dt = ‘2020-11-30’ group by user_id,ip having count(*) >= 1000 ) tt where rank <=3 order by user_id,num desc
此时rank 是对每个员工下不同IP分组计数降序排列，再筛选前三即可

这里注意，窗口函数分组不可以写为
row_number ()over(partitionby user_id，ip** _orderbycount(*)desc) rank
因为它的结果是对每个员工每个IP统计排序，rank 只有一种取值，等于1。
10,between..and ..注解
看网上提不同数据库对between..and ..的操作方式存在不同，便自己去试了下
select distinct dt from table
where dt between ‘2020-11-01’ and ‘2020-11-03’
结果如下：
dt
2020-11-02
2020-11-01
2020-11-03
说明presto sql对于between.and ..是按照闭区间方式处理的([2020-11-01,2020-11-03])
mysql也是包含边界的
另between还有取反的操作
where dt not between 1 and 3
等价于dt<1或dt>3**

11, 行列转换，一般应用于聚合数据块的转换

列数据块

行数据块

列转行
SELECT city ,kv[‘2021-02’] as “2021-02” ,kv[‘2021-03’] as “2021-03” ,kv[‘2021-04’] as “2021-04” FROM ( SELECT city, map_agg(time, num) kv FROM t GROUP BY city ) tt
其中：行列转换关键点在于建立一个（key,value）数组，或把数组的key抽出来当列名
SELECT city, map_agg(time, num) kv FROM t GROUP BY city

行转列
SELECT t1.city ,t2.time ,t2.num FROM data t1 CROSS JOIN unnest ( array[‘2021-02’, ‘2021-03’, ‘2021-04’], array[“2021-02”, “2021-03”, “2021-04”] ) t2 (time, num)
其中：拉平数组UNNEST

文档说明：可替代hive中LATERAL VIEW explode

https://zhuanlan.zhihu.com/p/84116504

presto常用sql函数

字符串函数
concat(string1,…,stringN)连接给定的字符串
length(string) 返回给定字符串的长度
lower(string) 返回字符串的小写格式
upper(string) 返回给定字符串的大写格式
lpad(string,size,padstring) 给定字符串的左填充
rpad(string,size,padstring) 给定字符串的右填充
ltrim(string) 从字符串中删除字符左侧空格
rtrim(string) 从字符串中删除字符右侧空格
trim(string) 从字符串中删除字符两侧空格
replace(string, search, replace) 替换字符串值
reverse(string) 反转对字符串执行的操作
split(string, delimiter) 拆分分隔符上的字符串,并返回最大限制的大小数组
split_part(string, delimiter, index) 拆分分隔符上的字符串并返回字段索引
strpos(string,substring) 返回字符串中子字符串的起始位置
substr/substring(string,start) 返回给定字符串的子字符串
substr/substring(string,start,length) 返回具有特定长度的给定字符串的子字符串
index(string,substring) 使用给定的子字符串返回字符串的索引
数学函数
abs(x) 返回x的绝对值
ceil/ceiling(x) 向上取整
floor(x) 向下取整
mod(n,m) 返回n除以m的余数
pow/power(x,p) x的p次方
random() 返回伪随机值
rand(n) 随机数
round(x) 返回x的舍入值
round(x,d) ‘d’小数位四舍五入的x值
sqrt(x) 返回x的平方根
日期和时间函数
current_date 返回当前日期
current_time 返回当前时间
current_timestamp 返回当前时间戳
current_timezone() 返回当前时区
now() 返回当前日期,时区与时区
localtime 返回当地时间
localtimestamp 返回本地时间戳
date_add日期加减
date_diff两个日期的差
其他时间转换操作见这里

正则表达式函数
regexp_extract_all(string,pattern) 返回与模式的正则表达式匹配的字符串
regexp_extract_all(string,pattern,group) 返回与模式和组的正则表达式匹配的字符串
regexp_extract(string,pattern) 返回与模式的正则表达式匹配的第一个子串
regexp_extract(string,pattern,group) 返回与模式和组的正则表达式匹配的第一个子字符串
regexp_like(string,pattern) 返回模式的字符串匹配。如果返回字符串,则该值将为true,否则为false
regexp_replace(string,pattern) 将与表达式匹配的字符串的实例替换为模式
regexp_replace(string,pattern,replacement) 将表达式匹配的字符串的实例替换为模式和替换
regexp_split(string,pattern) 拆分给定模式的正则表达式
JSON函数
json_array_contains(json,value) 检查json数组中存在的值。如果值存在,它将返回true,否则返回false
json_array_get(json_array,index) 获取json数组中索引的元素
json_array_length(JSON) 返回json数组中的长度
json_format(JSON) 返回json结构格式
json_parse(string) 将字符串解析成json
json_size(json,json_path) 返回值的大小
聚合函数
avg() 平均值
min() 最小值
max() 最大值
sum() 求和
count() 行数
逻辑运算/位运算

————————————————
版权声明：本文为CSDN博主「别说话写代码」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_21997625/article/details/115668285