该笔记来源于网络,仅用于搜索学习,不保证所有内容正确。
一、presto基础操作
逻辑操作 AND OR NOT
比较操作 > < >= <= = <> !=
范围操作 between and; not between and; ……
空值判断 is null; is not null
最大最小值 greatest(1,2,3); least(1,2,3)
条件表达式
case when then
if(condition, true_value, false_value)
nullif(value1, value2):value1 = value2返回null,否则返回value1
try(expression):表达式异常则返回null(防止分母为0,数字超过范围,无效cast等)
转换函数
cast(value as type);
try_cast(value as type) : 转换失败返回null
typeof(expr) :返回数据类型
数学运算 + - * / %
abs() 绝对值
ceil() 向上取整
floor() 向下取整
pow(x,p);power(x,p) x^p
rand();random() 返回[0,1)间随机数
round(): 同int()
round(x,d):保留基本d位小数
nan():not a number
is_nan(x): 判断x是否为nan
注:/与hive有差异!!!
presto 10/6=1
hive 10/6=1.6666666666666667
presto 中可采用: cast(10 as double)/6=1.6666666666666667
二、时间函数
0、当前日期/当前时间
presto:adm> select current_date,current_time,current_timestamp【=now()】
-> ;
_col0 | _col1 | _col2
------------+------------------+-----------------------------
2019-04-28 | 13:04:22.232 PRC | 2019-04-28 13:04:22.232 PRC
1、转时间戳
1)字符串转时间戳 (推荐)
即:‘2019-04-26’ 转换成 2019-04-26 00:00:00.000
select cast('2019-04-26' as timestamp)
-- 2019-04-26 00:00:00.000
select cast('2019-04-26 01:22:23' as timestamp)
-- 2019-04-26 01:22:23.000
2)按照format指定的格式,将字符串string解析成timestamp。
select date_parse('2019-04-06','%Y-%m-%d') 2019-04-06 00:00:00.000
select date_parse('2019-04-06 00:03:55','%Y-%m-%d %H:%i:%S') 2019-04-06 00:03:55.000
注:字符串格式和format格式需保持一致,以下为错误示例:
select date_parse('2019-04-06','%Y-%m-%d %H:%i:%S')
Invalid format: "2019-04-06" is too short
select date_parse('2019-04-06 00:03:55','%Y-%m-%d')
Invalid format: "2019-04-06 00:03:55" is malformed at " 00:03:55"
select date_parse('2019-04-06 00:03:55','%Y%m%d %H:%i:%S')
Invalid format: "2019-04-06 00:03:55" is malformed at "-04-06 00:03:55"
注:时间戳格式化 format_datetime(timestamp,‘yyyy-MM-dd HH:mm:ss’)
3)bigint 转时间戳
即:int型 转换成 2017-05-10 06:18:50.000
from_unixtime(create_time)
补充:时间转bigint:
select to_unixtime(current_date); 1556380800
2、转年月日/取年月日
推荐思路:先转时间戳,再格式化为年月日再date()为年月日。
1)时间戳取年月日
即:2017-09-18 13:40:31 转换成 2017-09-18
select date_format(current_date,'%Y-%m-%d')
select date(current_date)
select cast(current_date as date)
-- 2019-04-28
2)字符串转年月日
select date(cast('2019-04-28 10:28:00' as TIMESTAMP))
select date('2019-04-28')
select date_format(cast('2019-04-28 10:28:00' as TIMESTAMP),'%Y-%m-%d')
select to_date('2019-04-28','yyyy-mm-dd');
-- 2019-04-28
注:格式不同时date、to_date无法使用
select date('2019-04-28 10:28:00')
-- failed: Value cannot be cast to date: 2019-04-28 10:28:00
select to_date('2019-04-28 10:28:00','yyyy-mm-dd');
-- Invalid format: "2019-04-28 10:28:00" is malformed at " 10:28:00"
3)bigint 转年月日
date(from_unixtime(1556380800))
select date_format(from_unixtime(1556380800),'%Y-%m-%d')
-- 2019-04-28
3、日期变换:间隔、加减、截取、提取
1)求时间间隔 date_diff
date_diff(unit, timestamp1, timestamp2) → bigint
eg:select date_diff('day',cast('2019-04-24' as TIMESTAMP),cast('2019-04-26' as TIMESTAMP))
--2
注:与hive差异!!!
presto中 date_diff('day',date1,date2)【后-前】
hive,mysql中 datediff(date1,date2) 【前-后】
2)求几天前,几天后 interval、date_add
select current_date,(current_date - interval '7' day),date_add('day', -7, current_date)
2019-04-28 | 2019-04-21 | 2019-04-21
select current_date,(current_date + interval '7' day),date_add('day', 7, current_date)
2019-04-28 | 2019-05-05 | 2019-05-05
3)时间截取函数 date_trunc(unit, x)
截取月初
select date_trunc('month',current_date)
2019-04-01
截取年初
select date_trunc('year',current_date)
2019-01-01
4)时间提取函数 extract、year、month、day
extract(field FROM x) → bigint【注:field不带引号!】
year(x),month(x),day(x)
eg:
select
extract(year from current_date),
year(current_date),
extract(month from current_date),
month(current_date),
extract(day from current_date),
day(current_date);
-------+-------+-------+-------+-------+-------
2019 | 2019 | 4 | 4 | 28 | 28
4、转int
思路:先转timestamp,再to_unixtime转int
to_unixtime(timestamp_col)
三、字符串函数
presto中字符串只能使用单引号
注意:hive中字符串可以使用单引号或双引号,presto中字符串只能使用单引号。
eg:
presto:adm> select d_module from adm.f_app_video_vv where dt='2019-04-27' and d_module="为你推荐-大屏" limit 10;
Query 20190428_034805_00112_ym89j failed: line 1:76: Column '为你推荐-大屏' cannot be resolved
presto:adm> select d_module from adm.f_app_video_vv where dt='2019-04-27' and d_module='为你推荐-大屏' limit 10;
d_module
---------------
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
为你推荐-大屏
(10 rows)
基础字符串函数 concat length lower upper
拼接 concat(string1, ..., stringN) → varchar
取长度 length(string) → bigint
字母全部转换为小写 lower(string) → varchar
字母全部转换为大写 upper(string) → varchar
eg:
select lower('ABc'),upper('ABc')
abc,ABC
字符串填充 lpad rpad
字符串左填充 lpad(string, size, padstring)
string长度不足size则将padstring重复填充到左边直到长度等于size
string长度超过size则截图string左侧的size个字符
eg.
select lpad('csdfasg',10,'a') aaacsdfasg
select lpad('csdfasg',3,'a') csd
字符串右填充 rpad(string, size, padstring) → varchar
字符串清除空格 ltrim rtrim trim
清除字符串左侧空格 ltrim(string) → varchar
清除字符串右侧空格 rtrim(string) → varchar
清除字符串两侧空格 trim(string) → varchar
字符串替换字符 replace
替换字符-去掉string中的search: replace(string, search)
替换字符-将string中的search替换为replace:replace(string, search, replace)
eg:
select replace('23543','2'),replace('23543','2','8')
3543, 83543
字符串拆分 split
拆分字符串:
split(string, delimiter) -> array(varchar)
eg:select split('325f243f325f43','f');
[325, 243, 325, 43]
拆分字符串-拆分到第limit-1个分隔符为止:
split(string, delimiter, limit) -> array(varchar)
eg:
select split('325f243f325f43','f',2);
[325, 243f325f43]
select split('325f243f325f43','f',3);
[325, 243, 325f43]
拆分字符串-获取特定位置的拆分结果(注:index从1开始):
split_part(string, delimiter, index)
eg:select split_part('325f243f325f43','f', 4)
43
字符串定位 strpos position
定位函数-获取字符串中某个字符第一次出现的位置,从1开始:
strpos(string, substring) → bigint
position(substring IN string) → bigint
字符串截取 substr
截取函数-截取start右侧字符(含start):
substr(string, start) → varchar
【 substring(~)相同 】
eg:
select substr('325f243f325f43', 3),substr('325f243f325f43', -3)
5f243f325f43,f43
截取函数-从start开始向右侧截取length个字符(含start):
substr(string, start, length) → varchar
【 substring(~)相同 】
eg:
select substr('325f243f325f43', 3, 3),substr('325f243f325f43', -3,2)
5f2,f4
扩展:截取函数substr,定位函数strpos组合使用:
substr(remark,strpos(remark,'title'),strpos(remark,'status')-strpos(remark,'title')-3)
其他
string转UTF-8:to_utf8(string) → varbinary
补充:
二进制转int:crc32(binary) → bigint
二进制转string:from_utf8(binary) → varchar
eg:
select to_utf8('你好') ,crc32(to_utf8('你好')), from_utf8(to_utf8('你好'))
e4 bd a0 e5 a5 bd | 1352841281 | 你好
四、二进制函数(与字符串函数类似)
length、concat、substr、lpad、rpad等
md5(binary) → varbinary
crc32(binary) → bigint
eg:
presto:adm> select to_utf8('为你推荐-大屏'), crc32(to_utf8('为你推荐-大屏'));
_col0 | _col1
-------------------------------------------------+------------
e4 b8 ba e4 bd a0 e6 8e a8 e8 8d 90 2d e5 a4 a7 | 4200009045
e5 b1 8f |
(1 row)
五、正则表达式
返回string中符合pattern的元素: regexp_extract_all、regexp_extract
返回string中所有符合pattern的元素 :
regexp_extract_all(string, pattern) -> array(varchar)
eg:SELECT regexp_extract_all('1a 2b 14m', 'd+'); -- [1, 2, 14]
返回string中第一个符合pattern的元素 :
regexp_extract(string, pattern) → varchar
eg:SELECT regexp_extract('1a 2b 14m', 'd+'); -- 1
返回string中所有符合"pattern组合"的元素中指定pattern位的元素 :
regexp_extract_all(string, pattern, group) -> array(varchar)
eg:SELECT regexp_extract_all('1a 2b 14m', '(d+)([a-z]+)', 2); -- ['a', 'b', 'm']
返回string中第一个符合"pattern组合"的元素中指定pattern位的元素 :
regexp_extract(string, pattern, group) → varchar
eg:SELECT regexp_extract('1a 2b 14m', '(d+)([a-z]+)', 2); -- 'a'
判断string是否符合pattern: regexp_like
【可理解为多个like的组合,且比like组合高效】
regexp_like(string, pattern) → boolean
eg:
SELECT
regexp_like('1a 2b 14m', 'd+n'),
regexp_like('1a 2b 14m', 'd+m'),
regexp_like('1a 2b 14m', 'd+n | d+m')
false,true,true
替换string中符合pattern的元素: regexp_replace
替换字符-将 string 中符合 pattern 的元素替换为空 (移除元素) :
regexp_replace(string, pattern) → varchar
eg:SELECT regexp_replace('1a 2b 14m', 'd+[ab] '); -- '14m'
替换字符-将string中符合pattern的元素替换为replacement:
regexp_replace(string, pattern, replacement) → varchar
eg:
SELECT regexp_replace('1a 2b 14m', '(d+)([ab]) ', 'new'); -- newnew14m
SELECT regexp_replace('1a 2b 14m', '(d+)([ab]) ', '3c$2 '); -- '3ca 3cb 14m'
注:$2指第二个parttern位对应元素
替换字符-将string中符合pattern的元素替换为function结果 :
regexp_replace(string, pattern, function) → varchar
eg:SELECT regexp_replace('new york', '(w)(w*)', x -> upper(x[1]) || lower(x[2])); --'New York'
按pattern拆分string: regexp_split
拆分字符串-按pattern拆分 :
regexp_split(string, pattern) -> array(varchar)
eg:
presto:adm> SELECT regexp_split('1a 2b 14m', 's'),regexp_split('1a 2b 14m', '[a-z]+');
_col0 | _col1
---------------+----------------
[1a, 2b, 14m] | [1, 2, 14, ]
六、聚合函数
求和函数 sum
最大最小值函数 max min
最大值:max(x) → [same as input]
最大的n个值:max(x, n) → array<[same as x]>
最小值:min(x) → [same as input]
最小的n个值:min(x, n) → array<[same as x]>
注1:hive中没有 max(x, n)、min(x, n)
注2:max(x, n)、min(x, n) 与rank相比,书写更简单,但无法直接带出相关信息
eg:
select max(m_vvpv,3) from app.c_app_videodiscover_uv where dt='2019-04-27';
[3333, 2222, 1111]
最大最小值函数扩展 max_by min_by
取出最大y值对应的x值:max_by(x, y) → [same as x]
取出最大的n个y值对应的x值:max_by(x, y, n) → array<[same as x]>
取出最小y值对应的x值:min_by(x, y) → [same as x]
取出最小的n个y值对应的x值:min_by(x, y, n) → array<[same as x]>
eg:
presto:adm> select max_by(d_module_type,m_vvpv) from app.c_app_videodiscover_uv where dt='2019-04-27';
_col0
-------
其他
presto:adm> select max_by(d_module_type,m_vvpv,3) from app.c_app_videodiscover_uv where dt='2019-04-27';
_col0
--------------------
[其他, 搜索, 首页]
-- 等同于hive中(但没有取出m_vvpv)
select d_module_type,m_vvpv
from app.c_app_videodiscover_uv
where dt='2019-04-27'
order by m_vvpv desc
limit 3
d_module_type m_vvpv
1 其他 3333
2 搜索 2222
3 首页 1111
适用场景:video表取播放量最大的几个视频,user表取签到次数最多的几个用户等(不需聚合)
注:max_by无法实现如下聚合取top功能
-- hive 聚合
select d_module_type,sum(m_vvpv) m_vv
from app.c_app_videodiscover_uv
where dt='2019-04-27'
group by d_module_type
order by m_vv
limit 3
相关推荐 33333
2 首页 22222
3 搜索 11111
计数函数 count count_if
计数:count()
满足条件则计数:count_if()【hive中没有,同hive中 sum(if(condition,1,0))】
eg:
presto:adm> select count_if(d_module='为你推荐-大屏') from adm.f_app_video_vv where dt='2019-04-27' ;
_col0
---------
6666
近似计数函数 approx_distinct
approx_distinct(x) → bigint
count(distinct x)的近似计算,较count distinct速度快,约有2.3%的误差。
eg:
select approx_distinct(d_diu)
from adm.f_app_video_vv
where dt='2019-04-27'
and d_module='为你推荐-大屏';
select count(distinct d_diu)
from adm.f_app_video_vv
where dt='2019-04-27'
and d_module='为你推荐-大屏';
分组计数函数 histogram
返回x值及其count组成的map:histogram(x) -> map(K, bigint)
eg:
select histogram(client)
from app.c_app_videodiscover_uv
where dt='2019-04-27'
----------------------------
{其他=3, IOS=4, Android=4}
七、窗口函数
窗口函数和分组排序函数示例:
row_number() over (partition by u_appname order by share_dnu desc) rank
排序窗口函数对比 row_number、rank、dense_rank
1. row_number:不管排名是否有相同的,都按照顺序1,2,3…..n
1. eg:1234567
2. RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
1. eg:1233567
3. DENSE_RANK() 生成数据项在分组中的排名,排名相等不会在名次中留下空位
1. eg:1233456
**将每组分组排序个数限定在n以内[含n]:ntile(n) → bigint **
eg:
select
client,
d_module_type,
m_vvpv,
ntile(3) over (order by m_vvpv desc) rank
from app.c_app_videodiscover_uv
where dt='2019-04-27'
client | d_module_type | m_vvpv | rank
---------+---------------+---------+------
Android | 其他 | 7777 | 1
Android | 搜索 | 6666 | 1
Android | 首页 | 5555 | 1
Android | 相关推荐 | 4444 | 1
IOS | 其他 | 3333 | 2
IOS | 搜索 | 2222 | 2
IOS | 相关推荐 | 1111 | 2
IOS | 首页 | 999 | 2
其他 | 相关推荐 | 88 | 3
其他 | 首页 | 1 | 3
其他 | 其他 | NULL | 3
(11 rows)
返回排名/最大排名:percent_rank() → double
eg:
select
client,
d_module_type,
m_vvpv,
percent_rank() over (partition by client order by m_vvpv desc) rank
from app.c_app_videodiscover_uv
where dt='2019-04-27'
client | d_module_type | m_vvpv | rank
---------+---------------+---------+--------------------
Android | 其他 | 7777 | 0.0
Android | 搜索 | 6666 | 0.3333333333333333
Android | 首页 | 5555 | 0.6666666666666666
Android | 相关推荐 | 4444 | 1.0
其他 | 相关推荐 | 88 | 0.0
其他 | 首页 | 1 | 0.5
其他 | 其他 | NULL | 1.0
IOS | 其他 | 3333| 0.0
IOS | 搜索 | 2222 | 0.3333333333333333
IOS | 相关推荐 | 1111 | 0.6666666666666666
IOS | 首页 | 999 | 1.0
(11 rows)
八、数组、MAP、Json函数
数组:
SELECT ARRAY [1,2] -- [1, 2]
array_distinct(x) → array
array_max(x) → x
array_min(x) → x
array_sort(x) → array
Map:
map_keys(x(K, V)) -> array(K)
map_values(x(K, V)) -> array(V)
element_at(map(K, V), key) → V
扩展:取map中的key变成数组,数组中查看包含'cid'返回true:
contains(map_keys(event_args),'cid') = true
Json:
判断是否为json:is_json_scalar(u_bigger_json)
eg:
select is_json_scalar(u_bigger_json)
from edw.user_elog
where dt='2019-04-27'
limit 3
-------
false
false
false
string转json-推荐:json_parse(u_bigger_json)
eg:
select json_parse(u_bigger_json)
from edw.user_elog
where dt='2019-04-27'
limit 3
-- {"u_rank":",0,1,2","u_recsid":",100002,100002,100002","u_rmodelid":",17,17,17",
-- {"u_abtag":"35","u_device_s":"HWMYA-L6737","u_frank":"8","u_package":"com.bokec
-- {"u_abtag":"97","u_all_startid":"1556315003775","u_buglyupdate":"1","u_device_s
string转json-不建议:cast(u_bigger_json as json)
eg:
select cast(u_bigger_json as json) from edw.user_elog where dt='2019-04-27' limit 10;
-- "{"u_vpara":"0","u__":"1556317886230","u_callback":"jQuery17206597692994207338
_1556317875402"}"
获取json中某key的值:
select json_extract_scalar(json_parse(u_bigger_json),'$.u_abtag')
from edw.user_elog
where dt='2019-04-27'
limit 30
-- -------
-- 29
-- 21
-- 16
-- ~
判断value是否在json(json格式的字符串)中存在:
json_array_contains(json, value) → boolean
SELECT json_array_contains('[1, 2, 3]', 2)
判断json中是否含有某key
法1:失败
select json_array_contains('[1, 2, u_p_source, 3]', 'u_p_source')
法2:结合split和cardinality(获取array长度)
SELECT
split('[1, 2, u_p_source, 3]', 'u_p_source'),
split('[1, 2, 3]', 'u_p_source'),
cardinality(split('[1, 2, u_p_source, 3]', 'u_p_source')),
cardinality(split('[1, 2, 3]', 'u_p_source'))
["[1, 2, ",", 3]"]
["[1, 2, 3]"]
2
1
即:where cardinality(split(u_bigger_json,{{ para }}))>1
扩展:string格式的json中取某key的value
select
dt,
-- function1: split string
sum(cast(
split(split(split(split(u_bigger_json,'u_num')[2],',')[1],':')[2],'"')[2]
as int)) flower_send_pv,
-- function2: string to json, get value
sum(cast(
json_extract_scalar(json_parse(u_bigger_json),'$.u_num')
as int)) flower_send_pv_2,
count(distinct u_diu) flower_send_uv
from edw.user_ilog
where dt= cast(current_date - interval '1' day as varchar)
and u_mod='flower'
and u_ac='new_send'
group by
dt
dt | flower_send_pv | flower_send_pv_2 | flower_send_uv
------------+----------------+------------------+----------------
2019-04-27 | 8888 | 8888 | 5678
原文地址:https://blog.csdn.net/weixin_43676735/article/details/135534451
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_55470.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!