查询引擎:Presto 常用sql语句

作者:admin 发布时间:2024-03-27 19:03:45 浏览:883次

一、聚合函数

【count】计数
-- distinct 去重函数 :统计个数,不会统计为null的数据
select count(distinct member_id) as member_num from table
【sum】求和
select sum(amount) as all_amount from table
【avg】平均值
select avg(amount) as avg_amount from table
【max】最大值
select max(amount) as max_amount from table
【min】最小值
select min(amount) as min_amount from table
【percentile_approx】 百分位数 >> 报错
select percentile_approx(amount,0.75) as up_quartile from table
【array_agg】聚合为数组
select
member_id,
-- array_distinct对数组进行去重操作
array_distinct(array_agg(class)) as class
from
table
group by
member_id

二、时间函数

文本时间格式转时间戳
select to_unixtime(cast('2018-09-05' as timestamp))
select to_unixtime(TIMESTAMP '2023-07-09 12:59:01')
1.
文本时间格式转日期格式
select CAST('2023-07-09'AS DATE)
2.
时间戳转化文本时间格式
select format_datetime(from_unixtime(1234567890),'yyyy-MM-dd')
select from_unixtime(1688878741) -- 时间戳转日期
3.
时间加减
select cast(current_date - interval '1' day as varchar) -- 日期/月份/年 减函数
select date_add('DAY', 4, current_date) -- 日期/月份/年 加函数
select date_diff('day', cast('2023-02-01' as timestamp),cast('2023-01-01' as timestamp)) -- 日期差函数
select date_trunc('quarter',current_date) -- 返回当周/当月/当季度/当年的第一天
4.
当前日期(日期)
Select now() -- 当前时间(年月日时分秒)
Select current_date -- 当前日期
Select current_time -- 当前时间
5.

三、窗口函数

聚合函数作为窗口函数
SELECT SUM(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT COUNT(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT AVG(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT MIN(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT MAX(字段名称) over(partition by 字段1 ORDER BY 字段2)
1.排序窗口函数
Row_number() over(partition BY 字段1 ORDER BY 字段2) --返回的排序可以无重复数字,比如1,2,3
rank() over(partition BY 字股1 ORDER BY 字段2) --返回的排序可以有重复数字,有排序间隔比如1,1,3
dense_rank() over (partition BY 字段1 ORDER BY 字段2) --返回的排序可以有重复数字,但没有排序间隔 比如1,1,2
2.窗口函数(取第一行或最后一行的数据)
--返回字段a的第一行数据,加上order by的话,相当于min,max对应的窗口函数
SELECT first_value(字段a)over (partition BY 字段1 ORDER BY 字段2)
--返回字段a的最后一行数据,加上order by的话,相当于min,max对应的窗口函数
SELECT last_value(字段a)over(partition By 字段1 ORDER BY 字段2)
4. 分布窗口函数(适用于presto,计算某个值在一组有序的数据中累计分布百分比)
SELECT cume_dist() over(partition by 字段1 ORDER BY 字段2)-- 当前计算值所在位置/总行数,如果存在重复值,计算的时候,取重复值的最后
一行的位置
SELECT percent_rank(partition by 字段1 ORDER BY 字段2) -- 返回值=(rank-1)/(R0WS-1) 如果存在重复值,计算的时候,取重复值的最后一
行的位置
5. 前后窗口函数(数据偏移函数)
Select lag(字段a, 偏移行数 , 无偏移的取值) over(partition By 字段1 ORDER by 字段2) --返回字段a的前x行数据,可用于计算同一列数据前
后两行的差值
Select lead(字段a, 偏移行数, 无偏移的取值)over(partiton Bx 字段1 ORDER by 字段2)--返回字段a的后m行数据,可用于计算同一列数据前后两
行的差值

四、数学函数

SELECT RAND() --生成0-1的随机数字
SELECT ABS(-3.1415926) --绝对值函数
SELECT FLOOR(3.9415926) --向下取整
SELECT CEIL/CEILING(3.3415926) --向上取整
SELECT ROUND(3.94159263) --四舍五入函数
SELECT MOD(9,2) --取余所数

五、字符函数

1. 字符串查找函数
SELECT COALESCE (NULL,'abc') --查找返回非空的字段
SELECT strpos('acdefg','fg') --presto字符串查找函
SELECT INDEX('acdefg','fg') --presto字符串查找函数
2. 字符串替换函数
SELECT REPLACE('我是chinese','chinese',中国人') -- 字符串替换函数
SELECT regexp_replace('abcde][d]','(\])|(\[)','') -- 字符串替换函数(转义字符是'\')
3. 字符串截取函数
SELECT SUBSTRING('上海市浦东新区',1,10) -- 字符串截取函数
SELECT substr('上海市浦东新区',1,10) -- 字符串截取函数
4. 字符串长度计算函数
SELECT LENGTH('abc') -- 字符串长度计算函数(字节数)
5. 字符串连接函数
SELECT CONCAT('2023', "-" , "09" , "-","16") --字符串连接函数
6. 字符串分割函数
SELECT split('a,b,c,d',’,’),split('192.168.0.1',’\\.') --presto/hive 字符串分割为 arry函数(doris暂时没用)
结果:["a","b","c","d"],["192","168","0","1"]
7. 其他字符串函数
SELECT LOWER('ABC") --字符串的小写格式
SELECT UPPER('abc') --字符串的大写格式
SELECT REVERSE('abcdefg') --宁符串反转函数
SELECT REPEAT('a',4) --返回重复n次的字符串

如需转载请保留本文出处: https://zhe94.com/972.html

上一篇:mysql 复制表和表数据的方法
MySQL-窗口函数有哪些

MySQL-窗口函数有哪些

定义 窗口可以理解为记录集合,窗口函数就是在满足某种条件的记录集合上执行的特殊函数。 即:应用在窗口内的函数。
MySQL查询连续n天登录的用户的方法

MySQL查询连续n天登录的用户的方法

这篇文章介绍了SQL查询至少连续n天登录用户的方法,文中通过示例代码介绍的非常详细。对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
MySql中group_concat字符长度限制1024怎么办

MySql中group_concat字符长度限制1024怎么办

group_concat长度限制默认是1024,如果在实际使用过程中拼接好的字符串长度超过了最大长度,那么最后仅保留前1024个字符,解决group_concat长度限制有两个解决方法
mysql 怎么重启,Windows系统mysql8.0重启的方法详解

mysql 怎么重启,Windows系统mysql8.0重启的方法详解

Windows要重新启动 MySQL,你可以按照以下步骤进行操作,命令提示符界面中,输入以下命令来关闭 MySQL 服务
MySQL8.0的WITH查询实现递归遍历省市区

MySQL8.0的WITH查询实现递归遍历省市区

WITH 子句是 MySQL 中的一种 SQL 结构,又称为 Common Table Expression (CTE)。它在不影响原有 SQL 语句的情况下,允许开发人员临时创建一个内存中的结果集,然后对其进行操作。