滨州做网站多少钱,万能视频下载神器,山西省住房建设厅网站,上海建设银行官方网站该笔记来源于网络#xff0c;仅用于搜索学习#xff0c;不保证所有内容正确。文章目录 一、presto基础操作二、时间函数0、当前日期/当前时间1、转时间戳1#xff09;字符串转时间戳 #xff08;推荐#xff09;2#xff09;按照format指定的格式#xff0c;将字符串str…该笔记来源于网络仅用于搜索学习不保证所有内容正确。文章目录 一、presto基础操作二、时间函数0、当前日期/当前时间1、转时间戳1字符串转时间戳 推荐2按照format指定的格式将字符串string解析成timestamp。3bigint 转时间戳 2、转年月日/取年月日1时间戳取年月日2字符串转年月日3bigint 转年月日 3、日期变换间隔、加减、截取、提取1求时间间隔 date_diff2求几天前几天后 interval、date_add3时间截取函数 date_trunc(unit, x)4时间提取函数 extract、year、month、day 4、转int 三、字符串函数四、二进制函数与字符串函数类似五、正则表达式六、聚合函数七、窗口函数八、数组、MAP、Json函数 一、presto基础操作
逻辑操作 AND OR NOT
比较操作 !
范围操作 between and; not between and; ……
空值判断 is null; is not null
最大最小值 greatest(1,2,3); least(1,2,3)
条件表达式case when thenif(condition, true_value, false_value)nullif(value1, value2)value1 value2返回null否则返回value1try(expression)表达式异常则返回null(防止分母为0数字超过范围无效cast等)
转换函数 cast(value as type); try_cast(value as type) : 转换失败返回nulltypeof(expr) 返回数据类型数学运算 - * / % abs() 绝对值ceil() 向上取整floor() 向下取整pow(x,p);power(x,p) x^prand();random() 返回[0,1间随机数round(): 同int()round(x,d):保留基本d位小数nan():not a numberis_nan(x): 判断x是否为nan注/与hive有差异presto 10/61hive 10/61.6666666666666667 presto 中可采用 cast(10 as double)/61.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 PRC1、转时间戳
1字符串转时间戳 推荐
即‘2019-04-26’ 转换成 2019-04-26 00:00:00.000
select cast(2019-04-26 as timestamp)
-- 2019-04-26 00:00:00.000select cast(2019-04-26 01:22:23 as timestamp)
-- 2019-04-26 01:22:23.0002按照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 shortselect date_parse(2019-04-06 00:03:55,%Y-%m-%d)
Invalid format: 2019-04-06 00:03:55 is malformed at 00:03:55select 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’)
3bigint 转时间戳
即int型 转换成 2017-05-10 06:18:50.000
from_unixtime(create_time)补充时间转bigint
select to_unixtime(current_date); 15563808002、转年月日/取年月日
推荐思路先转时间戳再格式化为年月日再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-282字符串转年月日
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:003bigint 转年月日
date(from_unixtime(1556380800))
select date_format(from_unixtime(1556380800),%Y-%m-%d)-- 2019-04-283、日期变换间隔、加减、截取、提取
1求时间间隔 date_diff
date_diff(unit, timestamp1, timestamp2) → biginteg: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-21select current_date,(current_date interval 7 day),date_add(day, 7, current_date)2019-04-28 | 2019-05-05 | 2019-05-053时间截取函数 date_trunc(unit, x)
截取月初
select date_trunc(month,current_date)
2019-04-01截取年初
select date_trunc(year,current_date)
2019-01-014时间提取函数 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 | 284、转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 dt2019-04-27 and d_module为你推荐-大屏 limit 10;
Query 20190428_034805_00112_ym89j failed: line 1:76: Column 为你推荐-大屏 cannot be resolvedpresto:adm select d_module from adm.f_app_video_vv where dt2019-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重复填充到左边直到长度等于sizestring长度超过size则截图string左侧的size个字符eg.select lpad(csdfasg,10,a) aaacsdfasgselect 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替换为replacereplace(string, search, replace)egselect 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)egselect split(325f243f325f43,f,2);[325, 243f325f43]select split(325f243f325f43,f,3);[325, 243, 325f43]拆分字符串-获取特定位置的拆分结果注index从1开始
split_part(string, delimiter, index)egselect 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(~)相同 】egselect 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-8to_utf8(string) → varbinary补充
二进制转intcrc32(binary) → bigint
二进制转stringfrom_utf8(binary) → varchareg
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) → biginteg: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 | 4200009045e5 b1 8f |(1 row)五、正则表达式
返回string中符合pattern的元素 regexp_extract_all、regexp_extract
返回string中所有符合pattern的元素
regexp_extract_all(string, pattern) - array(varchar)egSELECT regexp_extract_all(1a 2b 14m, \d); -- [1, 2, 14]返回string中第一个符合pattern的元素
regexp_extract(string, pattern) → varcharegSELECT regexp_extract(1a 2b 14m, \d); -- 1返回string中所有符合pattern组合的元素中指定pattern位的元素
regexp_extract_all(string, pattern, group) - array(varchar)
egSELECT regexp_extract_all(1a 2b 14m, (\d)([a-z]), 2); -- [a, b, m]返回string中第一个符合pattern组合的元素中指定pattern位的元素
regexp_extract(string, pattern, group) → varchar
egSELECT 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, \dn),regexp_like(1a 2b 14m, \dm),regexp_like(1a 2b 14m, \dn | \dm)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); -- newnew14mSELECT regexp_replace(1a 2b 14m, (\d)([ab]) , 3c$2 ); -- 3ca 3cb 14m注$2指第二个parttern位对应元素替换字符-将string中符合pattern的元素替换为function结果
regexp_replace(string, pattern, function) → varchar
egSELECT 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]注1hive中没有 max(x, n)、min(x, n)
注2max(x, n)、min(x, n) 与rank相比书写更简单但无法直接带出相关信息
eg
select max(m_vvpv,3) from app.c_app_videodiscover_uv where dt2019-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 dt2019-04-27;_col0
-------其他presto:adm select max_by(d_module_type,m_vvpv,3) from app.c_app_videodiscover_uv where dt2019-04-27;_col0
--------------------[其他, 搜索, 首页]-- 等同于hive中但没有取出m_vvpv
select d_module_type,m_vvpv
from app.c_app_videodiscover_uv
where dt2019-04-27
order by m_vvpv desc
limit 3d_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 dt2019-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))】
egpresto:adm select count_if(d_module为你推荐-大屏) from adm.f_app_video_vv where dt2019-04-27 ;_col0---------6666近似计数函数 approx_distinct
approx_distinct(x) → bigint
count(distinct x)的近似计算较count distinct速度快约有2.3%的误差。 egselect approx_distinct(d_diu) from adm.f_app_video_vv where dt2019-04-27 and d_module为你推荐-大屏;select count(distinct d_diu) from adm.f_app_video_vv where dt2019-04-27 and d_module为你推荐-大屏;分组计数函数 histogram
返回x值及其count组成的maphistogram(x) - map(K, bigint)eg
select histogram(client)
from app.c_app_videodiscover_uv
where dt2019-04-27----------------------------{其他3, IOS4, Android4}七、窗口函数
窗口函数和分组排序函数示例
row_number() over (partition by u_appname order by share_dnu desc) rank
排序窗口函数对比 row_number、rank、dense_rank 1. row_number:不管排名是否有相同的都按照顺序123…..n1. eg12345672. RANK() 生成数据项在分组中的排名排名相等会在名次中留下空位1. eg12335673. DENSE_RANK() 生成数据项在分组中的排名排名相等不会在名次中留下空位1. eg1233456**将每组分组排序个数限定在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 dt2019-04-27client | d_module_type | m_vvpv | rank
---------------------------------------Android | 其他 | 7777 | 1Android | 搜索 | 6666 | 1Android | 首页 | 5555 | 1Android | 相关推荐 | 4444 | 1IOS | 其他 | 3333 | 2IOS | 搜索 | 2222 | 2IOS | 相关推荐 | 1111 | 2IOS | 首页 | 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 dt2019-04-27client | d_module_type | m_vvpv | rank
-----------------------------------------------------Android | 其他 | 7777 | 0.0Android | 搜索 | 6666 | 0.3333333333333333Android | 首页 | 5555 | 0.6666666666666666Android | 相关推荐 | 4444 | 1.0其他 | 相关推荐 | 88 | 0.0其他 | 首页 | 1 | 0.5其他 | 其他 | NULL | 1.0IOS | 其他 | 3333| 0.0IOS | 搜索 | 2222 | 0.3333333333333333IOS | 相关推荐 | 1111 | 0.6666666666666666IOS | 首页 | 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) → arrayMap
map_keys(x(K, V)) - array(K)
map_values(x(K, V)) - array(V)
element_at(map(K, V), key) → V扩展取map中的key变成数组数组中查看包含cid返回truecontains(map_keys(event_args),cid) trueJson:
判断是否为jsonis_json_scalar(u_bigger_json)eg:select is_json_scalar(u_bigger_json)from edw.user_elogwhere dt2019-04-27limit 3-------falsefalsefalsestring转json-推荐json_parse(u_bigger_json)
eg:
select json_parse(u_bigger_json)
from edw.user_elog
where dt2019-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_sstring转json-不建议cast(u_bigger_json as json)
eg:
select cast(u_bigger_json as json) from edw.user_elog where dt2019-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 dt2019-04-27
limit 30
-- -------
-- 29
-- 21
-- 16
-- ~判断value是否在jsonjson格式的字符串中存在:
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 stringsum(cast(split(split(split(split(u_bigger_json,u_num)[2],,)[1],:)[2],)[2] as int)) flower_send_pv,-- function2: string to json, get valuesum(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_modflower
and u_acnew_send
group by dtdt | flower_send_pv | flower_send_pv_2 | flower_send_uv
--------------------------------------------------------------2019-04-27 | 8888 | 8888 | 5678
